Solved

Stuck with VBA and SumProduct in Excel

Posted on 2004-04-07
16
690 Views
Last Modified: 2012-08-14
I am lookinfg at implementing obtaining a Sumproduct from read-only Excel sheets. For practice, I used a sheet with 1300 rows and placed the formula

=SUMPRODUCT((RANGE="BOOKS")*(REGION=6101)*(UNITS))

which correctly obtained 19141 (Units sold).  I wrote a quick OLEDB Jet4.0 query on the Excel sheet as
SELECT Sum(UNITS) AS n FROM [Data$] WHERE (RANGE="BOOKS" AND REGION=6101);

which also obtained 19141 units. But wishing to stay with Excel I tried VBA

n = application.SUMPRODUCT((RANGE="BOOKS"),(REGION=6101),(UNITS))
If VarType(n) = 10 Then
   sProd = "Value Returned Error"
else
   sProd = n
end if

which returns error.  What am I doing wrong?

stan

P.S. I posted this question earlier, but it doesn't show up, so I am repeating the post. If it duplicates, I apologize.
0
Comment
Question by:stanl
  • 6
  • 6
  • 4
16 Comments
 
LVL 1

Expert Comment

by:doubleglazing2
Comment Utility
Try:

[replace 'Book.xls' with you workbook-name ]:

N = Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )

if N=10 then
        sProd = "Value Returned Error"
else
        sProd = n
end if

==============================
OR
==============================

N = Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )

if N=10 then
        sProd = msgbox("Value Returned Error")
else
        sProd = msgbox(n)
end if

==============================
OR
==============================

if Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )=10 then

        sProd = msgbox("Value Returned Error")

else

        sProd = msgbox(n)

end if

==============================
OR
==============================

if Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )=10 then

        sProd = "Value Returned Error"

else

        sProd = n

end if
0
 
LVL 1

Expert Comment

by:doubleglazing2
Comment Utility
What does "Region=6101" mean? Is there cell range called "Region", or "Region=6101", or "6101"?
0
 
LVL 1

Expert Comment

by:doubleglazing2
Comment Utility
As you can see, the function you would be using is a "WorksheetFunction" i.e. SumProduct, written like this:

Application.WorksheetFunction.SumProduct(  ..., ..., ..., ...)

You have to put between those commas, either names of named ranges or juts plain ranges on the current worksheet. To get the value you assign the result to a variable (as you've done already.)

Then if you want a message box to display the results, you use the msgbox("...") function. Or you might want to assign the value to a variable.

To refer to a named range like 'BOOKS' for example, you do this:

                                   Range("Book.xls!BOOKS")

where "Book.xls" is the name of the workbook.

0
 
LVL 1

Expert Comment

by:doubleglazing2
Comment Utility
OK. I just realised that you're checking for errors using VarType. It's prbably best to ignore that (i.e. cut it out) and then see whats going wrong with your code. Even if you DO put it back in the code, its probably best to say "if VarType(n) <> 2 then ..." because as stands now, you can get a date or currency value from n, and "VarType(n)" will return you false i.e. no errors. Best to check it's an integer or combine an integer-check with a check for nullity or emptiness i.e.

"if (VarType(n) <> 2) AND (Vartype<>0) then ..."

or what about:

"if VarType(n) <> 2 AND (VarType(n) =  (0 or 1)) then ..."
0
 

Author Comment

by:stanl
Comment Utility
You are confusing me.  I am just trying to take something that I know already works, either as function embedded in an Excel cell, or as the results of an OLEDB query, and obtain the same results with VBA.  Therefore I already know I am not going to get a date or currency value, hence I am only looking for an error.  Yes, both RANGE and REGION are ranges as well as column headings

RANGE   ....    REGION      UNITS
BOOKS           6101            100
BOOKS           6102              59
PETS              6101            110
CARS             6101             200
BOOKS            6101               47


Do I have to use either Eval() or Union() to get the conditional ranges?
0
 
LVL 1

Expert Comment

by:VosJ
Comment Utility
I think you have to replace the , with *

n = application.SUMPRODUCT((RANGE="BOOKS"),(REGION=6101),(UNITS))

like

n = application.SUMPRODUCT((RANGE="BOOKS")*(REGION=6101)*(UNITS))

0
 

Author Comment

by:stanl
Comment Utility
That Produces an "Unknown Runtime Error"
0
 
LVL 1

Expert Comment

by:VosJ
Comment Utility
I've tested it and this code gives me a good result.

Don't forget to adapt the code to the correct names and captions.

Dim myFormula As String

myFormula = "SumProduct((rangex=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"
           
n = Evaluate(myFormula)

Debug.Print n
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:stanl
Comment Utility
That gives a "type mismatch" error (I changed rangex to RANGE as that is the name used in the worksheet)
0
 
LVL 1

Expert Comment

by:VosJ
Comment Utility
You have to be carefull with the name 'range' because that is an already excisting word in Excel VBA.

You better should rename RANGE to something else.
0
 

Author Comment

by:stanl
Comment Utility
yes, I changed it to

myFormula = "SumProduct((rng=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"

and received "type mismatch"

I then passed the application object to my function and tried

myFormula = "application.SumProduct((rng=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"

which evaluated to a vartype of 10, or an error...

0
 
LVL 1

Expert Comment

by:VosJ
Comment Utility
I tested my code under Excel97 en Excel2000, and both gave the same result.

I used your small table as test.
rng        region    units
books      6101      100
books      6102      110
pets      6101      59
cars      6101      120
books      6101      47
*******
This is the complete code for the macro.
Sub macro1()

Dim myFormula As String

myFormula = "SumProduct((rng=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"
           
n = Evaluate(myFormula)


Debug.Print myFormula

Debug.Print n

End Sub

*******
And this is the result:

SumProduct((rng="books") * (region=6101) * (units))
 147

The only thing for the moment that I can think off is related to SumProduct itself. The columns used must have the same number of elements.
0
 

Author Comment

by:stanl
Comment Utility
I pasted your code into a new macro, made the necessary changes and ran it - nothing happened.

If you would like, I can send you the worksheet via email?
0
 
LVL 1

Expert Comment

by:VosJ
Comment Utility
Yes, feel free to send me the worksheet. The email-adres is jan@orlahdata.be.
0
 
LVL 1

Accepted Solution

by:
VosJ earned 250 total points
Comment Utility
I replied to your mail a few days ago. Apparently it did not arrive. I'm resending the email. If you don't receive the mail in the next hours, please let me know. Maybe there is somewhere in the line a firewall blocking xls-files.
0
 

Author Comment

by:stanl
Comment Utility
I received your revisions/macro; I guess my problem is I was trying to do it from within a .wsc file using VbScript, which uses eval() not evaluate(). But thanks again.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
countTriple  challenge 8 70
sumDigits challenge 9 95
while loop over for loop 7 79
Not needed 13 53
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
The purpose of this article is to demonstrate how we can use conditional statements using Python.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now