Define Arguments in a function

Alvinpereira

New Member
Joined
Apr 28, 2015
Messages
19
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?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
You could declare F as an optional parameter, it needs to be set as a Variant in order to work with the IsMissing function.

Code:
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
How about a two parameter function?

Code:
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

New Member
Joined
Apr 28, 2015
Messages
19
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129

ADVERTISEMENT

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

New Member
Joined
Apr 28, 2015
Messages
19
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. :biggrin:
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,863
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top