Étiquette : vba

The data type of a variable specifies the kind of data that the variable can store. You specify the data type of a variable by including the keyword As. The syntax is as follows:

Dim VariableName As Type

If no data type is specified, the default type is Variant. VBA supports the following data types for variables:

Name	Type	Details
Byte	Numeric	Integer from 0 to 255.
Integer	Numeric	Integer from -32,768 to 32,767.
Long	Numeric	Integer from –2,147,483,648 to 2,147,483,647.
Currency	Numeric	Fixed-point number from –922,337,203,685,477.5808 to 922,337,203,685,477.5807
Single	Numeric	–3.402823E38 to –1.401298E-45 (negative); 1.401298E–45 to 3.402823E38 (positive)
Double	Numeric	–1.79769313486232E308 to –4.94065645841247E-324 (negative); 4.94065645841247E-324 to 1.79769313486232E308 (positive)
String	Text	Text.
Date	Date	Date and time.
Boolean	Boolean	True or False.
Object	Object	Microsoft object.
Variant	All	Any data type (default if not declared).

NOTE
When you find a statement in a VBA procedure that assigns a value to a variable, you can quickly locate the variable’s definition by selecting the variable name and pressing Shift+F2 or choosing View / Definition. Visual Basic jumps to the variable declaration line. Press Ctrl+Shift+F2 or choose View / Last Position to return to the previous cursor location.

Boolean Type

You can use the Boolean data type to store Boolean values. Boolean variables can only take two values: True or False. A Boolean variable occupies two bytes of space. The declaration is:

Dim ProductAvailable As Boolean

A Boolean variable, when converted to another type, returns -1 for True and 0 for False. When converting a numeric value to Boolean, 0 returns False and all other numbers return True.

Numeric Data Types

These types are used for mathematical operations such as addition, subtraction, multiplication, etc. For example: percentage calculation, stock price, fees, invoices, age, etc. In VBA, there are six numeric data types:

• Byte, Integer, and Long for integer values
• Single and Double for decimal values
• Currency for monetary values

NOTE
When you declare a variable as an integer type (e.g., Long) and assign it a decimal number, no error occurs, but only the integer part is retained.

More than a decade ago, memory optimization was important. Today, with increasingly powerful computers, such concerns are obsolete. You can use Long for all integers and Double for all decimals.

Date Type

You can use the Date data type to store a date. Each Date variable occupies eight bytes. The range for Date is from January 1, 0100 to December 31, 9999, and the time from 0:00:00 to 23:59:59.

