5  Functions and Sub-Procedures

Procedures are the building blocks of VBA code. They are used to perform specific tasks or actions. A procedure can be a Function or a Sub-Procedure (or Sub).

The main difference between a Function and a Sub is that a Function returns a value, while a Sub does not. Using procedures helps in organizing code, making it more modular and easier to maintain.

This chapter will discuss Functions and Subs in VBA, along with examples and exercises.

Modular programming is a software design technique that emphasizes breaking down programs into smaller, self-contained modules or procedures. Each module performs a specific task and can be reused in different parts of the program. This approach makes code more manageable, easier to understand, and less error-prone.

5.1 Functions

A Function is a block of code that performs a specific task and returns a value. User Defined Functions (UDFs) are custom functions created by the user to perform specific calculations or tasks that are not available in Excel’s built-in functions.

In VBA, a function is declared with the Function keyword and typically specifies a return data type. Functions can be called from other procedures, spreadsheets, or other functions.

5.1.1 Anatomy of a Function

In VBA, a function consists of the following parts:

• Function Name: The name of the function.
• Parameters: The values received by the function when it’s called and their data types.
• Return Type: The data type of the value returned by the function, specified after the As keyword (e.g., As Integer, As String).
• Function Signature: The function name, parameters, and return type.
• Function Body: The code that performs the function’s task.
• Function Return: The value returned by the function.
• Return Value: The value returned by the function.
• End Function: The end of the function.

To use a function, you need to:

• Pass the required arguments: The number and type of arguments must match the function’s parameters. Otherwise, an error will occur.
• Call/Invoke the Function: Use the function name followed by parentheses and arguments (if any). The function can be called from other functions, Sub-Procedures, or spreadsheets (like the pre-defined functions in Excel, such as SUM, AVERAGE, etc.).
• Capture the return value if needed: The return value can be assigned to a variable or used directly.

Arguments vs. Parameters

The difference between arguments and parameters is that arguments are the actual values passed to a function, while parameters are the variables that receive the values. These terms are often used interchangeably, but it’s important to understand the distinction. The parameters are placeholders for the arguments that will be passed to the function.

For example, in Listing 5.1, we have a function to add one to a number. For example, if the number 1 is passed to the function, it will return 2. This function:

• Receives a number (parameter “num”)
• Adds one to the number received and assign the result to variable “result”
• Return the variable result. Conventionally, the value is assigned to the function name itself (AddOneTo = result).

Listing 5.1: Function to add one to a number.

Function AddOneTo(num)

    'Assign result of calculation to variable "result"
    result = num + 1
    
    'Assigning value in result to function return value
    AddOneTo = result 
    
    'Everything you put here will be executed!
    
'The function ends here (end of code block)
End Function

Return Value

Notice that the value is assigned to the function name itself (AddOneTo = result). This is a convention in VBA, where the function name is used to return the value.

Limitations of Calling UDFs in Excel

A UDF called from a cell can only return a value and cannot modify the worksheet. For example, you cannot use a UDF to change the value of another cell or insert a new row. This will return a #VALUE! error in the cell where the UDF is called.

Notice that the function AddOneTo does not specify the data type of the parameter num or the return type. In VBA, you can omit the data type of the parameter and the return type, but it is recommended to specify them explicitly. We will see how to specify the data type of the parameter and the return type later in this chapter (see Section 5.3.1).

5.1.2 Exiting a Function Before Completion

In VBA, you can exit a function at any point using the Exit Function statement. This statement is used to immediately exit the function and return control to the calling procedure.

For example, in Listing 5.2, we have a function to add two numbers and exit. The function will not execute the code after the Exit Function statement.

Listing 5.2: Function to add one to a number and exit if the number is negative.

Function SumAndExit(n1, n2)
    
        SumAndExit = n1 + n2
        Exit Function

        'This code will not be executed
        SumAndExit = 0

End Function

5.1.3 Pre-Defined Functions

Pre-defined functions are built-in functions that perform common tasks. They are used to simplify coding and avoid redundancy.

