Excel VBA Course (7): Excel VBA Language Basics - Syntax

This article looks at syntax among the basics of the Excel VBA language.

This is a continuation of the previous article.

Excel VBA Course (6): Excel VBA Language Basics - Variables

3.2. Language Syntax

The syntax of the VBA language is the same as that of Visual Basic. You don't need to know all the syntax at first, just the syntax summarized here is sufficient. For the entire syntax, refer to the Visual Basic CHM help file attached below.

Online help (available in English only) for the VBA language is available at the URL below.

https://docs.microsoft.com/en-us/office/vba/api/overview/language-reference

3.2.1. conditional statement (IF)

Conditionally executes a group of statements according to the value of an expression.

Single line IF syntax

If condition Then [statements] [Else elsestatements]

Single line IF sample code

If Digits = 1 Then MyString = "One" Else MyString = "More than one"

Multiple line IF syntax

If condition Then
    ​​​​[statements]
[ElseIf condition -n Then
    ​​​​[elseif statements]...]
[Else
    ​​​​[else statements]]
End If

Visual Basic Help

• condition
  • essential element. An expression of one or more of two forms:
  • Trueme Falseas an expression or string expression that evaluates to conditionthis Nullthe other side Falsewill be treated as
  • TypeOf objectname Is objecttype It is an expression in the form Objectnameis an object reference, objecttypeis a valid object type. Objectnamethis objecttypeThe expression is True if the object type specified by ; False otherwise.
• statements
  • It is an optional element in block form, ElseRequired in single-line form with no clauses. Separate two or more statements with a colon conditionthis TrueIf so, it will run.
• condition -n
  • optional element. conditionis equal to
• elseif statements
  • optional element. related condition-nthis TrueOne or more statements that are executed if .
• else statements
  • optional element. Prior conditionor condition-ndiet TrueOne or more statements that are executed if not .

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ifthenelse-statement

Multiple line IF sample code

Dim Number, Digits, MyString
Number = 53    ' 변수를 초기화합니다.
If Number < 10 Then
    Digits = 1
ElseIf Number < 100 Then '조건이 True로 평가되면 다음 문이 실행됩니다.
    Digits = 2
Else ' 조건이 False로 평가되면 다음 문이 실행됩니다.
    Digits = 3
End If

3.2.2. Condition function (IIF)

Returns one of two values depending on the result of evaluating an expression.

IIF is a function and is used to briefly express If ~ Else ~ End If statements.

IIF grammar

IIf(expr, true part, false part)

Visual Basic Help

Component	Explanation
expr	essential element. expression you want to evaluate
true part	essential element. exprthis TrueThe value or expression to return if .
false part	essential element. exprthis FalseThe value or expression to return if .

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/iif-function

IIF sample code

Function CheckIt (TestMe As Integer)
    CheckIt = IIf(TestMe > 1000, "Large", "Small")
End Function

3.2.3. Conditional statement (Select Case)

Executes one of several groups of statements depending on the value of the expression.

Select Case Conditional Syntax

Select Case test expression
    [Case expressionlist-n
            [statements-n]] ...
    [Case Else
            [else statements]]
End Select

Visual Basic Help

Component	Explanation
test expression	essential element. Any numeric or string expression.
expression list-n	Case This is essential if you are using a statement. expression, expression To expression, Is comparisonoperator expressionIt consists of a delimited list of one or more of the same types. To Keywords specify a range of values. To If keywords are used, smaller values are ToYou must come to the door. To specify a range of values, the comparison operator (Is liberal arts Likeexcept for the door) Is Use keywords. Is If you do not enter a keyword, it will be inserted automatically.
statements-n	optional element. testexpressioncorresponding to expressionlist, the corresponding one or more statements are executed.
else statements	optional element. testexpressioncorresponding to CaseIf no clause is present, one or more of the corresponding statements are executed.

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/select-case-statement

Select Case conditional sample code

Function Bonus(performance, salary)
    Select Case performance
        Case 1
            Bonus = salary * 0.1
        Case 2, 3
            Bonus = salary * 0.09
        Case 4 To 6
            Bonus = salary * 0.07
        Case Is > 8
            Bonus = 100
        Case Else
            Bonus = 0
    End Select