Dates must be enclosed in hash signs (#) when assigned:

MyDate = #6/5/01# 
MyDate = #June 6, 2001#

VBA converts the literal date into the mm/dd/yyyy format. You can also assign a time similarly.

Object Type

An object variable refers to an entire object, like a Range or Worksheet. These variables are important because:

• They simplify your code.
• They speed up execution.

The Object data type can hold references to any object. However, it’s preferable to declare variables as the specific object type they reference:

Dim GenericObj As Object 
Dim SpecificObj As Worksheet

• GenericObj can reference any object.
• SpecificObj can only reference a Worksheet.

Using Object is called late binding, meaning the object type isn’t known until runtime, which disables IntelliSense and slows performance slightly.

Using a specific type is called early binding, enabling IntelliSense and helping you write code faster.

Declaring a specific object type doesn’t initialize it. Example:

Dim mySheet As Worksheet 
Set mySheet = Worksheets("Sheet6")

Use the Set keyword when assigning an object reference. You may also need to use the New keyword:

Set ObjectVar = New ObjectType 
Dim ObjectVar As New ObjectType

Without Object Variable:

Sub NoObjectType() 
    Worksheets("Sheet3").Range("C4").Value = "ELIE" 
    Worksheets("Sheet3").Range("C4").Font.Size = 16 
    Worksheets("Sheet3").Range("C4").Font.Name = "Verdana" 
    Worksheets("Sheet3").Range("C4").Font.Bold = True 
    Worksheets("Sheet3").Range("C4").Font.Italic = True 
End Sub

With Object Variable:

Sub WithObjectType() 
Dim myRange As Range Set 
     myRange = Worksheets("Sheet6").Range("C4") 
     myRange.Value = "ELIE" 
     myRange.Font.Size = 16 
     myRange.Font.Name = "Verdana" 
     myRange.Font.Bold = True 
     myRange.Font.Italic = True 
End Sub

String Type

Use the String type when your variable holds text. Strings are enclosed in quotation marks.

Dim myVar As 
String myVar = "This is a string"

By default, strings are variable-length. You can also declare fixed-length strings:

Dim myVar As String * 50

This sets the string length to exactly 50 characters. Extra characters are truncated; shorter strings are padded with spaces.

VBA supports only concatenation as a string operation, using & or +. Prefer & to avoid confusion:

Dim x As String 
x = "Visual Basic " & "for Applications"

Variant Type

The Variant type is the default for undeclared variables. You can declare it explicitly:

Dim myVar As Variant

Variants can hold strings, dates, booleans, or numbers and automatically convert between types. Numeric variants need 16 bytes; string variants need 22 bytes plus character storage. Due to memory usage, prefer explicitly declaring types.

User-Defined Types (UDT)

A user-defined type (UDT) lets you group different data types into a single structure. You define it using the Type statement, usually at the top of the module:

Type ClientInfo 
     Company As String 
     Phone As Long 
     City As String 
     POBox As Long 
     Income As Double 
End Type

Declare an array of this type:

Dim Clients(1 To 20) As ClientInfo

Access individual fields:

Clients(5).Company = "CESTAD ANALYTICS" 
Clients(5).Phone = 699072798 
Clients(5).City = "Douala" 
Clients(5).POBox = 15652 
Clients(5).Income = 17598000

Limitations:

• Cannot instantiate dynamically.
• Cannot validate fields.
• UDTs are static structures.

Use classes to overcome these.

Identifying Variable Types

Use VarType() to determine the data type of a variable:

Sub TestVariables() 
     firstName = "chancelin" 
     MsgBox VarType(firstName) 
     age = 33 
     MsgBox VarType(age) 
End Sub

Returns 8 (String), then 2 (Integer).

Value Type	Return Value
Variant	0
Null	1
Integer	2
Long	3
Single	4
Double	5
Currency	6
Date/Time	7
String	8
Boolean	11
Byte	17

If you perform math on a non-numeric Variant, you get a Type Mismatch error.

Functions to Test Variable Values

Numeric Value
Use IsNumeric() to test if a Variant holds a number:

Sub TestNumeric() 
     name = "chancelin" 
     MsgBox IsNumeric(name) 
End Sub

Returns False.

Date/Time Values
Date/time values are floating-point numbers. The integer part represents days since Dec 31, 1899; the decimal part represents time.

Example: 37786.75 = June 14, 2003, at 6:00 PM.

Use IsDate() to check for a date:

Sub TestDate() 
    myDate = "01-Feb-2002" 
    MsgBox IsDate(myDate) 
End Sub

Returns True.

Empty Value
Use IsEmpty() to check if no value has been assigned:

MsgBox IsEmpty(MyTest)

Returns True.

Null Value
A Variant can contain a special value Null, representing unknown or missing data. Use IsNull() to test for it.

Return Values

The table below lists constants returned by VarType():

Constant	Value	Description
vbEmpty	0	Variable is uninitialized.
vbNull	1	No valid data.
vbInteger	2	Integer
vbLong	3	Long integer
vbSingle	4	Single
vbDouble	5	Double
vbCurrency	6	Currency
vbDate	7	Date
vbString	8	String
vbObject	9	Object reference
vbError	10	Error code
vbBoolean	11	Boolean
vbVariant	12	Variant (only for arrays of variants)
vbDataObject	13	Non-ActiveX object reference
vbDecimal	14	96-bit scaled real
vbByte	17	Byte
vbLongLong	20	LongLong (64-bit only)
vbUserDefinedType	36	User-defined type
vbArray	8192	Array value

Shorter Variable Declarations

Advanced programmers may use abbreviations:

Detailed	Shorthand
Dim Client As Integer	Dim Client%
Dim MainClient As Long	Dim MainClient&
Dim Amount As Currency	Dim Amount@
Dim ClientInfo As String	Dim ClientInfo$

You can also use naming conventions to suggest data type, e.g.:

• str_message for a string
• i_SalesJanuary for an integer

A variable can simply be a single number, a piece of text, or a series of information that the program needs to retain and refer to during its execution. The program can change the value of a variable during its execution. That is why it is called a variable. If a variable has been defined as a given type, data specified as a different type will not be accepted. For example, if you define a variable as an integer, you cannot insert text into it.

Variable Declaration

To create a variable, you must declare it, that is, assign it a name that can then be reused to operate on the value stored in it. Variable declaration in VBA can be implicit or explicit. In other words, VBA programs can recognize a new variable without it being previously created in a declaration statement. You can also configure the Visual Basic Editor to require explicit variable declaration before use.

You can assign any name you like to a variable, as long as it follows these rules:

• It must begin with a letter.
• It cannot contain more than 255 characters.
• Periods, exclamation points, spaces, and the characters @, &, $, and # are not allowed.
• The variable name must not be a reserved word, meaning a word recognized as part of the Visual Basic language (function name, object, property, argument, etc.).

NOTE
Uppercase and lowercase letters are considered identical in variable names.

Implicit Declaration

It is not necessary to declare a variable before using it. You can simply include the statement:

DeclaVaria = 10

A variable will automatically be created for DeclaVaria as a Variant type (default type) and will have the value 10.

Variable types will be presented later in this chapter.
However, the problem is that this can lead to subtle errors in your code if you misspell the variable name in a later statement. For example, if you refer to DeVaria instead of DeclaVaria, you know what you mean, but VBA does not. It assumes DeVaria is a new variable and assigns it as such. The old variable DeclaVaria still exists but is no longer used. You now have two different variables, although you think you only have one. This can cause major issues that may take a long time to correct in your code.

Explicit Declaration

To avoid the issue of incorrect variable names, you can require VBA to always generate an error message whenever it encounters an undeclared variable.

To force variable declaration before use, add the Option Explicit statement. Its use helps prevent errors due to typos in variable names. To do this, place yourself in the Declarations section of the module’s Code window and type the Option Explicit statement, as shown in the following figure.

From now on, the appearance of variable names not previously declared using the Dim statement will generate an error, as in the figure.

You can also configure the Visual Basic Editor to automatically insert the Option Explicit statement in the Declarations section of every new module. Here’s how:

• Select the Options command from the Tools menu and go to the Editor tab.
• Check the box Require Variable Declaration, then click OK, as shown in the figure.

The method you use (implicit or explicit) depends on your personal preferences. Coding is often much faster with implicit declaration because you don’t need to define your variables beforehand. You can simply use them, and VBA will handle the rest. However, as mentioned earlier, this can lead to errors unless you have a good memory for the variables you use and the experience to know exactly what you are doing. Implicit declaration can also make it harder for someone else to understand your code. Using Option Explicit is the best practice and helps prevent runtime errors.

Declaring a Variable with the Dim Statement

Variable declaration is done using the Dim statement with the following syntax:

Dim VariableName As Type

Comments

• The Dim statement (short for Dimension) defines a name as a variable and allocates storage space for it.
• VariableName is the name chosen for this variable.
• The As keyword declares the data type.
• The Type argument represents the data type and can be values like Long (integer), Double (decimal), String (text type)… It is optional, but specifying a type often saves memory and improves program performance.

You can declare several variables on a single line:

Dim MyVar1, MyVar2, MyVar3 As Long

In this example, MyVar1 and MyVar2 are declared as Variant (the default type in VBA), and only MyVar3 is declared as Long. Why? Because Excel VBA requires that each variable’s type be declared explicitly, even if they’re on the same line (unlike C or many other languages). To declare all three variables as Long, the correct syntax is:

Dim MyVar1 As Long, MyVar2 As Long, MyVar3 As Long

Variable Scope and Lifetime

All procedures, functions, variables, and constants in VBA have their own scope. This means they can only be used in a specific area of the program’s code, precisely where they are defined.

For example, if variable A is defined in the body of a procedure named Procedure1(), then that procedure is its scope. So, if another procedure Procedure2() exists, you cannot use the same variable name in it. If you try, you will either get an error (if Option Explicit is active), or you will simply have another variable with the same name, unrelated to the first one.

There are three types of variable scope:

• Procedure-level scope: Variables declared with Dim or Static inside a procedure are recognized only within that procedure. These are called local variables.
• Module-level scope: Variables declared using Dim or Private outside of any procedure, in the declarations area of a module, can be used throughout that module only.
• Project-level scope: Variables declared with the Public keyword at the module level are available to all procedures in the project. These are called public variables.

A variable declared with Private retains its value only during the execution of the procedure where it is defined. After the procedure ends, its value is lost. When the procedure runs again, the variable is reinitialized. However, variables declared with Static retain their value after the procedure ends and while the program continues running.

Let’s examine the scope of procedures and functions.

Procedures and functions only have two levels of scope: module-level and project-level. The default is project-level. Therefore, a procedure or function can be called by any other procedure or function in the project. The optional Public keyword can also be used for project-level procedures. Its presence or absence has no effect on behavior.

If you want a procedure to be used only within the current module, use the Private keyword. Note that this not only restricts the procedure’s scope but also prevents it from being used as an independent procedure—it can only be called from another procedure.

Finally, the Static keyword can be used with procedures or functions. It does not affect the scope but affects all variables declared within it. In this case, all local variables retain their values after the procedure ends and preserve them when called again.

Example:

Public B1 As String
Private B2 As Integer
Dim B3 As Single
Sub Procedure1()
   Dim B4 As Integer
   Static B5 As Integer
   B1 = "Un texte"
   B2 = 2
   B3 = 3.14
   B4 = B4 + 4
   B5 = B5 + 5
   MsgBox B4
   MsgBox B5
End Sub
Sub Procedure2()
   Procedure1
   MsgBox B1
   MsgBox B2
   MsgBox B3
   MsgBox B4
   MsgBox B5
   Procedure1
End Sub

Comments

• In this example, B1 is defined at the project level (with Public), B2 and B3 at the module level, B4 at the procedure level (in Procedure1()), and B5 is defined as Static within Procedure1().
• When Procedure2() is called, it first calls Procedure1(), which assigns values to all five variables and displays the values of B4 and B5.
• Once Procedure1() ends, Procedure2() displays the current values of all five variables. Variables B1, B2, and B3 retain their values since they are declared at the project or module level. Variables B4 and B5, having procedure scope, are empty—each procedure uses its own variables even if they share names.
• When Procedure1() is called again, the values of B4 and B5 are updated and displayed. B4 is reset to 4, since it is reinitialized with each call, but B5, being static, retains its previous value, and is incremented to 10.

The Excel VBA Macro Recorder

The easiest way to create a macro is to record your worksheet actions using a valuable tool called Record Macro. All you have to do is turn on the macro recorder, perform the actions that make up the task you want to automate, then turn off the recorder once you’re done. While the macro recorder is active, every action you perform—selecting a cell, entering a number, formatting a range, almost anything—is recorded and represented as VBA code in a new macro. As you’ll see later, when you run the macro created by the recorder, your task is executed automatically as if you had done it manually.
The macro recorder is useful for repetitive common tasks that you’d prefer not to do manually.

Create Your First Macro Using the Macro Recorder

Let’s suppose we need to create a macro that activates a worksheet. So, if we have Sheet1 active, we will write a macro to activate worksheet Sheet2.

• Start Microsoft Excel and make sure the cell pointer is on Sheet1.
• To activate the macro recorder, go to the Developer tab on the ribbon, and in the Code group, click Record Macro.

In the Record Macro window that opens, set the required parameters for the recorded procedure: for example, assign the name ActivateSheet2 to the macro, enter appropriate explanations in the Description field, and leave the Store macro in field unchanged. Click OK.

The Macro Name and Description fields are used to specify the macro’s name and its description. A macro name must not contain spaces, and the description is important for reusable macros, as over time it becomes difficult to remember why a given macro was created. By default, macros are named Macro1, Macro2, etc. To facilitate macro recognition, it’s better not to use a standard name but a unique one that describes its purpose.

In last Figure, notice the small box next to Ctrl+ in the Shortcut key section. You can place any letter of the alphabet in this field, and when pressed with the Ctrl key, it becomes a handy way to run the macro.

A shortcut key is not mandatory; in fact, most of your macros won’t need one. But if you choose to assign one, it’s best to use Ctrl+Shift+Key rather than just Ctrl+Key. Excel has already assigned Ctrl plus the 26 letters of the alphabet to built-in shortcuts for various tasks, and it’s best not to override them. For instance, Ctrl+C copies text. However, if you assign Ctrl+C to your macro, you will override the default function and lose the ability to use Ctrl+C to copy text in that workbook.
To use the shortcut key option, click the Shortcut Key field, press Shift, then a letter like S. You’ve now created the shortcut Ctrl+Shift+S, which won’t interfere with Excel’s main shortcuts.

The Store macro in dropdown allows you to select the workbook where the macro will be saved. If you select Personal Macro Workbook, the macro will be saved in a special hidden workbook where macros are stored. This workbook is always open but hidden, and the macros in it are available for other workbooks. To view your personal macro workbook, go to the View tab, and in the Window group, click Unhide.
If you select This Workbook (the default choice), the macro is stored in the active workbook. If you select New Workbook, it will be saved in a new workbook.

• While recording the macro, go to Sheet2 (the pointer is on cell A1).

Click the Stop Recording button in the Code group under the Developer tab to stop recording the macro.6.

• Save your workbook in a format that supports macros: go to the File tab on the ribbon and select Save As. In the Save As dialog box (Figure 4), choose a save location, enter the workbook

name, and in the Save as type dropdown, choose Excel Macro-Enabled Workbook. Click Save.

• Go to the Developer tab, click the Macros button in the Code group, and in the Macro window that opens, select your macro from the list (Figure 5) and click Edit.

The screen displays the VBA Editor with the standard module containing the macro code just recorded:

Sub ActivateSheet2() 
' 
' ActivateSheet2 Macro 
' 
   Sheets("Sheet2").Select 
End Sub

Notes

• A macro starts with the Sub statement and ends with End Sub. We’ll go deeper into macro structure in later chapters.
• ActivateSheet2() is the macro name.
• The instruction Sheets("Sheet2").Select is the macro body.

Now, without closing this workbook, create another workbook by going to the File tab, selecting New, and choosing Blank Workbook.

• Go to the Developer tab and click Macros in the Code group.
  In the Macro window (Figure 6), specify the name of the macro you created and click Run. Ensure that this macro activates Sheet2 in your workbook.

Assigning a Created Macro to a Button

Now let’s assign our created macro to a button placed on the Quick Access Toolbar. Follow these steps:

• Right-click on the Quick Access Toolbar and select More Commands… (Figure 7)

In the Excel Options window that opens, in the Quick Access Toolbar category, select Macros from the Choose commands from dropdown list .

Select the macro ActivateSheet2 that you created in the left column and use the Add >> button to move it to the right column. Note that the Modify button is now available at the bottom of the right column—it’s used to assign an icon to the corresponding macro. Click the Modify button.

• In the Modify Button window that opens (Figure 9), choose a symbol for the button using your mouse and, if needed, in the Display Name field, edit the macro name. This display name will appear as a tooltip in the Quick Access Toolbar. Click OK.

• In the Excel Options window under the Quick Access Toolbar category, the macro button appears in the right column (Figure 10). Click OK.
• Make sure the button with the recorded macro appears in the Quick Access Toolbar.

Automating Worksheet Tasks with Controls

Excel also includes a complete set of controls—command buttons, text boxes, checkboxes, etc.—that you can place on a worksheet if needed. To view the available controls, go to the Developer tab, and in the Controls group, click the Insert button.

Note: the controls under the Form Controls group are primarily intended for compatibility with files from older Excel versions (up to Excel 97) that use these controls. They are much more limited in capability than the ActiveX Controls. Some of these form controls cannot be used at all in recent Excel documents (text box, list box, combo box). However, these controls have advantages not found in ActiveX controls—for example, they can be placed on chart sheets.

ActiveX controls are standalone components from various applications and can also be used in Microsoft Excel. This group includes controls similar to many in the Form Controls group (UserForm).

In addition to standard controls, you can use extra controls. Excel comes with several of these, such as multimedia controls that let you play sound or video directly from the worksheet. You can also connect controls from other programs or use custom-built controls.

In the worksheet module, you can create procedures that handle events triggered by these controls—for example, pressing a button, selecting a list item, checking an option box, etc. These actions can automatically trigger calculations, build charts, change chart types, and more.

Using a Command Button Control on a Worksheet

We’ll demonstrate how to use an ActiveX Command Button on a worksheet. Suppose that when we click the button, placed on Sheet1, Sheet2 is activated.

• Start Excel and make sure the cell pointer is on Sheet1.
• Go to the Developer tab, and in the Controls group, click the Insert dropdown list.
• Click on the Command Button (ActiveX Control) and move directly to the worksheet—your pointer turns into a thin cross.

Select a location on the worksheet, press the left mouse button, and while holding it, draw the button to the required size. Then release the button.
Note that once the Command Button appears on the sheet, Design Mode is activated.

On the first button’s surface, the default label CommandButton1 is automatically displayed (Figure 14). If you create a second Command Button now, it will be labeled CommandButton2, and so on.

NOTE:
Like any graphical object, a button can be drawn while holding Shift to give it a square shape, or Alt to snap it to the worksheet grid. With the sizing handles, you can adjust its dimensions, and with the move handle, you can set its position.

Right-click the created Command Button and in the context menu, select Properties to open the Properties window (see Figure 14). The Command Button is an object, meaning it has properties, methods, and events.

The Caption property sets the name displayed on the button surface. The Name property identifies the object in code. In this case, it’s also CommandButton1. In the Properties window, change the Caption from « CommandButton1 » to Sheet2 (to indicate that this button activates Sheet2). Optionally, test other properties: BackColor, Font, ForeColor, Shadow. Finally, close the Properties window.

• We’ll now write the procedure to handle the Click event. When the event is triggered, Sheet2 will be activated.
  Double-click the created Command Button (ensure Design Mode in the Developer tab is still active). This opens the VBA editor with the worksheet module for Sheet1, and automatically adds the starting and ending lines of the event procedure:

Private Sub CommandButton1_Click() 
End Sub

• Open the Windows file where you created the ActivateSheet2 macro. Make sure the required module is displayed in the VBAProject – Project Explorer window.
• Copy the macro’s line into the clipboard:

Sheets("Sheet2").Select

Of course, you could manually type it, but that’s slower and risks typos. Now insert that line inside the event procedure:

Private Sub 
   CommandButton1_Click() 
   Sheets("Sheet2").Select 
End Sub

Return to Sheet1. The button works only when Design Mode is off. So, click Design Mode in the Controls group on the Developer tab to deactivate it.
8. Test the created Command Button: click it. If everything was done correctly, it will activate Sheet2.

 

Another Example Using the Macro Recorder

Suppose you manage a data table daily, such as the one shown in the following figure, which displays the number of items sold by your company in its East, West, North, and South regions.

The daily task consists of sorting the table primarily by region, then by item. Your boss wants the columns NAME and REGION to switch places, so that REGION appears in column A and NAME in column B. To improve readability, the numbers in the AMOUNT column must be formatted with a comma separator, and the headers for REGION, NAME, and AMOUNT must be bolded. Next Figure16 shows the finished table, as requested by your boss.

This is normally a six-step process, which is quite tedious but part of your professional responsibilities.

NOTE

Whenever you create a macro, it’s a good idea to plan ahead: think about why you’re creating the macro and what you want the macro to do. This is especially important for complex macros, as you want your code to be efficient and accurate, using only the instructions necessary to get the job done properly. Avoiding excessive code will result in macros that run faster and are easier to edit or troubleshoot. For instance, prepare your workbook in advance to avoid recording unnecessary actions. Make sure the worksheet you’re working on is active and the relevant range is visible.

To complete the task, proceed as follows:

• Insert a new column at column A.
• Select the REGION column, cut it, and paste it into the new column A, to the left of NAME.
• Delete the now-empty column where REGION was.
• Select the range A1:C13 and sort in ascending order by REGION, NAME, and AMOUNT.
• Select range C2:C13 and format the AMOUNT values with a thousands separator.
• Select range A1:C1 and apply bold formatting to the headers.

These steps are not only repetitive but prone to human error. The good news is, if you perform the steps correctly while recording the macro, the task can be reduced to a single mouse click or keyboard shortcut, with VBA doing the heavy lifting for you.

To record the macro that performs this task, follow these steps:

• Start the macro recorder: go to the Developer tab on the ribbon and in the Code group, click Record Macro.
• In the Record Macro dialog box, enter "FormatData" in the Macro name field, select This Workbook for Store macro in, then click OK.
• Now perform the six steps listed above.
• Stop the macro recorder by clicking Stop Recording in the Code group on the Developer tab.

As a result, the following macro will be written to a standard module:

Sub FormatageDonnees()
' FormatageDonnees Macro
' Data Formatting

    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Columns("C:C").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste

    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft

    Range("A1:C13").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A13"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B13"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C13"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C13")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Range("C2:C13").Select
    Selection.NumberFormat = "#,##0"

    Range("A1:C1").Select
    Selection.Font.Bold = True
