Convert String from Excel Range.Value to Equation in VBA

I have some variables stored as strings in worksheets that I would like to do calculations on in VBA after I define the variables as values.

Example; In cell A1, I have... "((Pmat * 5) + (Pmdf + 150) - (Zmdf * 1.5)) / 2"

Pmat, Pmdf, and Zmdf are all variables that are assigned numbers in VBA. For instance let; Pmat = 10, Pmdf = 45, and Zmdf = 20. I would end up with 107.5 as my value.

However I can't simply do; X = Range.Value
If I pull A1 value into VBA as "Formula = Range.Value ". What should I Dim Formula as so that VBA knows part of the info in that range is variables and other parts are part of the equation?

Try

Please Login or Register  to view this content.

Originally Posted by Jakobshavn

I would use a two-dimensional array to hold the variables. (This facilitates replacing substrings with values) See the attached.

This works. Will be confusing keeping track of what position in the array means what. Instead of having Pmat = 5, I would have to know it as ary(1, 2). I will end up having over 70 variable names. I am trying to avoid looping though all of them.

I do like the MultiArray and Replace. I was thinking that I was just setting my data type wrong via Dim with "Formula". Not String, Not Variant. Wanted to know if a Data Type would haddle this.

Originally Posted by Norie

Where do you want to calculate this formula?

The calculated formula is done in VBA several times (with the variables changing each time). I will then store each calculation in an Array (I know this part). At the end I export the Array with all of its data (I know this part too).