VBA Evaluate returns #VALUE for some expressions (SUMPRODUCT and IF)

[Uster]

I'm trying to move some SUMPRODUCT formulas from my sheets to some VBA code but am running into the problem that they evaluate to #VALUE
I am moving out the SUMPRODUCT because they seem to be slowing down the saving and loading of the Excel-file (doing sumproduct of 1 mio rows in 100s of different places in the file) and I am guessing SUMPRODUCT is still more efficient than any code I would write myself.


I've created a test sheet to isolate the error and my code looks like this:


Sub Q()


Dim a As String, b As String, c As String, d As String
Dim x As String, y As String


x = "D1:D6"
y = "E1:E6"


a = "(1+1+1+1)"
b = "SUM(" & x & ")"
c = "IF(4<5;""YES"";""NO"")"
d = "SUMPRODUCT(" & x & ";" & y & ")"




Sheets("Sheet1").Range("A1").Value = a
Sheets("Sheet1").Range("A2").Value = b
Sheets("Sheet1").Range("A3").Value = c
Sheets("Sheet1").Range("A4").Value = d

Sheets("Sheet1").Range("B1").Value = Evaluate(a)
Sheets("Sheet1").Range("B2").Value = Evaluate(b)
Sheets("Sheet1").Range("B3").Value = Evaluate(c)
Sheets("Sheet1").Range("B4").Value = Evaluate(d)

End Sub




In the ranges D1 to D6 and E1 to E6 I have entered the values 1 to 6.

Problem is that a and b evaluate fine to 4 and 21 respectively, while c and d evaluate to #VALUE, where I would expect "YES" and 91.
Any suggestions?

Uffe

Using Excel 2007

[lecxe]

Hi Uffe
Welcome to the board

VBA uses English settings, the parameters of the functions are comma separated.

Try replacing the semicolons that you use as parameter separator by commas:



c = "IF(4<5,""YES"",""NO"")"
d = "SUMPRODUCT(" & x & "," & y & ")"

[Aussiebear]

Just curious here....

x="D1: D6".

I'm assuming that D1: D6 is a range, which is normally expressed as "D1: D6", for it to be expressed as a string shouldn't it be ""D1: D6"" by using double double quotation marks?

[lecxe]

Just curious here....

x="D1: D6".

I'm assuming that D1: D6 is a range, which is normally expressed as "D1: D6", for it to be expressed as a string shouldn't it be ""D1: D6"" by using double double quotation marks?

Hi Aussiebear

You are right, if we wanted the double quotes inside the string we'd have to do that.

In this case, however, you don't want them, inside the formula you don't want double quotes for the string

Ex., for the first formula with the range,



x = "D1:D6"
...
b = "SUM(" & x & ")"


you want the formula to be evaluated:


SUM(D1:D6)

as you'd write it in the worksheet.

[Uster]

Hi lecxe,

Thanks for the fast reply.
I tried replacing semicolons with commas. Now I get #NAME? instead of #VALUE! for c and d.
Tried looking up #NAME? but didn't seem to get anything relevant to this problem. Any ideas?

Aussibear: lecxe is right, it works as intended without the doublequotes.

Uffe

[snb]

VBA only speaks US english and US international settings:

c = "IF(4<5,""YES"",""NO"")"
d = "SUMPRODUCT(" & x & "," & y & ")"

[Uster]

Thank you for your reply snb.
As I posted to lecxe I've tried replacing the semicolons with commas, which is what I understand that you are suggesting. Now I am just getting #NAME? instead of #VALUE!

Uffe

[lecxe]

That error would happen if, for ex., the names of the functions were not recognised.

You are using the English names for the functions as posted, right? Not the equivalent Danish names for the functions?

[Uster]

Thank you. That did the trick.
I was using the Danish function names, yesterday when I encountered the issue I was at work, where I run an English version, so I just tried to replicate it here at home where I run a Danish version.
So replacing the semicolons with commas will fix my problem at work on monday.

Thank you again.

[lecxe]

I'm glad it helped. Thanks for the feedback.

[Aussiebear]

X & Y were dimmed as String. Should they have dimmed as Range instead?

[snb]

@Aussiebear

No they shouldn't.

If you enter a worksheetfunction, using VBA, you have to write it as a complete string:

"=Sum(A1:K10)"
or
"=sumproduct(A1:A10,C1:C10)"

[Aussiebear]

After playing around with the "Strings"

Sub Q()
Dim a, b, c, d, x, y As String

x = "D1:D6"
y = "E1:E6"
a = "(1+1+1+1)"
b = "SUM(" & x & ")"
c = "IF(4<5,""YES"",""NO"")"
d = "SUMPRODUCT(" & x & "," & y & ")"


With Sheet1

.Range("A1").Value = a
.Range("A2").Value = b
.Range("A3").Value = c
.Range("A4").Value = d
.Range("B1").Value = Evaluate(a)
.Range("B2").Value = Evaluate(b)
.Range("B3").Value = Evaluate(c)
.Range("B4").Value = Evaluate(d)

End With

End Sub

[Paul_Hossler]

@Aussiebear - very minor nit



Dim a, b, c, d, x, y As String


a, b, c, d, x are really variants

y is the only 'true' string



Option Explicit
Sub AussieBear_1()
Dim x, y As String

MsgBox VarType(x)
MsgBox VarType(y)

End Sub

[Aussiebear]

Oh God.... not another variation. I get what you are saying Paul, as its in the method of declaring the dim's. My apologies if I mislead anyone.