VBA Programming Language

Welcome to VBA

Attribute VB_Name = "D01_WelCome2VBA"
'---
' Module    : D01_WelCome2VBA
' Purpose   :
'---

Option Explicit

'---
' Procedure : HelloVBA
' Purpose   :
'---
Public Sub HelloVBA()
    Debug.Print "Hello VBA"
End Sub

'---
' Procedure : HelloVBA2
' Purpose   :
'---
Sub HelloVBA2()
    Debug.Print "Hello VBA2"
End Sub

Variables

Attribute VB_Name = "D02_VariablesExplicit"
'---
' Module    : D02_VariablesExplicit
' Purpose   :
'---

Option Explicit

'---
' Procedure : DoVariablesExplicit
' Purpose   :
'---
Public Sub DoVariablesExplicit()
    Dim FullName As String
    Dim Age As Long
    Dim DateOfBirth As Date
    Dim Salary As Double
    
    FullName = "John Doe"
    DateOfBirth = #1/3/1970#
    Age = 50
    Salary = 5000#
    
    Debug.Print FullName
    Debug.Print DateOfBirth
    Debug.Print Age
    Debug.Print Salary
    
End Sub
Attribute VB_Name = "D03_VariablesImplicit"
'---
' Module    : D03_VariablesImplicit
' Purpose   :
'---

'Option Explicit

'---
' Procedure : DoVariablesImplicit
' Purpose   :
'---
Public Sub DoVariablesImplicit()
'    Dim FullName As String
'    Dim Age As Integer
'    Dim DateOfBirth As Date
    
    FullName = "John Doe"
    DateOfBirth = #1/3/1970#
    Age = 50
    
    Debug.Print FullName
    Debug.Print DateOfBirth
    Debug.Print Age
End Sub

Local Variables

Attribute VB_Name = "D04_LocalVars"
'---
' Module    : D04_LocalVars
' Purpose   :
'---

Option Explicit

'---
' Procedure : DoLocalVars1
' Purpose   :
'---
Sub DoLocalVars1()
    Dim num As Long
    Dim str As String
    Dim dt As Date
    
    num = 5
    str = "Hello"
    dt = #12/31/1970#
    
    Debug.Print num, str, dt
End Sub

'---
' Procedure : DoLocalVars2
' Purpose   :
'---
Sub DoLocalVars2()
    Dim num As Long, str As String, dt As Date
    
    num = 5
    str = "Hello"
    dt = #12/31/1970#
    
    Debug.Print num, str, dt
End Sub

'---
' Procedure : DoLocalVars3
' Purpose   :
'---
Sub DoLocalVars3()
    Dim num1, num2, num3 As Long
    
    num1 = 5
    num2 = 10
    num3 = 15
    
    Debug.Print num1, num2, num3
    
End Sub

'---
' Procedure : DoDateTime
' Purpose   :
'---
Sub DoDateTime()
    Dim Today As Date
    Dim StartTime As Date
    Const FirstDay As Date = #12/31/2017#
    Const Noon = #12:00:00 PM#
    
    Debug.Print FirstDay
    Debug.Print Noon
End Sub

'---
' Procedure : DoConcat
' Purpose   :
'---
Sub DoConcat()
    Dim str1 As String, str2 As String
    Dim num1 As Long, num2 As Long
    
    str1 = "Hello "
    str2 = "World"
    Debug.Print str1 + str2
    Debug.Print str1 & str2
    
    num1 = 10
    num2 = 20
    Debug.Print num1 & num2
End Sub

Module Variables

Attribute VB_Name = "D05_ModuleVar"
'---
' Module    : D05_ModuleVar
' Purpose   :
'---

Option Explicit

Dim m_num As Long

'---
' Procedure : DoModVar1
' Purpose   :
'---
Public Sub DoModVar1()
    m_num = 100
    Debug.Print "m_num = " & m_num
End Sub

'---
' Procedure : DoModVar2
' Purpose   :
'---
Public Sub DoModVar2()
    m_num = 200
    Debug.Print "m_num = " & m_num
End Sub

Global Variables

Attribute VB_Name = "D06_Globals"
'---
' Module    : D06_Globals
' Purpose   :
'---

Option Explicit

Public g_num As Long

'---
' Procedure : SetGlobals
' Purpose   :
'---
Public Sub SetGlobals()
    g_num = 1000
End Sub

Attribute VB_Name = "D06_GlobalVar"
'---
' Module    : D06_GlobalVar
' Purpose   :
'---

