VBA code example

Example 1: vba collection

Sub TestCollection()
    ' Creation
    Dim myCol As New Collection
    ' Add items
    With myCol
        .Add "Something"
        .Add "Smth before", , 1     ' at first position
        myCol.Add "Blue", "color"
        .Add Item:=Date, Key:="Now"
        Debug.Print myCol.Count     ' > 4
    ' Get items (you can loop through Items collection)
        Debug.Print myCol(1)        ' > Smth before
        Debug.Print myCol("color")  ' > Blue
        Debug.Print .Item("Now")    ' > 26/03/2021
    ' Delete items
        .Remove (1)
        .Remove ("color")
    ' Clear
        Set myCol = New Collection
    End With
End Sub

Example 2: vba InStrB

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP"    ' String to search in.
SearchChar = "P"    ' Search for "P".

' A textual comparison starting at position 4. Returns 6.
MyPos = Instr(4, SearchString, SearchChar, 1)    

' A binary comparison starting at position 1. Returns 9.
MyPos = Instr(1, SearchString, SearchChar, 0)

' Comparison is binary by default (last argument is omitted).
MyPos = Instr(SearchString, SearchChar)    ' Returns 9.

MyPos = Instr(1, SearchString, "W")    ' Returns 0.

Example 3: excel vba >>>

'Many programming languages have a bitwise zero-fill right-shift operator: >>>
'VBA does not. However, it can be emulated in a performant function:

Public Function ShiftRightZeroFill&(ByVal n&, Optional ByVal shifts& = 1)
    Dim d&
    If shifts = 0 Then ShiftRightZeroFill = n: Exit Function
    If n And &H80000000 Then
        shifts = shifts - 1
        n = (n And &H7FFFFFFF) \ 2 Or &H40000000
    End If
    Select Case shifts
        Case 0:  d = n
        Case 1:  d = n \ 2&
        Case 2:  d = n \ 4&
        Case 3:  d = n \ 8&
        Case 4:  d = n \ 16&
        Case 5:  d = n \ 32&
        Case 6:  d = n \ 64&
        Case 7:  d = n \ 128&
        Case 8:  d = n \ 256&
        Case 9:  d = n \ 512&
        Case 10: d = n \ 1024&
        Case 11: d = n \ 2048&
        Case 12: d = n \ 4096&
        Case 13: d = n \ 8192&
        Case 14: d = n \ 16384&
        Case 15: d = n \ 32768
        Case 16: d = n \ 65536
        Case 17: d = n \ 262144
        Case 18: d = n \ 262144
        Case 19: d = n \ 524288
        Case 20: d = n \ 1048576
        Case 21: d = n \ 2097152
        Case 22: d = n \ 4194304
        Case 23: d = n \ 8388608
        Case 24: d = n \ 16777216
        Case 25: d = n \ 33554432
        Case 26: d = n \ 67108864
        Case 27: d = n \ 134217728
        Case 28: d = n \ 268435456
        Case 29: d = n \ 536870912
        Case 30: d = n \ 1073741824
        Case 31: d = &H0&
    End Select
    ShiftRightZeroFill = d
End Function
    
'----------------------------------------------------------------------------    
    
'Don't be off-put at the size of the function. This is many times faster than 
'any other VBA function that carries out bitwise zero-filled right-shfits.
'The hard-coded values are much faster than calculating with exponentiation.

MsgBox ShiftRightZeroFill&(-9, 2)		<--displays:  1073741821
    

'NB: Remember that VBA Longs are signed.

Example 4: vba with

With MyObject 
 .Height = 100 ' Same as MyObject.Height = 100. 
 .Caption = "Hello World" ' Same as MyObject.Caption = "Hello World". 
 With .Font 
  .Color = Red ' Same as MyObject.Font.Color = Red. 
  .Bold = True ' Same as MyObject.Font.Bold = True. 
 End With
End With

Example 5: vba >>>

'Many programming languages have a bitwise zero-fill right-shift operator: >>>
'VBA does not. However, it can be emulated in a performant function:

Public Function ShiftRightZeroFill&(ByVal n&, Optional ByVal shifts& = 1)
    Dim d&
    If shifts = 0 Then ShiftRightZeroFill = n: Exit Function
    If n And &H80000000 Then
        shifts = shifts - 1
        n = (n And &H7FFFFFFF) \ 2 Or &H40000000
    End If
    Select Case shifts
        Case 0:  d = n
        Case 1:  d = n \ 2&
        Case 2:  d = n \ 4&
        Case 3:  d = n \ 8&
        Case 4:  d = n \ 16&
        Case 5:  d = n \ 32&
        Case 6:  d = n \ 64&
        Case 7:  d = n \ 128&
        Case 8:  d = n \ 256&
        Case 9:  d = n \ 512&
        Case 10: d = n \ 1024&
        Case 11: d = n \ 2048&
        Case 12: d = n \ 4096&
        Case 13: d = n \ 8192&
        Case 14: d = n \ 16384&
        Case 15: d = n \ 32768
        Case 16: d = n \ 65536
        Case 17: d = n \ 262144
        Case 18: d = n \ 262144
        Case 19: d = n \ 524288
        Case 20: d = n \ 1048576
        Case 21: d = n \ 2097152
        Case 22: d = n \ 4194304
        Case 23: d = n \ 8388608
        Case 24: d = n \ 16777216
        Case 25: d = n \ 33554432
        Case 26: d = n \ 67108864
        Case 27: d = n \ 134217728
        Case 28: d = n \ 268435456
        Case 29: d = n \ 536870912
        Case 30: d = n \ 1073741824
        Case 31: d = &H0&
    End Select
    ShiftRightZeroFill = d
End Function
    
'----------------------------------------------------------------------------    
    
'Don't be off-put at the size of the function. This is many times faster than 
'any other VBA function that carries out bitwise zero-filled -right-shfits.
'The hard-coded values are much faster than calculating with exponentiation.

MsgBox ShiftRightZeroFill&(-9, 2)		<--displays:  1073741821
    

'NB: Remember that VBA Longs are signed.

Tags:

Vb Example

Recent Posts