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


---
Message posted from http://www.ExcelForum.com/

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_S tdModule)
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



"htb " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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.





Rob van Gelder wrote:
*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_S tdModule)
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 " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com