6  Data IO in VBA

Data IO (Input/Output) operations are essential in programming to read data from external sources, process it, and write the results back to the same or different sources. In VBA, you can perform data IO operations using various methods, including:

• Excel Sheets: Read and write data to Excel worksheets using the Range and Cells objects.
• Keyboard: Collect input using the InputBox function.
• UserForms: Collect and display data using custom forms, including MsgBox and text boxes.
• Console: Output data to the console with Debug.Print. The console is the Immediate Window in the VBA editor (activated with Ctrl+G).

Other IO Methods in VBA

Additional methods include working with text files, databases, web services, APIs (Application Programming Interfaces), emails (via the Outlook object model), and other formats such as XML and JSON. These methods can be used for both input and output depending on the specific need. Research and explore these methods based on your requirements.

6.1 Writing Data To Console (Immediate Window)

The console is a useful tool for debugging and displaying information while running VBA code. A console is a text-based interface where you can output messages, variables, and other information during program execution.

Some common uses of the console include:

• Displaying intermediate results during program execution.
• Debugging code by printing variable values.
• Providing feedback to the user about the program’s progress.
• Logging information for troubleshooting and analysis.
• Testing and verifying the correctness of the program’s logic.
• Monitoring the flow of control in the program.
• Checking the values of variables at different points in the program.

To write data to the console in VBA, you can use the Debug.Print statement. The Debug.Print statement outputs text, variables, and expressions to the console for debugging purposes. The syntax for the Debug.Print statement is Debug.Print expression, where expression is the text, variable, or expression you want to output. For example:

• Debug.Print "Hello, World!": Outputs the text “Hello, World!” to the console.
• Debug.Print variableName: Outputs the value of the variable variableName to the console.
• Debug.Print expression1, expression2: Outputs multiple expressions separated by commas to the console.
• Debug.Print "Value of x is: ", x: Outputs the text “Value of x is:” followed by the value of the variable x to the console.

The console in VBA is called the Immediate Window, and you can view the output of Debug.Print statements in this window. To open the Immediate Window in the VBA editor, press Ctrl+G or go to View > Immediate Window.

Debugging in VBA

Once you are finished debugging, it is a good practice to remove or comment out Debug.Print statements from your code to keep it clean and efficient.

In Listing 6.1, we use the Debug.Print statement to output messages and data to the Immediate Window in the VBA editor.

Listing 6.1: Printing data to the Immediate Window using the Debug.Print statement. The output includes a benchmark table with method names, runtimes, and costs. Using commas allows you to print multiple values on the same line, aligned in columns.

Sub PrintDataInImmediateWindow()
    
    Debug.Print "## Benchmark ########################"
    Debug.Print "Method", "Runtime(s)", "Cost($)"
    Debug.Print "Exact", "7200", "500"
    Debug.Print "Heuristic", "60", "700"
    Debug.Print "ML", "60", "600"
    
End Sub

Figure 6.1: VBA Immediate Window result of the code in Listing 6.1.

6.2 Showing Data in Message Boxes

Message boxes are dialog boxes that display information, prompt the user for input, or provide feedback during program execution. In VBA, you can use the MsgBox function to show message boxes with different types of buttons, icons, and titles. The syntax for the MsgBox function is MsgBox(prompt, [buttons], [title], [helpfile], [context]), where:

• prompt: The message to display in the message box.
• buttons: The type of buttons to display in the message box (optional).
• title: The title of the message box (optional).
• helpfile: The help file to use for the message box (optional).
• context: The context ID for the help file (optional).

The prompt argument is required, while the other arguments are optional.

Message Box Buttons

You can customize the buttons displayed in the message box by specifying the buttons argument. Typical buttons values include:

• vbOKOnly: Display only the OK button (default).
• vbOKCancel: Display OK and Cancel buttons.
• vbYesNo: Display Yes and No buttons.

You can also chose the icon displayed in the message box by specifying the buttons argument. Typical buttons values for icons include:

• vbCritical: Display a critical message icon.
• vbQuestion: Display a question mark icon.
• vbExclamation: Display an exclamation point icon.

You can combine the button and icon values using the + operator, for example, vbExclamation + vbOKOnly.

