Simple user defined functions

[Db396]

Hi

I've been trying my hand at creating a few functions and all has gone well.

However, one function will simply not work and I'm baffled.

Below are two functions that I have created which are very similar. The first, ROS, works perfectly well in queries and returns the expected result. The second, BrCov always returns 999.

I've checked the field properties in the table and all have the same properties: Data Type = Number, Field Size = Integer, Format = Fixed, Decimal Places = 0, Default Value = 0.

Would very much appreciate comments.

Thanks
David

Public Function ROS(Sales_Units, Number_Of_Stores As Double)

If Sales_Units = 0 Then
ROS = 0

ElseIf Number_Of_Stores = 0 Then
ROS = 0

Else
ROS = Format(Sales_Units / Number_Of_Stores, "Standard")

End If

End Function

Public Function BrCov(Stock, Sales As Double)

If Stock = 0 Then
BrCov = 999

ElseIf Sales_Units = 0 Then
BrCov = 999

Else
BrCov = Format(Stock / Sales, "Standard")

End If

End Function

 

[gemma-the-husky]

a few observations,

----

in the second function, you have sales in the header, but

ElseIf Sales_Units = 0 Then

in the body, so sales_units will get declared "on the fly", and is always 0 therefore.

I would add "option explicit" at the top of the module (indeed every module). This forces you to declare all variables before you use them - it helps prevent stuff like this. There is a tools/options setting "require variable declaration" which should be turned on, to do this.

------

another thing is - in the declaration

Public Function BrCov(Stock, Sales As Double)

stock is declared as a variant, not as a double. This may be what you want, but it may be a misunderstanding. In VBA every variable has to be typed separately.

-----

the function itself can also by typed - eg in this case a double maybe - so rather than formatting the function result to return a formatted string, it may be better to return the function value and let the calling function decide how to format the result

at the moment you are returning a variant.

so instead, eg

Public Function BrCov(Stock, Sales As Double) as DOUBLE
...
end function

and then

myresult = format(brcov(stockval, salesval),"standard")


-----
finally, when you post, there is an "advanced tab" - which has an option to put code inside code tags which makes code far easier to read.

 

[jdraw]

In addition to Dave's comments, I would recommend you get a copy of the free utility
MZTools for VBA. It has many useful features.

 

[Db396]

Many thanks for the really helpful advice. All is working now. I'm kind of getting into this so will read up on the basics of coding.

Thanks
David