Procedures

class: center, middle, inverse, title-slide

.title[
# Procedures
]
.author[
### Lars Relund Nielsen
]

---

layout: true

<div class="my-footer">
<span>
<a href="https://bss-osca.github.io/tfa/mod-vba-procedures" target="_blank">Notes</a>
 | 
<a href="04_vba-procedures-slides.html" target="_blank">Slides</a>
 | 
<a href="https://github.com/bss-osca/tfa/blob/master/slides/04_vba-procedures-slides.Rmd" target="_blank">Source</a>
 | 
<a href="https://github.com/bss-osca/tfa/blob/master/vba/vba-template.xlsm" target="_blank">Excel file</a>
</span>
</div>

---

## Procedures

- A procedure is a piece of code stored in a module. 
- A procedure contains a series of computational steps that will be carried out when the procedure is called.

VBA has two kinds of procedures:
.pull-left[
Subs:
  - Can make changes to the worksheet.
  - Can modify its surroundings.
  - Can be executed by a button (a macro - if no arguments).
  - Cannot return anything.
]
.pull-right[
Functions:
  - Can return something.
  - Can be used in Excel.
  - Cannot be used as a macro.
]

---

## Declaring a procedure

Sub:

```
   Sub SubName(arg1 As <datatype>, arg2 As <datatype>)
      <code>
   End Sub
   ```

Function:

```
   Function FunctionName(arg1 As <datatype>, arg2 As <datatype>) As <return datatype>
    <code>
    FunctionName = value  ' assign a return value to the function
   End Sub
   ```

Hint: You can use `Exit Sub`/`Exit Function` to exit the sub/function early in the code.

---

## Calling a prodedure

Use the `Call` keyword to call a sub:
  ```
  Call SubName(arg1, arg2)
  ```

Call a function by assigning its return value to a variable
  ```
  result = FunctionName(arg1, arg2)
  ```

---

## Optional input arguments

A procedure may often be called with the same input argument value. To avoid always specifying the value, you may set a default value using the `Optional` keyword:
  ```
  Sub MySub(arg1 As {datatype}, Optional arg2 As {datatype} = defaultValue)  
  ```
You can now call the procedure using:
  ```
  Call MySub(arg1)  ' assume that arg2 = defaultValue
  ```
Note: Every parameter following an optional parameter in the procedure definition must also be optional.

Let us try to run procedures `TM4_ConvertKg`-`TM4_TestingFormatCell` in the Excel file.

---

## Input `ByRef` or `ByVal`

.left-column-wide[
.midi[
There are two ways of passing arguments to procedures:
  ```
  Sub MySub(ByRef arg1 As <datatype>, ByVal arg2 As <datatype>)
    ' Some code
  End Sub
  ```
]]
.right-column-small[
<img src="./img/byref-vs-byval.gif" width="100%" style="display: block; margin: auto;" />
]
.pull-left[
.midi[
   * Argument `arg1` is passed **by reference** (default). 
     - No new memory is allocated. 
     - The procedure can have changed the value of `arg1` 
]
]
.pull-right[
.midi[
   * Argument `arg2` is passed **by value**. 
     - A copy of the variable is created in memory. 
     - The procedure cannot change the value of `arg2`.
]
]

.midi[
* Using `ByRef` is faster and saves memory since we do not have to allocate new memory. 
* We may use `ByRef` to return updated values of the input arguments.
]

---

## Using built-in functions

- VBA has a set of [built-in functions](https://bettersolutions.com/vba/functions/complete-list.htm) such as `Abs`, `Log` and `Date`. You call them by just writing their name:
  ```
  dtm as Date
  dtm = Date()
  ```
- You can also use the [worksheet functions](https://docs.microsoft.com/en-us/office/vba/excel/concepts/events-worksheetfunctions-shapes/list-of-worksheet-functions-available-to-visual-basic) in Excel. You call them using the `WorksheetFunction` object:
  ```
  sum = WorksheetFunction.Sum(Range("A1:D5"))
  ```
  
Let us have a look at procedures `TM4_TestWorksheetfunctions` and `TM4_TestVBAfunctions` in the Excel file.

---

## Example - Selection of test persons

* Want to select a group of test persons for a virus vaccine.
   * Given: A group of infected persons and a group of possible test persons.
   * Effect dependent on the persons’ height. Test results can be used on infected persons whose height is within a range of 2 cm from the height of the test person. 
   * For example, if the cure is tested on a non-infected person of height `\(172.2\)`, then any infected person whose height is in the interval `\([170.2 ; 174.2]\)` is *covered* by the test. 
   * Data are given in worksheet *TM4_Virus*

Given a test person we make a function `TM4_TestCover` that return the number of new infected persons covered. A person is already covered if that person has a 1 in the *Covered* column.

*This example is a slightly modified version an exam assignment (exam 2021-A6).*

---

## Greedy strategy

To find the right test persons the following greedy strategy is used:

- Step 1	Select the test person (not already selected) that can cover most new infected persons (not yet covered). If more than one test person have the same cover, select the one with the smallest ID. 
   - Step 2 Add ones to the *Covered* column for all infected persons covered by the test person.
   - Step 3 Go to Step 1 until found the test persons needed.
 
We implement the the greedy strategy in sub `TM4_FindTestPersons`.