Visual Basic for Applications (VBA) in Excel - Introduction to programming

Visual Basic for Applications

Visual Basic for Applications is simplified impementation of Microsoft's programming language Visual Basic 6. VBA is used for automation operations in applications such as Microsoft Excel and also expands it's capabilities. You can find VBA in all Microsoft Office applications, AutoCAD, WordPerfect and many others.

Visual Basic for Application appeared in Excel (Excel version 5) in 1994, earlier (up to version 4) only allow macros to automate those tasks that you can perform using the keyboard. Now, in addition to recording macros you can also independently write your own procedures.

Using Record Macro functionality

While learning VBA and also later on during work, you might find that Record Macro is very useful. It allows to make few clicks and see how those clicks should be written in code.

While using Record Macro as a learning tool pay attention to:

Recording only one step at once
Using the Select and Selection
Using With blocks
Many unnecessary commands

Few more tips for recording macros in Excel:

Avoid using the mouse: When possible, use keyboard shortcuts instead of the mouse to perform actions while recording a macro. This will help ensure that the macro is consistent and reliable across different systems and configurations.
Use relative references: If you are recording a macro that will be used on different ranges or cells, use relative references instead of absolute references. This will ensure that the macro works as intended regardless of the specific cell or range it is applied to.
Keep it simple: When recording a macro, try to keep the code as simple and straightforward as possible. Avoid including unnecessary steps or complex logic that could make the macro harder to understand or maintain.
Document your macro: Once you have recorded your macro, add comments and documentation to the code to help others understand what it does and how it works. This will make it easier to maintain and update the macro in the future.
Optimize for performance: Finally, if you plan to use the macro on large datasets or in a high-performance environment, take steps to optimize the code for speed and efficiency. This may include using arrays instead of loops, avoiding unnecessary calculations, and using built-in Excel functions instead of custom code.

Basics of programming in VBA

Keyboard shortcuts

in Excel

Alt + F8 --> View Macros, the list of all recorded and written macros (sub procedures only)
Alt + F11 --> Open the Visual Basic editor

in Visual Basic Editor (VBE)

F7: Open the VBA editor
Alt + F11: Switch between Excel and the VBE
Ctrl + G: Open the Immediate window
F1: Open the VBE Help window
F2: Rename the selected object
F3: Show the AutoComplete list for the current statement
F4: Toggle between the Object and Code windows
F5: Run the current procedure
F6: Toggle between the code window and the immediate window
F7: Check the syntax of the current statement
F8: Step through the code
F9: Toggle a breakpoint on the current line
F10: Run the current line and go to the next line
F11: Step into the current procedure
Shift + F11: Step out of the current procedure
Ctrl + Break: Stop the currently running procedure
Ctrl + F2: Show the Object Browser
Ctrl + F3: Show the Watch window
Ctrl + F4: Close the current window
Ctrl + F6: Toggle between open windows
Ctrl + F7: Move the current window
Ctrl + F8: Resize the current window
Ctrl + F9: Minimize the current window
Ctrl + F10: Maximize the current window
Ctrl + Shift + F2: Create a new module
Ctrl + Shift + F7: Show the Code window
Ctrl + Shift + F8: Show the Properties window
Ctrl + Shift + F9: Show the Locals window
Ctrl + Shift + F10: Show the Project Explorer
Ctrl + Shift + F11: Show the Immediate window
Ctrl + Shift + F12: Show the Editor Options dialog box

Customization of Visual Basic Editor

When start working with VBE you should consider some customizations.

If you start from the standard configuration you should:

In Options (menu Tools):
1. disable Auto Syntax Check (disables annoying notifications about syntax errors, only annoying but not all of them)
2. enable Require Variable Declarations (adds Option Explicit to every new module)
In Toolbars (menu View):
1. Turn on Edit toolbar (contains comment and uncomment block option)

Types of procedures

There are two types of procedures: Sub and Function

Writing a Sub Procedure

A Sub procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements that performs actions but doesn't return a value. A Sub procedure can take arguments, such as constants, variables, or expressions that are passed by a calling procedure. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses.