Option Explicit

'---
' Procedure : DoGlobalVar
' Purpose   :
'---
Public Sub DoGlobalVar()
    SetGlobals
    Debug.Print "g_num = " & g_num
End Sub

Constants

Attribute VB_Name = "D07_Constants"
'---
' Module    : D07_Constants
' Purpose   :
'---

Option Explicit

Private Const m_constant = 5000
Public Const g_constant = 10000

'---
' Procedure : DoConstant
' Purpose   :
'---
Sub DoConstant()
    Debug.Print m_constant
    Debug.Print g_constant
    
'    m_constant = 6000

End Sub

Utilities

Attribute VB_Name = "D08_Util"
'---
' Module    : D08_Util
' Purpose   :
'---

'---
' Procedure : AddTwoNum
' Purpose   :
'---
Function AddTwoNum(num1, num2)
    AddTwoNum = num1 + num2
End Function

'---
' Procedure : DoAddTwoNum
' Purpose   :
'---
Sub DoAddTwoNum()
    Debug.Print AddTwoNum(2, 5)
End Sub

If… Then… End If

Attribute VB_Name = "D09_IfEndIf"
'---
' Module    : D09_IfEndIf
' Purpose   :
'---

Option Explicit

'---
' Procedure : DoPosOrNeg1
' Purpose   :
'---
Sub DoPosOrNeg1()
    Dim any_num As Long
    
    If any_num > 0 Then
        Debug.Print "Positive number"
    ElseIf any_num = 0 Then
        Debug.Print "Number equal to 0"
    Else
        Debug.Print "Negative number"
    End If
End Sub

'---
' Procedure : DoPosOrNeg2
' Purpose   :
'---
Sub DoPosOrNeg2()
    Dim any_num As Long
    
    If any_num >= 0 Then
        If any_num = 0 Then
            Debug.Print "Number equal to 0"
        Else
            Debug.Print "Positive number"
        End If
    Else
        Debug.Print "Negative number"
    End If
End Sub

'---
' Procedure : EvenOrOdd
' Purpose   :
'---
Public Sub EvenOrOdd()
    Dim num As Long
    
    If num Mod 2 = 0 Then
        Debug.Print num & " is even"
    Else
        Debug.Print num & " is odd"
    End If
    
End Sub

'---
' Procedure : FindLargest
' Purpose   :
'---
Public Sub FindLargest()
    Dim num1 As Long, num2 As Long, num3 As Long, largest As Long
    
    If (num1 >= num2) And (num1 >= num3) Then
        largest = num1
    ElseIf (num2 >= num1) And (num2 >= num3) Then
        largest = num2
    Else
        largest = num3
    End If
    
    Debug.Print "The largest number among " & num1 & ", " & num2 & " and " & num3 & " is " & largest
End Sub


'---
' Procedure : CheckBoolean
' Purpose   :
'---
Public Sub CheckBoolean()
    Dim a As Long, b As Long, BoolVal As Boolean
    
    BoolVal = a < b
    If BoolVal Then
        Debug.Print "BoolVal is true"
    Else
        Debug.Print "BoolVal is false"
    End If
    
End Sub

Select Case… End Select

Attribute VB_Name = "D10_SelectCase"
'---
' Module    : D10_SelectCase
' Purpose   :
'---

Option Explicit

'---
' Procedure : CalCIncomeTax
' Purpose   :
'---
Public Sub CalCIncomeTax()
    
    Dim gross_sal As Double, tax_pct As Double, tax_amt As Double
    
    Debug.Print "You have entered gross salary = " & gross_sal

    Select Case gross_sal
        Case Is = 0
            Debug.Print "Salary is 0 !"
            Debug.Print "Unable to process... exiting"
        Case Is <= 100000
            Debug.Print "Salary less than or equal to 100000"
            Debug.Print "Income tax not applicable"
        Case 100001 To 500000
            Debug.Print "Income tax slab A"
            tax_pct = 0.1
        Case 500001 To 700000
            Debug.Print "Income tax slab B"
            tax_pct = 0.2
        Case 700001 To 1000000
            Debug.Print "Income tax slab C"
            tax_pct = 0.3
        Case Else
            Debug.Print "Income tax slab D"
            tax_pct = 0.35
    End Select
    
    tax_amt = gross_sal * tax_pct
    Debug.Print "Income tax amount = " & tax_amt

End Sub

Loops