End Function

3.2.4. Repeat statement (For)

Repeats a group of queries the specified number of times.

For Next Loop Syntax

For counter= start To end [Step step]
    [statements]
    [Exit For]
    [statements]
Next [counter]

Component	Explanation
counter	essential element. A numeric variable used as a loop counter. This variable cannot be a Boolean or an array element.
start	essential element. Counterstarting value of
end	essential element. Counterfinal value of
step	optional element. changes each time the loop is executed. counteris the amount of if not specified stepdefaults to 1.
statements	optional element. ForWow Next One or more statements in between that are executed the specified number of times.

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement

For Next loop sample code

Dim Words, Chars, MyString
For Words = 10 To 1 Step -1     ' 10번 반복을 설정합니다.
    For Chars = 0 To 9    ' 10번 반복을 설정합니다.
        MyString = MyString & Chars    ' 숫자를 문자열에 추가합니다.
    Next Chars    ' 카운터 증가
    MyString = MyString & " "    ' 빈 칸을 추가합니다.
Next Words

3.2.5. Loop (For Each)

Iterates through a group of statements for each element of an array or collection.

For Each Loop Syntax

For Each element In group
    [statements]
    [Exit For]
    [statements]
Next [element]

Component	Explanation
element	essential element. A variable used to iterate through the elements of a collection or array. in the collection elementcan only be a Variant variable, a generic object variable, or a specific object variable. in an array elementis only Variant It can be only a variable.
group	essential element. The name of each collection or array of objects (except arrays of user-defined types).
statements	optional element. groupOne or more statements that are executed on each item in .

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/for-eachnext-statement

For Each loop sample code

Dim Found, MyObject, MyCollection
Found = False    ' 변수를 초기화합니다.
For Each MyObject In MyCollection    ' 각각의 요소에 대해 반복합니다.
    If MyObject.Text = "Hello" Then    ' Text가 "Hello"라면.
        Found = True    ' Found변수를 True로 설정합니다.
        Exit For    ' 루프를 종료합니다.
    End If
Next

3.2.6. loop (While)

As long as the given condition is true, the series of statements continues to run.

while loop syntax

While condition
    ​​​​[statements]
Wend

Component	Explanation
condition	essential element. Trueme FalseNumeric or string expression that evaluates to . if conditionif is null conditionsilver Falsewill be treated as
statements	optional element. given condition TrueExecutes one or more statements while

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/whilewend-statement

While loop sample code

Dim Counter
Counter = 0    ' 변수를 초기화합니다.
While Counter < 20    ' 변수 Counter 값을 검사합니다.
    Counter = Counter + 1    ' 변수 Counter 값을 1씩 증가시킵니다.
Wend    ' 변수 Counter의 값이 19보다 크면 While 루프를 종료합니다.
Debug.Print Counter        ' 직접 실행

3.2.7. Loop control (Exit, Continue)

Exit For can be used to stop repetition when a specific condition is met during execution of a loop statement. However, the continue statement to skip repetition is not supported by VBA. Control with IF block or Goto statement.

How to use If block

• How to wrap an If block so that a statement is executed only when a specific condition is met
• This can be used when continue processing is performed based on the variable value that was determined at the beginning of the code.
• Difficult to use if conditions are complex or variable values are not initially determined

For iSht = 1 To aBook.Worksheets.Count
    Set oSht = aBook.Sheets(iSht)
 
    If (IsColumnMappingSheet(oSht)) and (lRowOffset >= 0)
        ...
        ...
    End If
Next iSht

How to use Goto

• It is used when continuing processing is performed in the middle of the code or under a specific condition.
• Label the position to move to at the bottom of the loop (right above Next) and use the Goto statement in the If conditional statement
• Can be used at the beginning or in the middle

For iSht = 1 To aBook.Worksheets.Count
    Set oSht = aBook.Sheets(iSht)
 
    If Not IsColumnMappingSheet(oSht) Then GoTo Continue_To_Next_Sheet
    ...
    If lRowOffset < 0 Then GoTo Continue_To_Next_Sheet
    ...
