Excel VBA Tutorial: Learn To Use VBA To Overcome Macro Recorder Limitations

Reposted from
http://www.vbaclass.com/blog/excel-vba-tutorial-learn-to-use-vba-to-overcome-macro-recorder-limitations/

If you record a macro where you sort your data, the macro records the last contiguous row in the data set. Next time you run the macro, if you have more rows of data the recorded macro wont correctly sort to the new last row. Also, anytime you record a macro to sort data, the macro records the name of the sheet where you sorted the data. If your data appears on a sheet with a different name, the macro won’t work.

In our previous blog tutorial I instructed how to dim a variable to capture change (see http://www.vbaclass.com/blog/excel-vba-class-free-vba-tutorial-variables/).

In this lesson I will explain how to use Excel Visual Basic Applications to find the last row of data and concatenate a variable into a recorded macro.

To follow along, download this sample workbook.

Many VBA blogs recommend what I consider an ineffective method of locating the last row of data. Those blogs recommend you use the Goto > Special > Last Cell command.

Goto Command (Keyboard shortcut is Ctrl+G)

Excel Goto Command

Goto Dialog – Special Button

Excel Goto Special

Goto Special Dialog

Goto Special Last Cell

In VBA, the code to achieve that command is

Selection.SpecialCells(xlCellTypeLastCell).Select

However theres a potential problem with using that command. If you have deleted rows or columns, this brings you to the end of the deleted data, not the active data. Once data is placed in a cell the cells status changes from clean to dirty. The Goto > Special > Last Cell command may bring you past the end of the actual data.

If you try Goto > Special > Last cell on the Sheet1 worksheet tab in the workbook you downloaded (link) notice you end up in an empty cell. There used to be data there, but it was deleted. The Goto > Special > Last Cell command isn’t aware of this condition.

A more reliable method is, assuming you know that you have data starting in column A, go to the bottom of the spreadsheet, like cell A1000000. From cell A1000000 press ctrl+up arrow key to jump to the last row of data.

In VBA I would code this as follows:

Range(“A1000000”).Select
Activecell.end(xlup).select
Dim lastrow
Lastrow=activecell.row

In this code, the lastrow variable stores the row of the activecell, the last cell in column A that contains a value.

Now that youve located the last row you can concatenate your lastrow variable into any Excel macro.

In Excel, the concatenation operator is the & symbol. You could use the & concatenation operator in a formula as follows:

Concatenate Operator

In VBA the concatenation operator works in a similar fashion with variables.

In the workbook you’ve downloaded previously (link) Sheet1 has 86 rows of data, while Sheet2 has 89 rows of data. I recorded a macro to sort the data on Sheet1 by State (column C). Here is the code generated by sorting the data.

Sub Macro1()
Range(“C3”).Select ‘I right clicked on cell C3 to access the sort command
‘*******************All of this code is a sort command in VBA************* ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear ‘Problem with the sheet name
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“A1:H86”) ‘Here is where the macro recorder hard-codes row 86
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
‘******************End of the Data Sort command***************************
End Sub

Now, if you try to run the macro on Sheet2, it fails for two reasons. First, the recorded macro hard-codes the name of the sheet three times. Even if we fix the sheet name issue, it will still only sort to row 86, but Sheet2 has 89 rows of data.

To fix this problem we need to substitute the activesheet object for the code that reads Worksheets(“Sheet1”). In the updated code shown below, i have commented out the bad lines and marked where i used the activesheet object. The second issue is the last row as Range(“A1:H86”), that’ where i’ll concatenate the lastrow variable. The edited macro appears below

Sub Macro2()

‘*************Add Code To Find The Last Row*************
Range(“A1000000”).Select
ActiveCell.End(xlUp).Select
Dim Lastrow
Lastrow = ActiveCell.Row
‘*************End of Code To Find The Last Row*************

Range(“C3”).Select

‘*************Commented Out Recorded Macro Code*************
‘ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
‘ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
‘With ActiveWorkbook.Worksheets(“Sheet1”).Sort

‘*************Code using Activesheet Instead*************
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort

‘*************Concatenate Last Row Here*************
‘.SetRange Range(“A1:H86”) ‘I commented out the recorded macro code here
.SetRange Range(“A1:H” & Lastrow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

I hope you will find this free Microsoft Excel VBA Tutorial useful. If you have questions or comments please contact me for assistance. If you have other topics you recommend I should blog about with Excel VBA please feel free to make suggestions.

To contact Excel Class Training about Excel VBA Classes visit our contact page here

Report Hidden Rows With An Excel VBA Macro

Report Hidden Rows With An Excel VBA Macro

find hidden rows with vbaMany Excel users choose to hide a row of data on a worksheet for a variety of reasons. When Excel users collaborate it’s difficult to know that a row has been hidden without careful visual inspection of every row. If a hidden row contains zero values there is no issue. However, if a hidden row contains non-zero values it’s an issue to be concerned about.

Excel has a feature to report that rows may be hidden and then offer to unhide them. However the Excel feature does not tell us which sheets or which rows were hidden or unhidden. To use the Excel feature select the File Tab > Info > Check for Issues > Inspect Document command.

Excel’s Sum function does not ignore hidden rows, which is why Microsoft recommends we use the Subtotal function with the 109 function_number argument instead.

The subtotal function might read:
=SUBTOTAL(109,cell_range)

To help our students produce a report of hidden rows, Excel Class is pleased to share a program that will report if your workbooks have hidden rows. Realize that the speed at which this program operates is proportional to the number of sheets and the number of rows of data per sheet to examine. Even in a large workbook the program should finish in under 5 minutes. However, if the program appears to fail you can press and hold the Esc key on your keyboard to cancel execution.

Simply:

  1. Copy the code in the courier font below into a blank new workbook, from the Option Explicit to the End Sub at the very bottom;
  2. In any Excel workbook: Hold the Alt key and tap the F11 function key to open the VB Editor.
  3. Select the Insert menu > Module command.
  4. Paste the copied code into the empty white code window.
  5. To run the program, close the VB Editor and switch back to Excel.
  6. Select the View tab of the ribbon, then click the Macros button to the far right (or choose the View Macros command).
  7. Select the FindHidden program and click the run button.

The program will prompt you to open a workbook to inspect.

Option Explicit
Dim msg ‘msgbox prompt
Dim ws As Worksheet
Dim wb As Workbook
Dim r As Range
Dim RowCount ‘How many rows based on .xls or .xlsx
Dim RowCounter
Dim Sheet_Counter
Dim HasValues As Boolean
Dim Exists As Boolean
Dim Report As Boolean
Dim LastRow As Long
Dim LastCol As Long

Public Sub FindHidden()
‘Purpose: Ask user to open a workbook to examine;
‘look at every sheet, if any row is hidden, add a report sheet
‘detailing the sheet name, row(s) hidden, and if they contain
‘non zero values

‘Error Handling
‘On Error GoTo errorhandler

‘Speed It Up
With Application
.ScreenUpdating = False
.MultiThreadedCalculation.Enabled = True
End With

‘Step 10: Ask user to open a workbook to examine
msg = “Please choose a workbook to examine”
MsgBox prompt:=msg, Buttons:=vbExclamation + vbOKOnly, Title:=”Select a workbook”

With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With

Set wb = ActiveWorkbook
wb.Activate

‘Step 20: Determine file format for row count
If Len(wb.Name) – InStr(1, wb.Name, “.”) = 3 Then
RowCount = 65535
Else
RowCount = 1048576
End If

‘Step 30: Loop through sheets
For Each ws In wb.Sheets

‘Select the sheet if not hidden
If ws.Visible = True Then
ws.Select
Else
GoTo Skip_This_Sheet
End If

‘Step 35: Find the last row and last column
Call Find_End_Of_Data

‘Step 40: On any sheet if a row is hidden report it

For RowCounter = 1 To LastRow
If Rows(RowCounter).Height = 0 Then

‘Set a report variable to report hidden row
Report = True

‘Reset hasvalues variable
HasValues = False

‘Determine if hidden row contains a non-zero value
If Application.WorksheetFunction.Count(Rows(RowCounter)) > 0 Then
HasValues = True
End If

‘Step 50: Make a report
‘Find if HiddenRowReport sheet exists

Exists = False

For Sheet_Counter = 1 To wb.Sheets.Count
If wb.Sheets(Sheet_Counter).Name = “HiddenRowReport” Then
Exists = True
Exit For
End If
Next Sheet_Counter

‘If no HiddenRowReport sheet, add it and name it
If Exists = False Then
wb.Activate
wb.Sheets.Add before:=Sheets(1)
wb.Sheets(1).Name = “HiddenRowReport”
wb.Sheets(“HiddenRowReport”).Range(“A1”) = “Sheet Name”
wb.Sheets(“HiddenRowReport”).Range(“B1”) = “Row”
wb.Sheets(“HiddenRowReport”).Range(“C1”) = “Contains Non-Zero Values?”
End If

‘Find last row, offset and report it
wb.Sheets(“HiddenRowReport”).Select
‘Range(“A” & rowcount).End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell = ws.Name
‘Autofit column
ActiveCell.EntireColumn.AutoFit
ActiveCell.Offset(0, 1) = RowCounter
‘Autofit column
ActiveCell.Offset(0, 1).EntireColumn.AutoFit
ActiveCell.Offset(0, 2) = HasValues
‘Autofit column
ActiveCell.Offset(0, 2).EntireColumn.AutoFit
End If

‘Go back to sheet being searched for hidden rows
ws.Select

Next RowCounter
Skip_This_Sheet:
Next ws

‘Step 60: Message if a report was created
If Report = True Then
MsgBox “There is a report of hidden rows”
wb.Sheets(“HiddenRowReport”).Select
Else
MsgBox “No Hidden Rows Were Found”
End If

Exit Sub

errorhandler:

If Err.Number <> 0 Then
MsgBox “There was a problem”
ActiveWorkbook.BuiltinDocumentProperties(“Comments”) = Err.Number & ” ” & Err.Description
End If

End Sub

Public Sub Find_End_Of_Data()
LastRow = Range(“A1”).SpecialCells(xlCellTypeLastCell).Row + 1
LastCol = Range(“A1”).SpecialCells(xlCellTypeLastCell).Column + 1
End Sub

Two Criteria Lookups

Two Criteria Lookups

In the process of teaching Excel over the past 20 years I have found that most students are often shocked and unaware that Vlookup and Index/Match can only locate the first item in a list. They find this unacceptable and I would tend to agree. When a value repeats in many rows, Microsoft suggest you use the upcoming Xlookup function to locate what is known as the ‘nth’ occurrence, but most Office365 installations will not offer that feature before July 2020 – at the earliest! For a long time there has been a workaround that does not require Xlookup, which despite Microsoft’s best intentions likely is not necessary!


In the “real world” a list typically includes items that repeat. Consider an invoice, it will typically include many line items for a single invoice number. Where one column may contain a repeating invoice number, the line items in rows could be for the same product with different size or color options. I recently came across several clients who require this functionality. While the topic of this blog post is beyond most students needs, I include it here because the class notes have finite space. This post assumes that students have taken the advanced Excel class and already understand how to work with Index and Match functions.


To follow along with the example, I recommend you download this sample workbook: Index Match Two Criteria


In this example notice that column A contains invoice numbers that repeat (orange cells – first criteria), and column B shows a unique price (second criteria). The goal is to locate the correct quantity for each invoiced item based on its price.


two criteria lookups

The formula required in cell C2 is:


=INDEX(DataToSearch!$F$1:$F$5061,MATCH(1,(InvoiceTwoCriteria!A2=DataToSearch!$B$1:$B$5061)*(InvoiceTwoCriteria!B2=DataToSearch!$E$1:$E$5061),0))


Given the formula’s complexity, I also recommend simplifying the readability by using named ranges, which could read:


=INDEX(Quantity,MATCH(1,(InvoiceTwoCriteria!A2=InvoiceNumber)*(InvoiceTwoCriteria!B2=Price),0))


After copying one of these formulas, take a moment to study and read it but… do not simply press the enter key, this formula requires the use of an array – hold both Ctrl+Shift while pressing the Enter key. As you study the format the formula, pay special attention to the placement of the open and close parentheses!


Because it is any array formula, we cannot reference an entire column, it requires a fixed range with absolute references (recall that named ranges are also absolute references). Also bear in mind that array formulas have limitations that can be found at this URL: https://docs.microsoft.com/en-us/office/troubleshoot/excel/arrays-limitations


The way to read and interpret the formula is:


Index (return) a value from column F. The Match lookup value of 1 creates a matrix of 1/0 values. The Match lookup array for criteria one reads: Look for the value in cell A2 (invoice #1) by searching cells B1 to B5061. The multiplication symbol before the second lookup array is treated like an AND operator. The second lookup array reads: find the price in cell B2 (34.95) by searching cells E1 to E5061. When the matrix returns a 1 match (true) for the invoice in column B, and a 1 match (true) for the price in column e, then return the corresponding value from column F (price). And if some of you are wondering, yes, this can be extended to include three criteria if needed!


If you would like to practice creating these types of formulas, download the following workbook and enable macros. Note that the submit answer feature is not expecting you use named ranges, though not incorrect the program is looking for range references, not named ranges! Index Match Two Criteria Lab


SumProduct Function Applications

SumProduct Function Applications

When teaching Excel classes students occasionally ask about the SumProduct function. There are many aspects of SumProduct that mirror the SumIfs functions capabilities but SumProduct can produce different answers when combined with the unary operator. This tutorial is not meant to be a thorough discussion of SumProduct but is meant to provide an addendum to class notes.

Click Here To Download A Sample Workbook

I have provided a picture of the worksheet for those who cannot open Excel workbooks:
Sumproduct

SumProduct – Example 1

The SumProduct function multiplies one array by another array. An array is typically a range of cells in a single column. In the first example imagine that our goal is to calculate the total gross pay for all employees in all divisions and departments. The ‘old fashioned’ Excel approach is to write a formula to calculate hours * rate for each employee (cells G2:G21) then Sum those values. In this Level 1 example, we would need to generate 20 formulas in 20 cells to find the answer, where a SumProduct function calculates the result with less effort.

With SumProduct
=SUMPRODUCT(E:E,F:F)

Without SumProduct
=(E2*F2)+(E3*F3)+(E4*F4)+(E5*F5)+(E6*F6)+(E7*F7)+(E8*F8)+(E9*F9)+(E10*F10)+(E11*F11)+(E12*F12)+(E13*F13)+(E14*F14)+(E15*F15)+(E16*F16)+(E17*F17)+(E18*F18)+(E19*F19)+(E20*F20)+(E21*F21)

SumProduct – Example 2

About fifteen years ago Excel did not offer a SumIfs function and SumProduct was an alternative to using an array formula. Like the SumIfs function, SumProduct can work conditionally using criteria. Where SumIfs only allows AND criteria, SumProduct also supports OR criteria (but this generally double counts and generates incorrect results). Several studies have shown that SumIfs functions calculate significantly faster than SumProduct functions.

In example 2 imagine that we want to only sum the gross pay if the division equals Maine and the department equals Sales. In this example the asterisk (*) between Array1 and Array2 operates as an AND operator. The formula reads, If column C contains “Maine” AND Column D contains “Sales”, then Sum the value in column E times column F. If we use a plus (+) instead of an asterisk, this represents an OR criteria but double counts the products of Maine and Sales.

With SumProduct
=SUMPRODUCT((C:C=”Maine”)*(D:D=”Sales”),E:E,F:F)

With SumIfs
=SUMIFS(G:G,C:C,”Maine”,D:D,”Sales”)

Array Formula Sum If
{=SUM(IF((C:C=”Maine”)*(D:D=”Sales”),E:E*F:F))}

SumProduct – Example 3

At times we see a double hyphen (- -) in a formula. In Excel the double hyphen is known as a unary operator. The purpose of the unary operator is to force text values to 0/1 values where zero means false and one means true.

SumProduct Formula
=SUMPRODUCT(–(C:C=”Maine”)*–(D:D=”Sales”),E:E,F:F)

Like example 2 except now the – operator converts values where a cell contains Maine or Sales to 0 for false and 1 for true, then uses the corresponding 1 values to determine which values in columns E and F are multiplied and summed.

SumProduct is also used to compare values in two columns to count how many words or numbers match (typically columns in separate workbooks). In general, do not sort the data, this technique compares corresponding values within the same row.

SumProduct Formula
=SUMPRODUCT(–($C:$C=”Maine”),–($I:$I=”Maine”))

This formula asks Excel to compare the values in column C to the values in column I for the word Maine. The unary operator converts results to 0 for false or 1 for true, and then the SumProduct function sums the values for 1/true.

SumProduct can also compare the value in two columns to count identical values

SumProduct Formula
=SUMPRODUCT(–(E:E=40),–(K:K=40))

Like the previous example, this formula asks SumProduct to sum the 1 values if the numbers in column E and K are identical and equal 40.

SumProduct – Example 4

The most valuable use of the SumProduct is to calculate a weighted average (this question only occurs about once every ten years). The straight average sums the values and divides by the count. A weighted average considers some values have a higher weight. In this example imagine the weighted average is based on the count of Divisions, where there are six Connecticut values, four Maine values, and five New Hampshire and five Vermont values.

In this example the SumProduct weights the average gross pay based on the count of divisions.

SumProduct Formula – Longhand notation for weighted average
=SUMPRODUCT({780,780,860,504,589,504,350,213,860,589,472.15,176,860,880,443.75,880,472.15,780,840,350},{4,6,4,6,5,6,5,6,5,6,5,5,5,5,5,5,6,4,4,5})/SUM(H2:H21)

SumProduct Formula – Shorthand notation for weighted average
=SUMPRODUCT(E2:E21,F2:F21,H2:H21)/SUM(H2:H21)

Data Tables And Interactive Charts

The Excel Class blog provides an opportunity to supplement the material we teach in our class. This tutorial assumes our readers are familiar with named ranges. (See: Advanced Excel Class)

Excel’s data table feature is a what-if analysis tool that allows users to create tables (rows/columns) of results based on a one or two variable formula. Data tables are useful for simple calculations like future value tables or mortgage payment tables to examine different scenarios based on varying input assumptions. Our previous post examined array formulas with vlookup (Vlookup Array Formula Tutorial) and data tables are an extension of the discussion of array formulas.

Download the finished workbook here

In the following example we use a data table to show rates of return for an investment and then use the data table as the basis of an interactive chart using an offset function in an array formula.

The formula for Future Value is:

FV = PV * (1 + i) ^ t

Where:

FV means future value

PV means present value

i is the interest rate

t is time

To Create A Data Table

1. Set up variable inputs, optionally (recommended) name the cells to use in the formula;
Data Table Step 1

2. To the right of the PV label, in cell B1, type a number like 1. In this example 1 means $1, our present value.

3. In a separate cell, create the formula that references the named ranges. In this example the formula appears in cell C4 which will become the upper left corner of the data table;
Data Table Step 2

4. To the right and below the formula, add variable inputs;
Data Table Step 3

5. Select the range to use for the data table, including the formula in the upper left corner;
In this example select cells C4:K24;

6. On the ribbon, select the Data tab > What If Analysis > Data Table command;
Select a row input cell (refer to a cell with a name); In this example, cell B2 is the row input (interest).
Select a column input cell (refer to a cell with a name); In this example, cell B3 is the column input (time).
Click the OK button to generate the data table.
Data Table Step 5

Note: The formulas produced by the data table in cells D5:K24 are array formulas. Be aware that array formulas remove the ability to insert or delete a row or a column inside the data table.

To Create An Interactive Chart Based On The Data Table

1. In cell C26, type a 0 (zero);

2. Select (highlight) cells D26:K26;

3. Type an = (equals) sign;

Enter the following formula:
=OFFSET(D5:K5,C26,0)

To create an array formula, hold the Ctrl+Shift keys and press the Enter key.

The offset function offsets the range D5:K5 by the value in cell C26 (zero rows)
Interactive Chart Array Formula

4. To add an interactive spinner control, show the Developer tab of the ribbon. To show the Developer tab, right click any tab of the ribbon, choose the Customize Ribbon… command. In the Customize Excel Options dialog, check the Developer tab.

5. On the Developer tab of the ribbon, select the Insert > Form Controls > Spinner command.
Developer Tab Insert Spinner Command

6. Click and drag a small spinner control in a cell to the left of cell C26.

7. Right click the spinner control, left click the Format Control command.

In the Format Control dialog:

Set the minimum value to 0

Set the maximum value to 20 (the number of years in column C)

Set the link cell to cell C26
Developer Tab Insert Spinner Command

8. The final step is to create the interactive chart.

Select cells D26:K26

On the ribbon, select the Insert tab > 2D Line Chart command
Insert 2D Line Chart

9. To see the chart become interactive, click the up arrow on the spinner control to increase the value in cell C26.

Note that as the value increases the offset function plots different rows of the data table in the chart.

Final note: I am obligated to give credit to Tushar Mehta for his excellent charting tutorials. Learn more about Tushar’s chart tutorials here: http://tushar-mehta.com/excel/charts/ In fact, I always encourage Excel students to study Tushar’s tutorials on: 3D Surface Charts, Normal Distribution (Bell Curves), Waterfall (Cash flows), and Values That Differ By Magnitude.

Excel Tutorial – Vlookup Multiple Columns To The Right With An Array Formula

In the process of teaching Excel classes, many students ask:

How can I write a single vlookup function that returns multiple values to the right, instead of a single value, which causes me to write many vlookup functions in many columns.

This tutorial does not teach vlookup, that feature is taught in the Introduction and Advanced classes. Here we focus on vlookup and array formulas. The purpose of this tutorial is to supplement the class notes.

When performing calculations in Excel, typically a single cell contains a formula with functions that return a single value. An array formula is different in that it will typically include many cells (rows or columns) and return many values. Where I would teach students to press the enter key to commit an edit, to create an array formula we are required to hold Ctrl+Shift+Enter.
To follow along with this example, please download a workbook with this link:

Vlookup Array Workbook

Though not required, I suggest when you open this workbook, click the Enable macros command which will create two windows and tile them side by side.
In this example, the idea is to write a single vlookup function on the VlookupOrders sheet. The single vlookup will find the order number on the Data sheet and return three values, the Company Name, the Country and the Sale.

Example Of Data In Vlookup Array Workbook

vlookup array formula purpose

1) On the VlookupOrders worksheet tab, select/highlight cells C2:E2
2) Create the following formula:
=VLOOKUP(B2,Data!B:F,{3,2,5},0)