End Sub

Comments

• As seen before, all macros begin with a Sub statement (short for Subroutine, commonly known as a macro), which includes the macro name followed by parentheses.
• The comments you see in a recorded macro reflect the information entered in the Record Macro dialog box. For example, if you assign a shortcut key or enter a description, these appear as comments in the code.
• The remaining lines are VBA instructions representing every action performed while recording the macro.

Here’s a breakdown:

• You selected column A.
• You inserted a new column.
• You selected column C, cut it, and pasted it into column A.
• You selected the now-empty column C and deleted it.
• You selected range A1:C13 (the data table).
• You sorted the data.
• You selected C2:C13 to format the numbers.
• You applied a thousands separator.
• You selected A1:C1 for the headers.
• You bolded the font in those cells.
• You stopped the macro recording, which ends with End Sub.

Improving the Recorded Macro

It’s easy to generate a macro with the Record Macro button. However, the recorded macro often lacks efficiency and produces unnecessary code. To be fair, the Record Macro tool isn’t designed to write optimized code. Its purpose is to produce VBA code that mirrors your on-screen actions exactly.

There’s no rule that says you must always clean up your recorded macros. For simple tasks that work as intended, keeping them as-is can be perfectly fine.

However, for most recorded VBA code, the unnecessary and inefficient parts can’t be ignored. Moreover, if you plan to share your VBA work, you’ll want it to look clean and professional.

