I would disagree with the categorization of my solution as a
workaround. I suspect that the belief that an explicit datatype
coercion is a workaround stems from the all the implicit type
conversions that both XL and VBA engage in -- so much so that when the
type conversion doesn't happen automatically, people get stuck.

Typically, one should recognize the fact that different functions work
with different datatypes. In those cases where the developer knows how
to coerce one data type into something else that is acceptable to a
function, it should be the responsibility of the developer to do so.

IMO, the software has no business doing implicit conversions.

In the OP's problem, we had a variable of the Date datatype, and a
function that required a double (or a string) for its argument. Since
we know that XL's dates are stored as numbers (i.e., XL has no date
datatype), it was safe to coerce the date into a double.

One of the benefits of VB.Net is the ability to use a 'Option Strict'
clause. This will require the developer to explicitly request every
datatype-to-datatype conversion; none will be automatic.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Bill:

I agree. It doesn't make complete sense to me either. Look at the
thread a little further down regarding: Can't Find Value.
It seems like the same problem involving passing values between VB and
XL using variables. Something in the object model seems inconsistent to
me, but it is more likely that I don't understand it as much as I would
like to.


Tushar's solution seems to be a good workaround to convert the variable
to a value by applying the type conversion function to the variable.

Bill Buckner wrote:
JWolf,

That makes sense except in the same worksheet function call I use another variable "insurance" which is defined in the following code. So if your response is the reason I keep getting "type" errors, why does it work for some and not for others? Could the fact that one is a date and one is an integer have anything to do with my problem? Maybe the fact that I do some calculations on the variable "insurance"
before it is sent to the worksheet function causes some type conversions to take place???

Dim insurance As Integer
Dim retireDate As Date
insurance = Range("Insurance_Selection")
retireDate = CDate(Range("Retire_Date_Primary").Value)

Application.VLookup(Range("Retire_Date_Primary"), Range("Pension_Table"), 21 + insurance - 1) '<= This works
Application.VLookup(retireDate , Range("Pension_Table"), 21 + insurance - 1) '<= This fails