Excel2003 on WinXP

I have a problem that I'm not sure how to solve; I've decided to post the
short version first (in case it is easy and someone can tell me how to fix
it). If that doesn't work I'll post the much longer description with more
code (I drafted the post, then was overwhelmed by the size of the potential
post and whether anyone would even read it all). Basically, I'm getting a
byref error when trying to call a function in a different module. It may be
that the function expects a date and my values are a numeric representation
of the date- that's my best guess right now, even though I'd think Excel
knows the number represents a valid date?

'Module1
'--------------------------------------------------------------------------
Public IntermediateArray As Variant
'I set this equal to a sheetrange to set the size, then do lots of data
manipulations on the array contents
'IntermediateArray(n, 10) is a department code, like "Engineering"
'IntermediateArray(n, 15) is a number representing the days since 1/1/1900,
such as 39651
'--------------------------------------------------------------------------

'Module2
'--------------------------------------------------------------------------
Option Base 1

Public Function LoadStaffing(DeptID As String, uDate As Date) As Variant
'do stuff
LoadStaffing = Array(rVal1, rVal2, rVal3)

End Function
'--------------------------------------------------------------------------

I tested the function within the same module by using the following sub, and
it returns the expected value:
'--------------------------------------------------------------------------
Sub Test_staffingmodel()
X = LoadStaffing("Engineering", "8/1/2008")
MsgBox X(1)
End Sub
'--------------------------------------------------------------------------

However, Module3, I try to use the function like this:
'--------------------------------------------------------------------------
Dim AvailableTime_ThisDayThisDept as variant
AvailableTime_ThisDayThisDept = LoadStaffing(IntermediateArray(eachday, 10),
IntermediateArray(eachday, 15))
'--------------------------------------------------------------------------
and when the sub gets called, I get "Compile Error: ByRef argument type
mismatch" error. The code in other modules has already finished running (it
takes about 8 minutes to run). As far as I can tell, it dies here at the
module level, it does not run and then crash at this line of code. I checked
some of the other variables in this module with debug.print and it doesn't
appear that any of them are populated with values from the sub's code.

Is this an issue with the date format not being able to accept numbers? Or
some weird cross-module limitation of variant arrays?

Thanks for any advice,
Keith

You can not send 39651 into a argument that expects a date... 39651 is a long
integer. That is a type mismatch. If you put 39651 into a cell and format it
as a date and send that in to the funciton it will work.
--
HTH...

Jim Thomlinson


"ker_01" wrote:

> Excel2003 on WinXP
>
> I have a problem that I'm not sure how to solve; I've decided to post the
> short version first (in case it is easy and someone can tell me how to fix
> it). If that doesn't work I'll post the much longer description with more
> code (I drafted the post, then was overwhelmed by the size of the potential
> post and whether anyone would even read it all). Basically, I'm getting a
> byref error when trying to call a function in a different module. It may be
> that the function expects a date and my values are a numeric representation
> of the date- that's my best guess right now, even though I'd think Excel
> knows the number represents a valid date?
>
> 'Module1
> '--------------------------------------------------------------------------
> Public IntermediateArray As Variant
> 'I set this equal to a sheetrange to set the size, then do lots of data
> manipulations on the array contents
> 'IntermediateArray(n, 10) is a department code, like "Engineering"
> 'IntermediateArray(n, 15) is a number representing the days since 1/1/1900,
> such as 39651
> '--------------------------------------------------------------------------
>
> 'Module2
> '--------------------------------------------------------------------------
> Option Base 1
>
> Public Function LoadStaffing(DeptID As String, uDate As Date) As Variant
> 'do stuff
> LoadStaffing = Array(rVal1, rVal2, rVal3)
>
> End Function
> '--------------------------------------------------------------------------
>
> I tested the function within the same module by using the following sub, and
> it returns the expected value:
> '--------------------------------------------------------------------------
> Sub Test_staffingmodel()
> X = LoadStaffing("Engineering", "8/1/2008")
> MsgBox X(1)
> End Sub
> '--------------------------------------------------------------------------
>
> However, Module3, I try to use the function like this:
> '--------------------------------------------------------------------------
> Dim AvailableTime_ThisDayThisDept as variant
> AvailableTime_ThisDayThisDept = LoadStaffing(IntermediateArray(eachday, 10),
> IntermediateArray(eachday, 15))
> '--------------------------------------------------------------------------
> and when the sub gets called, I get "Compile Error: ByRef argument type
> mismatch" error. The code in other modules has already finished running (it
> takes about 8 minutes to run). As far as I can tell, it dies here at the
> module level, it does not run and then crash at this line of code. I checked
> some of the other variables in this module with debug.print and it doesn't
> appear that any of them are populated with values from the sub's code.
>
> Is this an issue with the date format not being able to accept numbers? Or
> some weird cross-module limitation of variant arrays?
>
> Thanks for any advice,
> Keith
>
>
>