Continue_To_Next_Sheet:
Next iSht

3.2.8. Procedure

Procedures typically include subs and functions.

Sub procedure syntax

[Private | Public] [Static] Sub name [(arglist)]
    [statements]
    [Exit Sub]
    [statements]
End Sub

Component	Explanation
Public	optional element. In all procedures within the module Sub Indicates that the procedure can be called. if Option Private If used in a module with statements, the procedure cannot be called from outside the project.
Private	optional element. Sub Indicates that a procedure can only be called by other procedures within the module in which it is declared.
Friend	optional element. Used only in class modules. Sub Indicates that the procedure is recognized throughout the project, but not in the object's instance controller.
Static	optional element. Sub Indicates that the procedure's local variables are persisted across function calls. Even if used in a procedure Sub Variables declared outside Static can't have properties.
name	essential element. Sub The name, which follows standard variable naming conventions.
arglist	optional element. when called Sub A list of variables representing the arguments passed to the procedure. In case of multiple variables, separate them with a comma (,) symbol.
statements	optional element. Sub A set of statements executed in a procedure.

arglist The argument has the following components:

[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]

Component	Explanation
Optional	optional element. A keyword indicating that the argument is not required. OptionalUsing arglistAll arguments used are optional. Optional You must declare it using a keyword. every Optional argument is Variantshould be Optionalsilver ParamArrayCannot be used with.
ByVal	optional element. Indicates that the argument is pass-by-value.
ByRef	optional element. Indicates that the argument is pass-by-reference. In Visual Basic ByRefis the default.
ParamArray	optional element. arglistis used as the last argument in Variant of the elements Optional Indicates that it is an array. ParamArray You can use any number of arguments by using keywords, ByVal, ByRef or OptionalCannot be used with.
varname	essential element. The name of the variable representing the argument, which follows standard variable naming conventions.
type	optional element. The data type of the argument passed to the procedure. Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (currently not supported), Date, String (variable length string only), Object, Variant OptionalOtherwise, you can also specify user-defined types and object types.
defaultvalue	optional element. is a constant or constant expression, Optional Valid only for parameters. Object In the case of the format, an explicit default Nothingonly possible.

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/sub-statement

Sub procedure sample code

' 하위 프로시저 정의입니다.
' 두개의 인수를 가진 하위 프로시저입니다.
Sub SubComputeArea(Length, TheWidth)
    Dim Area As Double    ' 지역 변수를 선언합니다.
    If Length = 0 Or TheWidth = 0 Then
    ' 만약 두 인수가 모두 0이면,
        Exit Sub    ' Sub를 즉시 종료합니다.
    End If
    Area = Length * TheWidth    ' 사각형의 면적을 계산합니다.
    Debug.Print Area    ' 지역을 디버그 창에 인쇄합니다.
End Sub

Function procedure syntax

