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.
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.
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.
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.
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.
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
Related articles:
Full Table of Contents for Excel VBA Courses
Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures
Excel VBA Course (6): Excel VBA Language Basics - Variables
Excel VBA Course (5): Excel File Extension, VBE, Font Settings
Excel VBA Course (4): Working with Excel Object Model
Excel VBA Course (3): Excel Object Model
Excel VBA Course (2): Excel VBA Basics
Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)
English 
한국어 
简体中文 
日本語 
Deutsch 
Español