Jim- thanks for the clarification. Rather than sticking these in cells and
reformatting (since I have thousands) I'll look to see if I can find any way
to format it directly within VBA before passing the parameter.

Thanks!
Keith

"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:FF26501D-9652-4614-9315-(E-Mail Removed)...
> You can not send 39651 into a argument that expects a date... 39651 is a
> long
> integer. That is a type mismatch. If you put 39651 into a cell and format
> it
> as a date and send that in to the funciton it will work.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "ker_01" wrote:
>
>> Excel2003 on WinXP
>>
>> I have a problem that I'm not sure how to solve; I've decided to post the
>> short version first (in case it is easy and someone can tell me how to
>> fix
>> it). If that doesn't work I'll post the much longer description with more
>> code (I drafted the post, then was overwhelmed by the size of the
>> potential
>> post and whether anyone would even read it all). Basically, I'm getting a
>> byref error when trying to call a function in a different module. It may
>> be
>> that the function expects a date and my values are a numeric
>> representation
>> of the date- that's my best guess right now, even though I'd think Excel
>> knows the number represents a valid date?
>>
>> 'Module1
>> '--------------------------------------------------------------------------
>> Public IntermediateArray As Variant
>> 'I set this equal to a sheetrange to set the size, then do lots of data
>> manipulations on the array contents
>> 'IntermediateArray(n, 10) is a department code, like "Engineering"
>> 'IntermediateArray(n, 15) is a number representing the days since
>> 1/1/1900,
>> such as 39651
>> '--------------------------------------------------------------------------
>>
>> 'Module2
>> '--------------------------------------------------------------------------
>> Option Base 1
>>
>> Public Function LoadStaffing(DeptID As String, uDate As Date) As Variant
>> 'do stuff
>> LoadStaffing = Array(rVal1, rVal2, rVal3)
>>
>> End Function
>> '--------------------------------------------------------------------------
>>
>> I tested the function within the same module by using the following sub,
>> and
>> it returns the expected value:
>> '--------------------------------------------------------------------------
>> Sub Test_staffingmodel()
>> X = LoadStaffing("Engineering", "8/1/2008")
>> MsgBox X(1)
>> End Sub
>> '--------------------------------------------------------------------------
>>
>> However, Module3, I try to use the function like this:
>> '--------------------------------------------------------------------------
>> Dim AvailableTime_ThisDayThisDept as variant
>> AvailableTime_ThisDayThisDept = LoadStaffing(IntermediateArray(eachday,
>> 10),
>> IntermediateArray(eachday, 15))
>> '--------------------------------------------------------------------------
>> and when the sub gets called, I get "Compile Error: ByRef argument type
>> mismatch" error. The code in other modules has already finished running
>> (it
>> takes about 8 minutes to run). As far as I can tell, it dies here at the
>> module level, it does not run and then crash at this line of code. I
>> checked
>> some of the other variables in this module with debug.print and it
>> doesn't
>> appear that any of them are populated with values from the sub's code.
>>
>> Is this an issue with the date format not being able to accept numbers?
>> Or
>> some weird cross-module limitation of variant arrays?
>>
>> Thanks for any advice,
>> Keith
>>
>>
>>