A key VBA development principle is: Avoid Select and Activate unless necessary. These are often the main reason for slow-running macros.

For example, these two recorded lines:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight

Can and should be simplified to:

Columns("A").Insert Shift:=xlToRight

Similarly, this code:

Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste

Can be replaced with:

Columns("C").Cut Destination:=Columns("A")

And this code:

Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Can be simplified to:

Columns("C").Delete Shift:=xlToLeft

By streamlining the code, your FormatData macro becomes more readable and much more efficient.

Introduction to the Visual Basic Editor (VBE) in Excel
It is fair to say that for many Excel users, worksheets, pivot tables, charts, and hundreds of formula functions are all the tools they need to satisfactorily manage their spreadsheet activities. For them, the familiar workbook environment is the only aspect of Excel they see, and it is obviously the only aspect of Excel they are likely aware of.
But Excel has a separate, less visible environment working behind the scenes called the VBE (Visual Basic Editor), which is interconnected with the workbook environment even if no programming code exists in the workbook. The two environments constantly but quietly work together, sharing information about the workbook as a whole. The Visual Basic Editor is a user-friendly development environment where programmed instructions are stored to make your spreadsheet applications work.

How to Launch the Visual Basic Editor
The VBE icon is found in the Developer tab of the ribbon. By default, the Developer tab is not automatically displayed with the other ribbon tabs. Use the following steps to make the Developer tab visible:
■ Click on the File tab, then click the Options button as shown in Figure. The Options dialog box opens.