See more details about the MsgBox function at Microsoft’s official documentation.

Listing 6.2: Printing data to message boxes using the MsgBox function. The output includes a simple message box with different styles and titles. The vbExclamation style displays an exclamation point icon.

Sub PrintDataUsingMsgBox()

'   Print "Hello, World!" in a message box
    MsgBox "Hello, World!"
    
'   Print "Hello, World!" in a message box with 
'   the title "My VBA First Program'
    MsgBox "Hello, World!", , "My VBA First Program"
     
'   Print "Hello, World!" in a message box with 
'   "vbExclamation" style with the title "My VBA First Program'
    MsgBox "Hello, World!", vbExclamation, "My VBA First Program"
    
End Sub

In Listing 6.3, we use the MsgBox function to display a message box with Yes and No buttons. The user can click on either button to provide input, and the response is stored in a variable for further processing. The response values are 6 for Yes and 7 for No.

Listing 6.3: Printing data to a message box with Yes and No buttons using the MsgBox function. The output includes a message box with a question mark icon and the title “Confirmation”. The response is stored in the variable response, and the result is printed to the Immediate Window. The value of response will be 6 for Yes and 7 for No.

Sub PrintDataUsingMsgBoxWithYesNo()

    'Display a message box with Yes and No buttons
    response = MsgBox("Do you want to continue?", vbYesNo+vbQuestion, "Confirmation")
    Debug.Print response

End Sub

6.3 Reading Data From Keyboard

To read data from the keyboard, you can use the InputBox function to prompt the user for input.

In Listing 6.4, we read a textual input and a numerical input from the user using the InputBox function. The syntax for the InputBox function is InputBox(prompt, [title], [default], [xpos], [ypos]), where:

• prompt: The message to display in the input box.
• title: The title of the input box (optional).
• default: The default value for the input box (optional).
• xpos: The x-coordinate of the input box (optional).
• ypos: The y-coordinate of the input box (optional).

Listing 6.4: Reading data from the keyboard using the InputBox function. The user is prompted to enter their name and age, and the inputs are displayed in message boxes.

Option Explicit

Sub ReadDataFromKeyboard()
    
    'A textual input
    Dim textualInput As String
    textualInput = InputBox("Please enter your name:")
    MsgBox (textualInput)
    
    'A numerical input
    Dim numericalInput As Integer '<--- VBA will convert numerical input
    numericalInput = InputBox("Please enter your age:")
    MsgBox (numericalInput)

End Sub

Reading Numerical Data

If the type of the variable is not specified, the InputBox function will return the input as a string, even if the input is numerical.

6.4 IO in Excel Spreadsheets

To write data to an Excel sheet, you can use:

1. The Cells property of the Worksheet object.
2. The Range object to specify the cell or range of cells where you want to write the data.

6.4.1 Cells Property

The Cells property of the Worksheet object allows you to access cells using row and column indices.

The syntax for reading data using the Cells property is Cells(row, column), where:

• row: The row index of the cell.
• column: The column index of the cell.

The syntax for writing data using the Cells property is Cells(row, column) = value, where:

• row: The row index of the cell.
• column: The column index of the cell.
• value: The data you want to write to the cell.

In Excel, rows and columns are indexed starting from 1. For example, cell A1 is in the first row and first column, cell B2 is in the second row and second column, and so on.

For example, in Listing 6.5, we write data to an Excel sheet using the Cells property. The values “A1”, “B1”, “C1”, and “D1” are written to the first row, and “A2”, “B2”, “C2”, and “D2” are written to the second row.

Listing 6.5: Writing data to the active worksheet using the Cells property.

Sub WriteDataInSpreadsheetUsingCells()

Cells(1, 1) = "A1"
Cells(1, 2) = "B1"
Cells(1, 3) = "C1"
Cells(1, 4) = "D1"

Cells(2, 1) = "A2"
Cells(2, 2) = "B2"
Cells(2, 3) = "C2"
Cells(2, 4) = "D2"
End Sub

Overwriting Data

Be careful when writing data to Excel sheets using the Cells property. If you write data to cells that already contain data, the existing data will be overwritten. Pressing Ctrl+Z (Undo) in Excel won’t revert the changes made by VBA code.