Syntax of Sub procedure

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

Example:

 Sub SayHello()
   MsgBox "Hello world!"
 End Sub

Writing a Function Procedure

A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is similar to a Sub procedure, but a function can also return a value. A Function procedure can take arguments, such as constants, variables, or expressions that are passed to it by a calling procedure. If a Function procedure has no arguments, its Function statement must include an empty set of parentheses. A function returns a value by assigning a value to its name in one or more statements of the procedure.

Syntax of Function procedure

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

Example:

 Function RectangleArea(SideA As Double, SideB As Double) As Double
   RectangleArea= SideA * SideB
 End Function

Optional Arguments

You can also define functions to have Optional arguments. These are arguments that the user can supply if they want, but if they are omitted, the function will assign a default value to them.

 Function Rectangle(A As Double, B As Double, _
                    Optional Parimeter As Boolean = False) As Double
     
    If Parimeter = False Then
        Rectangle = A * B
     Else
        Rectangle = 2 * A + 2 * B
    End If
 End Function

Variables

Variable is a named storage location that can contain data that can be modified during program execution.

Each variable has a name that uniquely identifies it within its scope. A data type can be specified or not.

Variable names:

must begin with an alphabetic character,
must be unique within the same scope,
can't be longer than 255 characters,
can't be the same as VBA function or keywords used in VBA,
can't contain spaces.

Data types

This table contain mostly used types of variables.

To see all types please look up into Data Type Summary topic in VBE Help (in Visual Basic Editor press F1 and type Data Type Summary into search box).

Data type	Storage size	Range
Byte	1 byte	0 to 255
Boolean	2 bytes	True or False
Integer	2 bytes	-32,768 to 32,767
Long	4 bytes on 32-bit systems, 8 bytes on 64-bit systems	-2,147,483,648 to 2,147,483,647 on 32-bit systems, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems
Single (single-precision floating-point)	4 bytes	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double (double-precision floating-point)	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency (scaled integer)	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date	8 bytes	January 1, 100 to December 31, 9999
Object	4 bytes	Any Object reference
String (variable-length)	10 bytes + string length	0 to approximately 2 billion
Variant (with numbers)	16 bytes	Any numeric value up to the range of a Double
Variant (with characters)	22 bytes + string length (24 bytes on 64-bit systems)	Same range as for variable-length String

Declaring variables

Variables declared implicitly

Variables declared implicitly receive default data type VARIANT

Example:

a = 3

If you have enabled Option Explicit - implicit declaration of variables is not possible.

Declaration by the Dim statement

Dim VariableName As DataType

Declaring several variables at once

Dim a, b, c as Integer

What type are the variables a, b and c?

The correct statements

Dim a as Integer, b as Integer, c as Integer

or each variable separately

Dim a as Integer
Dim b as Integer
Dim c as Integer

Scope and Visibility

Scope refers to the availability of a variable, constant, or procedure for use by another procedure.

There are three scoping levels: procedure-level, private module-level, and public module-level.

You determine the scope of a variable when you declare it. It's a good idea to declare all variables explicitly to avoid naming-conflict errors between variables with different scopes.

DIM - local variable; a variable or constant defined within a procedure is not visible outside that procedure - only the procedure that contains the variable declaration can use it.

PRIVATE - private variables are available only to procedures in that module or user form

PUBLIC - public variables are available to all procedures in all modules in a project, public variables and constants has to be declared in the Declarations section of a module

STATIC - variables declared with the Static statement retain their values as long as the code is running

Example:

 Static FirstVariable as Integer
 Public SecondVariable As String
 Public Const FirstConstatn= "value of constant"

Private and Public variables and constants has to be declared in the Declarations section of a module (above first procedure).

Lifetime of variables

The time during which a variable retains its value is known as its lifetime. The value of a variable may change over its lifetime, but it retains some value. When a variable loses scope, it no longer has a value.

a) A procedure-level variable declared with the Dim statement retains a value until the procedure is finished running. If the procedure calls other procedures, the variable retains its value while those procedures are running as well.