Attribute VB_Name = "D11_Loops"
'---
' Module    : D11_Loops
' Purpose   :
'---

Option Explicit

'---
' Procedure : DemoForNext
' Purpose   :
'---
Public Sub DemoForNext()
    Dim i As Long
    
    For i = 0 To 20 Step 1
        Debug.Print "i=" & i;
        
        If i Mod 2 = 0 Then
            Debug.Print " Skipping loop iteration"
            GoTo Continue
        End If
        
        Debug.Print " Checking i=7, ";
        If i = 7 Then
            Debug.Print "Terminating loop";
            Exit For
        End If
        
        Debug.Print "Checking loop condition"
        
Continue:
    Next i
    
End Sub


'---
' Procedure : DemoForNextNegativeStep
' Purpose   :
'---
Public Sub DemoForNextNegativeStep()
    Dim i As Long
    
    For i = 5 To 1 Step -1
        Debug.Print i
    Next i
End Sub

'---
' Procedure : DemoDoWhile1
' Purpose   :
'---
Public Sub DemoDoWhile1()
    Dim i As Long
    i = 0
    
    Do While i <= 9
        Debug.Print i
        i = i + 1
    Loop
    
End Sub

Arrays

Attribute VB_Name = "D12_ArrayVars"
'---
' Module    : D12_ArrayVars
' Purpose   :
'---

Option Explicit

'---
' Procedure : DoArrayVars
' Purpose   :
'---
Public Sub DoArrayVars()

    Dim arrA(5) As Long             ' only highest index mentioned, default lowest index is 0 (zero)
    Dim arrB(0 To 4) As String      ' lowest and highest indices mentioned
    Dim arrC(1 To 5) As Date        ' lowest (not default) and highest index is mentioned
    
    arrA(0) = 0: arrA(1) = 1: arrA(2) = 2: arrA(3) = 3: arrA(4) = 4
    Debug.Print arrA(0), arrA(1), arrA(2), arrA(3), arrA(4)
    
    arrB(0) = "a": arrB(1) = "b": arrB(2) = "c": arrB(3) = "d": arrB(4) = "e"
    Debug.Print arrB(0), arrB(1), arrB(2), arrB(3), arrB(4)

    arrC(1) = #1/1/2016#: arrC(2) = #1/1/2017#: arrC(3) = #1/1/2018#: arrC(4) = #1/1/2019#: arrC(5) = #1/1/2020#
    Debug.Print arrC(1), arrC(2), arrC(3), arrC(4), arrC(5)

End Sub

'---
' Procedure : DoMultiDArrayVars
' Purpose   :
'---
Public Sub DoMultiDArrayVars()
    Dim D2Array(1, 3) As String
    
    D2Array(0, 0) = "R0C0": D2Array(0, 1) = "R0C1": D2Array(0, 2) = "R0C2": D2Array(0, 3) = "R0C3"
    D2Array(1, 0) = "R1C0": D2Array(1, 1) = "R1C1": D2Array(1, 2) = "R1C2": D2Array(1, 3) = "R1C3"

    Debug.Print D2Array(0, 0), D2Array(0, 1), D2Array(0, 2), D2Array(0, 3)
    Debug.Print D2Array(1, 0), D2Array(1, 1), D2Array(1, 2), D2Array(1, 3)
    
End Sub

'---
' Procedure : Do2DDynArrayVars
' Purpose   :
'---
Public Sub Do2DDynArrayVars()
    Dim D2Array() As String, r As Long, c As Long
    
    r = 1
    c = 3
    ReDim Preserve D2Array(r, c)

    D2Array(0, 0) = "R0C0": D2Array(0, 1) = "R0C1": D2Array(0, 2) = "R0C2": D2Array(0, 3) = "R0C3"
    D2Array(1, 0) = "R1C0": D2Array(1, 1) = "R1C1": D2Array(1, 2) = "R1C2": D2Array(1, 3) = "R1C3"

'    shrink the array
    r = 1
    c = 2
    ReDim Preserve D2Array(r, c)

'    expand the array
    r = 1
    c = 3
    ReDim Preserve D2Array(r, c)

End Sub

'---
' Procedure : DemoNestedForNext
' Purpose   :
'---
Public Sub DemoNestedForNext()
    Dim MyArray(9, 9) As Long
    Dim i As Long, j As Long
    For i = 0 To 9
        For j = 0 To 9
            MyArray(i, j) = -1
        Next j
    Next i
End Sub

Leave your comment; I love them!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s