vlookup array formula purpose

Note: For the third argument, the col_index_num, we are allowed to use as many values as needed. In this example I am trying to illustrate that as much as I can return three columns to three cells — in any sequence — we can choose any number of columns as needed. However, to vlookup four columns, in step 1, select/highlight four cells. Also note that we cannot simply add parentheses around the values of the col_index_num, use curly braces {} (squiggly brackets – typically found to the right of the letter P on your keyboard).

3) To convert this to an array formula, hold the Ctrl+Shift keys and press the Enter key. The array formula adds {} (curly braces) around the vlookup formula.

A final word of warning: Array formulas have drawbacks. One drawback is that array formulas recalculate a bit slowly over thousands of rows. A bigger drawback is that we have lost the ability to insert a new column within the arrayed formula range. To verify the loss of the ability to insert a column inside the array, on the VlookupOrders sheet, right click column D and left click the Insert command.

Excel Class VBA Macro Adds IfError Functions To Every Formula

Excel Class VBA Macro Adds IfError Functions To Every Formula

Excel VBA MacroToday a student asked Excel Class if there was a macro to automatically add an IfError function to every formula on every sheet in a selected workbook, assuming there was not already an IfError function used.

On short notice the Excel Class instructor provided the following code which we wanted to share with our readers.

Remember that macros have no Undo command. Once you run this VBA macro the only undo is to close the file without saving changes. The macro leaves a selected workbook open so you can review the changes before deciding to save or not.

To use this code, press the Alt+F11 key to open the VB Editor.
Select the Insert menu > Module command.
Then paste the following code into the empty window:
To run the program, press the F5 key, or from Excel select the Ribbon View Tab > Macros button and run the AddIfError macro.

Public Sub AddIffError()
‘purpose: IF there is a formula add iferror
‘if there’s an iferror already, ignore the formula
‘process all sheets in wb

‘Handle Errors
On Error GoTo Errorhandler

‘Step 10: Ask user to open a wb
MsgBox “Please click OK to select a workbook to process”

‘Step20: Prompt user to open wb to process
With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With

‘Step30: On every sheet add iferror
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets

If ws.Visible = True Then ws.Select

‘Select all cells
Range(“A1”).Select
ActiveCell.CurrentRegion.Select

Dim r As Range
For Each r In Selection
If Left(r.Formula, 1) = “=” Then
If Left(r.Formula, 8) = “=IFERROR” Then
‘Do Nothing
Else
Dim myformula
myformula = Right(r.Formula, Len(r.Formula) – 1)
r = “=IfError(” & myformula & “,0)”
End If
End If
Next r

Next ws

Exit Sub
Errorhandler:
If Err.Number <> 0 Then
MsgBox “Something went wrong”
End If

End Sub

If you want to learn more about Excel VBA programming check out our 2-day Excel VBA course in New York City.