■ Click on the Customize Ribbon item on the left, which displays two vertical lists, as shown in Figure 2. Note that the list on the right has a dropdown menu above labeled Customize the Ribbon.
■ Select the Main Tabs item in the Customize the Ribbon dropdown list.

■ In the list of Main Tabs, select Developer and click OK. You will now see the Developer tab in your ribbon, as illustrated in Figure.

To finish, click on the Visual Basic button in the Developer tab of the ribbon, as shown in Figure.

Another quick and easy way to access the Visual Basic Editor is by pressing Alt + F11 on your keyboard. You can do this from any worksheet.
NOTE:
The Alt + F11 keyboard shortcut allows you to access the Visual Basic Editor in all versions of Excel.

Main Windows of the VBE

The Visual Basic Editor can display a number of different windows, depending on what you want to see or do. For most tasks, you should become familiar with four windows: the Project Explorer window, the Code window, the Properties window, and the Immediate window. Figure 1.5 shows what the VBE looks like with these four windows.
Figure 1.5 The four main VBE windows

The Project Explorer Window

The Project Explorer window is a vertical pane on the left side of the VBE. It displays a hierarchical list of currently open projects and their elements. A VBA project may contain the following elements:
■ Worksheets
■ Chart sheets
■ ThisWorkbook: the workbook in which the project is stored
■ Modules: special sheets where programming code is stored
■ Classes: special modules that allow you to create your own objects
■ Custom Forms (UserForms)
■ References to other projects

