Names for Values/Cells/Formulas and Actions


1) Introduction:
2) Names that refer to Values/Cells/Ranges/Formulas:
3) Names that refer to Actions:
4) Summary:


1) Introduction:

In this short lecture we will examine how to associate names with two different
kinds of things in Excel: values/cells/formulas and sequences of actions. In
the first case, we will learn various ways to name values/cells/formulas and
access them through their names. In the second we will dip our toe into the
waters of using Visual Basic as a scripting language in Excel, by learning how
to record a macro, associate it with a button, and replay the macro by pressing
the button. We will briefly examine the recorded macros, and later in the
quarter learn how to enhance them (as I did on the Macros3 worksheet).


2) Names that refer to Values/Cells/Ranges/Formulas:

In Excel we can make a name refer to a value, cell, range of cells, or formula,
and then use the name to represent the value it refers to. Names must be
words with no spaces. To make it easy to read multiword names, we can use the
convention of capitalizing the first letter of each word or put the underscore
character (_) between words: e.g., ClassAverage or class_average or even
Class_Average. When we use these names in formulas, it makes them easier to
read and understand: the formula might use the defined names x, y, and z
instead of refering to the cells storing these values B2, C10, and D15; the
former names come from the domain of the problem, the latter the names of Excel
cells (which are less mnemonic).

Most of the operations on names (defining them, using them, and managing them)
are controlled by the Defined Names group on the Formulas tab. For convenience,
you might put the Name Manager Icon on the Quick Access Toolbar. Clicking this
icon allows us to see the names that we have defined, examine what they refer
to (whether they refer to a value, cell, range of cells, or formula), 
change what they refer to, and delete them.

In the pop-up window named "Name Manager" we can click New... to get a pop-up
window named "New Name". We can enter four major pieces of information here.
Going from top to bottom:

   1) The textbox named Name: allows us to type the name we want to define

   2) The pull-down list named Scope: allows us to specify where in the
   workbook the name can be easily used: the options are all worksheets in a 
   workbook or one specific sheet. We can actually use any name on any
   worksheet, but we need to write the name in a more complicated way if it
   is defined only for a different worksheet.
   
   3) The textbox named Comment: allows us to write a short or long comment
   describing the name. If we have lots of names in a workbook, this
   documentation can be useful.

   4) The textbox named Refers to: allows us to specify a value, cell/range,
   or formula. Whenever we use this name, its meaning is what it refers to (we
   will see examples soon). The default here is the address of the cell we
   clicked before clicking the Name Manager icon.

Finally, after we enter information for 1, 2, and 4 (3 is optional), we can
click OK and we will return to the Name Manager pop-up window (and see these
four pieces of information for the new name we defined). Note that when we
enter information for 4, we can click on cells in the worksheet or type their
addresses, along with functions names, etc. (anything we can type in a cell).

In the Name Manager pop-up window, we can click the titles Name, Value, Refers
To, Scope, and Comment to sort the names according to that attribute.

We can also select a currently defined name in the Name Manager pop-up window
(click anything on the name's line) and click Edit... (or just double click any
line) to change information for 1, 3, and 4 (actually, even just selecting a
name allows us to change what it refers to in the bottom of the Name Manager
pop-up window). Unfortunately, if we want to change 2, we need to delete the
name and define a new one: it might be useful to copy (and then paste) the
information in the Refers to text box.

Actually, the Name Manager pop-up window list 5 pieces of information: the 4
above plus the Value of the name. The value is the value of the cell or formula
that the name refers to: if the name refers to a range of values, the first
few are shown in Array Notation (which we will cover soon).

We can also select a currently defined name in the Name Manager pop-up window
and click Delete to remove that name. In fact, we can select a variety of lines
here and then delete them all by clicking Delete. Note that if a worksheet is
protected, then we cannot delete names in the Name Manager (although we can
still see the names there).

-->Open the the workbook distributed with this lecture.
-->On the Name worksheet, click the Name Manager icon; notice the 9 names
-->  there.
-->Click the Name title which sorts the names in reverse alphabetical order;
-->  click the Name title again and the names are sorted in alphabetical order
-->Click on each name to see what it refers to and verify that...
-->  Alpha1, Alpha1, and Alpha1 refer to the same cell address, but on three
-->    different worksheets: Macro, Macro2, and Macro3. Remember that we can
-->    preface a cell by the name of the worksheet it is on and an exclamation
-->    mark, as in Macro!$B$2
-->  dT and g refer to the values (0.5 and 32.17 respectively)
-->  theta and s refer to the cells to right of the labels Angle and Speed
-->  AveryLongNameForPasting refers to a value (0)
-->  RangeToSum refers to a range (A18:A21)
-->  SumOfRangeToSum refers to a formula (=SUM(RangeToSum))

