PDA

View Full Version : Implementing formulas with variables into VBA

Itakeyokes
11-13-2017, 05:36 AM
Hi I'm new to VBA.
I'm struggling to write a script for an equation with several variables.
Mathematically the equation is written in the form:
X/L = AuU* ((1-e)2 (1+30(1-f)3 ) / d2 )
I am attempting to add the variables (letters) and the overall relationship so that it can be graphed.
Any pointers, insight or help would be appreciated.
Thanks,
ITY

Paul_Hossler
11-13-2017, 07:47 AM
X/L = AuU* ((1-e)2 (1+30(1-f)3 ) / d2 )

It's really hard to tell what are the independent variables and the independent one

Assuming that

1. X/L is a single number (just written wrong for VBA)
2. AuU is a single number and not Au times U
3. (1-e)2 means (1-e) squared
4. (1-f)3 means (1-f) cubed
5. d2 means d squared

then a user defined function (UDF) in the workbook would be one way (below). The other would just be a long worksheet formula

Option Explicit

Function XoverL(AuU As Double, e As Double, f As Double, d As Double) As Double
XoverL = AuU * ((1 - e) ^ 2 * (1 + 30 * (1 - f) ^ 3) / d ^ 2)
End Function

Sub test()
MsgBox XoverL(100, 0.5, 12, 9)
End Sub

It's be a lot easier of offer suggestions if you attach a sample workbook with some dummy (but realistic) data (see my sig)

SamT
11-13-2017, 08:10 AM
Assuming that "X" is the column you want the results in, and "A" is the column with AuU in, "E" has e in, "F" has f, and "D" has d
'Standard Module Code.
Option Explicit

Public Function XoverL(AuU As Range, e As Range, f As Range, d As Range) As Double
XoverL = AuU * ((1 - e) ^ 2 * (1 + 30 * (1 - f) ^ 3) / d ^ 2)
End Function

In Column X, the Formula is "=XoverL(AU,E,F,D)

Copy that formula down to the bottom of the table .Edit the letters in the Cell Formula to represent the actual Column Letters the Values are really in and place the cell formula in the appropriate Results column.