ByRef Argument type mismatch?

[Gasman]

Hi everyone,

Why do I get a Byref argument Type mismatch with this from the Immediate window

st=#3/13/2017#
en=#4/12/2017#
? vartype(st)
 7 
? vartype(en)
 7 

? calcbonus(19,st,en,1) ' error when I try this?

yet

Sub testCalcBonus()
Dim curBonus As Currency
TempVars("BonusAmt") = 0
TempVars("BonusCount") = 0
'TempVars("Bonus") = CalcBonus(19, #3/27/2017#, #3/27/2017#, 2)
TempVars("BonusAmt") = CalcBonus(19, #3/13/2017#, #4/12/2017#, 1)

MsgBox "Bonus " & TempVars("BonusAmt") & " for count of " & TempVars("BonusCount")

End Sub

runs and produces the correct result?

First line of function is

Public Function CalcBonus(plngEmployeeID As Long, pdtStartDate As Date, pdtEndDate As Date, piBonusPeriod As Integer)

 

[Frothingslosh]

I'm off to a meeting and can't reply in detail, but my first guess is that your PC is set to UK date format, and 4/12 can be either of two dates.

 

[Gasman]

Hi Frothingslosh,
Yes I am in the UK, and started with UK format and then changed it to US format, as UK did not work.
Amended end date to #04/15/2017# to avoid any misunderstanding, but still happens.

 

[static]

If the parameter datatype is explicitly declared you need to pass a variable of the same type

st=#3/13/2017#
en=#4/12/2017#
? vartype(st)
7
? vartype(en)
7

? CalcBonus(19, CDate(st), CDate(en), 1)


edit

Although st and en are date values the variables themselves are variant.
You can pass a date to a variant but can't pass a variant to a date.

 

[Gasman]

Thank you static.

I *thought* was doing that with the # delimiters and that was being confirmed with the vartype check?
Your solution gets the required result.
Now I just have to remember where I have to use this CDate()

I even tried it with UK format and that also works? :banghead:
Thank you.

 

[static]

Sorry just edited my post while you were replying.

 

[MarkK]

Now I just have to remember where I have to use this CDate()

No, you don't have to remember, the compiler will warn you.

 

[Gasman]

No, you don't have to remember, the compiler will warn you.

That is just the problem, I was just trying to test out the function from the immediate window.
Probably best to stick with creating a sub to test. then?. The compiler can then do it's work.

 

[static]

You don't need to create variables to test a procedure though. Just pass the values.

? CalcBonus(19, #3/13/2017#, #4/12/2017#, 1)

#3/13/2017# can only be a date. But here..

st = #3/13/2017#

st holds a date value, but it's datatype isn't set so it could just as easily hold an array of strings.