Notice two things: 

 1) Names can be used anywhere in a workbook (regardless of what Scope says),
      so the "Refers to" of each name that refers to a cell/range starts with
      the name of the worksheet that the cell/range is on, followed by an !
      followed by the cell/range on what worksheet.

 2) All the cells have absolute references.

-->Try deleting some names that refer to simple things: e.g., dT which refers
-->  to a value, s which refers to a cell, RangeToSum which refers to a range,
-->  and SumOfRangeToSum which refers to a formula.
-->Technically, all names refer to formulas that contain values, cells/ranges,
-->  or general formulas with operators and functions.
-->Close the Name Manager and observe that some cells contain the error
-->  #Name? because they use a name that is no longer defined
-->Click the Undo icon to restore those names.

We can also click the Filter pull-down menu on the right of the Name Manager
pop-up window and click Names Scoped to Worksheet (to see only those names
whose Scope is some) or  Names Scoped to Workbookt (to see only those names
whose Scope is the entire workbook); clicking Clear Filter shows all names
again.

We can add a bunch of names and the cells they refer to in one operation, by
selecting a rectangular range of cells (two columns) and then clicking the
Create from Selection icon (again in the Defined Names group ont he Forumulas
tab). We then check the box that accurately describes where the names are in
the selected range. So, if we select Left Column, then every name in the left
column will be defined to refer to the cell to its right.

-->Select the range A13:B15 and click the Create from Selection icon
-->The Top row and Left column are both checked (both options make sense to
-->  Excel); uncheck to Top row box and then click OK
-->Use the Name Manager to see that names Name1, Name2, and Name3 are now
-->  added, refering to the cells to their right (B13, B14, and B15)
-->Delete these three names

Once we have named cells, we can type these names into in formulas.
If we use a name that we have not defined (possibly misspelling a name that
we have already defined), the value of the cell will show as #NAME?. If we want
to use a name scoped to a different sheet, we must preface the name with
the name of that sheet and an ! mark.

-->See the formulas in cells B8 and C8 for formulas with legal names

When entering a formula, if we click a cell that is named, the name is used in
the formula instead of the cell's column/row specification (of course we can
always type the column/row if we want it). If we are entering a formula we can
paste a name into it (instead of typing the name or clicking the cell it refers
to) by clicking the Use in Formula icon and then clicking on the name we want
to paste. The name might be a long one, that we don't want to type (possibly
because we might misspell it).

The names that appear when we click the Use in Formula icon are all the names
that are scoped to the workbook and all the names scoped to the worksheet on
which we are typing the formula. For example, sometimes we want the name X
on one worksheet to refer to a cell on that worksheet, and the namx X on 
another worksheet to refer to a cell on that worksheet. We can do this by
specifying each by a scope restricted to its worksheet, NOT using the Workbook
scope. If a name should mostly/always be used just on one worksheet, we can
choose the Worksheet scope for it (but we can still refer to it on another
worksheet, by using the name of that sheet and an ! mark.

-->In any blank cell, enter =2* and then click cell B2 and watch it put in
-->   theta into the formula.
-->Now type + and then click the Use in Formula icon and then click the name
-->  to appear next in the formula

If we instead type the cell's column/row specification, the column/row
specification will appear in the formula, not the name (this is different
than clicking on the named cell, discussed above).

If a formula has column/row specifications that are named cells, we can convert
these specifications to names by clicking the disclosure triangle to the right
of the Define Name icon and then clicking Apply Names. First we select the
cells that we are going to put names in, then click the disclosure triangle to
the right of the Define Name icon and then click Apply Names. Then, we click
all the name substitutions we want to make in pop-up window named "Apply
Names" (clicking a selected name again will unselect it) and then we click OK.

-->Select the cell B9 and examine its formula.
-->Click the disclosure triangle to the right of the Define Name icon
-->Click Apply Names
-->Click the s and theta names; then click OK; notice the changes to the
-->  formula in cell B9

-->Examine the formula in A23, which is =SUM(RangeToSum), where the name
-->  RangeToSum refers to =Name!$A$18:$A$21 (a range right above it)
-->Change a value in some cell A18:A21; notice the change to A23
-->Likewise, examine the formula in A24, which is just =SumOfRangeToSum, where
-->  the name is defined by the formula =SUM(RangeToSum)
-->Change a value in some cell A18:A21; notice the change to A23

We can also make good use names in Charts, which we will see in the next
lecture.


3) Names that refer to Actions:

In this section we will learn how to record a sequence of actions in Excel
(called a macro) and then replay those actions whenever useful. At the start
of recording the macro, we will be prompted to associate it with a name; then
we can run the macro by using its name or associating the name as the action to
perform when we click a button form placed on the spreadsheet.

To start recording a macro, click the Record Macro icon in the Code group of
the Developer tab and fill in the "Macro name" text box in the pop-up window
named"Record Macro". Use meaningful and reasonably long names using letters,
numbers (and no spaces). Use the default ("This Workbook") for the "Store Macro
In" pull-down list. Finally, Click the OK button.

At this point you can perform any operations on the worksheet, selecting cells,
entering values or formulas in cells, copy/cutting/pasting cells (including
Paste-Values, which is especially useful in macros), setting the formats/
conditional formats of cells, etc., all while, Excel is remembering/recording
your commands. 

To stop recording a macro, select the Stop Recording icon (where the Record
Macro icon formerly appeared: this space toggles back and forth between these
two icons).

Once a macro is recorded you can run it or delete it by first clicking the
Macros icon, followed by typing the macro's name (or selecting the macro) and
clicking the Run or Delete button. We will discuss the Step Into and Edit
buttons later in the quarter, when we learn more about Visual Basic.

Using the Step Into and Edit commands, we can test and debug our macro. It is
often the case that the first time that we record a complicated macro we will
make some mistake, but not realize it until we run the macro in different
circumstances to test it.

For now, if we do discover that the macro is wrong, we should just delete it
and rerecord it; we can also just start recording a macro (using the commands
above) and type in the name of the existing macro that we want to correct;
Excel will then prompt us, asking whether or not to replace the existing macro
with the new one. Later, when we learn more about Excel, we will directly
edit these macros to correct or enhance them.

Note that when some macros are recorded, they process the currently selected
cell (what is selected before the macro is recorded/run). So to run it
correctly, we must first select a cell and then run the macro. Other macros
start by selecting some cell and don't care which cell is selected.

Running macros using the Macros icon is cumbersome. We can simplify running a
macro by creating a button form and associate clicking it with automatically
running a macro.

To create such a button, we click the Insert icon in the Controls group on the
Developer tab. Then we click the button icon, which is first under Forms
Controls, NOT the button under ActiveX Controls. IMPORTANT: the scrollbars
we've learned to use are selected from the ActiveX Controls, but the buttons
come from Forms Controls. It is important to know and remember this difference.

Once we have made our selection, we move the cursor, which  will look like a +
to the upper-left hand corner of the cell(s) that will contain the button.
Press down of the left button of the mouse, then while holding it down, drag
the rectangle that appears on the screen down and to the right, until it is at
the lower-right hand corner of cell(s) that will contain the button, and then
remove your finger from the button.