You can activate the Project Explorer window in three ways:
■ From the View menu, select Project Explorer.
■ From the keyboard by pressing Ctrl + R.
■ From the Standard toolbar by clicking the Project Explorer button.

The Code Window

The Code window is used for Visual Basic programming and for viewing and editing recorded macros and existing VBA procedures. Each module can be opened in a separate Code window. There are several ways to activate the Code window:
■ In the Project Explorer window, select the appropriate UserForm or module, then click the View Code button.
■ In the menu bar, choose View | Code.
■ On the keyboard, press F7.

The Code window is made up of several parts:
■ The code input area
■ Two dropdown lists at the top of the Code window, allowing you to quickly navigate through your Visual Basic code
■ The margin indicator bar used by the VBE to display helpful markers during editing and debugging
■ Two icons at the bottom-left of the Code window:

• Procedure View displays one procedure at a time in the Code window
• Full Module View displays all procedures in the selected module

The Properties Window

The Properties window is located in the lower part of the left vertical pane of the VBE. It allows you to view and set properties for various objects in your project. Object properties can be viewed alphabetically or by category by clicking the appropriate tab:
■ Alphabetic Tab: Lists all properties of the selected object in alphabetical order.
You can modify a property’s setting by selecting the property name and typing or selecting the new value.
■ Categorized Tab: Lists all properties of the selected object by category.
You can collapse the list to view categories or expand a category to see its properties. The plus (+) sign to the left of a category name indicates that the list can be expanded. The minus (–) sign means the category is currently expanded.

