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").R ange("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 ?

Declare ABC as Double instead of Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"G.P.N.L. c.v.a." wrote in message
...
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").R ange("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 ?

Sorry, Niek, doesn't make any difference
Gilbert

"Niek Otten" wrote in message
...
Declare ABC as Double instead of Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"G.P.N.L. c.v.a." wrote in message
...
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").R ange("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 ?

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." wrote:

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").R ange("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 ?

--

Dave Peterson

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" wrote in message
...
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." wrote:

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").R ange("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 ?

--

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." wrote:

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" wrote in message
...
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." wrote:

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").R ange("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 ?

--

Dave Peterson

--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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." wrote:

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" wrote in message
...
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." wrote:

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").R ange("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 ?

--

Dave Peterson

--

Dave Peterson

Dates are misterious beasts!

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

"G.P.N.L. c.v.a." wrote:

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

"Dave Peterson" wrote in message
...
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." wrote:

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" wrote in message
...
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." wrote:

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").R ange("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 ?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson