Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

New VLOOKUPWORKBOOK

0

I've used the below VBA for creating the New VLOOKPUP workbook function to search & collet data to a master worksheet from every single worksheets within the workbook, it works fine for the first entry but the issue i am facing that the reurn values colleted never updated when change or update the values  witthin the workbook unless re-type the lookup value again or at least delete one letter and re-type it again and hit enter to force the VLOOKUP function to work and update the return value with the new values. i am looking for any suggestions to solve this issue. thanks

VBA used

Function VLOOKUPWORKBOOK( _

    lookup_value As Variant, _

    table_array As Range, _

    col_index_num As Integer, _

    Optional range_lookup As Boolean, _

    Optional sheets_to_exclude_1 As String, _

    Optional sheets_to_exclude_2 As String, _

    Optional sheets_to_exclude_3 As String, _

    Optional sheets_to_exclude_4 As String, _

    Optional sheets_to_exclude_5 As String _

)

 Dim mySheet As Worksheet

Dim value_to_return

Dim sheets_to_exclude

 On Error Resume Next

 'Put sheets to exclude into an array

sheets_to_exclude = Array(sheets_to_exclude_1, sheets_to_exclude_2, sheets_to_exclude_3, sheets_to_exclude_4, sheets_to_exclude_5)

 'Cycle through all sheets in the workbook

For Each mySheet In ActiveWorkbook.Worksheets

     'Check if the sheet needs to be excluded or not.

    If Not (UBound(Filter(sheets_to_exclude, mySheet.Name)) > -1) Then

         With mySheet

             Set table_array = .Range(table_array.Address)

             'Run the actual vlookup function on the current sheet that is being looped though.

            value_to_return = WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, range_lookup)

         End With

         'Exit the loop once have a value to return.

        If Not IsEmpty(value_to_return) Then

            Exit For

        End If

     End If

 Next mySheet

 'Send the result back to the cell that contains the function.

VLOOKUPWORKBOOK = value_to_return

End Function

Answer
Discuss

Answers

0
Selected Answer

Hi Samir and welcome to the Forum.

Functions (UDFs) are assumed to be non-volatile by VBA but I found that you can make them update when their dependencies change (i.e. be volatile).

In the attached file, I made a simple function in Module1:

Function wbProduct(x As Long) As Long

Application.Volatile True

wbProduct = x * Sheet2.Cells(1, 1).Value * Sheet3.Cells(1, 1)

End Function

which I entered in cell A1 of Sheet1 as:

=wbProduct(6)

The cell then shows the value of (6 * Sheet2!A1 * Sheet3!A1) and will update if the 6 is changed or A1 in either sheet is changed. You can check this by adding another window and changing A1 cells as you view the sheet  containing the formula (e.g. with windows arranged vertically)

To use ths approach, you will need to add a new first line to your Function code, so:

Function VLOOKUPWORKBOOK(<< existing arguments>>)

Application.Volatile True

<< existing code>> 

Hope this fixes things for you. If so, please mark this Answer as Selected.

Discuss

Discussion

Problem solved
Much appreciated for your answer
SamirOmar (rep: 2) Sep 23, '22 at 11:30 am
Great! Thanks for selecting my answer, Samir. 
John_Ru (rep: 3992) Sep 23, '22 at 2:10 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login