Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •