Input Boxes and Message Boxes

The use of the InputBox, MsgBox and Application.InputBox were mentioned in the last chapter, and so are not addressed further here in detail. However, it is worth noting that the use of these approaches means that code execution is halted until some feedback is provided by the user, which can be inconvenient in cases where one wishes to complete code to run automatically and without intervention.

For…Next Loops

The use of a For…Next loop is the most basic and important approach to automate repetitive operations involving calculations. The core syntax is typically of a form such as:

For i = 1 To 1000
…Code to be executed (that involves i)
Next i

or

For i = 1 To Range("NLoops")
…Code to be executed (that involves i)
Next i

These create a looping process in which an indexation variable i starts with the value 1 and is incremented by the default of 1 each time that a step of the loop is executed (and hence takes the values 1, 2, … . in sequence).

Some basic points to note are:

• The Next statement is required to define at what point to start to repeat the operation within the loop, rather than moving to the code steps that are intended to be run after the loop.
• The i after the Next statement is optional, but its inclusion renders the code easier to read and so is less error-prone. Especially where there are multiple or embedded loops, the “closing” of the loops in this way is important.
• The code within the loop will (almost) always depend in the value of the indexation number i in some way.
• The indexation variable (here: i) could essentially be given any (valid, non-reserved) name. Of course, it is recommended and common practice for the name of a variable to provide some indication as to the nature of the variable, so that names such as i or iCount and so on are frequently used for integers (as are j, k, l, m and n), with x, y, z often being used for variables that can take any value.

In some cases, increments other than 1 are needed, for example:

• For i = 1 To 1000 Step 2 will loop through the values for i of 1, 3, 5, 7, … , 999.
• For i = 1000 To 1 Step -1 will loop through values 1000, 999, 998, … 1.

For Each… In…Next

A For Each…In…Next loop can be used to automate the repetition of operations by working through the items in an object collection (rather than through a set if integers or a structured indexation set when using a For…Next loop). A typical syntax would be:

For Each mySheet in Worksheets
…Code to be executed (that involves mySheet)
Next mySheet

Note that the name mySheet in the above is simply an indexation variable, and one which would (generally) be used within the loop to specify the object on which the operation is to be performed (such as mySheet.Delete); once again, the name is essentially arbitrary (within the constraints of name validity, such as no spaces, and no reserved words). Of course, it makes sense from a transparency perspective to use a name that indicates the nature of the object within that context.

The uses of this type of loop include:

• To delete specific worksheets in a workbook, such as those whose name starts with a specific word or another identifier.
• To reformat all charts (ChartObjects) in a workbook.
• To make a list of the names of all worksheets in a workbook.

It is also worth noting that the items in the object collection are worked through in their implicit numerical order (even if such an order is not immediately obvious to a modeller or user), just as a For…Next loop uses a positive step of one by default. For example, code such as:

For Each nm in Names
MyStrName = Range(nm).Name.Name
Next nm

would work through the names of the named ranges in a workbook. Note that the word Name is required twice in the above code: since Names refers to the collection of named ranges, Names.Name refers to a specific name object (which has several properties, including its name) and so Names.Name.Name refers to the name.

If…Then

The If…Then statement is essentially self-explanatory, creating the situation where the code conducts different operations depending on some condition. The simplest form is essentially:

If ConditionTest Then
… first possible set of instructions
Else
… second set of instructions
End If

Note that there are cases in which an operation is required to be conducted only when a condition is met, with no operation required when the condition is not met. In such cases, one may consider leaving out the Else statement. However, it is generally more transparent to state explicitly that no operation is required; this can be achieved by using the Else statement but with a simple comment line afterwards.

Where there are multiple conditions to be tested, the ElseIf statement can be used, before a single Else:

If ConditionTest-1 Then
… first possible set of instructions
ElseIf ConditionTest-2 Then
… second possible set of instructions
ElseIf ConditionTest-n Then
… nth possible set of instructions
Else
… final possible set of instructions
End If

Select Case…End Select

This can be used to execute one of a set of statements depending on the result of a test. In a sense, it is an alternative to an If…Then statement, being more transparent in some cases (such as when running scenarios), simply because it is often visually easier to identify in the code which case is to apply in specific circumstances. A typical syntax is:

ScNo = InputBox("Enter Scenario Number")
Select Case ScNo
Case 1 To 3
… Apply the relevant operation (e.g. lookup function based on ScNo)
Case 4, 5
… Apply the relevant operation (e.g. lookup function based on ScNo)
Case 6 To 10
… Apply the relevant operation (e.g. lookup function based on ScNo)
Case Else
MsgBox "INVALID CASE NUMBER: RERUN AND CHECK YOUR ENTRY"
End Select

GoTo

The GoTo statement may also be used to determine branching that is to apply as the result of the evaluation of a condition (such as If…Then or Case condition) or with an On Error statement to branch to an error-handling procedure (see later).

As an example, in the following, where a user has provided an invalid entry into the InputBox, the Case Else statement will be applied, resulting in the code using the GoTo command to return to the scenario input code line at the beginning of the Select Case statement.

  TrySelectCaseAgain:
ScNo = InputBox("Enter Scenario Number")
Select Case ScNo
Case 1 To 3
'…Apply the relevant operation (e.g. lookup function based on ScNo)
Case 4, 5
'…Apply the relevant operation (e.g. lookup function based on ScNo)
Case 6 To 10
'…Apply the relevant operation (e.g. lookup function based on ScNo)
Case Else
  GoTo TrySelectCaseAgain
End Select

Note that the key syntax is the use of a (valid, but essentially arbitrary) name after the GoTo statement, and the use of that name (followed by colon) to indicate the place in the code at which one wishes to return (without the colon, an error message would arrive as one would be effectively creating a variable without specifying any operation to be conducted on it, so the code line would be incomplete).

Do…While/Until…Loop

The Do…Loop structure is useful when one wishes to repeat aspects of code execution an indefinite number of times, until a condition is satisfied.

Note that:

• The structure is typically used with one (but not both) of either the While statement or the Until statement. The first continues looping whilst a condition is still true, whilst the latter loops until the condition is met for the first time.
• The While (or the Until) conditional statements can be used at either the beginning or the end of the loop, depending on whether one wishes for the loop's operations to be conducted at least once or not: that is, the two generic structures with the While form are:

Do While ConditionToCheck
… Operation to run ….
Loop

and

Do
… Operation to run …
Loop While ConditionToCheck

(The use of Until also has two analogous generic structures.)

An additional, and similar, structure is the While…Wend construct (although it is sometimes regarded as less flexible and clear than the others):

While ConditionToCheck … Operation to run ….
Wend

One important example of these is in the resolution of circular references, as discussed in Chapter 10.

Note that when using such looping structures (including with the use of GoTo for correcting input data in the previous example), there could be a possibility that the condition is never met, irrespective of how many times the loop is run. Therefore, such loops may also need to include an indexation number which increments at each iteration of the loop, and which exits the procedure if a preset number of iterations of the loop is exceeded (using the Exit Sub statement at the appropriate point in the code).

Calculation and Calculate

An important topic is that of the recalculation of Excel as a macro is running. There are two main objectives:

• To ensure that correct numerical values result during code execution. A macro that has been written or recorded in an environment in which Excel's calculation options are set Automatic (under File/Options/Formulas) may implicitly rely on Excel having (automatically) recalculated at the required times. Such a macro may give incorrect results if run when the calculation options are set to Manual, because the recalculations will not occur. Some techniques that can be used to reduce the chance of such cases arising (by forcing a focus during the process as to when recalculations are required) are:
  • Switch the Excel calculation settings to Manual, before the recording process.
  • Switch the Excel calculation settings to Manual as the first step of the recording process.
  • Write and/or test the macro when the calculations are set to Manual.
• To optimise computational time, conducting (as far as possible) only those calculations that are necessary. This is a highly non-trivial topic: of course, the recalculation of a workbook during code execution increases computational time taken, so that ideally one should only recalculate when it is necessary. However, it can be rather subtle to determine whether a recalculation is necessary or not, partly because – when Excel is set to Automatic calculation – some actions that trigger recalculation may not initially be expected to do so (such as entering new data, deleting or inserting a row, renaming a worksheet, hiding or unhiding rows, some filtering actions, creating or modifying named ranges, and so on). Further, only specific parts of a model may in theory be required to be updated as a macro is run. For example, some parts may require recalculation only toward the beginning or end of a macro (or set of macros), whereas other items may need to be calculated at each step of a loop.

Workbook recalculation settings can be applied from within the code (e.g. at the start and at the end) using the following syntaxes (which could be established by recording a macro as one selects the calculation settings):

• Application.Calculation = xlManual for manual recalculation.
• Application.Calculation = xlSemiautomatic for automatic recalculation, with the exception of DataTables.
• Application.Calculation = xlAutomatic for automatic recalculation, including DataTables.
• Application.Iteration = True for using iterative calculations to resolve any circular references.

As mentioned above, in many cases it can make sense to create macros which by default place a workbook on Manual recalculation at the beginning and then only calculate when necessary. On the other hand, there is a risk when doing so that some necessary recalculation steps are overlooked.

When one wishes to control calculation during the run of code, the possibilities are:

• Calculate or Application.Calculate (recording of F9 short-cut). This invokes Excel's “smart” recalculation engine, which calculates only items that have changed since the last recalculation, Volatile functions and conditional formats, and items dependent on others, or which are otherwise flagged as needing recalculation (sometimes known as “dirty” items). It calculates these items for all open workbooks and would update DataTables as required if the Automatic Except Tables (xlSemiautomatic) setting has been used for a workbook.
• Application.CalculateFull (recording of Ctrl+Alt+F9 short-cut). This forces a recalculation of all formulas in all open workbooks, including all DataTables.
• Application.CalculateFullRebuild (recording the Shift+Ctrl+Alt+F9). This forces a complete rebuild of all dependencies and a full calculation of the workbook.

