There is a growing need to closely look at historical and current data in order to identify and extract valuable information that may help the metallurgist improve the process. Unfortunately, such tasks cannot simply be delegated to IT engineers, data scientists or statisticians who aren’t domain experts and do not appreciate the physical and chemical mechanisms pertaining to the metallurgical processes. Indeed, only the metallurgist knows the kind of information, relationship or mechanism that is valuable and the type of data that can be rejected/discarded.

With a little knowledge of VBA programming, however, a lot more can be accomplished in a much shorter time by automating most of the tasks. It does not even require any other software or programming languages such as Matlab, C, C++, Java, Python, and so on.

Depending on your Excel version, your Workbook interface may be slightly different from Figure 1, yet all versions have the same basic components. At the top are the menus, the toolbars or ribbons, the address and formula bars where we can see the address (column name and row number) of a selected cell (e.g., B5) and the formula it contains (e.g., = B3 + B4). The sheets are made of cells where rows and columns intersect. The sheet names/tabs and the status bar are found at the bottom of the workbook.

Figure 1: Microsoft Excel’s user interface

To help you with this course, let’s add the developer’s menu and ribbon as follows:

- First, right click on the ribbon and select “Customize the Ribbon”, or go to menu "File" then "Options" and select “Customize Ribbon” in the pop-up window.

- On the right hand side, select the check box "Developer" under the "Main Tabs" list box and click "Ok" to confirm and close the window. The common developer’s tools should appear under menu “Developer” and its ribbon.

To protect your computer from unsafe macros, please click on the button “Macro security” under the menu “Developer” and “Disable all macros with notification” under the “Macro settings” tab. Then go to “Trusted Locations” on the left-hand pane, “Add new location” and “Browse” to the folder where your [trusted macro enabled] Excel files are. Now let’s have a look at the Visual Basic Editor (VBE) in Figure 2. To open the VBE window choose one of the following options:

- right click on any sheet name/tab and select “View Code”, or

- click on button "View Code" under menu "Developer", or

- press the keys ALT+F11 on your keyboard.

The top sections of the Visual Basic Editor, like most Windows applications, are made of menu and tool bars. By default, the left hand side shows the “Project Explorer” and “Properties Window” panes. If the properties window does not appear, click on the button “Properties Window” on the toolbar or select “Properties window” under menu “View”. The right hand pane is the code window where we write the Visual Basic for Application (VBA) codes and is by default wider than the project explorer pane unless you resized it. You can also click on menu “View” and “Watch Window” to show the “Watches” window below the code that is helpful when debugging (see additional information and debugging at the end of this course).

Figure 2: Visual Basic Editor’s user interface

Note that an Excel project is made of the objects listed in the project explorer:

- Microsoft Excel Objects. They are made of workbook and sheets.

If you right click on any item in the project browser that belongs to the current project, you can also insert some:

- Modules where more codes can be written.

- Forms (windows/dialog boxes) which you can customise to interact with the user.

- Class modules where you can create more classes of objects which we won’t discuss in this course.

In VBA like in any other object oriented programming languages, classes of objects have attributes (properties) and methods (or functions). Some methods are inherent to the objects while additional ones can be user defined.

For example, all objects have a property called “name”. If you select “Sheet1” in the project explorer, its properties are shown in the properties windows (e.g., Name, Visible, etc…). Above the code window just under the ribbon, select the object "Worksheet" under the "(General)" dropdown/combo box and its methods/events or procedures are listed in the combo box next to it. Excel automatically selected the "SelectionChange" method and writes an empty subroutine in the code window as below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

The keyword Private means a method is only accessible from within the object's scope. Changing it to Public will make it accessible from anywhere within the entire project.

As you might have guessed, Sub stands for subroutine. When the method is called, the codes written within a subroutine will be executed until its last line End Sub. One of the differences between a subroutine and a function is that a function may return a value (by assigning the value to the function name) when it terminates while a subroutine doesn't. Nevertheless, you can perform as many calculations as you want within a subroutine.

