a simple one, vba excel variables, arguments in function to declare or not to declare?

[wbrowse]

Hi,

I am learning VBA from ground 0. By the way is it a good idea? I don't
use a Mac but VBA support will not be available on Macs... How about
Pc?

As I am learning on my own, here is someh=thing I find a little
tricky:

Sub Order()
Dim UnitsOrdered, RemainingUnits As Integer
UnitsOrdered = 50
RemainingUnits = CheckAndUpdateStocks(UnitsOrdered)
blabla
blabla
End Sub

Function CheckAndUpdateStocks(UnitsOrdered As Integer)
blabla
blabla
End Function

When executing this, VBA tells me "Compile Error" & "ByRef argument
type mismatch"

Now if I go on modifying :

Function CheckAndUpdateStocks(UnitsOrdered As Integer)
as
Function CheckAndUpdateStocks(UnitsOrdered)

Then everything works fine. Why?

Thanks if you can help it out

Pcl

 

[wbrowse]

Oops I forgot another point:

If in the main Sub I declare the variable in a second position like :

Sub Order()
Dim RemainingUnits, UnitsOrdered As Integer
....

Then in the function, I can go on with

Function CheckAndUpdateStocks(UnitsOrdered As Integer)
....

Is there anything I should find strange here?

pcl

 

[Jay Freedman]

The problem is in the syntax of the Dim statement. You _must_ include an
'As' clause for each variable you define. If you omit the 'As' clause, VBA
defaults to declaring the variable as a Variant data type. By writing

Dim UnitsOrdered, RemainingUnits As Integer

you have inadvertently declared UnitsOrdered as a Variant, not the intended
Integer. When that Variant is passed to CheckAndUpdateStocks which expects
an Integer parameter, VBA throws a wobbly.

To avoid mishaps like this, it's a good idea to declare each variable in a
separate Dim statement, and even better to follow it with a comment about
the use or expected range of values:

Dim UnitsOrdered As Integer ' number of units, > 0
Dim RemainingUnits As Integer ' number of units still in stock

In cases where you intend to use a Variant, declare it explicitly:

Dim MyVariant As Variant ' used to hold temporary array

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.