b) In a standard module or a class module, it retains its value until you stop running your code.

Data types checking

TypeName - returns a String that provides information about a variable.

Sub CheckDataType()
Dim a, b, c, d, e, f
    a = "text"
    b = 23
    c = 4565768
    d = 45.67
    e = True
    f = #1/1/1999#
 
'Debug.Print result is returned to the Immediate Window (Ctrl + G)
Debug.Print TypeName(a)   'String
Debug.Print TypeName(b)   'Integer
Debug.Print TypeName(c)   'Long
Debug.Print TypeName(d)   'Double
Debug.Print TypeName(e)   'Boolean
Debug.Print TypeName(f)   'Date

End Sub

IsNumeric - Returns a Boolean value indicating whether an expression can be evaluated as a number.

IsNumeric(7)         'returns True
IsNumeric("7")       'returns True
IsNumeric("seven")   'returns False

Constants

Declares constants for use in place of literal values. Constans are Private by default.

Syntax:

[Public | Private] Const constname [As type] = expression

Example:

 Const VAT As Double = 0.23

Quiz

Operators

Operators are used to:

perform mathematical calculations
perform comparisons
combine strings
perform logical operations

Arithmetic Operators

^ raise a number to the power of an exponent

- difference between two numbers or indicate the negative value of a numeric expression

* multiply two numbers

/ divide two numbers and return a floating-point result

\ divide two numbers and return an integer result

Mod divide two numbers and return only the remainder

+ sum two numbers

Comparisons Operators

< less than

<= less than or equal to

> greater than

>= greater than or equal to

= equal to

<> not equal to

Is compare two object reference variables (object1 Is object2, SourceRange Is Nothing)

Like compare two strings (string Like pattern, "something" Like "s*")

Concatenation Operators

+ sum two numbers or string concatenation

& string concatenation of two expressions

Logical Operators

Not logical negation

And logical conjunction (true when all of expressions are true)

Or logical disjunction (true when at least one of expressions is true)

Xor logical exclusion (true if one, and only one expression is true)

Operator Precedence

When several operations occur in an expression, each part is evaluated and resolved in a predetermined order called operator precedence.

When expressions contain operators from more than one category, arithmetic operators are evaluated first, comparison operators are evaluated next, and logical operators are evaluated last. Comparison operators all have equal precedence; that is, they are evaluated in the left-to-right order in which they appear. Arithmetic and logical operators are evaluated in the following order of precedence:

Arithmetic Operators	Comparision Operators	Logical Operators
raise a number to the power (^)	equal (=)	Not
negative value (-)	difference(<>)	And
multiply, divide(*, /)	less than(<)	Or
dividing (\)	greater than (>)	Xor
modulo (Mod)	less than or equal to (<=)	Eqv
sum, subtract(+, –)	greater than or equal to (>=)	Imp
concatenation (&)	Like Is

Conditional Expressions

Conditional statements evaluate whether a condition is True or False, and then specify one or more statements to run, depending on the result. Usually, a condition is an expression that uses a comparison operator to compare one value or variable with another.

If...Then...Else Statement

Conditionally executes a group of statements, depending on the value of an expression.

Syntax:

 If condition Then [statement] [Else elseStatement]