PEMDAS and the Mathematical Order of Operations with Microsoft Excel

Microsoft Excel works similarly to using a calculator but there are important differences. On a calculator to perform a calculation you might type numbers, then math symbols like plus (+) or minus (-) and then you would press the equal key to complete your calculation.In Excel we don’t end with the equal sign, we begin with the equal sign. Additionally, you need to be aware of the mathematical order of operations, commonly associated with the acronym PEMDAS (Please Excuse My Dear Aunt Sally).

What the order of operations theorem states is that parenthesis () overrides exponentiation (^) which has a higher precedence than multiplication (*) or division (/) and that multiplication and division have a higher order of precedence than addition (+) and subtraction (-). For those of us who don’t have degrees in rocket science, exponentiation is the term used to say raise one number to the power of another number, like 28 which would equal 256 (2*2 eight times).

To see how PEMDAS applies to real world examples let’s look at two formulas:

Example of PEMDAS:

= 2 + 3 * 4

Most people would read that formula from left to right and calculate 2+3 equals 5 and 5 times 4 equals 20, and it wouldn’t be entirely wrong. However, because multiplication has a higher order of precedence than addition, Excel will first calculate 3*4 equals 12 and 12+2 equals 14.

Therefore, if you want the formula to produce 20 instead of 14 you need to override the order of operations by grouping the 2+3 terms inside parentheses as in:

= (2 + 3) * 4

In Excel formulas it is common to see many many parentheses. That is because most functions (predefined formulas) use parentheses to enclose the arguments (inputs) and those arguments may nest other functions in parentheses and each function may require parenthesis around the inputs. For example,

=IF(D2=”R”,INDEX($K$3:$K$8,MATCH(B2,$J$3:$J$8,FALSE)*C2),IF(D2=”C”,INDEX($H$3:$H$8,MATCH(B2,$G$3:$G$8,FALSE)*C2),IF(D2=”H”,INDEX($N$3:$N$8,MATCH(B2,$M$3:$M$8,FALSE)*C2))))

What is important to realize about using parentheses is that if you use () symbols you have to be careful to balance every open parentheses with a close parenthesis or the formula will produce an error. If you’re unsure of why this formula uses $ signs in the cell references, please refer back to our blog about absolute versus relative cell references.

To learn more about our Excel Training in New York City contact us here.