The subroutine Worksheet_SelectionChange uses an input variable named Target of type Range of cells. There is nothing in between the parentheses for subroutines that do not have any input variable.

Populate the worksheet with some texts and numbers and let’s write our first VBA codes for the Worksheet_SelectionChange subroutine as shown in Example 1. The worksheet’s intrinsic method here is SelectionChange, so the codes will be executed every time you select or click a different cell than the currently active/selected cell. Once the worksheet is populated, select different cells to see how it works. The attached Mines.xlsm file is an example of the classic minesweeper game using the Worksheet_SelectionChange(ByVal Target As Range) procedure. You may not understand it all now but it’s good to explore.

Example 1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'variable declaration

Dim Message2Show As String

'assign value to the variable Message2Show

Message2Show = "Cell adress: " & Target.Address & vbCrLf & _

"Column: " & Target.Column & vbCrLf & _

"Row: " & Target.Row & vbCrLf & _

"Value: " & CStr(Target.Value) ' use the underscore character _ to break one line of code in multiple lines

'Display the content of the variable Message2Show in a message box

MsgBox Message2Show, vbInformation, "My first VBA code"

End Sub

Comments are written after a single quote or apostrophe (') and are not executed. Comments are important to explain the code to its future readers.

The keyword Dim is used to declare variables at the beginning of every subroutine and function. Here the variable Message2Show will contain some texts to show in a message box so it is defined as of type String. Variables declaration is not compulsory in VBA unless you type "Option Explicit" at the top of the code window to make it so. It is however a good practice to declare variables whenever possible. VBA is not case sensitive so once you declare the variable Message2Show, it will be automatically written as such even if you write all in lowercase (message2show) in any statement/line of the codes. More importantly, variable names must not have any space or special characters. Instead, the underscore character or capital first letter words can be used to clarify the meaning of their names.

Dots “.” are used to refer to objects’ properties and methods or events. Cells are called by the range of their names (e.g. B5) or by their row and column numbers.

For instance, cell B5 in Sheet1 can be referred to as Thisworkbook.sheets(“Sheet1”).Range(“B5”) or  Thisworkbook.sheets(“Sheet1”).cells(5,2) which means the intersection of the fifth row and the second column (column B). For flexibility, it is recommended to refer to cells with their row and column numbers (5,2) instead of range (“B5”). If “Sheet1” is the name of the first sheet, it can also be referred to as Thisworkbook.sheets(1) while the second sheet would be Thisworkbook.sheets(2) and so on. That is, the sheets’ names are written between double quotes but the sheets’ numbers aren’t.

The currently selected cell is the ActiveCell and to get or set its address and value, we use the dot referencing ActiveCell.Address and ActiveCell.Value. In our example, the ActiveCell is the same as the input variable Target for the subroutine Worksheet_SelectionChange whenever the user selects a different cell. Therefore, we can use Target.Address, Target.Column, Target.Row and Target.Value to access its address, column and row numbers as well as its content or value, respectively.

If a statement is too long, you can break it into more lines with the underscore character “_” and Excel will read and treat them as one single line of code.

The operator to concatenate strings or texts in VBA is the ampersand "&" symbol. Note that strings or texts are always written between double quotes. The operator to assign a value to the string variable Message2Show is the equal “=” symbol. The visual basic's carriage return and line feed vbCrLf creates a new line in the output, which in this example is the message to show via a message box.

The Target.Value here is converted to the type String with the type cast function CStr when it is concatenated with other texts or strings. That is, the Target.Value is treated as a string even if it’s a number. While it doesn't make much difference in this example since MsgBox automatically does the type conversion, it could cause an error in other situations without a proper type conversion.

The command MsgBox tells the code to prompt the user with the message to show, using a specific message box style vbInformation that is optional. This style shows the blue information icon. The message box title "My first VBA code" is also optional.

Table 1 lists the most common types of variables you will encounter.

Table 1: Common variable types and their respective storage size and ranges

Save the workbook as “Macro-enabled Excel file” (xslm file extension) and see how it works. Note that xlsx files are not “macro-enabled” and will ignore any code it may contain.
Download the file Example1.xlsm for more details.