or, you can use the block form syntax:

 If condition Then
     [statements] 
 
   [ElseIf condition-n Then
     [elseIfStatements] ... 
 
   [Else
     [elseStatements]]
 
 End If

You can use the single-line form (first syntax) for short, simple tests. However, the block form (second syntax) provides more structure and flexibility than the single-line form and is usually easier to read, maintain, and debug.

Select Case Statement

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

Syntax:

 Select Case testExpression
   [Case expressionList-n
       [statements-n]] ...
   [Case Else
       [elseStatements]]
 
 End Select

Quiz

Exercises

Write a procedure which asks for your name using INPUTBOX and then displays a welcome message using MSGBOX.
Write a procedure that will check if the number is greater, smaller or equal to 7. The program at the end should display the number introduced by the user and the appropriate message.
Write a procedure that will check if the number is odd or even. The program at the end should display the number introduced by the user and the appropriate message.
Use MsgBox to display a message asking user whether they want to continue or not, and process answer.
Write a program that asks you to enter two numbers and then display greater of them.
Write a program that asks you to enter three numbers, then displays the greatest of them.
Write a program evaluating the students tests. The program should asks you to enter number of points and then display the number of points and the score:
- points 91 to 100 - A
- points 81 to 90 - B
- points 71 to 80 - C
- points 61 to 70 - D
- points 51 to 60 - E
- points 0 to 50 - F
- > 100 or < 0 - error message
When you finish all exercises try to find weak points of your procedures. For example try to type wrong input data (seven as a word instead of a digit).

Loops

Looping allows you to run a group of statements repeatedly. Some loops repeat statements until a condition is False; others repeat statements until a condition is True. There are also loops that repeat statements a specific number of times or for each object in a collection.

For ... Next

You can use For...Next statements to repeat a block of statements a specific number of times. For loops use a counter variable whose value is increased or decreased with each repetition of the loop.

syntax:

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

example:

Sub ForLoop_example()
  Dim i As Long
  For i = 1 to 5
    MsgBox i
  Next 
End Sub

For Each ... Next

For Each...Next statements repeat a block of statements for each object in a collection or each element in an array. Visual Basic automatically sets a variable each time the loop runs.

syntax:

Dim variableName As Variant
For Each variableName In collectionName
  [statements]
  [Exit For]
  [statements]
Next variableName

example:

Sub ForEachLoop_example()
  Dim wks As Variant
  For Each wks In Worksheets
    MsgBox wks.Name
  Next arkusz
End Sub

Do While (or Until) ... Loop

You can use Do...Loop statements to run a block of statements an indefinite number of times. The statements are repeated either while a condition is True or until a condition becomes True.

Repeating Statements While a Condition is True
Repeating Statements Until a Condition Becomes True

There are two ways to use the While (and Until) keyword to check a condition in a Do...Loop statement. You can check the condition before you enter the loop, or you can check it after the loop has run at least once.

syntax:

Do [{While | Until} condition]
  [statements]
  [Exit Do]
  [statements]
Loop

Do
  [statements]
  [Exit Do]
  [statements]
Loop [{While | Until} condition]

example:

Sub DoUntil_example()
  Dim x
  Do
    x = InputBox("")
    Debug.Print x
  Loop Until x = ""
End Sub

Exit For/Do

You can exit a For...Next statement before the counter reaches its end value by using the Exit For statement. The same applies for the For Each ... Next statement and Do ... Loop. In the last case use Exit Do.

Exercises

1. What will be the output of the following loops?

A

Dim i As Integer
i = 10
Do While i < 10
  MsgBox i
  i = i + 1
Loop

B

Dim i As Integer
i = 10
Do Until i >= 10
  MsgBox i
  i = i + 1
Loop

C

Dim i As Integer
i = 10
Do
  MsgBox i
  i = i + 1
Loop While i < 10

D

Dim i As Integer
i = 1
Do
  MsgBox i
  i = i + 1
Loop Until i >= 10

E

Dim i As Integer
i = 13
Do
  MsgBox i
  i = i - 2
Loop Until i < 2

F

Dim i As Double
For i = 0.9 To 0 Step -0.3
  Debug.Print i
Next

G

Dim i As Integer
For i = 1 To 0 Step -0.3
  Debug.Print i
Next

H

Dim i As Integer
For i = 30 To 0 Step -0.6
  Debug.Print i
Next

2. Write a procedure that will perform a countdown from 10 to 0, and then displays the message "end of the countdown".
3. Write a program that calculates the sum of 5 consecutive integers, starting from 1.

Working with data in Worksheet

Range

Range("A1").Value = "The value in cell A1"
Range("A3:B4").Value = "The range of cells"
Range("B5,B7,B9").Value = "various cells"
Range("D:D 6:6").Value = "value at the intersection of column D and row 6"
Range("RangeName").Value = "a named range"
Range("BoldRange").Font.Bold = True 'set bold font for the named range

Cells

Cells(2,4).Value = "row 2 column 4"
Workbooks("test.xls").Worksheets("Arkusz1").Cells(5, 6).Value = "test"

ActiveCell

ActiveCell.Value = "Active cell"
ActiveCell.Offset(1,0).Value = "under"
ActiveCell.Offset(-1,0).Value = "above"
ActiveCell.Offset(0,1).Value = "right"
ActiveCell.Offset(0,-1).Value = "left"

Sorting data

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A3") _
       , SortOn:=xlSortOnValues, Order:=xlDescending
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:A3")
       .Apply
    End With

Range("F1:F3").Sort key1:=Range("F1:F3"), _
  order1:=xlAscending

Exercises

Write a program that enters into the cells from A1 to A20 all the numbers from 1 to 20.
Create a macro that will ask you about cell address then will show you the data from this cell. If the cell is empty macro will show message "Cell [cell address] is empty"
Write a program that will fill 56 cells in column B with different interior colors (from 1 to 56). Use the collection of ColorIndex (Cells.Interior.ColorIndex = 12).
Write a program that, starting from the active cell will introduce 20 consecutive natural numbers (starting with 1) and then below will introduce the sum of these 20 numbers.
Write a program that generates a multiplication table to 100.
Write a program that will create a checkerboard pattern.

See for the example below:

Arrays

An array is a group of elements of the same type that have a common name. You refer to a specific element in the array by using the array name and an index number For example, you can define an array of 7 string variables to hold the names of the weekdays. If you name the array WeekDaysNames, you can refer to the first element of the array as WeekDaysNames(1), the second element as WeekDaysNames(2), and so on.

Declaring static arrays

Dim staticArray(10) As Integer
lub
i = Array(1, 2, "eer", 5, 6)

Static array at declaration receives dimension and size, which can not be modified in the next steps of the program. The value of 10 in parentheses array tells us the dimension and the size of the array. In this example we are dealing with an array consisting of 11 elements and 1 dimension. By default, arrays are indexed from 0, if you want to change the starting index you can do so in accordance with the following example.

Dim staticArray(1 to 10) as Integer

If you would like VBA to assume that 1 is the lower index for all arrays that declare only the upper index, include the following statement before any procedures in your module:

Option Base 1 

Dim staticArray(10)  'Now the first index number is 1

Declaring dynamic arrays

A dynamic array doesn’t have a preset number of elements. You declare a dynamic array with a blankset of parentheses:.

Dim dynamicArray() as String

Before you can use a dynamic array in your code, you must use the ReDim statement to tell VBA how many elements are in the array.

Redim dynamicArray(1 to 3)

Any subsequent change size of the array by default deletes its contents. If you want to preserve the existing values, use ReDim Preserve.

ReDim Preserve dynamicArray(1 to 4)

Declaring multidimensional arrays

Dim MyArray(-10 to 0, 1 to 100, 200 to 250) as Integer
'Three-dimensional Array. The first dimension indexed from -10 to 0, the second from 1 to 100 and the third from 200 to 250.

An array in VBA can have up to 60 dimensions.

Example1

Sub testing1()

Dim counter as Integer
Dim myArray(1 to 3) as string
    myArray(1) = "one"
    myArray(2) = "two"
    myArray(3) = "three"

For counter = LBound(myArray) To UBound(myArray)
 Debug.Print myArray(counter)
Next
     
End Sub

Example2

Sub testing2()

Dim counter1 As Integer
Dim counter2 As Integer

Dim myArray(1 To 3, 1 To 2) As String
    myArray(1, 1) = "one"
    myArray(2, 1) = "two"
    myArray(3, 1) = "three"
    
    myArray(1, 2) = "alpha"
    myArray(2, 2) = "beta"
    myArray(3, 2) = "gamma"
    
For counter1 = LBound(myArray, 1) To UBound(myArray, 1)
   For counter2 = LBound(myArray, 2) To UBound(myArray, 2)
       Debug.Print myArray(counter1, counter2)
   Next
Next
     
End Sub

Error handling

Error handling is an essential part of writing robust and reliable VBA code. Simply put, error handling is a way to anticipate and handle errors that can occur during the execution of your VBA code. When your VBA code encounters an error, it can either crash your entire program or generate unexpected results. This is where error handling comes in to play.

Here's a simple example to illustrate how error handling can be used in VBA:

Suppose you have a VBA function that takes two arguments and divides them, like this:

Function Divide(num1 As Double, num2 As Double) As Double
    Divide = num1 / num2
End Function

This function works perfectly fine when num2 is not zero. However, if num2 is zero, the function will throw a run-time error "division by zero". This is where error handling can be used to gracefully handle this error.

To handle this error, you can use the On Error statement to specify a custom error handler:

Function Divide(num1 As Double, num2 As Double) As Double
    On Error GoTo ErrorHandler
    Divide = num1 / num2
    Exit Function
    
ErrorHandler:
    MsgBox "Error: Division by zero"
    Divide = 0
End Function

In this updated function, if an error occurs during the execution of the function, VBA will jump to the ErrorHandler label and execute the code there. The error message will be displayed in a message box, and the function will return a default value of zero.

This is just a simple example, but error handling can become much more complex depending on the requirements of your program. However, the basic concept remains the same: anticipate potential errors and handle them gracefully to avoid crashes and unexpected results.

Useful Functions

Strings functions

"String" the type of data used to store the text

Sub Examlpe1()
Dim strVaraible As String
    strVaraible= "VBA"
    strVaraible = strVaraible & " - Visual Basic for Applications"

    MsgBox strVaraible
End Sub

Mid

Mid(string, start[, length])

Returns a Variant (String) containing a specified number of characters from a string.

Sub Example2()
Dim text As String
    text = "She sells sea shells"
    
    MsgBox Mid(text, 11, 3)
End Sub

Left, Right

Left(string, length)
Right(string, length)

Returns a Variant (String) containing a specified number of characters from the left/right side of a string

Sub Example3()
Dim text As String
    text  = "She sells sea shells"
    
    MsgBox Right(text , 6)
End Sub

LTrim, RTrim, Trim

LTrim(string)
RTrim(string)
Trim(string)

Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).