Therefore, it’s important to ensure that you are writing data to the correct cells to avoid unintentional overwriting of existing data. Do a backup, and a backup of the backup, before running VBA code that writes data to Excel sheets!

6.4.2 Range Object

The Range object in VBA allows you to specify a range of cells where you want to write data.

The syntax for writing data using the Range object is Range("cell1:cell2") = value, where:

• cell1: The address of the first cell in the range.
• cell2: The address of the last cell in the range.
• value: The data you want to write to the range.

For example, in Listing 6.6, we write data to an Excel sheet using the Range object.

Listing 6.6: Writing data to the active worksheet using the Range object.

Sub WriteDataInSpreadsheetUsingRange()

Range("A1") = "A1"
Range("B1") = "B1"
Range("C1") = "C1"
Range("D1") = "D1"

Range("A2") = "A2"
Range("B2") = "B2"
Range("C2") = "C2"
Range("D2") = "D2"

End Sub

You can also specify the cells in a range using the Range object with the Cells property. For example, in Listing 6.7, we write data to a range of cells using the Range object with the Cells property.

Listing 6.7: Writing data to a range of cells using the Range object with the Cells property.

Sub WriteDataInSpreadsheetUsingRangeCells()

Range(Cells(1, 1), Cells(1, 1)) = "Header"

6.4.2.1 Defining Ranges

When working with ranges in Excel, you can define ranges using the Range object with different methods:

1. Using Cell Addresses: You can define a range by specifying the addresses of the first and last cells in the range. Example:
   • Range("A1:B2") defines a range from cell A1 to cell B2.
   • Range("A1", "B2") is equivalent to the above.
   • Range("A1:B2,C3:D4") defines a non-contiguous range.
2. Using Named Ranges: You can define a range using named ranges defined in Excel. Example:
   • Range("MyRange") refers to a named range called “MyRange”.
   • Range("MyRange1,MyRange2") refers to multiple named ranges.
   • Range("MyRange1:MyRange2") refers to a range between two named ranges.
   • Range("MyRange1,MyRange2,C3:D4") refers to a combination of named ranges and cell addresses.
3. Using Offset: You can define a range by offsetting from a specific cell. The syntax is Range("Cell").Offset(RowOffset, ColumnOffset). Example:
   • Range("A1").Offset(1, 2) defines a range starting from cell A1 and offset by 1 row and 2 columns.
   • Range("A1").Offset(0, 2) defines a range starting from cell A1 and offset by 2 columns.
4. Using Entire Rows or Columns: You can define a range by selecting entire rows or columns. Examples:
   • Range("1:1") selects the entire first row.
   • Range("A:A") selects the entire first column.
   • Range("1:1,3:3") selects multiple rows (1 and 3).
   • Range("A:A,C:C") selects multiple columns (A and C).
   • Range("1:1,A:A") selects a combination of rows and columns (first row and first column).
   • Range("1:1,A:A,C3:D4") selects a combination of rows, columns, and cell addresses (first row, first column, and range C3:D4).

• Using Cells: You can define a range using the Cells property of the Worksheet object. Example:
  • Range(Cells(1, 1), Cells(2, 2)) defines a range from cell A1 to cell B2.
  • Range(Cells(1, 1), Cells(2, 2), Cells(3, 3)) defines a non-contiguous range.
  • Range(Cells(1, 1), Cells(2, 2), "C3:D4") defines a combination of cells and cell addresses.
  • Range(Cells(1, 1), Cells(2, 2), Range("C3:D4")) defines a combination of cells and ranges.
  • Range(Cells(1, 1), Cells(2, 2), Range("C3:D4"), "E5") defines a combination of cells, ranges, and cell addresses.

To define a named range in Excel, you can:

1. Select the Range: Select the range of cells you want to name, right-click, and choose Define Name. Then, enter the name for the range.
2. Use the Name Box: Click in the Name Box (the box to the left of the formula bar), enter the name for the range, and press Enter.

