VBA101: A practical guide for VBA user

This post contains 2 parts: How to declare VBA Variables and How to write VBA “Functions”

How to declare VBA Variables

Normal Variables

Dim myVar As String
myVar = “Hello World”
Dim myObj As Object
Set myObj = anotherObj

Class” Variables

Private x As String
Sub myVar()
x = “Hello World”
End Sub

How to write VBA “Functions”

No parameter, No return

Sub MySub()
‘ do something
End Sub
MySub

Parameter, No return

Sub MySub(x As Integer)
‘ do something
End Sub
MySub myVar

More than one Parameter, No return

Sub MySub(x As Integer, y As Integer)
‘ do something
End Sub
MySub myVar, myAnotherVar

Optional Parameter, No return

Sub MySub(x As Integer, y As Integer, Optional z As Integer)
If IsMissing(z) Then
‘ do something
End If
End Sub
MySub myVar, myAnotherVar
MySub myVar, myAnotherVar, myOptionalVar

No parameter, Return

Function MySub() As String
‘ MySub is automatically returned
End Function
Str = MySub

Pass By Values/By Reference

Sub MySub(ByVal x As Integer, ByRef y As Integer)
‘ do something
End Sub
MySub myVar, myAnotherVar

Advertisement