In many practical cases, it is usually sufficient to simply use the Application.Calculate statement at the appropriate places in the code (such as when new data is brought in or generated etc.). This is a quick recalculation approach, because in most cases only part of the input data or a few formulae change between calculations. (Note that the word “Calculate” is shown in Excel's Status Bar if a higher-level build is required (such as a full rebuild), or if the model contains a circular reference.)

Some additional points to bear in mind concerning the recalculation and optimisation of the run time are:

• If one is unsure whether a recalculation is necessary, it will generally be better to favour using too many recalculations, rather than too few.
• Most of the benefit in terms of improving computational speed generally results from making a few key changes to the code; the achievement of a perfect optimisation may not be a sensible objective (if computation speed is of genuine fundamental importance, one may need to work outside the Excel/VBA environment, and to use pre-compiled code, not code that compiles at run time, as is the case with VBA).
• The overall run time is often highly influenced by the underlying design and structure of the model, as well as the VBA code. For example:
  • The inappropriate use of lookup functions (such as VLOOKUP) or the use of Volatile functions (such as OFFSET and INDIRECT) can slow down a model significantly.
  • Within the VBA code, the use of Copy/Paste operations (and the selection of objects in general) will be much less efficient than the use of assignment statements (or the direct manipulation of objects without selecting them explicitly).
  • The use of VBA arrays rather than Excel cells or ranges to store data (see later) or intermediate calculations may be a more efficient way to generate optimised run time. It allows one a clearer view of when Excel recalculates or needs to do so. Excel will not recalculate during the conducting of operations that involve purely the values in the arrays (whereas equivalent operations conducted on tables of data in Excel ranges may force a recalculation each time that a data point in the range is changed).
  • In principle, one can structure models according to which parts need recalculation at the same time, so that different parts of the model can be recalculated only when absolutely necessary (individual worksheets or ranges). This would be a requirement if one were to aim to achieve maximal computational efficiency, although in some cases, even small changes in a model can make a difference.

Where one wishes to ensure that the calculation of specific worksheets is switched off as the code is running, one can use code such as:

With AnalysisSheet
  .EnableCalculation = False
End With

In such cases, toward the end of the code run, the recalculation of such as worksheet would then need to be reset and performed:

With AnalysisSheet
  .EnableCalculation = True
  .Calculate
End With

(i.e. the first would be used toward the beginning of the overall code, and the second toward the end).

Similarly, individual ranges can be calculated using the Range.Calculate method. In other words:

• Application.Calculate calculates all open workbooks.
• Worksheets("Sheet1").Calculate calculates only the specified sheet.
• Worksheets("Sheet1").Rows(2).Calculate calculates only the specified range (the specified row).

Dependents and volatile cells which are outside the range are ignored.

Screen Updating

One simple way to speed up code execution (by roughly 30% in many cases) is to switch off the automated updating of the Excel display as the code is running. This can be done by using (toward the beginning of the code):

Application.ScreenUpdating = False

and (toward the end of the code):

Application.ScreenUpdating = True

The main disadvantage is that having visibility of the display can sometimes help in debugging, or help one to see the progress of code execution, and it is sometimes simply comforting to see the actions being performed. Thus, this technique may be most applicable at the later stages of code development, after testing is essentially complete.

Measuring Run Time

In principle, the time required to run code can be measured by using the VBA Timer function, which is expressed in seconds (its has no arguments so its parenthesis can be omitted or left empty). The code would generically read as:

Starttime = Timer()

… Conduct calculations or operations

Endtime = Timer()
RunTime = Endtime - Starttime

It is of course worth bearing in mind that:

• The Timer function is based on elapsed time since midnight, and so would be misleading if the code were run over this threshold. The more general Time function could be used in its place, but is slightly less convenient in that it provides the current system time in days, so generally needs to be converted to express the value in seconds (by the appropriate multiplication).
• If other applications are running at the same time or the computer is involved in other processing activities in the background (e.g. virus checking), then these will affect the run time; it is not easy to determine to what extent this plays a role in an individual run. Nevertheless, the overall run time (especially when measured several times) can provide a good indication as to the effectiveness of potential measures to improve overall speed and efficiency.
• If a step-through approach is used to check that such code is working, it will not return values that are representative of what would happen if the code were to be run, as the elapsed time will of course include that taken to step through the code.

The total run time should also be shown on Excel's StatusBar, using:

Application.StatusBar="RunTime " & Round(RunTime, 2) & " Seconds "

Displaying Alerts

In some cases, messages that display warnings in Excel must be disabled for the code execution to continue as desired. For example, an attempt to instruct Excel to delete a worksheet typically leads to a prompted warning that the operation is not reversible, and one needs to (manually) confirm that one indeed intends to continue with the instruction. The equivalent in VBA would also lead to the suspension of code execution at that point.

The default warnings can be switched off by using:

Application.DisplayAlerts = False

Of course in general it is beneficial to have such warnings active, and once they have been disabled in a section of code for a specific reason, they typically will need to be switched on again at the end of that section of code using:

Application.DisplayAlerts = True

Accessing Excel Worksheet Functions

VBA has several built-in functions which can be used in the code. For example, Sqr returns the square root of its argument (equivalent to Excel's SQRT), Log gives the natural logarithm (equivalent to Excel's LN) and Rnd returns a uniformly distributed random number between 0 and 1 (equivalent to Excel's RAND).

Some key points about these functions are:

• A list of the available VBA functions can be seen by typing VBA. in the Code window. When doing so, it will be seen that the number of arithmetic operations is very limited when compared with Excel. For example, although there is an extensive range of functions to manipulate text (such as Left, Mid, IsEmpty), the basic arithmetic operations corresponding to Excel's SUM, MIN and MAX functions do not exist (the specific list of mathematical and financial functions available can be accessed by typing VBA.Math. and VBA.Financial. ).
• A list of all worksheet functions that are available in VBA can be seen by typing WorksheetFunction. in the code window. These can be implemented within VBA by typing WorkSheetFunction.Sum( ), and so on. It is also often convenient to create a worksheet function object variable, so that individual functions can be more efficiently accessed from it:

Set wsf=Application.WorksheetFunction
Set wsfSum=wsf.Sum
Set wsfCount=wsf.Count
…

• For those worksheet functions which use the “dot” notation in Excel, such as many of the statistics functions in Excel 2010 onwards, an underscore is required when these are accessed within VBA (e.g. WorksheetFunction.StDev_S).
• Where VBA and Excel functions for the same calculation both exist (including Sqr, Rnd, Log ), the VBA functions must be used.

Executing Procedures Within Procedures

When code becomes large, it is useful to structure it into separate procedures, with each having a limited and clear functionality. This will require that subroutines and functions in the same module, same workbook or other workbooks are used.

When using several procedures, one will need to take care that the scope of each is appropriate. The scope of a procedure defines its availability for use by another procedure. Subroutines and functions can have one of two scopes:

• Public. When used in general code modules, such procedures are available within the project (workbook), as well as to any other project that references it. Public is the default scope for procedures (except for event procedures, which are Private by default), so most procedures do not need an explicit definition as Public.
• Private. The declaration of a procedure as Private (e.g. using Private Sub NameofSub()) would mean that it is available only to other procedures in the same module of the workbook, and not to the whole workbook or to other modules within it. A subroutine declared as Private will not display in Excel's Macro dialog box (other procedures that will not show in this dialog are those that require arguments, and those contained in add-ins). Similarly, a function declared as Private (e.g. using Private MyFunction(arg1, arg2)) will not be accessible from Excel, and also available only within its own VBA code module. The Option Private Module statement can be used at the beginning of a module to make all procedures in a module Private.

Subroutines and functions which are Public can be executed from the Excel workbook in which they are contained using the methods earlier in this text (e.g. use of the Macro dialog or the Formula/Insert Function menu).

When executing procedures from other procedures in the same workbook, the following possibilities exist:

• Subroutines which are Public can be executed (as can Private subroutines in the same module) by:
  • Using the Run method, typing the name in inverted commas followed by the parameter list (not enclosed in brackets), e.g. Run "SubName", arg1, arg2.
  • Using the Call statement, typing any required arguments in brackets, e.g. Call SubName(arg1, arg2). The Call statement can be omitted if the argument list is not enclosed in brackets, e.g. SubName arg1, arg2. The use of the Call statement is arguably preferable as it is a more explicit statement that control is being transferred to another procedure.
• Functions which are Public (or Private in the same module) can also be accessed in the above ways, although generally one is interested in knowing the return value of a function, so that alternatives or variants of these methods are used:
  • Using a variable to represent the value returned by the function (e.g. ValueToUse=MyFunction(arg1, arg2)).
  • Using the Run method, enclosing the procedure name and arguments in brackets (e.g. ValueToUse=Run("MyFunction", arg1, arg2) ).

When executing Public procedures in another workbook (whether from Excel or from VBA code), a reference needs to be created to the procedure either by:

• Preceding the name of the procedure with the name of the workbook in which it resides and using the Run method or the return value method (e.g. Run "Book2.xlsm!SubName" or x=Book2.xlsm!MyFunction(arg1,arg2).
• Creating a reference to the second workbook, using Tools/References in the VBE. In this case, the procedure can be accessed without preceding it with the workbook name, and the referenced workbook does not need to be open.

When passing arguments between procedures, one may also need to be careful with respect to whether this is done on a ByRef or ByVal basis, as discussed in Chapter 31.

Accessing Add-ins

When using add-ins (such as Solver), one first needs to make a reference within VBE to the add-in. This can be achieved using Tools/References, checking (tick-selecting) the relevant box; if the add-in box is not visible, it should be verified that the add-in has been loaded (by using the Excel Options menu, and under Options/Add-ins/Manage: Excel Add-ins, selecting Go).

Example: Numerical Looping

A frequently required technique is to work through individual elements of a range and write a different value in each cell. Figure 30.1 shows the code and the results of running it, where the objective is to write the integers from 1 to 10, and their squares, in two adjacent columns in Excel. Several points are worth pointing out:

• From the Project window, one can see that the name of the worksheet in the workbook is “30.1”, whereas the code name is Sheet1; this code name has been used to refer to the worksheet, rather than using Worksheets(“30.1”), so that the code would still work if the worksheet name were changed in the workbook.
• The With…End With statement is used to ensure that all operations are conducted with respect to the Cell A1 of the specified worksheet.
• The Offset property of the range is used to move down the rows and across the columns; the loop indexation i is used to represent the number of rows to offset, and the column offsets (of 0 and 1) are hard-coded. The Cells property could have been used in its place.

Example: Listing the Names of All Worksheets in a Workbook

A list of all the names of the worksheets in a workbook can be created by using code shown below, noting the following points:

• The For Each…Next construct is used to loop through the collection of worksheets in the workbook (known as Worksheets).
• The iCount variable is used as an indexation to ensure that the worknames are written under the other in Column D of the worksheet with the worksheet whose sheet code name is Sheet1. The variable is incremented by 1 at each pass through the loop.

iCount = 0
For Each ws In Worksheets
  With Sheet1.Range("D1")
    .Offset(iCount, 0) = ws.Name
  End With
  iCount = iCount + 1
Next ws

The individual items in collections can be accessed using code such as Workbooks.Item(3) or simply Workbooks(3). Such a technique may also be used to access individual elements of a range (for example Range("DataSet")(i) corresponds to Range("DataSet").Cells(i, 1).Value). However, in the context of object collections (such as Worksheets), one generally does not initially know how many items there are in the collection, so that this is frequently not the most appropriate way to refer to such items, unless the total number is first determined:

With ThisWorkbook
  N = Worksheets.Count
End With

Example: Adding a New Worksheet to a Workbook

The Worksheets collection can also be used to add a worksheet to the active workbook and to name this new worksheet. For example, if the worksheet is to contain results of some calculations, then it could be added as soon as the macro execution starts, using:

Worksheets.Add.Name = "Results"

One could also generalise the process, so that multiple Results worksheets can be added and named in accordance with the number of worksheets that are already in the workbook:

With ThisWorkbook
  N = Worksheets.Count
  Worksheets.Add.Name = "Results" & N
End With

The code could also be broken down into the separate steps of adding a new sheet and then renaming it:

With ThisWorkbook
  N = Worksheets.Count
  Set Sheetnew = Worksheets.Add
  Sheetnew.Name = "Results" & N
End With

Note that these code lines attach a number to the name of the Results worksheets that depends on the number of worksheets in the workbook, not according to the number of Results worksheets that already exist (so that the first Results sheet may receive a name such as Results3). To name the sheet according to the number of Results sheets only, one would need to count these: for example, a variable (such as iCount in the example above) could be used (initialised at 0, and incremented every time a new Results sheet is added). Alternatively, a For Each loop could be used to work through all worksheets in a workbook and count how many of them start with the word “Results”, i.e. by checking (for example) that the first seven letters of the name are “Results” (and incrementing a similar iCount variable if so). Although the former approach is computationally more efficient, in general both approaches may be required: the latter may be used to establish the initial value of the variable iCount, as the code may already contain Results worksheets, but once this initial value is established, it need be incremented only when a new Results worksheets is added (or deleted).

Example: Deleting Specific Worksheets from a Workbook

Worksheets can be deleted from a workbook using VBA code, but of course one would generally wish to switch off the display of the Excel alerts (as mentioned earlier). Also, one would wish only to delete specific worksheets, not all of them. The following code deletes all worksheets whose Excel name starts with the word “Results”. It uses the (VBA) Left function (and the Name property) to find the first seven characters of each worksheet name, and the VBA UCase function (equivalent to Excel's UPPER) to transform these characters into upper case for the comparison process (to ensure that all such worksheets are identified independently of the case of the spelling, whether lower-, upper- or mixed-case).

Application.DisplayAlerts = False
With ThisWorkbook
For Each ws In Worksheets
If UCase(Left(ws.Name, 7)) = "RESULTS" Then ws.Delete
Next ws
End With
Application.DisplayAlerts = True

Example: Refreshing PivotTables, Modifying Charts and Working Through Other Object Collections

The use of object collections can also be powerful to modify or manipulate other types of Excel objects. For example, the list of PivotTables (i.e. PivotTable objects) in a worksheet can be identified using:

Worksheets("NameofWorksheet").PivotTables

This can form the basis for other operations, such as:

NPvtTbls= Worksheets("SummarySheet").PivotTables.Count

In order to refresh (one or all) PivotTables, it would be most convenient to record the process of refreshing one (in order to establish the base syntax) and to adapt this as required. The resulting code may look like:

 For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
 Next pt

If one wanted to refresh specific PivotTables only, the code may look like:

For Each pt In ActiveSheet.PivotTables
 Select Case pt.Name
    Case "PivotTable1", "PivotTable3", "PivotTable6"
          pt.RefreshTable
     Case Else
  ' Do Nothing
   End Select
Next pt

For PivotTables that are spread across multiple worksheets, one may also loop through each worksheet in the workbook and refresh the PivotTables in each worksheet as one is doing so:

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
             pt.RefreshTable
  Next pt
Next ws

Of course, similar concepts apply to other object collections, with the properties and methods used being specific to those objects. For example, one could create a list of all named ranges in a workbook (as well as being able to see their scope and address) using code such as:

With ThisWorkbook
With Sheet1
icount = 0
For Each nm In Names
  With Range("A1")
    .Offset(icount, 0) = nm.Name
    .Offset(icount, 1) = nm.RefersToRange.Address
    icount = icount + 1
  End With
Next nm
End With
End With

When working with charts, the ChartObjects collection can be used to perform operations on all charts (NB: Charts is the collection of chart sheets, not that of embedded charts.) For example, the following code would work through all charts and move the legend to the bottom (once again, a macro that performed the operation once was recorded, and adapted to work within a loop):

For Each ws In Worksheets
 For Each co In ws.ChartObjects
   co.Activate
    With ActiveChart
      .SetElement (msoElementLegendBottom)
    End With
Next co
Next ws

(Note that, within the code, the charts need to be activated for this operation to work.)

Similarly, one could delete all comments in a workbook, working with the Comments collection.