Solved

Stuck with VBA and SumProduct in Excel

Posted on 2004-04-07
16
698 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
ID: 10793446
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
ID: 10793471
What does "Region=6101" mean? Is there cell range called "Region", or "Region=6101", or "6101"?
0
 
LVL 1

Expert Comment

by:doubleglazing2
ID: 10793597
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 1

Expert Comment

by:doubleglazing2
ID: 10793641
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
ID: 10793960
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
ID: 10821591
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
ID: 10822322
That Produces an "Unknown Runtime Error"
0
 
LVL 1

Expert Comment

by:VosJ
ID: 10830921
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
 

Author Comment

by:stanl
ID: 10831993
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
ID: 10832886
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
ID: 10833350
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
ID: 10833548
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
ID: 10834307
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
ID: 10835154
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
ID: 10867590
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
ID: 10871655
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
isEverywhere  challenge 19 77
maxMirror challenge 10 145
Need Multiple Versions of Python Using Virutalenvwrapper-win and Virtualenv 5 59
Bartender Integration Builder 3 20
Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

809 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