Named ranges can be used to refer to specific ranges of cells in Excel formulas and VBA code, making it easier to work with data in worksheets. It is a good practice to define named ranges for important data in your Excel workbooks to improve readability and maintainability. For example, if you have a range of cells containing sales data for a specific month, you can define a named range like “SalesJanuary” to refer to this range in your formulas and VBA code.

6.4.3 Where Will the Data Be Written?

When you write data without specifying a worksheet, VBA will use the active worksheet by default. If you want to write data to a specific worksheet, you should specify the worksheet using the Sheets collection.

The Sheets collection allows you to access worksheets by name or index. The syntax is Sheets("SheetName") or Sheets(Index), where:

• SheetName: The name of the worksheet you want to access (in double quotes, up to 31 characters).
• Index: The index of the worksheet you want to access (starting from 1).

The maximum number of worksheets in an Excel workbook is limited by available memory and system resources. Normally, the number of worksheets is limited to 255, but this can vary depending on the version of Excel and the system configuration.

For example, assuming we have a worksheet named “Sheet1” at the first position in the workbook, we can write data to this specific worksheet using the Cells property as shown in Listing 6.8.

Listing 6.8: Writing data to a specific worksheet using the Cells property. Both the worksheet name and index are used to access the worksheet.

Sub WriteDataInSpreadsheetUsingCells()

    Sheets("Sheet1").Cells(1, 1) = "A1"
    Sheets("Sheet1").Cells(1, 2) = "B1"
    Sheets("Sheet1").Cells(1, 3) = "C1"
    Sheets("Sheet1").Cells(1, 4) = "D1"

    Sheets(1).Cells(2, 1) = "A2"
    Sheets(1).Cells(2, 2) = "B2"
    Sheets(1).Cells(2, 3) = "C2"
    Sheets(1).Cells(2, 4) = "D2"
    
End Sub

When Option Explicit is used, you must declare the Sheets object before using it. The Dim statement is used to declare the variable targetSheet as a Worksheet object. The Set statement is used to assign the worksheet named “Sheet1” to the variable targetSheet. In Listing 6.9, we write data to the worksheet “Sheet1” using the Cells property considering the Option Explicit statement.

Listing 6.9: Writing data to a specific worksheet using the Cells property with Option Explicit enabled. The worksheet is accessed using the Sheets collection and the name of the worksheet.

Option Explicit

Sub WriteDataInSpreadsheetUsingCells()

    'Declare a variable "targetSheet" of type "Worksheet"
    Dim targetSheet As Worksheet
    
    'Set the variable to store the worksheet "Sheet1"
    Set targetSheet = Sheets("Sheet1")
    
    'Write data to the worksheet using the "Cells" property
    targetSheet.Cells(1, 1) = "A1"
    targetSheet.Cells(1, 2) = "B1"
    targetSheet.Cells(1, 3) = "C1"
    targetSheet.Cells(1, 4) = "D1"

    targetSheet.Cells(2, 1) = "A2"
    targetSheet.Cells(2, 2) = "B2"
    targetSheet.Cells(2, 3) = "C2"
    targetSheet.Cells(2, 4) = "D2"
    
End Sub

6.4.4 Inspecting Data in Excel

6.4.4.1 Locals Window

To see the content of a cell or range in Excel, you can use the Locals window in the VBA editor. The Locals window displays the variables and their values in the current scope, allowing you to inspect the content of cells, ranges, and other variables during debugging.

To open the Locals window in the VBA editor, go to View > Locals Window or press Ctrl+L. When you run your VBA code, the Locals window will display the variables and their values, including the content of cells and ranges.

Consider you want to inspect the content of range A1:A5 in the worksheet “Sheet1” using the Locals window. This range contains the values 1, 2, 3, 4, and 5. You can write the following code in a subroutine and run it to see the content of the range in the Locals window.

Listing 6.10: Inspecting data in an Excel worksheet using the Locals window in the VBA editor. The range A1:A5 is assigned to the variable rng.

Sub InspectDataInExcel()

    'Create a range object to store the target range
    rng = Sheets("Sheet1").Range("A1:A5")
    
End Sub

When you run the InspectDataInExcel subroutine, the Locals window will display the rng variable with the content of the range A1:A5.

To see the content of the range, you have to:

1. Open the Locals window in the VBA editor.
2. Add a breakpoint to the line after the range is assigned to the variable.
3. Run the subroutine.

The Locals window will display the rng variable with the content of the range A1:A5. If you expand the rng variable (by clicking on the + sign), you will see the values 1, 2, 3, 4, and 5 in the range.

6.4.4.2 Immediate Window

You can also use the Immediate Window to inspect the content of cells and ranges by typing the cell or range address followed by a question mark ? and pressing Enter. For example:

• Typing ?Range("A1") in the Immediate Window will display the value of cell A1.
• Typing ?Cells(1, 1) in the Immediate Window will display the value of cell A1.

Looping Through Cells and Ranges

Later, you will learn how to loop through cells and ranges in Excel using VBA code. This allows you to read, write, and manipulate data in cells and ranges more efficiently.

6.4.5 Examples

6.4.5.1 Reading Data

In Listing 6.11, we read data from an Excel worksheet called “Reading data” (see Table 6.1) using the Range and Cells objects. We read the headers from the first row and the data from the second row. The data is then printed in the Immediate Window using the Debug.Print statement.

Table 6.1: Data in the worksheet “Reading data” with headers in the first row and data in the second row.

	A	B	C	D	E
1	Header1	Header2	Header3	Header4	Header5
2	Data1	123	Data3	Data4	Data5

Listing 6.11: Reading data from an Excel worksheet using the Range and Cells objects. The headers are read from the first row, and the data is read from the second row.

Option Explicit

Sub ReadSingleDataFromSpreadsheet()
    
    'Declare a variable "targetSheet" of type "Worksheet"
    Dim targetSheet As Worksheet
    
    'Set the variable to store the worksheet "Writing data"
    Set targetSheet = Sheets("Reading data")
    
    'Variables to store headers' labels
    Dim header1 As String
    Dim header2 As String
    Dim header3 As String
    Dim header4 As String
    Dim header5 As String
    
    'Reading header using function Range(CellId)
    header1 = targetSheet.Range("A1")
    header2 = targetSheet.Range("B1")
   
    'Reading header using function Cells(RowIndex, ColumnIndex)
    header3 = targetSheet.Cells(1, 3)
    header4 = targetSheet.Cells(1, 4)
    header5 = targetSheet.Cells(1, 5)
    
    'Print all table headers in "Immediate Window"
    Debug.Print header1, header2, header3, header4, header5
    
    'Variables to store data entries in first row
    Dim entry1 As String
    Dim entry2 As Integer 'Second column has Integers!
    Dim entry3 As String
    Dim entry4 As String
    Dim entry5 As String
    Dim entry6 As String
    
    'Reading data in first row
    entry1 = targetSheet.Cells(2, 1)
    entry2 = targetSheet.Cells(2, 2)
    entry3 = targetSheet.Cells(2, 3)
    entry4 = targetSheet.Cells(2, 4)
    entry5 = targetSheet.Cells(2, 5)
    
    'Print all data entries from first row in "Immediate Window"
    Debug.Print entry1, entry2, entry3, entry4, entry5
    
End Sub

Notice that the worksheet is accessed using the Sheets collection and the name of the worksheet. The keyword Set is used to assign the worksheet to the variable targetSheet.

6.4.5.2 Writing Data

In Listing 6.12, we write data to an Excel worksheet called “Writing data” using the Cells and Range objects. We write the same data in different ways: in a single cell, in a single row, in a single column, in a 1x3 range of contiguous cells, in a 2x2 range of contiguous cells, and in a range of non-contiguous cells.

Listing 6.12: Writing data to an Excel worksheet using the Cells and Range objects.

Option Explicit

