we can create, in fact, multi dimensional tables, but mostly we can meet one or two dimensionals. General method is to assign a value beginning with 0. If we want to declare value starting with 1 we need to use a special fraze: Option Base 1 .
Also there’s no need to declare variable at the beginning, we can do it after.
Let’s write some simple codes :
1.simple example for one-dimensional
'simple example for one-dimensional
Sub TableEx1()
'declare data type for one-dimensional table
Dim Tbl(3) As String
'declare figures and the meaning
Tbl(0) = "Quarter 1"
Tbl(1) = "Quarter 2"
Tbl(2) = "Quarter 3"
Tbl(3) = "Quarter 4"
'use in practise
MsgBox Tbl(0)
End Sub
'--------------------------------------------
'three-dimensional for date format dd-mm-yyyy beginning with 1
Sub TableEx2()
'declare data type for three-dimensional table
Option Base 1
Dim Tbl(1 To 31, 1 To 12, 1950 To 2000) As Variant
Tbl(28, 10, 1989) = InputBox("What was the name of your birthday? _
Put birthdate x,x,x")
Tbl(28, 10, 1989) = WeekdayName(2, False, vbMonday)
'use in practise
MsgBox Tbl(28, 10, 1989)
End Sub
2.Two-dimensional
'for rows and columns
Sub TableEx3()
'declare data type for two-dimensional table
Dim Tbl(0, 1) As Variant
Dim x As Integer
Dim y As Integer
Tbl(0, 0) = "Header"
Tbl(0, 1) = 12345
Range("A1") = Tbl(0, 0)
Range("A2") = Tbl(0, 1)
End Sub
3.Declaring the variable after
Sub TableEx4()
Dim Tbl()
ReDim Tbl(1 To 12, 6) ' declare as two-dimensional
ReDim Tbl(1 To 50, 10, 2015 To 2030) 'declare as three-dimensional
End Sub
4. Array & Loop
Sub Looparray()
Dim numbers(1 To 3) As Integer
For i = 1 To 3
numbers(i) = InputBox("Podaj liczbę nr " & i)
Next i
For Each Number In numbers
MsgBox Number
Next
End Sub
5. Function Array, Lbound, Ubound
Sub TableEx5()
Dim table As Variant
table = Array(1, 2, 3, 4, 5)
Dim size As Integer
'size = UBound(table, 1) 'result is 4
'size = UBound(table, 1) - LBound(table, 1) + 1 'result is 5
'size = UBound(table, 1) - LBound(table, 1) 'result is 4
size = UBound(table, 1) + 1 - LBound(table, 1) 'result is 5
MsgBox size
End Sub
6. Redim Preserve
Sub ArrayEX1()
Dim numbers() As Integer
Dim size, i As Integer
size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
ReDim numbers(size)
For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i
MsgBox numbers(size)
ReDim Preserve numbers(3)
MsgBox numbers(1)
End Sub
Heart4DataScience 