Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    VBA instead of formula

    I'm doing some "code culling" with my current project.
    In some cases where I required code for a temporary calculation, I would use a formula, record macro of that formula, then use the macro to do the same calculation, then use another line of code to keep the Sum, but remove the formula because it's no longer required within that particular Loop.

    For example,

    Sub sumthis ()
    ' lines of code
    Sheets("test1").Range("D1").FormulaR1C1 = "=SUM(R4C9:R28C9)" ' adds formula
    Sheets("test1").Range("D1").Value = Sheets("test1").Range("D1").Value ' leaves Sum but no formula

    'more lines of code

    End sub
    actual formula: =SUM($I$4:$I$28)

    Is there a way of writing the code: FormulaR1C1 = "=SUM(R4C9:R28C9)"
    so it sums that range/column and have the result only in cell D1 so I can remove 1 line of code?




    Thanks

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    Perhaps you can use something like:
    [D1] = Application.Sum("i4:i28")

    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    zeddy.
    On test it showed in cell D1; #VALUE!

    test macro was

    Code:
    Sub testaddintion()
    
    [D1] = Application.Sum("i4:i27")
    
    End Sub

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    OOps!

    Try:
    [D1] = Application.Sum(Range("i4:i27"))

    zeddy

  5. #5
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks zeddy, that worked,
    but, it now needs to get "specific"

    I tried improvising to make it work like;

    test code
    Code:
    Sub testaddintion()
    
    Sheets("Sheet1").Range("D1").ClearContents ' used for testing
    [D1] = Application.Sum(Range("i4:i27")) ' works OK
    
    '---------------------------------------------------------
    
    Sheets("Sheet1").Range("D1").ClearContents ' used for testing
    
    ' to be specific
    Sheets("Sheet1").Range("D1") = Application.Sum(Range(Sheets("Sheet1").Range("I4:I27")))
    
    End Sub
    There is no syntax error, but it does score a,
    Run time error 9. subscript out of range.


    Can the code be so it's specific for Sheet1 and Cell Range ?

    Thanks

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    The code can be specific to another opened workbook if you really want!
    e.g.
    'Set zSource = Workbooks(zDataFilename).Sheets(1) 'use first sheet in datafile
    'Set zDest = Workbooks(zMergeFile).Sheets("Stock") '<<adjust sheetname if required!

    In your case, I think you could use this:

    Set zDest = sheets("Sheet1")
    Set zSource = sheets("Sheet1")

    zDest.[D1] = Application.Sum(zSource.Range("i4:i27"))

    ..assuming you have a sheet named "Sheet1" that is. If this named sheet doesn't exist, that will give you a Run time error 9. subscript out of range.

    zeddy
    Last edited by zeddy; 2013-08-02 at 10:49.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    And expect a reply from RG shortly.

    zeddy

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Alan,

    Minor syntax adjustment:
    Code:
    Sub TestAddition()
    
    Sheets("Sheet1").Range("D1") = Application.Sum(Range("Sheet1!I4:I27"))
    
    End Sub
    This also works:
    Code:
    Sub TestAddition()
    
    Sheets("Sheet1").Range("D1") = WorksheetFunction.Sum(Range("Sheet1!I4:I27"))
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks RG and zeddy

    from the smorgasbord of options,
    Code:
    Sub medium_rare()
    
    Sheets("Sheet1").Range("D1") = Application.Sum(Range("Sheet1!I4:I27"))
    
    End Sub
    will do for now.

    zeddy, your option will be applied when I get to that phase probably sooner than later cos this project is nearing the "split the workbooks" phase.

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    Just to further add to the mix, you can use Sheets("Sheet1") to refer to a specifically named sheet, or, in my opinion, a nice trick is to directly use the vba code name for the sheet e.g.
    Sheet1.Range("D1")
    ..this means the User can change that particular tab sheet from "Sheet1" to "Fred", but the vba code name doesn't change, i.e. it is still Sheet1, so you don't need to update the vba code.
    You can also easily change the vba code sheet name for any Sheet, using the vba properties window (F4) and selecting that particular sheet in the Project Explorer window (Ctrl+R)

    The reason you might want to do this is if you have say, Spanish versions of your file. You can have different (Spanish) tab names, but the code doesn't need changing.

    Just thought you might like to know that.

    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2013-08-02)

  12. #11
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Just for the sake of statistical trivia, this solution has eliminated 205 characters, ( including spaces within the syntax) of code.

    Multiply 205 on average by a conservative 10 = 2050 parts of VBA space, or "excess code weight" being culled.
    When the workbook is zipped and saved as a backup, currently it's 356KB

  13. #12
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Just thought you might like to know that.

    zeddy
    yes, good point....

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    ..have you tried saving it in .xlsb format????
    What size is it as .xlsb???
    I reckon it would be smaller, or at least comparable, to your zipped size.
    The .xlsb format is my preferred Excel filetype.
    You can include macros in .xlsb files.
    Smaller file size = faster loading on networks

    zeddy

  15. #14
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    zeddy,
    Never heard of xlsb format. (when googled)
    results
    ╭──────────────╥────
    │ ║ .xlsx │ .xlsb │
    ╞══════════════╬═════
    │ loading time ║ 165s │ 43s │
    ├──────────────╫───────
    │ saving time ║ 115s │ 61s │
    ├──────────────╫───────
    │ file size ║ 91 MB │ 65 MB │
    ╰──────────────╨───────
    hmm, something to consider
    Thanks
    XPDH

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Another abbreviated solution would be:
    Code:
    [d1] = WorksheetFunction.Sum([c2:c6])

Page 1 of 3 123 LastLast

Posting Permissions

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