Sub WriteDataInSpreadsheet()

    'Declare a variable "targetSheet" of type "Worksheet"
    Dim targetSheet As Worksheet
    
    'Set the variable to store the worksheet "Writing data"
    Set targetSheet = Sheets("Writing data")
    
    'Write data in cell B1 or (2,1) using command "Cells"
    targetSheet.Cells(1, 2) = "N (using ""Cells"")"
    
    'Write data in cell B3 or (2,3) using command "Range"
    targetSheet.Range("B3") = "N (using ""Range"")"
    
    'Write same data in a 1x3 range of contiguous cells (B5, C5, and D5)
    targetSheet.Range("B5:D5") = "N (1x3)"
    
    'Write same data in a 2x2 range of contiguous cells (B7, C7, B8, C8)
    targetSheet.Range("B7:C8") = "N (2x2)"
    
    'Write same data in a range of non-contiguous cells (B10, C11, D10)
    'Attention: separator is now "," (comma) for non-contiguous
    targetSheet.Range("B10,C11,D10") = "N (non-contiguous)"
    
    'Write same data in a 2x2 range of contiguous cells (B13, C13, B14, C14)
    targetSheet.Range( _
        targetSheet.Cells(13, 2), _
        targetSheet.Cells(14, 3)) = "N (2x2) (Cells inside Range)"

End Sub

6.5 Using Forms (UserForm)

To read data from custom forms, you can create a UserForm in VBA and add controls like text boxes, buttons, labels, etc. To create a UserForm, go to the VBA editor, right-click on the VBA project, select Insert, and then UserForm. You can then add controls to the UserForm and write code to handle user interactions. Once the UserForm is created, you can design it by adding controls like text boxes, buttons, labels, etc.

In Listing 6.13, we create a UserForm with two text boxes and a button. First, we create a UserForm named UserForm1 with two text boxes (TextBox1 and TextBox2) and a button (CommandButton1). Drag and drop the controls from the toolbox to the UserForm and adjust their properties as needed.

Then, we write code to read the data entered by the user in the text boxes when the button is clicked. This code should be added to the code module associated with the UserForm.

In Listing 6.13, we write code to read the data entered by the user in the text boxes when the button is clicked. After reading the data, we display it in a message box and close the UserForm.

Listing 6.13: VBA code to read data from text boxes when the button is clicked.

Private Sub CommandButton1_Click()
    
    'Read the text from TextBox1
    Dim text1 As String
    text1 = TextBox1.Text
    
    'Read the text from TextBox2
    Dim text2 As String
    text2 = TextBox2.Text
    
    'Display the text in a message box
    MsgBox "Text1: " & text1 & vbCrLf & "Text2: " & text2
    
    'Close the UserForm
    Unload Me
    
End Sub

In Listing 6.14, we write code to show the UserForm when the ReadDataFromUserForm subroutine is executed. This code should be added to a standard code module and can be executed to display the UserForm.

Listing 6.14: VBA code to show UserForm object called UserForm1 when the ReadDataFromUserForm subroutine is executed.

Option Explicit

Sub ReadDataFromUserForm()
    
    'Show the UserForm
    UserForm1.Show
    
End Sub

There a few ways to show the UserForm. In this chapter, we will cover the following methods:

1. Run the Subroutine: You can run the ReadDataFromUserForm subroutine from the VBA editor.
2. Assign to a Button: You can assign the ReadDataFromUserForm subroutine to a button on a worksheet. First, you need to create a button using the Insert menu in Excel and then assign the macro to the button.
3. Run from Another Subroutine: You can call the ReadDataFromUserForm subroutine from another subroutine (see example in Listing 6.14).

Other Methods to Run the UserForm

Other methods to run the UserForm include:

• Run Automatically: You can set the ReadDataFromUserForm subroutine to run automatically when the workbook is opened. This can be done by adding the code to the Workbook_Open event in the ThisWorkbook module.
• Run from a Custom Ribbon Button: You can create a custom ribbon button that runs the ReadDataFromUserForm subroutine.
• Run from a Keyboard Shortcut: You can assign a keyboard shortcut to run the ReadDataFromUserForm subroutine.
• Run from a Custom Menu: You can create a custom menu item that runs the ReadDataFromUserForm subroutine.
• Run from a Custom Toolbar Button: You can create a custom toolbar button that runs the ReadDataFromUserForm subroutine.

You can choose the method that best fits your needs and the user experience you want to create.

6.6 Exercises

6.6.1 Write Data to Excel Sheet

Write the headers “Name”, “Age”, and “Email Address” to cells A1, B1, and C1, respectively, using the Range object. Then, write your name, age, and email to cells A2, B2, and C2, respectively, using the Cells property.

