程序代写代做代考 Excel Topic 10 | Variables and Functions

Topic 10 | Variables and Functions

In this section we learn about working with a range of variables, data types and also to

appreciate the importance of using comments. We explore creating simple custom functions

and learn how to embed Solver in a macro.

Introduction | Learning Outcomes | Study Time: Week 8

In this section we learn about working with a range of variables, data types and also to appreciate the

importance of using comments. We explore creating custom functions and learn how to embed Solver

in a macro.

The learning objectives are to:

Explore working with a range of variables and data

types

Appreciate the importance of using comments

Learn how to create and use simple custom functions

Embed the Solver facility in a macro

Variables

We have already used variables in this module, such as the variable Ft which was used to represent the

forecast at time t. This allows us to express relationships such as

In essence, a variable is a named ‘container’ in which to keep a discrete item of data: we access that

piece of data by referring to the name of the variable.

In programming we don’t need to limit ourselves to single-letter names for our variables, so we have

the opportunity to use something more meaningful. Thus we might see a relationship such as

intTotalAnimals = intNumberDogs + intNumberCats

As well as using words in the variable names to describe the purpose of the variable, we typically use

a meaningful prefix. In this case, int indicates that the variables are integer values (integers are

whole numbers either positive, negative or zero).

Once we have defined our variables we can assign values to them. So for example if

intNumberDogs = 5, intNumberCats = 7

and intTotalAnimals = intNumberDogs + intNumberCats

we can calculate that intTotalAnimals has the value of 12.

When we use variables that are integers, we have no difficulty in adding then together or in fact

multiplying, squaring, subtracting, etc. However, in programming there are many different types of

data we can store in variables and each has its own characteristic methods of manipulation.

String Data Type

As variables come in a number different data types, indicating the data type of the variable in the

variable name reminds ourselves and others of the data type.

On the previous page we used “+” for addition to manipulate integers. This is fine if all the variables

concerned are indeed integers. However, if the data type of the variables is string (a string is a

sequence of characters), the use of the “+” as used above would have a very different result.

If

strFirstname = “John”, strSurname = “Smith”

and strFullName = strFirstname + strSurname

the variable strFullName then has a value of “JohnSmith” without a space in the middle. That is,

“adding” two strings concatenates them – sticks the strings together.

Other Data Types

Each data type in VBA has a range of ‘acceptable’ values. Some of the commonly used data types are:

Data Type Range of values

Boolean True or False

Integer
-32,768 to 32767 (Whole numbers, positive,

negative or zero)

Long
-2,147,483,648 to 2,147,483,647 (Allowing for

larger whole numbers)

Single

Double

(negative)

Double

(positive)

Date

String

Object

-3.4 x 1038 to 1.4 x 1045 (Decimals)

-1.8 x 10308 to -4.9 x 10-324 (Allowing for large

negative decimals)

4.9 x 10-324 to 1.8 x 10308 (Allowing for large

positive decimals)

1/1/100 to 12/31/9999

Sequences of characters

Any defined object

Stylish Variables

The use of a meaningful prefix tells us – the programmers and users – what the variable contains, but

VBA doesn’t know that!

It’s usually considered good style to tell VBA by “declaring” your variable before using it, i.e.

announce that you will use a variable of a given type later in the code. Historically this also allowed

scarce memory to be allocated for data. By default VBA will allow you to leave variables

“undeclared” and will try to make an intelligent decision about what type of variable it is and how it

should be treated. Declaring variables allows VBA to assign appropriate memory space and

manipulate data correctly thus allowing the code run efficiently. It also minimises the risk of

confusion later when you’re hunting for issues with your code.

You can insist on variables being declared by changing one of the options in the Tools menu when

you’re in the VBA editor (Alt + F11). This will add the words Option Explicit to the top of your

module editing space and then force you to be well behaved regarding your variables!!

Declarations and Comments

Declaring Variables

The format for declaring variables follows the following pattern

Dim intPeople as Integer

In general the declaration of variable type code is

Dim VariableName as DataType

Comments

We can enhance our declaration with additional information in the form of a comment.

Dim intPeople as Integer

‘ intPeople keeps a count of how many people come through

the door

Comments appear in green and are not interpreted or used by VBA. Note the use of an apostrophe to

start the comment. After entering an apostrophe in your code you should see the text which follows it

become green as you type.

Comments should be used in code to explain anything that isn’t self-explanatory. The idea is that

anyone reading your code should be able to understand how the code is designed to work. When you

are writing code you might write notes to yourself to remind yourself of what you have done, need to

do or are having problems with. However, when your code is complete and ready to be handed over to

a user, any comments to yourself that will not mean anything to other users should be removed.

At this point you should read over your code and comments as you would an essay before you hand it

in.

