Solved: Using a function in another function

[RadBrad]

First, please understand that I am self-taught (I know!), I have searched this site for the answer and the questions on this site are well beyond my scope. I am having problems understanding how to call a sub or a function from another function.

I have this function that calculates the lineal inches of material used to make metal pipe:


Public Function SpiralInches(ByVal diameter As Single, ByVal length As Single)

Dim a As Single
Dim b As Single

a = diameter
b = length


Dim pi As Single
pi = 3.14159

SpiralInches = (a * pi) * b / 4.74


End Function
I would like to use this formula in other equations, for example, to divide it by 12 to get the footage. This is as far as I get:


Public Function SpiralLF(ByVal diameter As Single, ByVal length As Single)

SpiralLF() = SpiralInches() / 12
End Function
The error I get is that SpiralInches "Argument not optional".

The first function works fine by itself in the worksheet (i.e. I can assign a cell to each variable and it calculates properly) and I realize that I can add the first function to a cell and divide it by 12 at that point. But I really want to understand how to call the first function in a second one.

I've tried to make SpiralInches a sub, both Public and Private, I've tried dimensioning different variables within the second function, and I've tried a "Call" to the first function, all for naught. I can't seem to pass the results of the first function to the second one properly and any help with this would be GREATLY appreciated!

I am using Excel2003.

Thanks in advance!
Brad

[Demosthine]

Good Afternoon.

You have two problems with your code...

In your SpiralLF Function, you receive the Diameter and Length, but you fail to pass them to your SpiralInches Function.

Second, when you are assigning the result of your math, you are assigning it to an Array. Remove the parenthesis from the your left operand.



Public Function SpiralLF(ByVal diameter As Single, ByVal length As Single)

SpiralLF = SpiralInches(diameter, length) / 12
End Function


Take care.
Scott

[mdmackillop]

You can also use optional parameters (must be last declared in the function)


Option Explicit
Sub Test()
MsgBox SpiralLength(10, 300)
MsgBox SpiralLength(10, 300, "foot")
MsgBox SpiralLength(10, 300, "yard")
End Sub

Public Function SpiralLength(ByVal diameter As Single, ByVal length As Single, Optional unit As String)

Dim a As Single
Dim b As Single
Dim pi As Single

a = diameter
b = length

pi = Application.pi

Select Case unit
Case ""
SpiralLength = (a * pi) * b / 4.74
Case "foot"
SpiralLength = (a * pi) * b / 4.74 / 12
Case "yard"
SpiralLength = (a * pi) * b / 4.74 / 36
End Select

End Function

[RadBrad]

I will try both of those. I've read about the case stuff and I don't think it's what I need, though I will try it out to see what it does. Thank you again!

[Bob Phillips]

MD's looks as though it should do what you want. Where he shows



MsgBox SpiralLength(10, 300)
MsgBox SpiralLength(10, 300, "foot")
MsgBox SpiralLength(10, 300, "yard")


try putting

=SpiralLength(10, 300)
=SpiralLength(10, 300, "foot")
=SpiralLength(10, 300, "yard")

in cells

[RadBrad]

What worked for me was to not pass the results as an array. The case situation might work for me, but there are other equations I need to calculate for inventory of the stock metal strips, thicknesses, etc. The base equation will be helpful for those.