VLOOKUP in VBA

[G.P.N.L. c.v.a.]

I am a beginner in VBA; nevertheless, I already have several working UDF's
in Excel 2003, though.

Why does TGI(A213) gives me "#VALUE!"
- while A213 contains "30/11/2013",
- the formula - in the right module - is :
Option Explicit

Function TGI(ABC As Date) As Single

TGI = Application.WorksheetFunction.VLookup(ABC, _
Range(Workbooks("INDEX.XLS").Worksheets("INDEX").Range("DBINDEX")),
_
2, _
True)

End Function
- the range DBINDEX in the sheet INDEX in the workbook INDEX.XLS is A2:G470,
and
- in that range cell A373 contains "30/11/2013" ?

Can somebody help me ?

 

[Niek Otten]

Declare ABC as Double instead of Date

 

[G.P.N.L. c.v.a.]

Sorry, Niek, doesn't make any difference
Gilbert

Declare ABC as Double instead of Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Dave Peterson]

How about:

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range("DBINDEX")

res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If

End Function

There's a difference between how application.vlookup() and
application.worksheetfunction.vlookup() works.

The first returns an error that can be checked -- that's why I like it.

The second causes a runtime error that has to be avoided with "on error" code.

======
Ps. This assumes that that other workbook is open, has a sheet named Index and
a valid range named DBINDEX.

You may want to add some checks.

Option Explicit
Function TGI(ABC As Date) As Variant

Dim res As Variant
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Workbooks("INDEX.XLS").Worksheets("INDEX").Range("DBINDEX")
On Error GoTo 0

If myRng Is Nothing Then
TGI = "Design error!"
Else
res = Application.VLookup(ABC, myRng, 2, True)

If IsError(res) Then
TGI = "Not Found"
Else
TGI = res
End If
End If
End Function

=========
And the big problem (that I don't like) is that if a cell in that table is
changed, excel doesn't know to recalculate your function.

You could add
application.volatile
at the top of your function

But even that will mean that your results could be waiting for excel's next
calculation before your function updates.

I guess I'm saying: Don't trust your results until you force a recalc!

 

[G.P.N.L. c.v.a.]

Thanks, Dave...but, indeed, it says "Not found", but that does of course not
help me.
What can cause this error ?
BTW, just to test, I changed "...(ABC As Date)" into "...(ABC As Variant)",
and then the result is "0" (?). Does that help ?
Sorry for the trouble,
Regards,
Gilbert

 

[Dave Peterson]

How about:

res = Application.VLookup(cLng(ABC), myRng, 2, True)

You can still pass the ABC variable as a date.

 

[G.P.N.L. c.v.a.]

Works !
Thank you, David !
(But why ?)

How about:

res = Application.VLookup(cLng(ABC), myRng, 2, True)

You can still pass the ABC variable as a date.

 

[Dave Peterson]

Dates are misterious beasts!

I don't know why VBA has as much trouble as it does with them.

Works !
Thank you, David !
(But why ?)