6.6.2 Read Data from Excel Sheet

Create a Sub called ReadUserData that reads the data from the Excel sheet and displays it in a message box. The data should be read from cells A1, B1, and C1 for name, age, and email address, respectively.

The message should be displayed in the following format:

Name: [Name]
Age: [Age]
Email Address: [Email Address]

String Concatenation and New Lines

To concatenate strings in VBA, you can use the & operator (e.g., "Name: " & name). To add new lines in a message box, you can use the vbCrLf constant or the vbNewLine constant (e.g., "Name: " & name & vbCrLf).

6.6.3 Debug Data in Excel Sheet

Create a Sub called DebugUserData that reads the data from the Excel sheet and prints it to the Immediate Window. Consider the data is stored in cells A1 and B1, where A1 contains a warehousing KPI and B1 contains the corresponding value (see Table 6.2).

Table 6.2: Data in the worksheet “Debug data” with KPIs and values.

	A	B
1	KPI	Value
2	Pick Accuracy (%)	98
3	Inventory Turnover	5.6
4	Order Cycle Time (days)	2

The data should be printed in the following format:

| KPI | Value |
|---|---|
| [KPI1] | [Value1] |
| [KPI2] | [Value2] |
| [KPI3] | [Value3] |

Markdown Table Format

Copy the data from the Immediate Window and paste it into a Markdown viewer to display it as a table. Markdown viewers like Dillinger or StackEdit can render Markdown tables. Markdown is a lightweight markup language with plain text formatting syntax.

6.6.4 Welcome User Form

Create a UserForm that displays a welcome message to the user when opened. The welcome message should include the user’s name, which the user can enter in a text box on the UserForm. Add a button to the UserForm that, when clicked, displays the welcome message with the user’s name in a message box.

6.6.5 Data Entry Form

Create a UserForm with text boxes for entering the following information:

1. Name
2. Age
3. Email Address

Add a button to the UserForm that, when clicked, reads the data entered by the user and writes it to the first row of an Excel sheet called “User Data”. The data should be written to cells A1, B1, and C1 for name, age, and email address, respectively.

6.6.6 Data Display Form

In an Excel sheet called “User Data”, write your name, age, and email address to cells A1, B1, and C1, respectively. Then, create a button in the worksheet that, when clicked, displays a UserForm with text boxes for name, age, and email address. The text boxes should be pre-filled with the data from cells A1, B1, and C1, respectively.

To alter the text boxes in the UserForm, you can use the TextBox.Text property to set the text displayed in the text box.

6.7 Multiple-Choice Questions

6.7.1 Values Inside Ranges

Consider the data shown in the Excel worksheet presented in Table 6.3.

Table 6.3: Data in the worksheet (Question 6.7.1).

	A	B	C	D
1	1	2	3	4
2	5	6	7	8
3	9	10	11	12
4	13	14	15	16

In Listing 6.15, we define four ranges rnd1, rnd2, rnd3, and rnd4 using the Range object.

Listing 6.15: Defining ranges rnd1, rnd2, rnd3, and rnd4 using the Range object (Question 6.7.1).

Sub WriteDataInSpreadsheetUsingRange()

    rnd1 = Range("A1:D2")
    rnd2 = Range("A1").Offset(1, 1)
    rnd3 = Range("A4").Offset(-3, 1)
    rnd4 = Range("A1,B2,C3,D4")

End Sub

If we use the Locals window to inspect the contents of the variables rnd1, rnd2, rnd3, and rnd4, what will be the values inside these ranges?

1. rnd1 = {1, 2, 3, 4, 5, 6, 7, 8}, rnd2 = {6}, rnd3 = {2}, and rnd4 = {1, 6, 11, 16}.
2. rnd1 = {1, 2, 3, 4, 5, 6, 7, 8}, rnd2 = {6}, rnd3 = {1}, and rnd4 = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16}.
3. rnd1 = {5, 6, 7, 8, 9, 10, 11, 12}, rnd2 = {6}, rnd3= {1}, and rnd4 = {1, 5, 9, 13}.
4. rnd1 = {5, 6, 7, 8, 9, 10, 11, 12}, rnd2 = {6}, rnd3= {2}, and rnd4 = {1, 6, 11, 16}.