For example, in VBA, common pre-defined functions include:

• Left: Extracts a specified number of characters from the beginning of a string.
• Right: Extracts a specified number of characters from the end of a string.
• Mid: Extracts a substring from within a string.
• InStr: Searches for a substring within a string and returns the position of the first occurrence.
• Replace: Replaces occurrences of a specified substring with another substring in a string.
• Trim: Removes leading and trailing spaces from a string.
• UCase: Converts a string to uppercase.
• LCase: Converts a string to lowercase.
• StrReverse: Reverses the characters in a string.

In Listing 5.3, we have a function to sum the lengths of two strings using the pre-defined function Len to get the length of each string.

Listing 5.3: Function to sum the lengths of two strings.

Function SumLengthsOfTwoStrings(str1, str2)
    
    len1 = Len(str1)
    len2 = Len(str2)
    
    SumLengthsOfTwoStrings = len1 + len2

End Function

Test the function “SumLengthsOfTwoStrings” in the Excel spreadsheet:

• Assign the value “Hello” to variable “str1”
• Assign the value “World” to variable “str2”
• Call the function “SumLengthsOfTwoStrings” passing the arguments “str1” and “str2” from the Excel spreadsheet using the = operator (e.g., =SumLengthsOfTwoStrings("Hello", "World")).

Pre-Defined Functions

Most programming languages have a set of built-in functions that perform common tasks. Therefore, before creating a new function, check if there is a pre-defined function that can be used.

5.2 Sub-Procedure (Sub)

A Sub-Procedure (or Sub) is a block of code that performs a specific task but does not return a value. Sub-procedures or Subs are typically used for actions such as:

• displaying a message box,
• copying data, or
• formatting a worksheet.

A “Sub” is a block of code that:

• Is declared with the Sub keyword.
• Does not return values.
• Can be called from
  • Functions,
  • Other subroutines.
• Is called/invoked from a function or another Sub:
  • using the Call keyword followed by the Sub name and any arguments, for example, Call MySub(arg1, arg2), or
  • using the Sub name followed by any arguments, for example, MySub arg1, arg2.

Keyword Call

In VBA, the Call keyword can be used to call a Sub-Procedure, but it is optional and often omitted in modern code. The use of parentheses when calling a Sub depends on whether you are using the Call keyword and if there are any arguments. Here are the valid ways to call a Sub-Procedure:

• Call MySub() – Uses the Call keyword and parentheses (can be used even if the Sub has no arguments).
• MySub – No parentheses or arguments, direct call to the Sub.
• Call MySub(arg1, arg2) – Uses the Call keyword with arguments and parentheses.
• MySub arg1, arg2 – Direct call without Call keyword, no parentheses, but includes arguments.

In modern VBA, the Call keyword is largely redundant and is mainly used for readability or compatibility with older code.

In Listing 5.4, we have a subroutine TestAddOneTo to test the function “AddOneTo” by:

• Assigning the value 1 to variable “x”,
• Calling the function “AddOneTo” passing the argument “x”,
• Assigning the return value in variable “result”,
• Displaying the value of “x” and “result” in a message box.

Listing 5.4: Sub-Procedure to test the function “AddOneTo”.

Option Explicit

Sub TestAddOneTo()
    
    x = 1 'Assign 1 to variable "x"
    
    'Call/Invoke function passing argument "x"
    'Assign return value in variable "result"
    result = AddOneTo(x)
    MsgBox x
    MsgBox result

End Sub

5.2.1 Anatomy of a Sub-Procedure

In VBA, a Sub-Procedure consists of the following parts:

• Sub Name: The name of the Sub-Procedure.
• Parameters: The values received by the Sub-Procedure when it’s called.
• Sub Signature: The Sub-Procedure name and parameters.
• Sub Body: The code that performs the Sub-Procedure’s task.
• End Sub: The end of the Sub-Procedure.

To use a Sub-Procedure, you need to:

• Call/Invoke the Sub: Use the Sub name followed by parentheses and arguments (if any).
• Arguments: The values passed to the Sub-Procedure when it’s called.

5.2.2 Exiting a Sub-Procedure Before Completion

In VBA, you can exit a Sub-Procedure at any point using the Exit Sub statement.

For example, in Listing 5.5, we have a Sub-Procedure to add two numbers and exit. The Sub-Procedure will not execute the code after the Exit Sub statement.

Listing 5.5: Sub-Procedure to add two numbers and exit.

Sub AddAndExit(n1, n2)
    
    MsgBox n1 + n2
    Exit Sub

    'This code will not be executed
    MsgBox 0

End Sub

Exiting a Sub-Procedure is useful when you want to stop the execution of the Sub-Procedure based on certain conditions or requirements without executing the remaining code (e.g., error handling).

5.3 Strong vs. Weak Typing

VBA allows you to use variables without explicitly declaring their types. This makes VBA a weakly typed language, where the data type of a variable is determined at runtime based on the value assigned to it. For example, if you assign a number to a variable, VBA will treat it as a number; if you assign text, it will treat it as a string. These non-declared variables are treated as Variant data types (see details in Chapter 8). Other examples of weakly typed languages include JavaScript and Python.

However, it is recommended to declare variables explicitly. Strongly typed languages, which are languages that require you to declare the type of each variable before using it, offer several advantages. For example, they help in catching typographical errors and enhance code reliability by ensuring that variables are declared with a specific data type. This way, you can avoid unexpected results due to data type mismatches. If you try to assign a string to an integer variable, a strongly typed language will raise an error, while a weakly typed language may convert the string to a number or vice versa.

5.3.1 Turning on Strong Typing

In VBA, you can turn on strong typing by using the Option Explicit statement. When you include Option Explicit at the beginning of a VBA module, it requires you to declare all variables before using them in your code.

To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, turn on the Require Variable Definition option. You find it on the Editor tab of the Options dialog box (in the VBE, choose Tools -> Options).

In Listing 5.6, we have a function to add one to a number with Option Explicit on.

Notice that anatomy of the function is similar to the previous example, but now we to explicitly declare the types of variables result and num. In Table 5.1, we have the anatomy of the function AddOneToWithOptionExplicitOn.

Table 5.1: Anatomy of the function AddOneToWithOptionExplicitOn with Option Explicit on.

Part	Description
Function Name	AddOneToWithOptionExplicitOn
Parameters	num of type Integer
Return Type	a number of type Integer
Function Signature	Function AddOneToWithOptionExplicitOn(num As Integer) As Integer

Now, we have to declare the variable types explicitly. However, in the parameter list, we can specify the type of the parameter num without using the Dim statement (this is a VBA convention). But in the body of the function, we need to declare the variable result using the Dim statement.

Listing 5.6: Function to add one to a number with Option Explicit on. Notice the use of Dim to declare variables, As Integer to specify the data type, and As Integer to specify the return type.

Option Explicit

'Notice: NOT "Dim num As Integer"
Function AddOneToWithOptionExplicitOn(num As Integer) As Integer

    Dim result As Integer 'Declare variable result as Integer
    result = num + 1
    AddOneToWithOptionExplicitOn = result

End Function

In Listing 5.7, we have a subroutine to test the function “AddOneToWithOptionExplicitOn”. Notice that the anatomy of the Sub-Procedure is similar to the previous example, but now we have to declare the types of variables x and result.

Listing 5.7: Sub-Procedure to test the function “AddOneToWithOptionExplicitOn”.

Option Explicit

Sub TestAddOneToWithOptionExplicitOn()
    
    Dim x As Integer 'Declare variable x
    x = 1 'Store 1 in x
    Dim result As Integer 'Declare variable result
    result = AddOneToWithOptionExplicitOn(x)
    MsgBox x
    MsgBox result

End Sub

5.3.2 Example: Function to Sum Five Numbers (Weak Typing)

