程序代写代做代考 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..
