Working with User-defined Functions

Question

I am trying to increase my understanding of user-defined functions.  The final function I want to use is somewhat complicated, so I would like to break it down into smaller bit functions, and then refer to each of those smaller function in the final function.  Can I do this?  The first function calcTnB works just fine., but the second one CalcCutLen which relies on the first function does not.  When I hover the mouse over the (calcTnB)  in the second function, the correct value shows, however, hovering over the CalcCutLen shows a zero and  I get a run-type error 13 type mismatch on the second line.  All the fields are defined as singles, but I suspect something else is going on.

Thanks so much for any help

Woops ... here is the code I forgot to attach on my last post.

Answer 1

The argument of CalcCutLen cannot be the function name calcTnB.

Since you want to call calcTnB in CalcCutLen, you will have to pass TopSize, TopSizeXs, Hem and HemXs.

For example

Public Function CalcCutLen(TopSize As Single, TopSizeXs As Single, Hem As Single, HemXs As Single) As Single

    CalcCutLen = calcTnB(TopSize, TopSizeXs, Hem, HemXs) + 25

End Function

Remarks:

The check If Not IsNull(…) is superfluous. An argument of type Single cannot be Null. If you want to allow Null as argument, you should declare the arguments as Variant:

Public Function calcTnB(TopSize As Variant, TopSizeXs As Variant, Hem As Variant, HemXs As Variant) As Single

This version will return 0 if an argument is Null. If you want it to return Null, you should make the function return a Variant too:

Public Function calcTnB(TopSize As Variant, TopSizeXs As Variant, Hem As Variant, HemXs As Variant) As Variant

Answer 2

I am trying to increase my understanding of user-defined functions.

On the subject of functions in general you might like to take a look at Functions.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file provides a simple introduction to the basics of writing functions in VBA, with simple examples.  None of the examples call another 'user defined function' in the way that you are, but there are examples which call built in functions, which illustrate how arguments passed into the first function are then passed into another function.

Answer 3

Add it to your other post instead of writing a duplicate.