CODEWITHSUNDEEP
stringexcelformulavba
user2118020
user2118020

Reputation: 1

VBA String to Formula

Hi I have created a quick function that evaluate string 

Function Evalue(ByVal str As String)
Application.Volatile
Evalue = Evaluate([str])
End Function

I have an array with all the formulas I use as string. Depending on the case I use INDEX/MATCH to choose the good formula then Evalue to evaluate it.

If the string formula is in 1 block like SUM(A1:A20) it works perfectly But if it is in several blocks like

SUM(A1:A20)-SUM(B1:B20) it returns a #NAME error, if I put the string in the array between ' ' or " " it returns #VALUE error.

Upvotes: 0

Views: 360

Answers (1)

user645280
user645280

Reputation:

** snip ** The original code works just fine as written.

The cell I tested looked like this:

=Evalue("SUM(A1:A3)-SUM(B1:B3)")

Here's the security problem:

Apparently the first time you open and save a workbook, storing code in a module is enough to make it accessible to be run from within worksheet cells. However, the next time you open the same worksheet (even if it is macro enabled) it's very much more difficult to get things working again. Apparently the original code works fine as written. However, Excel hints it exists and then reports a #NAME error when you try to use it. There's a thread here with more info:

http://social.msdn.microsoft.com/Forums/en-US/0a79b7d8-178e-4cf5-8bc3-782a58b55a8b/vbaexcel-function-defined-by-user-gives-error-personalxlsb-weird?forum=isvvba

Apparently it requires installing an "add-in" to perform the simplest operation. (Or you can use your personal.xlsb file)

Upvotes: 2

Related Questions