[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
    [statements]
    [name = expression]
    [Exit Function] 
    [statements]
    [name = expression]
End Function

Component	Explanation
Public	optional element. Function Procedure indicates that all procedures in all modules can be accessed. procedure is Option Private, it cannot be used outside the project.
Private	optional element. Function A procedure indicates that other procedures can be accessed only in the module in which it is declared.
Friend	optional element. Used only in class modules. Function Indicates that the procedure is recognized throughout the project, but not in the object's instance controller.
Static	optional element. Function Indicates that the procedure's local variables are preserved across calls. Static attribute is Function Even if a variable declared outside is used in a procedure, it has no effect on this variable.
name	essential element. Function, and follows standard variable naming conventions.
arglist	optional element. if you call Function A list of variables representing the arguments passed to the procedure. Multiple variables are separated by commas.
type	optional element. Function The data type of the value returned by the procedure is: Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (currently not supported), Date, String, (except variable length), Object, Variant, user-defined types
statements	optional element. Function A group of statements to be executed inside a procedure.
expression	optional element. Function returns a value.

arglist The argument has the following syntax and components.

[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]

Component	Explanation
Optional	optional element. Indicates that no arguments are required. For arguments to be used arglistAll of the following arguments in are optionally used and Optional You must declare it using a keyword. if ParamArrayis used Optionalcannot be used for any argument.
ByVal	optional element. Indicates that the argument is pass-by-value.
ByRef	optional element. Indicates that the argument is pass-by-reference. In Visual Basic ByRefis the default.
ParamArray	optional element. the last argument Variant of the elements Optional to indicate that it is an array arglistIt is used only as the last argument in . ParamArray A keyword allows an arbitrary number of arguments, which ByVal, ByRef, OptionalCannot be used with.
varname	essential element. The name of the variable representing the argument, following standard variable naming conventions.
type	optional element. The data type of the argument passed to the procedure is Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal(currently not supported) Date, String (for variable length), Object, Variant, a specific object type, etc. parameter is OptionalIf not, you can also specify a custom format.
defaultvalue	optional element. a constantI am a constant Optional Valid only for parameters. the format ObjectIf explicit, the default is NothingIt becomes only.

* Reference: The above contents were excerpted from the chm help translated into Korean. For online help, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statement

Function procedure sample code

' 다음의 사용자 정의 함수는
' 전달된 인수의 제곱근을 반환합니다.
Function CalculateSquareRoot(NumberArg As Double) As Double
    If NumberArg < 0 Then    ' 인수를 계산합니다.
        Exit Function    ' 프로시저 호출을 종료합니다.
    Else
        CalculateSquareRoot = Sqr(NumberArg)    ' 제곱근을 반환합니다.
    End If
End Function

3.2.9. error handling

Try, catch, and finally statements in Java and C# are not supported. Handle errors with the On Error Goto statement.

construction	Explanation
On Error GoTo label	If an error occurs, move the execution location to the specified label
On Error Resume Next	Ignoring the error that occurred and continuing with the next command
On Error Go To 0	Initialize the set error handling method (Goto, Resume)

The basic error handling code is as follows.

Sub ErrorTest()
    On Error GoTo ErrHandler 'Error 처리 시작 (=try)
 
    Dim i As Integer
    i = 1 / 0
 
    On Error GoTo 0 'Error 처리 종료 (=finally)
 
    Exit Sub
    
ErrHandler:  'Error 처리 (=catch)
    MsgBox Err.Description
End Sub

The code above intentionally generates a divide-by-zero error so that the statements in the ErrHandler area are executed.

3.2.10. Visual Basic (VB) Documentation Help (CHM)

I received the Korean Visual Basic manual written as a CHM (Compiled HTML Help) file before, but the source is unknown. It appears to be a help file included with Visual Studio 6.0.

If you are worried about viruses, please use the online help without downloading this file.

Before opening this file, you need to unblock it to use it normally. Select the file, right-click, check “Unblock” at the bottom right in Properties, click OK, and open the file. (see image below)

3.2.11. Office 2013 VBA Documentation (CHM, English)

This is a manual on how to handle Office 2013 products (Excel, Access, OneNote, Outlook, PowerPoint, Publisher, Visio, Word) with VBA.

https://www.microsoft.com/en-us/download/details.aspx?id=40326

If you expand the Install Instructions at the bottom of the URL above, it explains how to take action when the contents of the CHM file are not visible. This is about the “unblocking” previously described.

I couldn't find any offline help (CHM) for Office 2013 or later. If you have one or know the URL, please let me know in the comments.

---

In this article, we looked at VBA syntax. Only the most basic contents were selected, and if you understand this grammar clearly, you can read and write most VBA codes. Next, we will look at data types.

---

<< Related article list >>

• Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)
• Excel VBA Course(1): Overview of Excel VBA
• Excel VBA Course (2): Excel VBA Basics
• Excel VBA Course (3): Excel Object Model
• Excel VBA Course (4): Working with Excel Object Model
• Excel VBA Course (5): Excel File Extension, VBE, Font Settings
• Excel VBA Course (6): Excel VBA Language Basics - Variables
• Excel VBA Course (7): Excel VBA Language Basics - Syntax
• Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures
• Excel VBA Courses (9): Excel VBA How-To
• Excel VBA Course (10): Tools developed and used with Excel VBA
• Full Table of Contents for Excel VBA Courses