The following function adds five numbers, considering the Option Explicit statement is off. In this version, we do not explicitly declare the types of variables param1, param2, param3, param4, and param5. The function SumFiveNumbers receives five parameters and returns the sum of these numbers.

Listing 5.8: Function to sum five numbers without explicitly declaring the types of variables. The Debug.Print statement is used to print a message in the Immediate Window.

Function SumFiveNumbers(param1, param2, param3, param4, param5)
    
    Debug.Print "Entering in function SumFiveNumbers"
    SumFiveNumbers = param1 + param2 + param3 + param4 + param5

End Function

5.3.3 Example: Function to Sum Five Numbers (Strong Typing)

In Listing 5.9, we have a function to sum five numbers with the Option Explicit statement on. In this version, we explicitly declare the types of variables param1, param2, param3, param4, and param5 as Integer. This makes the code more robust, helping to catch errors at compile time.

But, it requires more typing and may seem verbose for simple functions. Long lines of code are hard to follow and may reduce readability.

Listing 5.9: Function to sum five numbers with Option Explicit on and explicit declaration of variable types.

Function AddFiveNumbers(param1 As Integer, param2 As Integer, param3 As Integer, param4 As Integer, param5 As Integer) As Integer
    Debug.Print "Entering in function AddFiveNumbers"
    AddFiveNumbers = param1 + param2 + param3 + param4 + param5
End Function

To make the code more readable, you can use underscores to break the line of code into multiple lines. Underscores serve the purpose of breaking a line of code or a statement into multiple lines for improved readability. They are used as line continuation characters.

Line Continuation

The syntax for line continuation is a space followed by an underscore (_).

In Listing 5.10, we have the same function as the previous example, but now we use underscores to break the line of code into multiple lines. Notice that by breaking the line of code into multiple lines, the code becomes more readable.

Listing 5.10: Function to sum five numbers with Option Explicit on and explicit declaration of variable types using underscores for line continuation. Long lines of code can be broken into multiple lines for improved readability.

Function SumFiveNumbers( _
        param1 As Integer, _
        param2 As Integer, _
        param3 As Integer, _
        param4 As Integer, _
        param5 As Integer) As Integer
        
    Debug.Print "Entering in function SumFiveNumbers"
    SumFiveNumbers = param1 + param2 + param3 + param4 + param5
End Function

Finally, in Listing 5.11, we have a subroutine to test the function “SumFiveNumbers”.

Listing 5.11: Sub-Procedure to test the function “SumFiveNumbers”.

Sub TestSumFiveNumbers()
    Debug.Print "Started Sub TestAddFiveNumbers"
    'Declaring 5 variables
    Dim arg1 As Integer, _
        arg2 As Integer, _
        arg3 As Integer, _
        arg4 As Integer, _
        arg5 As Integer
    
    Debug.Print "   Assigning variables"
    'Assigning values to arguments
    arg1 = 10
    arg2 = 20
    arg3 = 30
    arg4 = 40
    arg5 = 50

    'Invoking the function and getting the result
    Dim result As Integer
    result = SumFiveNumbers(arg1, arg2, arg3, arg4, arg5)
    
    'Printing in Immediate Window
    Debug.Print "Arg1", "Arg2", "Arg3", "Arg4", "Arg5", "Sum"
    Debug.Print arg1, arg2, arg3, arg4, arg5, result
    
End Sub

How to adjust the code to receive floating-point numbers?

To adjust the code to receive floating-point numbers, you need to change the data type of the parameters and the return type to Double instead of Integer. In Listing 5.12, we have the function SumFiveNumbers modified to receive floating-point numbers.

Listing 5.12: Function to sum five floating-point numbers with Option Explicit on and explicit declaration of variable types. This function also sum integers.

Function SumFiveNumbers( _
        param1 As Double, _
        param2 As Double, _
        param3 As Double, _
        param4 As Double, _
        param5 As Double) As Double

    SumFiveNumbers = param1 + param2 + param3 + param4 + param5
End Function

5.4 Naming Conventions

