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

Visual Basic for Applications

Visual Basic for Applications (VBA) is a simplified version of Microsoft's Visual Basic 6 programming language. It is used to automate tasks and extend functionality in programs such as Microsoft Excel. VBA is available in all Microsoft Office applications, as well as in other software like AutoCAD and WordPerfect.

VBA was introduced in Excel 5.0 in 1994. Earlier versions of Excel (up to version 4.0) only supported basic macros that automated tasks using keyboard actions. With the introduction of VBA, users can now record macros or write their own custom procedures in code.

Using the "Record Macro" Feature

The **Record Macro** tool is useful when learning VBA or performing repetitive tasks. It lets you record your actions (like clicks or formatting changes), then view the generated VBA code.

When using "Record Macro" as a learning tool, keep in mind:

1. Record one action at a time – it makes the code easier to understand.
2. Pay attention to how Excel uses `Select` and `Selection` – these often make the code less efficient.
3. Notice the use of `With` blocks – they help group related actions on the same object.
4. Be aware that Excel may record many unnecessary commands.

Here are some additional tips for recording macros effectively:

1. Use keyboard shortcuts instead of the mouse: This makes the macro more reliable across different systems.
2. Use relative references: This allows the macro to work on different cells or ranges, not just fixed locations.
3. Keep the macro simple: Avoid complex logic or extra steps that aren't needed.
4. Add comments to your code: This helps others (and your future self) understand what the macro does.
5. Optimize for speed: Use arrays instead of loops when possible, and prefer built-in Excel functions over custom calculations.

Recording macros is a great way to explore how actions are translated into VBA code, and it helps you learn how to write and modify your own procedures.

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 the Visual Basic Editor

When you start working with the Visual Basic Editor (VBE), it is helpful to make a few customizations to improve your experience and avoid common mistakes.

If you're using the default configuration, consider the following steps:

1. Go to the Tools menu and choose Options:
   1. Uncheck Auto Syntax Check: This prevents pop-up error messages when you make a syntax mistake. Errors will still appear in red, but without interrupting you.
   2. Check Require Variable Declaration: This automatically adds `Option Explicit` at the top of every new module. It forces you to declare all variables, which helps avoid bugs caused by typos in variable names.

1. Go to the View menu and select Toolbars:
   1. Enable the Edit toolbar: It includes useful buttons such as Comment Block and Uncomment Block, which help when editing code.

These settings help you write clearer, more reliable code and make the editor easier to use.

Types of Procedures

There are two main types of procedures in VBA: Sub and Function.

Writing a Sub Procedure

A Sub (short for "Subroutine") is a block of code that performs actions but does not return a value. Sub procedures can accept arguments (constants, variables, or expressions), or none at all. Even if there are no arguments, parentheses are required.

Syntax of a 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 similar to a Sub, but it returns a value. Like a Sub, it can take arguments. The return value is assigned by setting the function's name to a value.

Syntax of a Function procedure:

[Public | Private | Friend] [Static] Function Name[(arglist)] [As DataType]
   [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

Functions (and Subs) can include Optional arguments. These arguments are not required when calling the procedure. If omitted, a default value is used.

Example:

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

This function returns the area or perimeter of a rectangle, depending on the third argument.

Variables

A variable is a named storage location used to hold data that can change during program execution.

Each variable:

• must have a unique name within its scope,
• must start with a letter,
• cannot contain spaces,
• cannot exceed 255 characters,
• cannot be a reserved VBA keyword (like `If`, `Next`, etc.).

You may or may not specify a data type for a variable. If you don't, it will be treated as a `Variant` by default.

Data Types

This table shows the most commonly used VBA data types.

To see the complete list, search for Data Type Summary in the VBA Editor Help (press F1 and type the phrase).

Common VBA Data Types

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 (32-bit) / 8 bytes (64-bit)	-2,147,483,648 to 2,147,483,647 (32-bit) -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (64-bit)
Single (floating-point)	4 bytes	-3.402823E38 to -1.401298E-45 (negative) 1.401298E-45 to 3.402823E38 (positive)
Double (floating-point)	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 (negative) 4.94065645841247E-324 to 1.79769313486232E308 (positive)
Currency	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 + length	Up to ~2 billion characters
Variant (numbers)	16 bytes	Any numeric value up to Double range
Variant (strings)	22 bytes + string length	Same as variable-length String

Declaring Variables

• If you do not explicitly declare a variable and `Option Explicit` is not enabled, the variable is implicitly created as a `Variant`.

a = 3   ' a is implicitly declared as Variant

• To declare a variable explicitly, use the `Dim` statement:

Dim x As Integer

• Declaring multiple variables in one line:

Dim a, b, c As Integer   ' Only c is Integer; a and b are Variants

• Correct version:

Dim a As Integer, b As Integer, c As Integer

Scope and Visibility

The scope defines where a variable can be used.

• Dim – declares a variable local to a procedure.
• Private – limits the variable to the module or form it's declared in.
• Public – makes the variable accessible to all procedures in the project.
• Static – preserves the variable's value between procedure calls.

All `Private` and `Public` declarations must appear in the declarations section (top) of a module.

Static counter As Integer
Public userName As String
Public Const VatRate As Double = 0.23

Lifetime of Variables

• A `Dim` variable declared inside a procedure exists only while that procedure runs.
• A `Static` variable inside a procedure keeps its value between calls.
• `Public` and `Private` variables in modules exist until the code execution ends.

Checking Data Types

Use `TypeName` to check a variable's data type:

Sub CheckTypes()
    Dim a, b, c, d, e, f
    a = "text"
    b = 23
    c = 4565768
    d = 45.67
    e = True
    f = #1/1/1999#

    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

Use `IsNumeric` to check if a value can be evaluated as a number:

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

Constants

Use `Const` to declare constants. By default, they are `Private`.

Const Pi As Double = 3.14159
Public Const AppName As String = "MyApp"

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

Exercises

1. Write a procedure which asks for your name using INPUTBOX and then displays a welcome message using MSGBOX.
2. 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.
3. 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.
4. Use MsgBox to display a message asking user whether they want to continue or not, and process answer.
5. Write a program that asks you to enter two numbers and then display greater of them.
6. Write a program that asks you to enter three numbers, then displays the greatest of them.
7. 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
8. 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

1. Write a program that enters into the cells from A1 to A20 all the numbers from 1 to 20.
2. 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"
3. 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).
4. 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.
5. Write a program that generates a multiplication table to 100.
6. 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

1. 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
2. Create a program that will download expression from the cell "A1", and write it in uppercase in the second sheet
3. 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:

1. Start by typing the keyword Function followed by the name of the function you want to create.
2. Next, add any input parameters enclosed in parentheses. If the function doesn't require any input parameters, you can skip this step.
3. Then, write the code that performs the desired task inside the function block.
4. 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.

Naming Convention for Form Controls

Control Type	Prefix	Example
Text Box	txt	txtFirstName
Label	lbl	lblLastName
Command Button	cmd	cmdSave
Check Box	chk	chkAgree
Option Button	opt	optMale
List Box	lst	lstColors
Combo Box	cbo	cboCountry
Image	img	imgLogo
Frame	fra	fraAddress
Scroll Bar	sbr	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.