Sub Example4()
Dim text As String
    text = "     OOOOO      OOOOOO    "
    MsgBox LTrim(text) & vbCrLf & RTrim(text) & vbCrLf & Trim(text)
End Sub

Split

Split(expression[, delimiter[, limit[, compare]]])

Returns a zero-based, one-dimensional array containing a specified number of substrings.

Sub Example5()
Dim text As String, i As Integer
Dim MyArray() As String

text = "NobleProg Training how YOU want it!"
MyArray = Split(text, " ")

    For i = 0 To UBound(MyArray)
        MsgBox MyArray(i)
    Next i

End Sub

Len

Len(string)

Returns a Long containing the number of characters in a string or the number of bytes required to store a variable

Sub Example6()
Dim text As String
    text = "Supercalifragilisticexpialidocious"
    MsgBox Len(text)
End Sub

UCase, LCase

UCase(String)
LCase(String)

Returns a Variant (String) containing the specified string, converted to uppercase

Sub Example7()
Dim text As String
    text = "AbCdEfGhIjKlMnOpRsTuWxYz"

    MsgBox UCase(text)
    MsgBox LCase(text)
End Sub

Replace

Replace(expression, find, replace[, start[, count[, compare]]])

Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Sub Example8()
Dim text As String
    text = "Jack and Jue"
    MsgBox Replace(text, "J", "Bl")
End Sub

Exercises

Write a program that based on the user-specified name will create a code consisting of the first three letters and number of characters, for example: Adam -> Ada4
Create a program that will download expression from the cell "A1", and write it in uppercase in the second sheet
The user enters three words (use inputbox). Write these words on the sheet and next to them in a separate cell, enter the length of each of them (number of letters). Then, in a separate cell, write which one is the longest.

Files

ChDir

ChDir path

Changes the current directory or folder.

chDir "C:\"

CurDir

Returns a Variant (String) representing the current path

ChDrive

ChDrive drive

Changes the current drive

ChDrive ("D")

Name

Name oldpathname As newpathname

Renames a disk file, directory, or folder

Name "c:\course\oldName.txt" As "c:\course\newName.txt”

FileCopy

FileCopy source, destination

Copies a file.

FileCopy "file.txt", "b.txt"
FileCopy "c:\a.txt", "c:\b.txt"

Kill

Kill pathname

Deletes files from a disk.

Kill "c:\b.txt"

Mkdir

MkDir path

Creates a new directory or folder.

MkDir "c:\course"

Rmdir

RmDir path

Removes an existing directory or folder. An error occurs if you try to use RmDir on a directory or folder containing files. Use the Kill statement to delete all files before attempting to remove a directory or folder.