You will be marked on the inclusion and appropriateness of comments in your code for your

assignment.

Activity 10.1 | Create a Macro

In this activity you will copy the code given below to create a new macro.

1. Download the following Excel file which contains a range that has been

defined as rgNumberList. Note that the file is an “.xlsx” file not a

“xlsm” file as it doesn’t contain any VBA code yet.

Activity 10.1 Workbook

2. Go to the VBE and insert a module – from the Insert menu select Module as described in Topic 8 |

Introduction to VBA.

3. Type the following code Into the Module space:

Sub ShowMax()

Dim dblMax As Double

dblMax = WorksheetFunction.Max(Range(“rgNumberList”))

MsgBox dblMax

https://classes.myplace.strath.ac.uk/mod/resource/view.php?id=873318

End Sub

4. Try to predict the result of the macro ShowMax and to interpret each line of code. Then run the

macro.

5. Using your knowledge of Excel’s worksheet functions, edit the code in the macro to:

Find the minimum of the range

Find the sum of the range

Find the third largest number *

* Clue: it’s another built in function so try changing “Max” for “Large” using the

following syntax: Large(Range(“NameOfRange”),3)

Activity 10.1 | Explanation of Code

1. Dim dblMax As Double

This declares the variable dblMax with the data type ‘Double’. As we saw above, Double data type can

store precision floating point numbers from -1.8 x 10308 to -4.9 x 10-324 or from 4.9 x 10-324 to 1.8 x

10308

This data type allows for a very large range of numbers and great accuracy and so uses more memory

than the integer type, for example. Amazingly, however, each Double data type number uses only 8

bytes of memory! An integer type number uses only 2 bytes.

