General discussion

Locked

Excel VBA -Arrays

By naio ·
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?

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel VBA -Arrays

by naio In reply to Excel VBA -Arrays

Point value changed by question poster.

Collapse -

Excel VBA -Arrays

by sl-campbell In reply to Excel VBA -Arrays

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

Collapse -

Excel VBA -Arrays

by sl-campbell In reply to Excel VBA -Arrays

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

Collapse -

Excel VBA -Arrays

by sl-campbell In reply to Excel VBA -Arrays

Is this answered now?

Collapse -

Excel VBA -Arrays

by naio In reply to Excel VBA -Arrays

The question was auto-closed by TechRepublic

Collapse -

Excel VBA -Arrays

by classicmanpro In reply to Excel VBA -Arrays

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

Collapse -

Excel VBA -Arrays

by naio In reply to Excel VBA -Arrays

The question was auto-closed by TechRepublic

Collapse -

Excel VBA -Arrays

by naio In reply to Excel VBA -Arrays

This question was auto closed due to inactivity

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums