Wrox Programmer Forums
BOOK: Excel 2000/2002 VBA Programmer's Reference
This is the forum to discuss the Wrox book Excel 2000 VBA: Programmers Reference by John Green, Stephen Bullen, Felipe Martins, Brian Johnson; ISBN: 9780764544019
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2000/2002 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 18th, 2008, 06:01 PM
Registered User
Join Date: Jan 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 2-Dimensional Array - Excel 2003 VBA

I am trying to improve my coding skills in Excel VBA and I just can't seem to find a good, very basic, example of how to use a 2-dimensional array. I've looked in several reference books & browsed the Forum but can't find an example I can understand. What I was trying to do was define a 3 x 4 Array that has a numeric-Single data type and is located in Cells E1:H3.
    E F G H
1 3.4 8.1 11.6 100.3
2 5.2 7.3 45.2 632.4
3 1.7 9.6 39.7 161.5

I've been trying to use the LBound and UBound functions too, thinking this might be the best approach but none of my coding attempts work. That is why I did not include any.

I would like to be able, using code, see what is in each Array element and then be able to Sum the values and see the results in a variable.

I was able to create a 1-dimensional Array, shown below, but it is pretty basic.

Sub SampleArray()
    Dim Data(9) As Single
    Dim i As Integer
    Dim cum As Single

    For i = LBound(Data) To UBound(Data)
        Data(i) = Cells(i, 1)
        MsgBox prompt:=Data(i)
        cum = cum + Data(i)
        MsgBox prompt:=cum
    Next i
End Sub

I would appreciate any help submitted. Thanks

Old February 21st, 2008, 11:54 AM
Registered User
Join Date: Feb 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

 Sub SampleArray()
Dim Data(1 To 3, 1 To 4)
Dim Sum As Single
Dim iRow As Integer, iCol As Integer

    For iRow = LBound(Data, 1) To UBound(Data, 1)
        For iCol = LBound(Data, 2) To UBound(Data, 2)
            Data(iRow, iCol) = Cells(iRow, iCol)
            MsgBox "Data(" & iRow & "," & iCol & ") = " & Data(iRow, iCol)
            Sum = Sum + Data(iRow, iCol)
            MsgBox "Sum = " & Sum
        Next iCol
    Next iRow

    MsgBox "The final Sum = " & Sum

End Sub

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 VBA - Need to open a workbook in a fold bablhous BOOK: Excel 2003 VBA Programmer's Reference 0 December 21st, 2006 02:50 PM
Excel 2003 VBA Function RollingWoodFarm Excel VBA 15 August 2nd, 2006 04:24 PM
FTP Upload from Excel 2003 VBA Jersey Eric Excel VBA 3 January 5th, 2006 09:29 AM
excel 2003 vba book xeugx Excel VBA 3 June 27th, 2005 11:35 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.