excel vba make integer code example

Example 1: excel vba initialize entire array

'VBA function to create a 2D array with every element set 
'to the same initial value:

Function Init2D(rows, cols, Optional val)
    Dim i&, j&
    ReDim v(1 To rows, 1 To cols)
    If Not IsMissing(val) Then
        For i = 1 To rows
            For j = 1 To cols
                v(i, j) = val
            Next
        Next
    End If
    Init2D = v
End Function

'This version uses no loops:
Function Init2D(rows, cols, Optional val = "")
    Const NUMER = "index(offset(a1,,,ROWS,COLS)*0+VAL,,)"
    Const ALPHA = "index(rept("""",len(offset(a1,,,ROWS,COLS))<0)&""VAL"",,)"
    DoEvents
    Init2D = Evaluate(Replace(Replace(Replace(IIf(IsNumeric(val), NUMER, ALPHA), "VAL", val), "ROWS", rows), "COLS", cols))
End Function

'--------------------------------------------------------------------

'10 rows, 1 column, all elements = 0:
a = Init2D(10, 1, 0)

'20 rows, 5 columns, all elements = "abc":
a = Init2D(20, 5, "abc")

'--------------------------------------------------------------------

'1D array version:
Function Init1D(elems, Optional val, Optional base = 1)
    Dim i&, max&
    max = base + elems - 1 
    ReDim v(base To max)
    If Not IsMissing(val) Then
        For i = base To max
            v(i) = val
        Next
    End If
    Init1D = v
End Function

Example 2: excel vba make integer

Public Function MakeInteger%(LoByte As Byte, HiByte As Byte)
  If HiByte And &H80 Then
    MakeInteger = ((HiByte * &H100&) Or LoByte) Or &HFFFF0000
  Else
    MakeInteger = (HiByte * &H100) Or LoByte
  End If
End Function

Example 3: excel vba create an array from a range

v = [a1:b20]

'If v is dimensioned as a variant then he above creates a 2d array, 
'20 rows high by 2 columns wide and those 40 array elements contain
'the values from the specified range.

'The square brackets are shorthand notation. Another way to code the
'same thing is:

v = Range("a1:b20")

'In both of the above examples, the Range object is returning its 
'default... the Range.Value property. But keep in mind that the
'Range.Value2 property is about 20% quicker. So it could be more
'performant to code these two examples like so:

v = [a1:b20].Value2
v = Range("a1:b20").Value2