RmDir "C:\course"

FileDateTime

FileDateTime(pathname)

Returns a Variant (Date) that indicates the date and time when a file was created or last modified.

FileDateTime("C:\file.txt")

Dir

Dir[(pathname[, attributes])]

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

MsgBox Dir("") 'displays the name of the first file in the current directory
MsgBox Dir 'Another name of the file in the current directory

FileLen

Returns a Long specifying the length of a file in bytes.

FileLen("c:\a.txt")

GetAttr,SetAttr

GetAttr - Returns an Integer representing the attributes of a file, directory, or folder.

SetAttr - Sets attribute information for a file.

Dim attr As String
attr = GetAttr("c:\a.txt")
If attr = vbReadOnly Then
  Debug.Print "tylko do odczytu"
End If

SetAttr "c:\a.txt", vbNormal + vbReadOnly
SetAttr "c:\a.txt", (32 + 1)

These two lines above perform the same task - set the attributes for a file

Functions

In VBA, a function is a block of code that performs a specific task and returns a value to the calling code. Creating functions in VBA is a powerful way to reuse code, improve program structure, and make your code more readable and maintainable.

To create a function in VBA, you need to follow these steps:

Start by typing the keyword Function followed by the name of the function you want to create.
Next, add any input parameters enclosed in parentheses. If the function doesn't require any input parameters, you can skip this step.
Then, write the code that performs the desired task inside the function block.
Finally, use the Return statement to specify the value that the function should return to the calling code.

Here are some examples of different types of functions in VBA:

Function with no input parameters and return value

Function HelloWorld()
    MsgBox "Hello, world!"
End Function

This function simply displays a message box with the text "Hello, world!". It doesn't take any input parameters and doesn't return any value.

Function with input parameters and return value

Function AddNumbers(num1 As Integer, num2 As Integer) As Integer
    AddNumbers = num1 + num2
End Function

This function takes two input parameters, num1 and num2, and returns their sum as an integer. The As Integer statement specifies the data type of the return value.

Function with optional input parameters and return value

Function CalculateCircleArea(radius As Double, Optional precision As Integer = 2) As Double
    CalculateCircleArea = Round(radius ^ 2 * Application.WorksheetFunction.Pi, precision)
End Function

This function calculates the area of a circle given its radius, and optionally specifies the precision of the result. If the precision parameter is not specified, it defaults to 2 decimal places. The Round function is used to round the result to the specified precision.

Function that returns multiple values using an array

Function GetMinMax(ParamArray numbers() As Variant) As String
    Dim result(1) As Integer
    result(0) = Application.WorksheetFunction.Min(numbers)
    result(1) = Application.WorksheetFunction.Max(numbers)
    GetMinMax = "max= " & result(1) & " min= " & result(0)
End Function

The function takes a variable number of input parameters as an array of variants using the "ParamArray" keyword. The purpose of the function is to find the minimum and maximum values in the input array of numbers and return them as a string. The function starts by declaring a variable called "result" as an array of integers with a size of 2. This array will store the minimum and maximum values of the input array. The "Min" and "Max" functions from the "WorksheetFunction" object are used to find the minimum and maximum values in the input array. These functions return the minimum and maximum values as integers, which are then stored in the "result" array. Finally, the function returns a string that combines the minimum and maximum values in a human-readable format. The string is created by concatenating the words "max= ", the value of the second element of the "result" array (which contains the maximum value), the words " min= ", and the value of the first element of the "result" array (which contains the minimum value).