2. WorksheetFunction.Max(Range(“rgNumberList”)

This uses an built-in VBA function in the worksheet (MAX) to ascertain the highest value in the range

specified.

3. MsgBox dblMax

This is a very useful VBA statement which shows a message box. In this case the message reports the

maximum value from the range.

Notes about Functions

VBA has a large number of built-in functions that should save you from reinventing the wheel.

So far you’ve used Now, Max and Large and maybe some others but there are many others. You can

find out more about the functions available by typing “Functions” in the “Type your question here”

box at the top of the VBE.

In VBA for Dummies, there is a useful summary in Chapter 9 of the most commonly used functions.

You can also look them up in the Microsoft online help.

Mueller, J. & Dawsonera (2007) VBA for Dummies

Functions are a powerful feature of many programming languages and in the next section we will start

creating our own.

Creating and Using Functions | Functions Return a Single Value

The most important thing to know about VBA functions is that a function returns a single value. This

is the same as for the majority of built-in Excel functions in a spreadsheet, such as SUM,

AVERAGE etc.

In the previous lessons we have used the Max function and the Large function both of which return a

single number. By definition there is only one maximum and one number that is the third largest in a

set.

Even the Now function returns one value – the current date and time – although in this case multiple

pieces of information make up the single value, all of which go together to specify Now.

Function Procedures

So far the macros we have written are all Sub procedures.

Labelled Sub in the code window.

Sub NameOfSub() End

Sub

Performs an action or set of actions.

Another type of macro is the Function procedure.

Labelled Function in the code window.

Function NameOfFunction() End

Function

Performs a calculation and returns a value.

In each case the parentheses (brackets) after the sub or function name are required and are used to

specify arguments (input values) required by the procedure.

Although the set up is the same, a Sub and a Function are treated differently in VBA and Excel. A

Function procedure is added to the list of built-in functions in Excel and can be used in formulas in a

spreadsheet.

Activity 10.2 | Create a Custom Function

Below is an example of a simple function procedure with comments to

explain the code.

Function MultiplyThreeNumbers(num1, num2, num3)

‘The Function MultiplyThreeNumbers has three arguments; num1,

num2 and num3

MultiplyThreeNumbers = num1 * num2 * num3

‘The three arguments are multiplied together to calculate

the value returned by the function: MultiplyThreeNumbers

‘Note that the name of the function and the name of the returned

value are the same End Function

The new function is used below in a Sub. The function must be defined before it is used so, when you

are organising the code in your module in the VBE, the function definition must come before the Sub

in which it is to be used.

Sub ShowResult()

‘The three variables that will be used as arguments in the

function are declared as integers

Dim intNumber1 As Integer

Dim intNumber2 As Integer

Dim intNumber3 As Integer

‘A new variable for the value that is returned from the

function is declared as an integer Dim intResult As

Integer ‘The values of the three variables are assigned

intNumber1 = 2 intNumber2 = 3 intNumber3 = 5

‘The function is used to calculate the new variable intResult

using the intNumber variables as arguments

intResult = MultiplyThreeNumbers(intNumber1, intNumber2,

intNumber3)

‘The value returned from the function is output to the user in

a message box

MsgBox intResult

End Sub

Try out the code in Excel.

In the same workbook, try using your function in a worksheet.

Enter numerical values into three cells.

In another cell start to enter the formula =MultiplyThreeNumbers… and see how Excel offers you

your own function as an option.

Complete the formula by referencing the appropriate cells. Note that in this example they need to be

entered individually, e.g. “(A1,A2,A3)” and not “(A1:A3)”.

Marvel at your custom function.

Activity 10.3 | A Useful Function

In the previous example the values to be multiplied were included in the

actual code for the Sub – hard coded – so that if different numbers were to

be multiplied the macro would have had to have been edited.

Your task is to make changes to the Sub to make it more useful.

1. With a worksheet open enter numbers in the cells A1, A2 and A3. These are the numbers that will

be multiplied.

2. Copy the code from the previous example into a module in the VBE and edit the code so that the

numbers that are multiplied are the numbers in the cells A1 to A3.

3. Create a button that can be used to execute the procedure. At the bottom of the page is an image

showing how your result might look.

Tip: intNumber1 = Range(“A1”).Value

4. Copy and edit the code to create a second function in a Sub that adds the two numbers in cells A1

and A2 and multiplies the result by a third in cell A3.

5. Add a button that will run the new macro.

Activity 10.4: Parts A & B | Using Solver in a

Macro

Solver is a ‘special case’ of a function in VBA. In fact, there are several

VBA functions involved when you invoke Solver in a macro. In this task,

we will create a macro which can be applied to one of your forecasting

models.

Part A

Download the following Excel file. The last sheet in the workbook contains the sales data and

forecasts for a product using exponential smoothing. The initial value of alpha is 0.3. An error

analysis is also included.

Activity 10.4 Workbook

You are going to record a macro which uses Solver to minimise the MAD. Make sure that you

have read through and understood the steps you need to take before you start recording – have a

practice run if necessary. Once you are ready, follows the steps in Part B below.

Part B

On the Developer ribbon click Record Macro.

On the Data ribbon click Solver.

In the dialog box click Reset All.

Then:

Set the objective to $H$21

Choose Min

Set ‘By changing variable cells’ to $D$2

Add the constraint $D$2 >= 0

Add the constraint $D$2 <= 1 Click Solve When the Solver Results dialog box appears, click OK Click Stop Recording When you look at the code you will find some redundant lines, i.e. lines of code which are repeated unnecessarily. Delete these and you should be left with something like: SolverReset SolverOk SetCell:="$H$21", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$2", _ Engine:=1 SolverAdd CellRef:="$D$2", Relation:=1, FormulaText:="1" SolverAdd CellRef:="$D$2", Relation:=3, FormulaText:="0" This is an instance where the recorded code can vary depending on which version of Excel you are using (2007, 2010, 2013, Office 365) so it is important to inspect it carefully and test it in the version where you will use it. https://classes.myplace.strath.ac.uk/mod/resource/view.php?id=873319 Activity 10.4: Parts C & D | Using Solver in a Macro Part C Use the VBA help (see * note below) to find out more about the SolverOk and SolverAdd functions. What are the possible values of MaxMinVal and what do they mean? What does ‘Relation’ mean to SolverAdd? There is useful additional guidance available at http://msdn.microsoft.com/library. Search for the phrase “Using the Solver VBA Functions”. * Note: If this doesn’t work right away then you need to go to the VBE menu bar and select Tools > References then tick the box for Solver and click OK.

Part D

Now rename your first macro to something meaningful, e.g. minimise_ MAD, then copy it and

rename the copy to minimise_MSE. In this copy, edit the target cell to be the correct one for

minimising the MSE.

We can present these two options to the user with “option buttons” on the spreadsheet.

From the Developer ribbon insert two option buttons (click Insert then pick the option button

from the menu – it is at the right hand end of the top row).

Right-click each option button and select ‘Edit Text’. Change the caption for Button1 to ‘MAD’

and for Button2 to ‘MSE’.

Now right-click the button labelled MAD and assign the appropriate macro.

Do the same thing for the button labelled MSE.

Try clicking one of your buttons and see what happens.

It is not ideal that the Solver results dialog box pops up at the end – this might be confusing for

an end user who does not know what is going on behind the scenes. We can use an additional

parameter to suppress it. Edit your code so that the last line of each macro reads …

SolverSolve UserFinish:=True

Then try clicking your option buttons again.

Summary

In this section we examined working with a range of variables and data types as well as learning to

appreciate the importance of using comments. We practised creating simple functions and also

learned how to include Solver in a macro.

In this section we learned about:

Working with a range of variables and data types

The importance of using comments

Creating and using custom functions

Embedding Solver in a macro

In the next section we will learn about Decisions and Looping..