|
-------------------------
<< VBA FUNDAMENTALS
-------------------------
' MODULE-LEVEL DECLARATIONS:
Option Explicit ' required variable declarations
Option Base 1 ' or 0 array lower bound by default
Option Private Module
' -------------------------------------------------------------------------------------------------
Private Enum MyEnum ' or <Public> by default
A = 1
B = 2
C = 3
End Enum
Public Const T As Date = #6/26/2026 20:26:06# ' or <Private> by default
Const I As Integer = 6, S As String = "abc" ' variable-length String
Public lngL As Long ' or <Dim>/<Private> by default
Dim sgnS As Single, dblD As Double ' implicit conversions, non-strict typing
Dim strS As String * 60 ' fixed-length string up to 60 chars max
Dim objO As Object, varV As Variant ' variant by default without data type
Dim bytArray(5, 1 To 6) As Byte ' 6x6, 2dim array
Dim curArray() As Currency ' dynamic (multi-dimensional) array
' -------------------------------------------------------------------------------------------------
' PROCEDURE-LEVEL:
' or <Public> by default, or <ByVal> by default, <Optional> argument default assignment
Private Sub MySub1(ByRef intI As Integer, Optional strS As String = "abc")
' ...
Static blnB As Boolean ' retain value in procedure, no reset with <Static>
' ...
' <ReDim> array with confined previous values
ReDim Preserve lngArray(6, 6, 6) ' with <Preserve> only last dim size/upper bound dynamic
LBound(lngArray, 1) <= 0 ' lower bound dim1 subscript
UBound(lngArray, 3) = 6 ' upper bound dim3 subscript
' ...
Call MySub1(intI, strS) ' recursive call
' ...
End Sub
' or <Public> by default, custom UDF
Private Function MyFunc(ByVal curC As Currency) As Byte
' ...
MyFunc = ' not necessary to run VBA through macro
' ...
End Function
' declared variables retain value in <Static> procedure, code line breaker <_>
Static MySub2(ByRef objO As Object, _
Optional ByVal varV As Variant)
' ...
Dim intI As Integer ' static, no reset
IsMissing(varV) ' or <IsError> condition, only for variant optionality
varV = CDec(1.6) ' decimal subtype
' ...
Goto MyLabel ' code jump to label (non-structured programming)
' ...
Exit Sub ' procedure exit point
MyLabel: ' label
' ...
End Sub
' -------------------------------------------------------------------------------------------------
If blnCond Then MySub1 Else MySub2
If blnCond1 Then ' logical operators: <Not, And, Or, Xor, Imp, Eqv>
' ...
ElseIf blnCond2 ' or <If-Then-Else> nested in <Else>
' ...
Else
' ...
End If
Select Case intCond
Case 1 : MySub1
Case 2 : MySub2
Case Else : MySub3
End Select
Select Case intCond
Case 1, 2, 3
' ...
Case 4 To 6
' ...
Case Is >= 10
' ...
Case Else ' 7, 8, 9
' ...
End Select
' -------------------------------------------------------------------------------------------------
While blnCond ' comparative operators: =, <>, >, >=, ...
' ...
Wend
Do While blnCond ' or <Until> condition
' ...
Exit Do ' code flow control structure exit point
' ...
Loop
Do
' ...
Exit Do ' exit point
' ...
Loop Until blnCond ' or <While> condition
For intCnt = 1 To 6 Step 1
' ...
Exit For ' exit point
' ...
Next intCnt
For Each objCnt in objCollect
' ...
Exit For ' exit point
' ...
Next objCnt
' -------------------------------------------------------------------------------------------------
strS = TypeName(varV) ' variable/object type name
intI = &HFF ' 255 hexadecimal assignment
intI = &O77 ' 63 octal assignment
lngL = RGB(255,255,255) ' VBA color code
lngL = FileLen(strFilePath) ' file size in bytes
' -------------------------------------------------------------------------------------------------
Rem comments
varV = MsgBox(Prompt:="Hello",Title:="Message") ' message box function, named arguments
Debug.Print strS ' or <? strS> in immediate window
Stop ' enter break mode
End ' project memory reset
------------------------------------------
<< OBJECT-ORIENTED PROGRAMMING (OOP)
------------------------------------------
Dim objO As Object ' declare
With objO
.Property
.Method
End With
Set objO = Nothing ' deassign
objO Is Nothing ' uninitialized/deassigned object condition
------------------
<< EXCEL VBA
------------------
' <AUTO_OPEN> named sub in standard module runs when workbook opens (deprecated method)
Sub AUTO_OPEN()
' ...
End Sub
strS = Application.UserName ' user account
strS = Application.Path ' Excel application folder path
Application.DisplayAlerts = False ' remove alert messages
IsEmpty(varV) ' uninitialized variant condition
varV = Empty ' Excel empty cell, variant type
Empty = "" And Empty = 0
6 + Empty = 6
"abc" & Empty = "abc"
' -------------------------------------------------------------------------------------------------
' workbooks collection, lower bound 1
objWkBk = Application.Workbooks("Book1.xlsx")
Workbook("Book1.xlsx").Activate
objWkBk = ActiveWorkbook
' sheets collection (work + chart)
objWkSht = Application.Workbooks(1).Worksheets("Sheet1")
objChrSht = Application.Workbooks(1).Charts("Chart1")
objSht = Application.Workbooks(1).Sheets("Sheet1")
Sheets("Sheet1").Activate
objSht = ActiveSheet
' range, fully qualified object reference
objRng = Application.Workbooks(1).Worksheets(1).Range("A1")
objRng = Cells(6,6).Offset(-5,-5)
objRng = ActiveCell
objRng = Cells ' all cells
objRng = Activesheet.UsedRange ' confined used cell rectangle area
objRng = Range("6:6") ' row
objRng = Rows(6)
objRng = Range("F:F") ' column
objRng = Columns(6)
Range("A1:C3,D4:F6").Select ' noncontiguous ranges
objRng = Selection
' -------------------------------------------------------------------------------------------------
' Excel worksheet built-in function
dblD = Application.WorksheetFunction.Sum(Range("A1:A2")
dtmD = VBA.Now ' VBA built-in function
-------------------
<< ACCESS VBA
-------------------
' <AUTOEXEC> named macro runs when Access opens, run VBA function through <RunCode> in macro
Option Compare Database ' same rules as DB, or <Binary> or <Text> local
IsNull(varV) ' undefined data variant condition
varV = Null ' Access empty table field/form control, variant type
6 + Null = Null
"abc" & Null = "abc"
Empty & Null = Empty
IsNull(Null <> Empty) ' third boolean Null (exception) case condition
' <!> operator passes after string argument to before object default class member
objCtr = Forms!MyForm!MyControl
objCtr = Forms("MyForm").Controls("MyControl") ' default member reference shortcut for
objCtr = Forms.Item("MyForm").Controls.Item("MyControl")
|