Userforms

In VBA Excel, user forms are graphical interfaces that allow users to interact with the data and functionality of the worksheet. They are created using the UserForm object, which provides a container for various controls such as buttons, text boxes, list boxes, combo boxes, and others.

To create a user form in VBA Excel, follow these steps:

Open the VBA Editor by pressing Alt + F11 or by selecting Developer -> Visual Basic from the Excel ribbon.

Right-click on the project or workbook where you want to create the user form, and select Insert -> UserForm.

This will create a blank user form, where you can add various controls from the Toolbox. To open the Toolbox, select View -> Toolbox from the menu.

Drag and drop the controls you want to use onto the user form. You can resize and position them as needed.

To add functionality to the controls, you can write VBA code in the corresponding event procedures. For example, you can write code in the Click event of a button to perform a specific action when the user clicks on it.

Private Sub UserForm_Initialize()

    With ComboBox1
        .Clear
        .AddItem "New York"
        .AddItem "Paris"
        .AddItem "London"
        .AddItem "Berlin"
        .AddItem "Warsaw"
        .AddItem "Vienna"
    End With
    
    TextBox1 = ""
    
    OptionButton1 = True
    CheckBox1 = False
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Call UserForm_Initialize
End Sub

Once you have finished designing and coding the user form, you can display it by calling its Show method from a VBA procedure or event.

Naming Convention

Using a naming convention for form controls in VBA has several benefits:

Readability and clarity: Naming convention makes the code more readable and easier to understand. By using meaningful and consistent names for the form controls, it becomes easier to identify what each control does and how it's used.
Consistency: When using a naming convention, it ensures that all the controls have a consistent and predictable naming pattern, which makes the code more organized and easier to maintain.
Avoiding naming conflicts: By using a naming convention, it helps to avoid naming conflicts between different controls. This is especially important when working on larger projects with many forms and controls.
Compatibility with other developers: Using a naming convention is also beneficial when working with other developers, as it ensures that everyone follows the same naming convention, making the code more cohesive and easier to collaborate on.

Overall, using a naming convention for form controls in VBA can make the code more readable, organized, and easier to maintain, which can save time and reduce errors in the long run.

The naming convention for form controls:

Text box: txt (e.g. txtFirstName)
Label: lbl (e.g. lblLastName)
Command button: cmd (e.g. cmdSave)
Check box: chk (e.g. chkAgree)
Option button: opt (e.g. optMale)
List box: lst (e.g. lstColors)
Combo box: cbo (e.g. cboCountry)
Image: img (e.g. imgLogo)
Frame: fra (e.g. fraAddress)
Scroll bar: sbr (e.g. sbrZoom)

Again, the specific naming convention chosen may depend on personal preference and project standards, but using a consistent naming convention can make the code more organized and easier to read and maintain.

	Const variableName As DataType
	Dim variableName As DataType
	Set variableName As DataType
	Variable variableName As DataType

	Very Basic Applications
	Virtual Basic Applications
	Vague Basic Applications
	Visual Basic for Applications

	constantName As DataType = value
	Constant constantName As DataType = value
	Const constantName As DataType = value
	Var constantName As DataType = value

	Enables debugging features
	Sets the default font for the code editor
	Prevents certain keywords from being used
	Requires all variables to be explicitly declared

	Returns TRUE only if both operands are TRUE
	Returns TRUE if one or both operands are TRUE
	Returns TRUE only if both operands are TRUE, otherwise FALSE
	Returns FALSE if one or both operands are FALSE

	Boolean
	Stringy
	Array
	Decimal

	Proc
	Func
	Sub
	Method

	Private
	Global
	Public
	Local

	Protected
	Public
	Private
	Module

Visual Basic for Applications (VBA) in Excel - Introduction to programming