Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2006
    Location
    California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read in/Write out Excel data in VBA (Excel 2003)

    I am extremely new to using VBA and am working on some sample codes. I am only trying to read data into VBA from my Excel sheet (name the range) and then sum the columns within VBA using a loop and then write the answers back out to the Excel sheet, but I want to do all the calculations within VBA. I've attached what I've been trying to do. Thanks!

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Read in/Write out Excel data in VBA (Excel 2003)

    Welcome to Woody's Lounge!

    You could do this using formulas, of course, and it is also possible to use Excel worksheet functions in VBA. Here is an example using Sum in VBA:

    Sub SumCol()
    Dim rng As Range
    Dim ColCnt As Integer
    Dim RowCnt As Integer

    Set rng = Range("A1:H9")

    For ColCnt = 1 To rng.Columns.Count
    rng.Cells(rng.Rows.Count + 1, ColCnt) = _
    Application.WorksheetFunction.Sum(rng.Columns(ColC nt))
    Next ColCnt
    End Sub

    and here is an example that performs the calculations in VBA:

    Sub SumCol()
    Dim rng As Range
    Dim ColCnt As Integer
    Dim RowCnt As Integer
    Dim dblSum As Double

    Set rng = Range("A1:H9")

    For ColCnt = 1 To rng.Columns.Count
    dblSum = 0
    For RowCnt = 1 To rng.Rows.Count
    dblSum = dblSum + rng.Cells(RowCnt, ColCnt)
    Next RowCnt
    rng.Cells(rng.Rows.Count + 1, ColCnt) = dblSum
    Next ColCnt
    End Sub

  4. #3
    New Lounger
    Join Date
    Dec 2006
    Location
    California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read in/Write out Excel data in VBA (Excel 2003)

    Thanks Hans!

    I think I'll be visiting this site frequently! I'm just getting use to the VBA language, so I may have more questions.

    Thanks again, that worked!

    Melissa

  5. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read in/Write out Excel data in VBA (Excel 2003)

    Welcome to the Lounge Albie!

    With regard to your code, there are a couple of things I can spot:

    If you want your procedure visible under Tools > Macro > Macros (Alt+F8) it will need to be a Sub, as opposed to a Function.

    You need to terminate each For loop with a Next statement:
    <pre>For ColCnt = 1 To 8
    SumCol(ColCnt, 1) = 0
    For RowCnt = 1 To 9
    SumCol(ColCnt, 1) = SumCol(ColCnt, 1) + Ary(RowCnt, ColCnt)
    Next RowCnt
    Next ColCnt
    </pre>


    "Ary" is an undefined function in the above. I suspect you want Cells(RowCnt, ColCnt).Value here.
    SumCol is meant to be a cumulative total, yes? If so it should be declared as:
    Dim SumCol As Long 'if that's the type you want

    and should not carry subscripts.

    Very often, in XL VBA, it's far more efficient to use worksheet functions than loops. Take a look at Excel VBA Loops: Correct/Efficient Uses of Excel Loops. Do, For Each and While Loops for some good examples. Using VBA (without the loops), your code would simply be:

    SumCol = Application.WorksheetFunction.Sum (Range("A1:H9"))

    Hope that gets you on your way.

    Alan

  6. #5
    New Lounger
    Join Date
    Dec 2006
    Location
    California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read in/Write out Excel data in VBA (Excel 2003)

    Thanks for the welcome Alan!

    I really appreciate everyone's responses. I believe this will be a great site for me to help gain more knowledge of VBA.

    It does seem that using the worksheet functions makes more sense.

    I'll take your suggestions and see what I can do with my small code. [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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