Thread: Formula calculations in VBA (Excel 2003)

1. Formula calculations in VBA (Excel 2003)

I'm trying to figure out how to write some pretty complex sumproduct formulas with multiple criteria in my VBA and right now the only way I can think to do it is to actually have the VBA write the formulas in a cell using the Range.Formula = "Formula here" setup. What I would like to do is to be able to have the sumproduct formulas calculate and assign the value to a variable that I can just place in a cell. I'm having trouble figureing out how to calulate these sumproduct formulas in the VBA so I can just assign the value to a cell instead of the whole formula.

A secondary question to this is if there is another option to calculate these sumproduct formuls in my VBA. Will I need to replace the name ranges with the actual range or can the VBA still understand the name ranges without me declaring them?

What I'm seeing by my current approach is the formulas are being written but the VBA is taking way to long to run. Any suggestions to a more effecient approach would be greatly appreciated. Thanks!!

2. Re: Formula calculations in VBA (Excel 2003)

You can use Application.WorksheetFunction.SumProduct to calculate the result of a SUMPRODUCT formula in VBA. The arguments have to be specified the VBA way. Here is a simple example:

Range("D1").Value = Application.WorksheetFunction.SumProduct(Range("A1 :A5"), Range("B1:B5"))

The value of cell D1 is set to the result of the formula =SUMPRODUCT(A1:A5,B1:B5)

Another way to do this would be the following:

Range("D1").Formula = "=SUMPRODUCT(A1:A5,B1:B5)"
Range("D1").Value = Range("D1").Value

Here, we actually set the formula for cell D1, but then replace it with its result.

3. Re: Formula calculations in VBA (Excel 2003)

We would have to know much more details about the structure and what you are trying to do

In general, formulas are much more efficient than VB. Array formulas (both explicit - ones confirmed with ctrl-shift-enter and implicit - like SUMPRODUCT) can make the calculations very sluggish since each formul may contain hundreds if not thousands of individual calculations and they often have hundreds if not thousands of them in the worksheet, but also since they are all dependent on many cells, must ALL be recalculated for nearly any change within the worksheet.

VB can be used to calculate it more efficiently (array formulas tend to be a more "brute force" methodology) by saving intermediate calculations and going through a list once or twice instead of the hundreds or thousands of times that the arrays may require doing it

Steve

4. Re: Formula calculations in VBA (Excel 2003)

Hans,

Thank you for the suggestion. I think this is what I was looking for. I'll give the Application.WorksheetFunction.SumProduct command a try and see if this helps to reduce the time it takes the VBA to run.

Many thanks!!

5. Re: Formula calculations in VBA (Excel 2003)

Steve,

Thanks for the response. Of course I understand the need for more information but I tried to leave it vague as I'm trying to over haul the entire structure and find a new way around the numerous sluggish sumproduct formulas. The suggestion Hans mention sounds like it may be the way to go for me as it will give me the ability to get rid of the formulas that constantly have to recalculate each time something changes in the sheet.

Thanks again for the response. I do appreciate it!

6. Re: Formula calculations in VBA (Excel 2003)

Hans,

Question for you on the application.worksheetfunction.sumproduct approach. Will this approach allow me to set the formula up as and array that searches through columns of data for certain criteria? For example, the code below is what I thought I would need for this. This should just return a count of dates in the range \$A\$2:\$A\$1048 that are greater than equal to the first of the month and less than equal to the current date. For some reason this does not seem to work. Is it due to this command not working this way in the VBA or is it due to some missing syntax that I have left out. Please let me know your thoughts.

FM = Worksheets("SD").Cells(3, 2).Value ' Value is equal to the first of the month
CD = Worksheets("SD").Cells(1, 2).Value ' Value is equal to current date

Range("I5").Value = Application.WorksheetFunction.SumProduct((Range("\$ A\$2:\$A\$1048") <= CD) * (Range("\$A\$2:\$A\$1048") >= FM))

7. Re: Formula calculations in VBA (Excel 2003)

Unfortunately, the WorksheetFunction methods are much more limited than their formula counterparts. They don't handle array arguments well. the second method that I described will get around this:
<code>
Range("I5").Formula = "=SumProduct((\$A\$2:\$A\$1048<=SD!B1)*(\$A\$2:\$A\$1048>= SD!B3))"
Range("I5").Value = Range("I5").Value</code>

8. Re: Formula calculations in VBA (Excel 2003)

You can also use Evaluate in code to evaluate the criteria form of sumproduct formulas, but I'm not sure there's any benefit.
FWIW.

9. Re: Formula calculations in VBA (Excel 2003)

That's what I suspected when I couldn't get it to work. Thanks again for your help!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•