The Properties window can be accessed in three ways:
■ From the View menu, select Properties Window.
■ From the keyboard by pressing F4.
■ From the toolbar by clicking the Properties Window button.

The Immediate Window

The Immediate window is located at the bottom of the VBE, usually below the Code window, as shown in Figure 5.
The Immediate window is used to try out various instructions, functions, and operators present in the Visual Basic language before using them in your own VBA procedures. The Immediate window allows you to type VBA statements and immediately test their results without writing a full procedure. The Immediate window is like a notepad. Use it to test your statements. If the instruction produces the expected result, you can copy it from the Immediate window into your procedure.
If you do not see the Immediate window in your VBE, press Ctrl + G or, in the VBE menu bar, click View → Immediate Window.
To close the Immediate window, click the Close button in the upper-right corner of the window.

What is VBA?
VBA is a programming language created by Microsoft to automate operations in Excel. In addition to Excel, VBA can also manipulate other Microsoft Office applications such as Access, Word, PowerPoint, and Outlook. It is an extremely powerful tool that allows you to control many methods in Excel that you cannot or do not want to do manually. VBA is the tool you use to develop macros and manipulate objects to control Excel and other Office applications from within Excel. You do not need to purchase anything other than the Office suite to also have VBA. If you have Excel on your computer, then you have VBA on your computer.

