Define Arguments in a function

[Alvinpereira]

Hi,

Can you please help me with this doubt.

Suppose we have a formula HL = (F*L(V^2))/ (2*g*(d^4)) were V = G/A. Now just assume that F, L, G and D are constants i.e. F = 0.79, L = 1, G = 9.81, A = 0.04

So when i made a function i input Q as an argument with data type *** double.

Example:
Function HEADLOSS(Q As Double)


Dim F As Double
Dim L As Double
Dim A As Double
Dim G As Double
Dim D As Double
Dim V As Double


F = 0.79
L = 1
A = 0.04
G = 9.81
D = 0.2


V = Q / A


HEADLOSS = ((F * L) * (V ^ 2)) / ((2 * G) * (D ^ 4))


End Function

It works fine. But what i now need to do is make a function keeping in mind that all other variables except G can change. In the above example Q is the one which we pick up from the excel sheet. But sometimes if not Q which should be referred to, can be F also.

SO how to i put a case where i can select the Letter and that goes into the Argument and makes the rest constants in the same formula.

Can some one please help me?

 

[dave3009]

You could declare F as an optional parameter, it needs to be set as a Variant in order to work with the IsMissing function.

Function HEADLOSS(Q As Double, Optional F As Variant)


Dim L As Double
Dim A As Double
Dim G As Double
Dim D As Double
Dim V As Double


If IsMissing(F) Then
    F = 0.79
End If
L = 1
A = 0.04
G = 9.81
D = 0.2


V = Q / A


HEADLOSS = ((F * L) * (V ^ 2)) / ((2 * G) * (D ^ 4))


End Function

 

[ChrisM]

How about a two parameter function?

Function HEADLOSS(Var as String, dVal As Double)

Select Case Var

   Case "Q"
        'your code with dVal

   Case "D"
        'your code with dVal

......
End Select

Then in Excel:

=HEADLOSS("D", .0222)

 

[Alvinpereira]

HI ChrisM,

Thanks for your answers, when i try the second suggestion of your's i get a error saying duplicate variable used. that's cause the same variables are used in case Q and Case D. Example F L V etc are the same defined Dim's in each case. What do i do if i need to keep the same DIm's in all the cases.

 

[ChrisM]

Can you post your code? You should only Dim the variables one time, at the start of your function.

Set all your values next, A = 2 G = 4 etc

Then do the Select Case, where you will most likely overwrite one of the variables you just coded, for example:

Select Case ExcelInput1
Case "A"
A = ExcelInput2
End Select

 

[Alvinpereira]

Function HEADLOSS(var As String, dval As Double)


If var = "Q" Then


Dim F As Double
Dim L As Double
Dim A As Double
Dim G As Double
Dim D As Double
Dim V As Double


F = 0.79
L = 1
A = 0.04
G = 9.81
D = 0.2


V = dval / A


HEADLOSS = ((F * L) * (V ^ 2)) / ((2 * G) * (D ^ 4))




Else

If var = "F" Then

Dim Q As Double
Dim L As Double
Dim A As Double
Dim G As Double
Dim D As Double
Dim V As Double

Q = 0.79
L = 1
A = 0.04
G = 9.81
D = 0.2


V = Q / A


HEADLOSS = ((dval * L) * (V ^ 2)) / ((2 * G) * (D ^ 4))


End If


End Function

Above is my code. Sorry i dont understand the logic of certain things at times.

In the 1st case where Q is the data missing and which the user selects from the excel sheet =Headloss( "Q", some value), in this case Q is not listed in the DIm's statements. But as soon as i go the the next case i.e. "F" = Headloss("F", some value), then i need Q to be defined by default.

How do i hard code such a thing, so basically if Q is the variable i need F to be defined, IF "F', is the variable i need Q to be taken from the Dim's and so on and so forth for the other few variables.

 

[ChrisM]

Yes, I thought that was what you were doing, that is not correct. I'm on my phone so can't type a lot of code.

You only dim variables once, all variables, at the top of your code, first thing.

Next, set all variables = to their values, whether they are being changed or not. A= 1, F= 3, etc

Now do the select case. The only thing you do inside the Select is to set/reset the variables as needed. So now maybe A = 1.5 instead of 1.

After the Select, continue with your headloss formula.

Does that make sense?

 

[Alvinpereira]

Yes it Does. Thanks for all your time. Appreciate it !