0

I am attempting to import a string as a formula into VBA, modify that formula based on certain conditions (ultimately to be defined by the user), and then evaluate that formula numerically for the rest of my code.

I tried writing to a text file and reading back from it.

I tried multiple different formats for a formula in Excel that could be read and then evaluated ("X+Y",X+Y,"=X+Y",=X+Y). When I read in the formula directly from the spreadsheet, it is an array of strings:

Mass_Balances = Worksheets("Formulas").Range("B3:C4").Value
Debug.Print Mass_Balances(1, 1)
(mu * Comp(2, 1) / (K_s + Comp(2, 1)) * Comp(1, 1)) - b * Comp(1, 1)

This is good, as I want to modify these formulae, which can only be done if it stays a string.

However, when I type out the text of one of those formulae and assign it to another variable, it is a double (since all the variables in it are defined). This is what I eventually want, once the formulae (a set of mass balances) is complete.

Var = (mu * Comp(2, 1) / (K_s + Comp(2, 1)) * Comp(1, 1)) - b * Comp(1, 1)
Debug.Print Var
9705.5737704918

When I try to assign the text of one of those formulae (an element of Mass_Balances) and assign it to another variable (such as Var), it either remains a string, or if the variable I am assigning to was declared as a double, I get a type mismatch (to be expected!). Converting to a double also doesn’t work (not that I expected it to).

Var = Mid(Mass_balances(1, 1), 2)
Debug.Print Var
(mu * Comp(2, 1) / (K_s + Comp(2, 1)) * Comp(1, 1)) - b * Comp(1, 1)

Using Mid here gets rid of the leading equals sign, which I thought was necessary to have it be a formula and able to be evaluated.

I realize the equations for both Var and Mass_Balances(1,1) are the same, when I said earlier that I wanted to revise the equations and then get the value. However, I figured if I can’t figure a way to numerically evaluate the original formula, trying to do so after updating it would also be fruitless.

Community
  • 1
  • 1
Alex Wohlgemuth
  • 1
  • 1
  • 1
    There is no `eval()` in VBA like in other languages such as Javascript. There is `Application.Evaluate` but that only works on worksheet formulas, not on VBA. – Tim Williams Apr 01 '22 at 22:15
  • 1
    See this thread - https://stackoverflow.com/questions/1057670/create-a-new-object-using-the-text-name-of-the-class/48372415#48372415 – Tim Williams Apr 01 '22 at 22:30
  • Thanks! This covers a lot of aspects I'm not familiar with but I'll start reading up on it and give it a try! – Alex Wohlgemuth Apr 02 '22 at 01:05
  • 1
    You can use the Evaluate method(Worksheet.Evaluate is generally better than Application.Evaluate) but it has significant "quirks" that are worth understanding: see my blog post https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/ – Charles Williams Apr 02 '22 at 10:46
  • It looks like the function described in Answer #5 at the link below is my best bet. I think I'll have to save it for special cases, and unfortunately just hardcode for the others, because I'd have to use this function every time a formula needs to be evaluated which is...probably a lot. (https://stackoverflow.com/questions/4471884/how-to-turn-a-string-formula-into-a-real-formula/4472470) Thanks y'all for your help! I think this thread can be closed now. – Alex Wohlgemuth Apr 04 '22 at 15:34

1 Answers1

0

I really don't understand what are you trying to do, but.

If you want to convert a variable to string (for example your var variable), just type:

dim newvar$, var
newvar = cstr(var)

Then, if you want to print a formula in excel from vba, just use .Formula property (or .FormulaR1C1) Example,

with worksheets("Formulas")
    .range("A1").Formula = "=X+Y"
end with

.FormulaR1C1 it's used to relative reference.

LletDeCabra
  • 91
  • 3
  • I apologize for the lack of clarity. Here's an example of 'the big idea.' I read in a formula from Excel - eg, X+Y. Based on user input, the sub edits said formula - eg, from X+Y to (X+Y)/A. (these are all much, much simpler than the actual equations I'm working with, but I hope they illustrate the point) Then, once that formula is 'ready' to use in the sub, I want to be able to evaluate it. These equations will go through many iterations, so sending them to Excel for evaluation, then reading them back in, would slow things down a lot. I hope this is clearer! – Alex Wohlgemuth Apr 02 '22 at 00:49