What is a “macro”?
VBA is therefore a programming language, and it is also a macro language. Terminology confusion arises when referring to VBA code, which is a series of commands written and executed in Excel. So, what is a macro? A macro can be described as a sequence of instructions written in the VBA language that are stored in a module. When we call a macro, by pressing a button or using a key combination, the instructions stored in it are triggered.
With macros, we can not only streamline tasks that we perform frequently, but we can also extend Excel’s functionality by creating new functions to solve calculations that cannot be performed with the program’s standard functions.
There are two ways to create a macro. One uses the Record Macro tool, and the other involves writing the instructions directly in the Visual Basic Editor built into Excel. Since Excel 2007, Microsoft has distinguished between saving a workbook with macros (file extension .xlsm) and a workbook without macros (file extension .xlsx).

There is a big difference between VB and VBA!
With all the acronyms circulating in the world of computing, it’s easy to confuse certain terms. VB means Visual Basic, and it is not the same as VBA.
Although both VB and VBA are programming languages derived from BASIC and created by Microsoft, they are otherwise very different.
VB is a language that allows you to create executable, standalone applications that do not even require users to have Office or Excel installed on their computers.
VBA, on the other hand, cannot create standalone applications. It can only exist within a host application such as Excel and the workbook that contains the VBA code. For a VBA macro to run, its host application’s workbook must be open.
This book is based on VBA and how it controls Excel.

What can you do with VBA?
Everyone reading this book uses Excel for their own purposes, such as financial budgeting, forecasting, scientific data analysis, creating invoices, or tracking the progress of their favorite soccer team. One thing all readers have in common is the need to automate some frequently encountered task that takes too much time or is too cumbersome manually. This is where VBA comes into play.
Many VBA commands are at your disposal and are relatively easy to implement and customize for your daily needs.
Anything you can do manually, you can do with VBA — but VBA allows you to do it faster and with a reduced risk of human error.
Many things that Excel doesn’t allow you to do manually can be done using VBA. Here are a few examples of what VBA can do for you:

• Automate a recurring task: If you need to produce weekly or monthly sales and expense reports, a macro can create them in no time. And if the source data changes later in the day and you need to generate the updated report again — no problem, just rerun the macro!
• Automate a repetitive task: When you need to perform the same task on every worksheet in your workbook or in every workbook in a particular folder, you can create a macro to loop through each object and perform the action.
• Automatically run a macro when another action occurs: In some cases, you want a macro to run automatically so you don’t have to remember to run it yourself. For example, to automatically refresh a PivotTable when its source data changes, you can monitor those changes with VBA, ensuring your PivotTable always displays real-time results. This is called event-driven programming.
• Create your own worksheet functions: You can create your own worksheet functions, called User Defined Functions (UDFs), to handle custom calculations that Excel’s built-in functions do not support.
• Create full-scale applications driven by macros:
  If you’re willing to invest the time, you can use VBA to create large-scale applications with a custom Ribbon tab, dialog boxes, screen tips, and many other features.
• Create custom Excel add-ins:
  You’re probably familiar with some of the add-ins that come with Excel — for example, the Analysis ToolPak is a popular one. You can use VBA to develop your own add-ins for specific purposes.