The button will automatically be labelled with some text (which we will learn
how to change in a minute). A pop-up window named Assign Macro lets us choose
which existing Macro to run when this button is clicked (select the name of one
of the previously recorded macros in the window's list) or we can record a new
macro, right then and there (click the Record... button), which will be
recorded immediatley and automatically assigned to the button.

By right-clicking the button you can edite the text on its label, move
the button, resize the button, assign the button to a different macro, or
change the format of the text on the button.

To make any of these changes, first right-click the button (unlike with a
scroll bar, we don't need to be in Design Mode). Then, to...

   ...change the text on its label: select the text and change it.

   ...move the button: press on the grey boundary of the button and move the
      button to the desired location and then release the mouse.

   ...resize the button: press any of the circles on the grey boundary of the
      button, then resize it to the desired size and finally release the
      mouse.

   ...assign the button to a different macro: select Assign Macro and click
      the macro you want to assign it, and the click OK

   ...change the format of the text on the button: select Format Control and
      update information the Font tab.

-->On the Macro worksheet, examine the model of 2-d heat flow (for details of
-->  this model, see the book "Neuwirth and Arganbright: The Active Modeler:
-->  Mathematical Modeling with Microsoft Excel" section 3.4, on heat
-->   propagation in a 1-d and 2-d setting, pages 141-153); we will concentrate
-->   on the macros controlling the model, not the model itself
-->This worksheet has a rectangle at the top that represents the current
-->  temperature of all the cells in a 2-d piece of material
-->The temperatue on the boundary cells (in red) are held constant: the top and
-->  bottom sides are 0; the left and right sides are hotter in the center
-->Clicking the "Initialize" button runs the InitHeat macro, which puts the
-->  VALUE 0 in the Iteration cell (K4) and copies the VALUE 0 into all the
-->  cells inside the boundaries (you can change the values in red and the
-->  InitHeat macro will not change them
-->The chart at the bottom graphically shows the temperatures of the cells

-->The rectangle at the bottom predicts the temperatures of the cells for the
-->  next iteration in time. Notice the small difference between the 
-->  temperatures in the leftmost and rightmost white cells
-->Clicking the "Update" button runs the UpdateHeat1 macro, which copies the 
-->  VALUE (not the formula) of the Iteration number for the bottom rectangle
-->  (K16) into K4 and then copies the VALUES (not the formulas) of the
-->  temperature for all the cells from the bottom rectangle (C17:I25) into the
-->  top rectangle (C5:I13): IMPORTANT: when the macro pastes these values, it
-->  is using the Paste-Values option in the Paste icon: we are pasting the
-->  VALUES in theses cells, not the FORMULAS 
-->At this point, the chart is also updated and the bottom rectangle is updated
-->  with the newer temperatures for Iteration 2
-->Each time we click "Update", we simulate the heat flow for the next time
-->  iteration
-->Practice clicking the "Initialize" and "Update" buttons and watch their
-->  results

If we examine the cells in the range C5:I13 we will see just values. If we
examine the cells in the range C17:I25 we will see formulas, which compute
values based on the name Alpha1 (scoped to this worksheet, to refer to the real
value produced by the scroll bar) and values from selected cells in C5:I13.
Increasing Alpha1 will make the heat restributed over the area faster.

-->When you feel comfortable with what these macros do, delete the macros and
-->  these buttons, then rerecord these macros yourself and recreate the
-->  buttons that run them; check that you have duplicated their operation

-->On the Macro2 worksheet, there are 6 different rectangles. Each lower one
-->  advances one Iteration from the one above it, so the bottom rectangle
-->  shows the result of 5 iterations from the top one
-->Clicking the "Initialize" button runs the InitHeat macro, which puts the
-->  VALUE 0 in the Iteration cell (K4) and copies 0 VALUES into all the cells
-->  inside the boundaries.

-->IMPORTANT
-->This button runs the SAME InitHeat macro as on the previous worksheet.
-->Running the macro in THIS WORKSHEET updates cells in the same position, but
-->  on THIS WORKSHEET, so it does the same correct job on this worksheet.
-->Thus, you can use the same macro on different worksheets, so long as the
-->  work sheets use the same cells for the same purpose. All top rectangles
-->  on the Macro, Macro2, and Macro 3 worksheets occupy the same cells.

-->Clicking the "Update 5" button runs the UpdateHeat2 macro (different from
-->  the UpdateHeat1 macro run on the Macro worksheet), which copies the VALUE
-->  of the Iteration number for the bottom rectangle (K64) into K4 and then
-->  copies the VALUES of the temperature for all the cells from the bottom
-->  rectangle (C65:I73) to the top rectangle (C5:I13)
-->So each update here advances 5 iterations; we could use the same technique
-->  to advance any number of iterations, using more and more cells on the
-->  worksheet.

-->Clicking the "Update 1" button runs the UpdateHeat1 macro (same as on the
-->  Macro sheet).
-->As with the HeatInit macro, this one works on this page as well, because
-->  the second rectangles occupy the same cells.
-->So, on this worksheet we can either advance the Iterations by 5 or 1.

Notice that the red regions in the picture touch after clicking Update1 25
times, or after clicking Update5 5 times.

The Macro3 worksheet is the most general, but requires knowing some more about
Visual Basic to understand. When clicking the "Update via L1" button (which
runs the UpdateHeat3 macro), the Iteration will advance by whatever number is
in cell L1, by doing the equivalent of the UpdateHeat1 macro that many times).
The value in L1 initially is 20, and you can see those 20 iterations in the
chart, which smoothly updates its picture 20 times. You can put in any
non-negative number (negative numbers turn cell L1 red).

If you are interested, click the Macros icon and click the UpdateHeat3 macro,
and then click Edit. You should see a window with all three UpdateHeat macros.
The UpdateHeat3 macro appears as 

  Dim i As Integer
  For i = 1 To Range("L1").Value
    UpdateHeat1
  Next i

We will learn what all these things mean later in the quarter. But if you
already know something about programming, you might be able to figure out
how UpdateHeat3 works.


4) Summary:

Here is a quick summary of skills to acquire from working on this lecture.
You should be able to discuss each of these topics a bit, but more importantly
know HOW TO DO something in Excel,

Know how to create names for values, cells/ranges, and formulas, and how to
manipulate and use them when entering formulas in other cells.

Know how to record macros and run and delete them.

Know to to create buttons and manipulate the properties of buttons, including
assigning them macros to run when pressed.