When naming functions and Sub-Procedures, it is important to follow naming conventions to make the code more readable and maintainable. Here are some common naming conventions for functions and Sub-Procedures in VBA:

• Use descriptive names: Choose names that clearly describe the purpose of the function or Sub-Procedure to make the code self-explanatory, but avoid overly long names.
• Use PascalCase: Start the name with an uppercase letter and capitalize the first letter of each word (e.g., CalculateTotal, FormatData).
• Avoid abbreviations: Use full words instead of abbreviations to make the code more readable.
• Use action verbs: Names should typically start with a verb that describes the action performed (e.g., CalculateTotal, FormatData, DisplatMessage).
• Be consistent: Follow a consistent naming convention throughout your code to maintain readability and consistency.
• Avoid reserved words: Do not use reserved words or keywords as function or Sub-Procedure names (e.g., Function, Sub, Dim, If, Then).
• Avoid special characters: Avoid using special characters in function or Sub-Procedure names, except for underscores (_).

Reserved Words

Reserved words are words that have a special meaning in the programming language and cannot be used as identifiers (such as variable names, function names, or Sub-Procedure names). For example, in VBA, words like Function, Sub, Dim, If, Then, End, Loop, and Next are reserved words and cannot be used as identifiers.

5.5 Exercises

5.5.1 Testing Pre-Defined String Functions

Create a Sub to test each one of the following String functions: Len, Trim, LCase, UCase, Left, Right, Mid, Replace, StrReverse, and InStr.

See an example with pre-defined function “Len”, which returns the length (number of characters) of a string:

Sub TestLen()

    Debug.Print "Length of string ""Abracadabra"":", Len("Abracadabra")
    Debug.Print "Length of string ""VBA"":", Len("VBA")

End Sub

See below an example with the pre-defined function “Trim”, which removes leading and trailing spaces from a string:

Sub TestTrim()

    Dim InputText As String
    InputText = "       John   "
    Debug.Print " Input:"; InputText
    Debug.Print "Output:"; Trim(InputText)
    Debug.Print " Length of Input:"; Len(InputText)
    Debug.Print "Length of Output:"; Len(Trim(InputText))
    
End Sub

5.5.2 Testing Pre-Defined Math Functions

Create a Sub to test each one of the following pre-defined Math functions:

• Abs: Returns the absolute value of a number.
• Sqr: Returns the square root of a number.
• Round: Rounds a number to a specified number of decimal places.
• Int: Returns the integer part of a number (truncates the decimal).
• Pi: Returns the mathematical constant Pi (approximately 3.14159).
• Rnd: Generates a (pseudo) random number between 0 and 1.
• Exp: Returns the exponential value of a number.

5.5.3 Mayer Multiple Calculation

The Mayer Multiple is a ratio used to evaluate the price of Bitcoin in relation to its historical performance. It is calculated by dividing the current price of Bitcoin by the 200-day moving average (200 DMA) of its price, that is:

\[ \text{Mayer Multiple} = \frac{\text{Avg Price}}{\text{200 DMA}} \]

The logic for using the Mayer Multiple in trading decisions is simple:

• If the Mayer Multiple is greater than 2.4, it signals that Bitcoin is overbought and a potential sell signal.
• If the Mayer Multiple is less than 1.0, it indicates that Bitcoin is underbought and might be a good opportunity to buy.
• Otherwise, it suggests holding the position.

Create a function to calculate the Mayer Multiple given the current price of Bitcoin and the 200-day moving average (consider Option Explicit on). Your function has to:

• Receive the current price of Bitcoin and the 200-day moving average as arguments.
• Calculate the Mayer Multiple using the formula provided.
• Return the Mayer Multiple.

Then, use your function in Table 5.2 (column D) to calculate the Mayer Multiple for each day, and determine the trading decision based on the Mayer Multiple (column E), using an Excel function.

Table 5.2: Historical Bitcoin price in USD and 200-day moving average for each day. Use your function to calculate the Mayer Multiple and determine the trading decision based on the Mayer Multiple.

	A	B	C	D	E