Solution 6.7.1

Answer: rnd1 = {1, 2, 3, 4, 5, 6, 7, 8}, rnd2 = {6}, rnd3 = {2}, and rnd4 = {1, 6, 11, 16}.

• rnd1 = Range("A1:D2") selects a range from A1 to D2, giving the values: 1, 2, 3, 4, 5, 6, 7, 8.
• rnd2 = Range("A1").Offset(1, 1) moves 1 row down and 1 column right from A1 to B2, giving the value: 6.
• rnd3 = Range("A4").Offset(-3, 1) starts at A4 (value 13), moves 3 rows up and 1 column right, landing on B1, giving the value: 2.
• rnd4 = Range("A1,B2,C3,D4") selects specific cells A1, B2, C3, D4, giving the values: 1, 6, 11, 16.

6.7.2 Pixel Matrix Processing

A worksheet contains a 2D matrix representing an image, where each cell in the matrix contains a pixel value ranging from 0 to 255. You are tasked with reading the pixel values from the worksheet, one-by-one, and processing each pixel value using a function called ProcessPixel. You can assume that this function takes an integer pixel value as input and performs some processing on the pixel and saves the result.

In Table 6.4, you can see an example of the pixel matrix stored in the worksheet “Pixel Matrix” with a 5x5 “X” pattern. The value 255 represents a white pixel, and the value 0 represents a black pixel.

Table 6.4: Pixel matrix in the worksheet “Pixel Matrix” with a 5x5 “X” pattern.

	A	B	C	D	E
1	255	0	0	0	255
2	0	255	0	255	0
3	0	0	255	0	0
4	0	255	0	255	0
5	255	0	0	0	255

In Listing 6.16, you can see the pseudocode for reading the pixel values from the worksheet and processing each pixel value using the ProcessPixel function.

Listing 6.16: Pseudocode for reading the pixel values from the worksheet and processing each pixel value using the ProcessPixel function.

Option Explicit

Sub ProcessImage()

    Dim row As Integer
    Dim col As Integer
    Dim rowCount As Integer
    Dim colCount As Integer

    'Repeat if row <= rowCount
        row = 1
        'Repeat if col <= colCount
            col = 1
            'Save pixel value in position (row, col) to a variable
            'Process pixel value using the function ProcessPixel
            ProcessPixel(pixelValue)
            'Increment col by 1
        'Increment row by 1

'
End Sub

Which of the following code snippets correctly reads the pixel values from the worksheet and processes each pixel value using the ProcessPixel function? These code snippets should replace lines Save pixel value in position (row, col) to a variable and Process pixel value using the function ProcessPixel, respectively, in Listing 6.16.

Choose the correct code snippets from the following options:

1. Listing 6.17 (a)
2. Listing 6.17 (b)
3. Listing 6.17 (c)
4. Listing 6.17 (d)

Listing 6.17: Code snippets for Question 6.7.2.

(a)

Dim pixelValue As Integer
pixelValue = Range("A1").Offset(row, col)
ProcessPixel(pixelValue)

(b)

Dim pixelValue As Integer
pixelValue = Cells(row, col)
ProcessPixel(pixelValue)

(c)

pixelValue = Cells(row, col)
Call ProcessPixel(pixelValue)

(d)

pixelValue = Range("A1").Offset(row, col)
Call ProcessPixel(pixelValue)

Solution 6.7.2

Answer: Listing 6.17 (b).

Explanation:

• In Listing 6.17 (b), the pixel value is correctly read from the cell at position (row, col) using the Cells property. The ProcessPixel function is then called with the pixel value as an argument. Since Option Explicit is enabled, the pixelValue variable is declared before use.
• In Listing 6.17 (a), the offset will not work as expected because row and col start with 1. Therefore, the first pixel value will be read from cell B2 instead of A1.
• In Listing 6.17 (c), the Call statement is not required when calling a function in VBA. Besides, the pixelValue variable is not declared (Option Explicit is enabled).
• In Listing 6.17 (d), the Call statement is not required when calling a function in VBA and offset is not used correctly as in Listing 6.17 (a).