tokenizing equations

  • Thread starter Thread starter htb
  • Start date Start date
H

htb

I have a need to tokenize a cell's contents. in the worksheet,
Cell.value is a string, but is actually an equation that's output from
various instrumentation.

The format of the cell is something like (the beginning text is
included):

"formulaic fit: RU = A + D [(1-G^2)/(Q/P)^n]"

The format of the equation does not change, although the variables may
be positive or negative, which further complicates the process since it
will change the equation's operators. What I'd like to do is populate
a range of cells with each of the six variable parameters (e.g. A, D,
G, Q, P, n) so I can do some further processing. I haven't found a
good way to do this in VBA-- any suggestions or examples of other
solutions for this problem?

TIA-
htb
 
HTB,

You could use Named Ranges in Excel worksheet.
So A1 would have a name of A, A2 a name of D, A3 a name of G, etc...

You'll need to manipulate the formula though. Excel will spew at the [ ]
Also = must be the first character


Alternatively, you could investigate adding a code-module at run-time which
contains a function.

Keep in mind that the current procedure can't run the function directly -
that's why in this example there is a GetResult function.


Sub test()
Dim vbc As VBComponent, strFormula As String
Dim A As Double, D As Double, G As Double, Q As Double, P As Double, n
As Double
Dim dblResult As Double

strFormula = "RU = A + D * ((1-G^2)/(Q/P)^n)" 'note square brackets
removed

A = 1: D = 53: G = 12: Q = 2: P = 12: n = 2

Set vbc = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
With vbc
.CodeModule.AddFromString _
"Function RU(A as Double, D as Double, G as Double, " & _
"Q as Double, P as Double, n as Double) As Double" & vbNewLine &
_
" On Error Resume Next" & vbNewLine & " " & strFormula & _
vbNewLine & "End Function"
End With
dblResult = GetResult(A, D, G, Q, P, n)
ThisWorkbook.VBProject.VBComponents.Remove vbc

MsgBox dblResult
End Sub

Function GetResult(A As Double, D As Double, G As Double, Q As Double, P As
Double, n As Double) As Double
GetResult = RU(A, D, G, Q, P, n)
End Function



Rob
 
thanks Rob-
the code-module looks to be the best solution. Your first suggestio
might need a bit more tweaking- should be easy to strip off everythin
up to the "=", but subsequents steps might be tricky.




*HTB,

You could use Named Ranges in Excel worksheet.
So A1 would have a name of A, A2 a name of D, A3 a name of G, etc...

You'll need to manipulate the formula though. Excel will spew at th
[ ]
Also = must be the first character


Alternatively, you could investigate adding a code-module at run-tim
which
contains a function.

Keep in mind that the current procedure can't run the functio
directly -
that's why in this example there is a GetResult function.


Sub test()
Dim vbc As VBComponent, strFormula As String
Dim A As Double, D As Double, G As Double, Q As Double, P As Double
n
As Double
Dim dblResult As Double

strFormula = "RU = A + D * ((1-G^2)/(Q/P)^n)" 'note square brackets
removed

A = 1: D = 53: G = 12: Q = 2: P = 12: n = 2

Set vbc
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
With vbc
.CodeModule.AddFromString _
"Function RU(A as Double, D as Double, G as Double, " & _
"Q as Double, P as Double, n as Double) As Double" & vbNewLine &
_
" On Error Resume Next" & vbNewLine & " " & strFormula & _
vbNewLine & "End Function"
End With
dblResult = GetResult(A, D, G, Q, P, n)
ThisWorkbook.VBProject.VBComponents.Remove vbc

MsgBox dblResult
End Sub

Function GetResult(A As Double, D As Double, G As Double, Q A
Double, P As
Double, n As Double) As Double
GetResult = RU(A, D, G, Q, P, n)
End Function



Rob



htb > said:
I have a need to tokenize a cell's contents. in the worksheet,
Cell.value is a string, but is actually an equation that's outpu from
various instrumentation.

The format of the cell is something like (the beginning text is
included):

"formulaic fit: RU = A + D [(1-G^2)/(Q/P)^n]"

The format of the equation does not change, although the variable may
be positive or negative, which further complicates the proces since it
will change the equation's operators. What I'd like to do i populate
a range of cells with each of the six variable parameters (e.g. A D,
G, Q, P, n) so I can do some further processing. I haven't foun a
good way to do this in VBA-- any suggestions or examples of other
solutions for this problem?

TIA-
htb
Click to expand...
Click to expand...
 
Back
Top