Web Development

Ask something
Discussion Locked

Excel VBA -Arrays

0 helpful
8 replies

by naio

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am developing a program which requires-
1: Calculate the value of various variables
2: Create an array and store the values of these variables in a specifed location in the array

3: use the array to carry out further calcs.

i have createdthe procedure to read the values into the array...however i wish to debug this array to ensure the correct value is stored in the correct location...

How can i do this? Is there any way i can print the entire array on the screen?

8 replies

by sl-campbell

Just create a Sub Proceedure and loop through the elements of the Array, calling Debug.Print myArray (x,y) (for a 2 dimensional array - myArray x for a one dimensional array) at each element and check the results in the Test window.

Something like:

Public Sub DebugArray()

Dim myArray(3, 2) As String
Dim x As Integer
Dim y As Integer

myArray(1, 1) = "Test1"
myArray(2, 2) = "Test2"

For x = 0 To UBound(myArray, 1)
For y = 0 To UBound(myArray, 2)
Debug.Print "x: " & x; " y: " & y; " Value: " & myArray(x, y)
Next y
Next x
End Sub

by sl-campbell

My example is for a 2D array, and it could be addapted for a 1D to n Array but I guess I will now be more specific, showing how to use a 1D and a 2D, and for more dimesions you will need to increase the amount of loops proportionally in the 2D example. Copy the below 3 Subs and just call the TestArray

Public Sub TestArray()
Dim my1DArray(3) As String
Dim my2DArray(3, 4) As String

my1DArray(0) = "First item"
my1DArray(3) = "Fourth item"

Debug_1DArray my1DArray

my2DArray(1, 1) = "Test1"
my2DArray(2, 2) = "Test2"
my2DArray(3, 3) = "Test3"

Debug_2DArray my2DArray

End Sub

Public Sub Debug_1DArray(ByVal myArray As Variant)

Dim vItem As Variant
Dim iCnt As Long

On Error GoTo ErrDone

iCnt = LBound(myArray)

For Each vItem In myArray
Debug.Print "Item " & iCnt; " Value: " & vItem
iCnt = iCnt + 1
Next vItem

ExitProceedure:
Exit Sub

ErrDone:
GoTo ExitProceedure
End Sub

Public Sub Debug_2DArray(ByVal myArray As Variant)
Dim x As Long
Dim y As Long

On Error GoTo ErrDone

'Yes, I know, using LBound is better than using 0

For x = LBound(myArray, 1) To UBound(myArray, 1)
For y = LBound(myArray, 2) To UBound(myArray, 2)
Debug.Print "x: " & x; " y: " & y; " Value: " & myArray(x, y)
Next y
Next x

ExitProceedure:
Exit Sub

ErrDone:
GoTo ExitProceedure
End Sub

by classicmanpro

The following code will loop through the array and will note the array's number and value.
This code will work for 1D arrays but I'm not sure it will work for 2D and 3D arrays.(Testing makes practice)
--------------------------
Public Sub WriteArray()

Dim A as Integer
Dim strMessage as String

For A=Lbound(yourArray) to Ubound(yourArray)
strMessage=strMessage & A & " - " yourArray(A) & vbCrLf
Next A

MsgBox strMessage ' this string can be assigned to a text box for better viewing

End Sub

This discussion is closed to new replies.