VBA - Read string from Excel cell and execute as VBA Code
OK, I've searched and been unable to find a solution for my problem. I have VBA code that reads multiple Excel spreadsheets and outputs multiple CSV files for import into an accounting system. The VBA code is fairly generic and is controlled by a mapping definition between the import spreadsheets and output CSV files. Although I attempted to keep any calculations out of my code, it looks like this is going to be the best solution. But I still want to keep the code generic and give the end users the ability to maintain via Excel. I want to have a new column in the mapping that a "formula" can be entered and applied against the current field value. The "formula" might be an arithmatic operation or a VLOOKUP/translation. I'd like to basically put any Excel or VBA function in here and have it executed using "value" as my current field. I tried EVALUATE, but I get Error 2029. So, for example, I might put "value * -1" as the formula I want executed and maybe have a Range Name=CALC point to that formula. In my VBA code, I would expect to be able to have "value = CALC". I'm open to any and all suggestions. Last minute changes on deliveries, so need responses ASAP. Thanks in advance for any assistance.
Have you searched the error message?
Yes, the error message indicates a type mismatch and possibly that the EVALUATE only works reading Excel cells whereas the "value" field only exists in the VBA code. So, one possible solution is to write the "value" field to a cell and then attempt the evaluate; however, I didn't find this as a solution in my research so was looking for advice before wasting more time.
I'm not completely sure what you are aiming for, and it is vague as to what was tried already, but I have an idea that is a bit crude. Make the input "formula" be like a normal Excel formula (it may be better to treat it as plain text) but have something like VALUE (or VALUE1, VALUE2, etc.) for where the variable(s) would go. In the VBA code, do a string replacement of all instances of VALUE (or VALUE1, VALUE2, etc.) into the actual value to evaluate. Probably Range.Formula and Evaluate would help in this.
Thanks for the help. I've resolved my initial issues. Based on the Google research, I got the impression that Evaluate only works with cell references and will not work with VBA fields. So, I updated a temp cell with my initial value from the "value" field, then used the EVALUATE reading my original formula Text cell. So, final answer is:
1. Formula Cell = sum(Temp!$AA$1 * -1)
This has the reference to my temporary holding cell AA1 on TEMP worksheet.
2. VBA Code =
ActiveCell.value = value
value = Evaluate(FldCalc(cnt))
FldCalc is a Collection where I'm storing the text formulas.
What I'm hoping now, is that I can put almost any Excel or VBA formula into my Formula column and have it execute. But, I expect to have to tweek it as I go.
Again, thanks for the input.