Evaluate an Array Formula before inserting it in a cell

[todd.huttenstine]

Hey

I have this string that is programatically inserted into a cell as an
array formula:
=1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),
1)),
0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))


It returns a valid result when inserted as an array into a cell. But
I would like to be able to evaluate this
formula and get the result in VBA before I insert it into a cell.


Bob Phillips posted the following yesterday but its resulting in
runtime error 13 Type Mismatch:
MsgBox Activesheet.Evaluate( _
"1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT(" & _
"""1:""&LEN(A2))),1)),0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(" & _
"INDIRECT(""1:""&LEN(A2))),1))))")



Thanks
Todd

 

[Chip Pearson]

Use the Evaluate function:

Dim F As String
Dim V As Variant

F = "=SUM(A1:A10)"
V = Evaluate(F)
If IsError(V) = True Then
Debug.Print "ERROR IN FORMULA: " & F
Else
Debug.Print "RESULT: " & V
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[todd.huttenstine]

Its telling me I have an error in my formula but that cant be
correct. Something else is wrong. Here is the rest of my code:

TargetFormula = "=1*MID(" & TargetCell & ",MATCH(FALSE,ISERROR(1*MID("
& TargetCell & ",ROW(INDIRECT(" & Chr(34) & "1:" & Chr(34) & "&LEN(" &
TargetCell & "))),1)),0),LEN(" & TargetCell & ")-SUM(1*ISERROR(1*MID("
& TargetCell & ",ROW(INDIRECT(" & Chr(34) & "1:" & Chr(34) & "&LEN(" &
TargetCell & "))),1))))"

Range(RefEdit1.Value).FormulaArray = TargetFormula

Then when the formula is entered into the cell -per the value in my
rededit control - the value inserted into the cell is this array
formula:
=1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),
0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))

This formula gives me a valid result.

I am wanting to know the result ever before I put the formula into the
cell. How do I do this?

 

[todd.huttenstine]

I gave up so I tried another approach... I found an unused cell in the
active worksheet and updated that empty cell with the formula and set
the font to match the background so it would not appear to the user
that a cell got changed. I was planning to then reference this cell
and set the value of my control on the form equal to the value of the
cell, BUT!!! as usual.... something else went wrong. I am using
Refedit controls on a form and the refedit change events are not
firing. I see this is YET another bug in the refedit control. After
trying to be a bit creative with several other events, I could still
not get around these refedit bugs. Now I am very frustrated...

Anyway, assuming my above circumvention idea would have worked, I
believe it would have been a bad programming practice to do because my
code can be used from any workbook and if it used on a workbook that
has code in certain worksheet events, then that could create many
possible problems and thats not something I want to do.

So now I am back to square one.... and that is to figure out how in
the world to use the application.evaluate method to return my result
on my formula stored in a large string with various variables
comprising it. Any suggestions????

 

[Dana DeLouis]

Some commands don't work well 'Evaluated.'
Here's a small section of your code...

Sub Demo()
Dim v
'Bad
v = [Row(Indirect("1:3"))]
v = Evaluate("Row(Indirect(""1:3""))")

'Good
v = [Row(1:3)]
End Sub

Could you explain what the formula does? It appears to return the either
the same number, or an error.

 

[todd.huttenstine]

It looks at a string and extracts the numeric value from in between a
set of parenthesis. Also if there are 2 sets of parenthesis and one
set contains alpha characters, then it will extract the numeric values
and ignore the alpha. It will error out if the string contains 2 sets
of parenthesis where both contain some sort of number. It may also
error out in other scenarios that I have not tested, but it works for
what I need it to do.

So is there any hope for the eval function to work for this formula?

 

[Dana DeLouis]

Hi. Would this slight change work for you?
I like to use Replace when working with long strings.
It sounds like there may be a shorter formula, but I'm not sure.
This is set for Cell A2.

Sub Demo()
Dim s As String
Dim n As Variant

Const Fx As String = _
"1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(1:#),1)),0),#-SUM(1*ISERROR(1*MID(A2,ROW(1:#),1))))"

s = Replace(Fx, "#", Len([A2]))
n = Evaluate(s)
End Sub

 

[todd.huttenstine]

Hey

I tried your code and I got an Error 2015.

 

[Dana DeLouis]

I get that error when there is nothing in A2.
The following returns 123.

Sub Demo()
Dim s As String
Dim n As Variant

Const Fx As String = _
"1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(1:#),1)),0),#-SUM(1*ISERROR(1*MID(A2,ROW(1:#),1))))"

[A2] = "abc(123def)"
s = Replace(Fx, "#", Len([A2]))
n = Evaluate(s) '<- 123
MsgBox n
End Sub

 

[todd.huttenstine]

Hey

Now I am getting Runtime error 13 - Type mismatch.