1	Date	Avg Price	200 DMA	Mayer Multiple	Decision
2	2024-09-08	57650	58000
3	2024-09-09	57700	58000
4	2024-09-10	57950	58000
5	2024-09-11	58000	58000
6	2024-09-12	58300	58000
7	2024-09-13	57865	58000
8	2024-09-13	60023	58000

Solution

First, create a function to calculate the Mayer Multiple:

Option Explicit

Function MayerMultiple(currentPrice As Double, movingAverage As Double) As Double

    MayerMultiple = currentPrice / movingAverage

End Function

:::

Second, use the function in the Excel spreadsheet to calculate the Mayer Multiple.
In cell `D2`, use the formula `=MayerMultiple(B2, C2)` to calculate the Mayer Multiple for the first day and drag the formula down to apply it to the other days.

Third, in cell `E2`, use an `IF` formula to determine the trading decision based on the Mayer Multiple. For example, the formula could be:

```excel
=IF(D2 > 2.4, "Sell", IF(D2 < 1.0, "Buy", "Hold"))

5.6 Multiple-Choice Questions

5.6.1 Function Syntax

Which of the following functions will return the square of a number passed as an argument?

Choose the correct option:

• Listing 5.13 (a)
• Listing 5.13 (b)
• Listing 5.13 (c)
• Listing 5.13 (d)

Listing 5.13: Square Number Function (Question 5.6.1).

(a)

Function SquareNumber(num)
    result = num ^ 2
End Function

(b)

Function SquareNumber(num)
    Exit Function
    SquareNumber = num ^ 2
End Function

(c)

Function SquareNumber(num)
    SquareNumber = num ^ 2
End Function

(d)

Function SquareNumber(num)
    result = num ^ 2
    Exit Function
End Function

Solution 5.6.1

Answer: Listing 5.13 (c)

The function must return the square of the number passed as an argument. The correct syntax is to assign the result to the function name (SquareNumber = num ^ 2).

5.6.2 Sub-Procedure Syntax

Which of the following will display the message “Hello, World!” in a message box? Assume that the Sub Main is the entry point for the program.

Choose the correct option:

• Listing 5.14 (a)
• Listing 5.14 (c)
• Listing 5.14 (b)
• Listing 5.14 (d)

Listing 5.14: Hello World Sub-Procedure (Question 5.6.2).

(a)

Sub HelloWorld()
    MsgBox "Hello, World!"
End Sub

Sub Main()
    HelloWorld
End Sub

(b)

Sub HelloWorld()
    HelloWorld = "Hello, World!"
End Sub

Sub Main()
    MsgBox(HelloWorld())
End Sub

(c)

Sub HelloWorld()
    MsgBox "Hello, World!"
End Sub

Sub Main()
    MsgBox(HelloWorld())
End Sub

(d)

Sub HelloWorld()
    Exit Sub
    MsgBox "Hello, World!"
End Sub

Sub Main()
    Call HelloWorld
End Sub

Solution 5.6.2

Answer: Listing 5.14 (a)

The correct option is to define a Sub called HelloWorld that displays the message “Hello, World!” using the MsgBox function. The Main Sub then calls the HelloWorld Sub to display the message.

The most common mistake is to assume that the Sub should return a value like a function, which is not the case in VBA.

5.6.3 Eye Array Drawing Algorithm

Which of the following flowcharts correctly represents the algorithm of a Sub-Procedure called DrawEyeArray that generates a symmetrical array (eye pattern) in a spreadsheet?

The algorithm should create a square array of size N x N and set the value of the diagonal cells to 1 and the rest to 0.

For example, for a 5x5 array, the output should be:

Eye Pattern Array (5x5)

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

The Sub-Procedure DrawEyeArray should read the dimension N using the InputBox function and generate the array in the active spreadsheet.

Setting Excel Cell Values

Consider that the command Cells(row, col) = 1 sets the value of the cell in the row and col position to 1. For example, Cells(1, 1) = 1 sets the value of cell A1 to 1.

Select the correct flowchart from the following options:

1. Flochart in Figure 5.1 (a)
2. Flochart in Figure 5.1 (b)
3. Flochart in Figure 5.1 (c)
4. Flochart in Figure 5.1 (d)

graph LR
    A((Start)) --> B[/"N"/]
    B --> R["Set\nrow = 1"]
    R --> C{"Is\nrow > N?"}
    E -- No --> J["Increment\nrow by 1"]
    C -- No --> E{"Is\ncol > N?"}
    E -- Yes --> EYE{"Is\nrow\nequal to\ncol?"}
    EYE -- Yes --> F[/"Cells(row, col) = 1"/]
    EYE -- No --> G[/"Cells(row, col) = 0"/]
    F --> I["Increment\ncol by 1"]
    J --> C
    G --> I
    I --> E
    C -- Yes --> L((End))

(a)

graph LR
    A((Start)) --> B[/"N"/]
    B --> R["Set\nrow = 1"]
    R --> C{"Is\nrow >= N?"}
    E -- No --> J["Increment\nrow by 1"]
    C -- No --> D["Set\ncol = 1"]
    D --> E{"Is\ncol >= N?"}
    E -- Yes --> EYE{"Is\nrow\nequal to\ncol?"}
    EYE -- Yes --> F[/"Cells(row, col) = 1"/]
    EYE -- No --> G[/"Cells(row, col) = 0"/]
    F --> I["Increment\ncol by 1"]
    J --> C
    G --> I
    I --> E
    C -- Yes --> L((End))

(b)

graph LR
    A((Start)) --> B[/"N"/]
    B --> R["Set\nrow = 1"]
    R --> C{"Is\nrow > N?"}
    E -- No --> J["Increment\nrow by 1"]
    C -- No --> D["Set\ncol = 1"]
    D --> E{"Is\ncol > N?"}
    E -- Yes --> EYE{"Is\nrow\nequal to\ncol?"}
    EYE -- Yes --> F[/"MsgBox(1)"/]
    EYE -- No --> G[/"MsgBox(0)"/]
    F --> I["Increment\ncol by 1"]
    J --> C
    G --> I
    I --> E
    C -- Yes --> L((End))

(c)

graph LR
    A((Start)) --> B[/"N"/]
    B --> R["Set\nrow = 1"]
    R --> C{"Is\nrow > N?"}
    C -- No --> D["Set\ncol = 1"]
    D --> E{"Is\ncol > N?"}
    E -- Yes --> J["Increment\nrow by 1"]
    E -- No --> EYE{"Is\nrow\nequal to\ncol?"}
    EYE -- Yes --> F[/"Cells(row, col) = 1"/]
    EYE -- No --> G[/"Cells(row, col) = 0"/]
    F --> I["Increment\ncol by 1"]
    J --> C
    G --> I
    I --> E
    C -- Yes --> L((End))

(d)

Figure 5.1: Flowcharts for the Eye Array Drawing Algorithm of Sub DrawEyeArray (Question 5.6.3).

Solution 5.6.3

Answer: Flowchart in Figure 5.1 (d)

The correct flowchart represents the algorithm for generating an eye pattern array in a spreadsheet.

The algorithm reads the dimension N using the InputBox function, sets the initial row and column values, and then iterates over the rows and columns to set the cell values based on the condition “Is row equal to col”.

The VBA code is as follows:

Sub DrawEyeArray()

    N = Int(InputBox("Enter the dimension N:"))
    
    row = 1
    Do While row <= N

        col = 1
        Do While col <= N
            If row = col Then
                Cells(row, col) = 1
            Else
                Cells(row, col) = 0
            End If
            col = col + 1
        Loop
        row = row + 1
    Loop

End Sub

VBA code for the Sub-Procedure DrawEyeArray to generate an eye pattern array in a spreadsheet. The code is here for reference only. You do not need to know the VBA syntax to answer the question.