CODEWITHSUNDEEP
excelvbauser-defined-functionsvlookup
VolStudent
VolStudent

Reputation: 29

VLookup in UDF in Sub vs Sheet

I have simple UDF supposed to change the value of a VLookUp Function in case no value is found.

Function get_value(what,where) 'what ->  e.g. 01/31/2020,02/28/2020
                               'where -> Sheet2!A:B
jump:
If IsError(Application.VLookup(what,where,2,False)) Then
  what = what - 1
  GoTo jump
   
  Else
  get_value = Application.VLookup(what,where,2,False)

  EndIf

End Function

When calling the function in a sub everything works as intended!

However, calling the function in a Worksheet (i.e =get_value(A2,Sheet2!A;B), whenever there is no value for the date of A2 and the what is supposed to be changed in the loop (to look up the value of the new date i.e. 02/28/2020 to 02/27/2020) the function returns #VALUE! instead of the actual value for 02/27/2020

I have tried using a Do WhileLoop instead of GoTo but this does not solve it.

I come from Python and this circuity in VBA leaves me baffled...

Upvotes: 0

Views: 98

Answers (1)

BigBen
BigBen

Reputation: 50067

Using a Do loop (and specifying a maximum number of iterations in case what is never found):

Function get_value( _
    ByVal what As Date, _
    ByVal where As Range _
) As Variant
                  
    Const maxIterations As Long = 100 ' change as needed

    Do
        get_value = Application.VLookup(CLng(what), where, 2, False)
        what = what - 1
        
        Dim counter As Long
        counter = counter + 1
    Loop While IsError(get_value) And counter < maxIterations

End Function

Using CLng on what is what seems to be the trick here. In testing, your version was an infinite loop; what was never found. VLookup and dates can be finicky. However, GoTo is generally to be avoided, as it's a slippery slope towards spaghetti code.

Upvotes: 2

Related Questions