Select Feeds in excel

RSS feeds on this page are compiled from some of the Excel sites that I use ferquently to get ideas or solutions to my Excel problems or those sites that I visit to expand my knowledge into areas that are new to me such as Python for Excel. This may not be a static site and likely to grow as I find more useful sites in the listed topics.

My Online Training Hub - Upgrade Your Skills In Excel, Word and Outlook

Dynamic Dependent Data Validation (Thu, 26 Aug 2021)
Before we had the luxury of dynamic array functions, creating dependent data validation lists typically required using multiple tables and named ranges, as shown here. Setting them up was laborious, however now that we have dynamic array functions, creating dynamic dependent data validation lists is much easier. Note: this technique requires a Microsoft 365 license […] The post Dynamic Dependent Data Validation appeared first on My Online Training Hub.
>> Read more

IFERROR in Power Query Using TRY OTHERWISE (Thu, 19 Aug 2021)
In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error. Power Query doesn't have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it's called try […] The post IFERROR in Power Query Using TRY OTHERWISE appeared first on My Online Training Hub.
>> Read more

pythonpip.com

Read CSV file Using Numpy (Tue, 21 Sep 2021)
I’ll show you how to read a csv file and convert it into a NumPy array in this post. We’ll write NumPy data to a CSV file. The CSV file will then be read and transformed to a Numpy array. I’ll show you how to read a CSV file using both numpy.loadtxt() and numpy.genfromtxt() methods. […] The post Read CSV file Using Numpy appeared first on pythonpip.com.
>> Read more

How To Delete File If Exists In Python (Mon, 20 Sep 2021)
In this article, we’ll learn here how to delete a file if exist using python. we’ll look at how to use os.remove() and os.ulink to remove a file if it’s the only one that exists. In Python, the OS module contains functions for interfacing with the operating system. We’ll check the following conditions before the […] The post How To Delete File If Exists In Python appeared first on pythonpip.com.
>> Read more

Compucademy

The Collatz Conjecture with Python (Mon, 30 Aug 2021)
The Collatz Conjecture is an unsolved problem in Mathematics which lends itself nicely to exploration with Python. The idea is simple: Pick a positive whole number. If it’s odd, multiply it by 3 and add 1. If it’s even, divide it by 2. Now you have a new number. Apply the same rules to the […]
>> Read more

Number Placement Puzzle with Python (Fri, 23 Jul 2021)
This post is about a fun puzzle called the number placement puzzle. I have provided an interactive console program that lets you explore it. I have also provided a couple of algorithms for solving the puzzle. Interactive Number Placement Puzzle Put the numbers provided in the correct positions to make all the inequalities true. Brute […]
>> Read more

P3 Adaptive

Blog - Excel Dashboards VBA

Find And Replace in Excel - Hidden Issue (Fri, 02 Jul 2021)
The Replace function in Excel is fast - really fast and it works very nicely on a range of data to find and replace data. However, the replace function has a hidden bug and this article explores the problem with an example and a video.
>> Read more

Power BI Dashboard Examples (Tue, 01 Jun 2021)
Anyone attending any of the CPA Australia Power BI courses that I run will know that I place a lot of emphasis on the final product in Power BI. A well designed dashboard simplifies the data and presents it in a logical manner. The dashboard spins by department.
>> Read more

Comments on: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool

By: Matthew (Mon, 05 Jul 2021)
I would actually really like to advertise this on https://www.techbusinessnews.com.au if you don't mind. Could you send me over further details.
>> Read more

By: George White (Fri, 11 Jun 2021)
Hi Jon, my database has 5,000 records. Power Query brings in a sample of 1,000 records. I do the transformations. Everything is perfect. How do I get Power Query to do it's magic on all 5,000 records? I'm just missing something.
>> Read more

OLAP.com

Take a Moment to Reconsider OLAP (Tue, 25 Feb 2020)
We are loving this article by Neil Raden on diginomica.com, I’m Feeling Sentimental about OLAP and You Should Too. In it Raden makes the case that the OLAP tech that existed in the 90’s plus the new strides that have been made in recent years as far as scale, speed, CPU power, and functionality, makes […] The post Take a Moment to Reconsider OLAP appeared first on OLAP.com.
>> Read more

Business Intelligence Versus Predictive Analytics (Mon, 24 Feb 2020)
Business Intelligence is needed to know what really happened in the past, but you also need predictive analytics to optimize your resources to make better decisions and take actions for the future. The post Business Intelligence Versus Predictive Analytics appeared first on OLAP.com.
>> Read more

Peltier Tech

Prevent Overlapping Data Labels in Excel Charts (Mon, 24 May 2021)
This article offers a set of VBA routines to remove overlapping of a set of vertically aligned data labels in an Excel chart. The post Prevent Overlapping Data Labels in Excel Charts appeared first on Peltier Tech.
>> Read more

Slope Chart with Data Labels (Thu, 06 May 2021)
This tutorial shows how to construct a slope chart in Excel and provides a simple VBA procedure to apply and format data labels quickly. The post Slope Chart with Data Labels appeared first on Peltier Tech.
>> Read more

Dynamic Charts Using Dynamic Arrays (Thu, 25 Mar 2021)
With Dynamic Arrays, it is easy to define variable sized calculations. It is also easier to define Names for use in dynamic charts. The post Dynamic Charts Using Dynamic Arrays appeared first on Peltier Tech.
>> Read more

Install an Excel Add-In in Excel for Mac (Tue, 16 Mar 2021)
Add-ins provide useful functionality to Excel. Here is how to reliably install an add-in in Mac for Excel, so it is always available when needed. The post Install an Excel Add-In in Excel for Mac appeared first on Peltier Tech.
>> Read more

Dynamic Array Histogram (Mon, 08 Mar 2021)
Dynamic Arrays make it easier than ever to generate a data range for a histogram, including a normal curve overlay. LET and LAMBDA functions make it even easier and more flexible. The post Dynamic Array Histogram appeared first on Peltier Tech.
>> Read more

VBA Test for New Excel Functions (Wed, 10 Feb 2021)
Excel has awesome new Dynamic Array, LET, and LAMBDA functions. Here's a VBA test for whether a user's installation supports these functions. The post VBA Test for New Excel Functions appeared first on Peltier Tech.
>> Read more

Convert Line Chart to Step Chart with VBA (Mon, 21 Dec 2020)
This tutorial shows how to write a simple VBA procedure that will convert a line chart to a step chart, without the tedious data arrangement required by the manual protocol. The post Convert Line Chart to Step Chart with VBA appeared first on Peltier Tech.
>> Read more

Calculate Nice Axis Scales with LET and LAMBDA (Tue, 08 Dec 2020)
Here's how to use new Excel features LET and LAMBDA plus Dynamic Arrays to calculate nice axis scales, much better than old-school formulas and VBA. The post Calculate Nice Axis Scales with LET and LAMBDA appeared first on Peltier Tech.
>> Read more

Charts for Excel Upgrade (Mon, 30 Nov 2020)
I'm pleased to announce a major upgrade, to Peltier Tech Charts for Excel 4.0. The previous major upgrade to 3.0 was over five years ago. This upgrade includes new Layer and Step charts and a new Waterfall variation, plus other new features and enhancements. The post Charts for Excel Upgrade appeared first on Peltier Tech.
>> Read more

Conditional Donut Chart (Wed, 21 Oct 2020)
Create a donut chart, plotting extra formatted segments and using formulas to show and hide these segments, to achieve the conditional formatting appearance. The post Conditional Donut Chart appeared first on Peltier Tech.
>> Read more

The VBA Tutorials Blog

How to Filter a Column with VBA AutoFilter (Fri, 17 Sep 2021)
Filtering hides rows from a table. Filtering data is a foundational component in understanding large datasets. Some applications only need filtering on one column while others may be more complex. This article will explore filtering on a single column, but we have helpful tutorials on [applying multiple filters with VBA AutoFilter][how to apply multiple filters] and sorting columns using VBA. The Filter Range A Word on Terminology The Field Parameter Defining the Filter Criteria Filter For a String Filter For Multiple Strings Filter For Values Special Filters Leverage User Input Clearing Filters The Filter Range The first thing to ask yourself when filtering is “what range should I filter?” The .AutoFilter method filters only one column at a time. To filter multiple columns requires an iteration loop or the use of the .AdvancedFilter method, which we touched on in our article about filtering unique values. The .AutoFilter method operates on Range objects, which both Range and Columns objects satisfy. Since we want to filter an entire column, we can use either of these lines: Columns(1).AutoFilter 'to filter Column 1 (same as A) Range("A:A").AutoFilter 'to filter Column A (same as 1) If you want to filter a defined range rather than an entire column, specify so in your initial Range object: Range("A1:A300").AutoFilter. This allows you to filter up to a certain row, leaving other rows alone. A Word on Terminology Excel filters apply across entire rows! You cannot filter only some columns, because filtering hides the entire row. Filtering some columns but not others would break the integrity of the data, erasing the relational connections between columns. To explain what we mean, try visualizing this scenario. Let’s say we have First Names in column A and Last Names in column B. It wouldn’t make sense to filter only rows in A because then first and last names would no longer be matched appropriately. Therefore, we usually speak of filtering ON a column, meaning we can look for all first names that start with J, but we cannot filter only Column A without hiding values in B. The phrase filter on [target] implies we use the target as the criteria, but we still hide rows across the entire sheet. The Field Parameter The .AutoFilter method has a parameter Field. This comes in handy when your Range spans multiple columns. For example, Range("B:E").AutoFilter Field:=3 'filters the third column, Column D Range("B:E").AutoFilter Field:=1 'filters the first column, Column B When the Range object consists of only one column, such as Columns(3).AutoFilter, the Field parameter is still required - you’d just set it to 1. Defining the Filter Criteria Filters are based on two Criteria parameters and a set of special Excel operators, for a total of 3 parameters. Only a single criteria is required to filter, but all three can be used to filter a single column. You can practice using this CSV, which contains this dataset:

Filter For a String Let’s say you want to find all orders from the North region. We can filter the Region column like this: Range("E:E").AutoFilter Field:=1, Criteria1:="North" which yields

Note the filter is applied only to Column E, as indicated by the filter icon at the top of column E. Filter For Multiple Strings What if we wanted all data from the North and the West regions? This is where Criteria2 and Operator comes in. The Operator parameter accepts, among others, xlAnd and xlOr. In mathematics terms, AND intersects and OR unions. In non-technical terms, AND requires both criteria to be true, while OR requires only one (but at least one). So, to filter for all North and all West records, we could use this code: Range("E:E").AutoFilter Field:=1, Criteria1:="North", Criteria2:="West", Operator:=xlOr

Filtered for North or West You can even use wildcards (the * symbol) in strings. For example, if you wanted to find all first names that started with F or G, you could use Range("B:B").AutoFilter Field:=1, Criteria1:="F*", Criteria2:="G*", Operator:=xlOr If you wanted cities that started with a C and end with an S, you could use xlAnd to force both conditions to be true, like we do in this example: Range("C:C").AutoFilter Field:=1, Criteria1:="C*", Criteria2:="*S", Operator:=xlAnd This would yield cities like Columbus and Colorado Springs. Except not in this dataset, because Colorado was not yet in existence when the Declaration of Independence was signed, and this list is mostly signatories of that document. You may have noticed Sun Tzu (who didn’t live in Ohio or sign the Declaration of Independence) appearing 4 times with exactly the same data. Duplicates like this are common issues in data science, so we made a guide showing you how to filter duplicates (using unique records), too. Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Filter For Values Business revolves around numbers, so surely we can filter on more than just strings? That’s right, we can also filter on values, like the Amount values in Column G. To find all orders over 500 - perhaps for expedited shipping - use the Criteria1 parameter and set it equal to ">500": Range("G:G").AutoFilter Field:=1, Criteria1:=">500" The Criteria2 and Operator pramaters are not necessary since we are filtering the Amount column on only one criterion. Special Filters We’ve talked about xlAnd and xlOr but the Operator parameter also enables some special filters, like xlTop10Percent. This will display the top 10% of the values in a field, so in a dataset of 50, you’ll get 5 and in a dataset of 1500, you’ll get 150. Although the name of the operator is xlTop10Percent, you can adjust the actual percentage using Criteria1: 'filter on Amount column (G) Range("G:G").AutoFilter Field:=1, Criteria1:=5, Operator:=xlTop10Percent 'top 5 percent Range("G:G").AutoFilter Field:=1, Criteria1:=25, Operator:=xlTop10Percent 'top 25 percent The Operator parameter also has an xlTop10Items option that restricts the response to X number of items, where X is defined using the Criteria1 parameter. 'filter on Amount column (G) Range("G:G").AutoFilter Field:=1, Criteria1:=5, Operator:=xlTop10Items 'top 5 items Range("G:G").AutoFilter Field:=1, Criteria1:=10, Operator:=xlTop10Items 'top 10 items Leverage User Input One neat thing about filtering is your filters don’t need to be hardwired. You can ask the user for input since the Criteria parameters accept variables. This example simply asks for a region name in a message box, but with a bit of work on userforms you could design a nice dashboard with many business-specific options and constraints. Sub filterOnRegion() Dim regionName As String regionName = InputBox("Enter the Region name") Range("E:E").AutoFilter Field:=1, Criteria1:=regionName End Sub Clearing Filters Filters cannot be applied independently using .AutoFilter. Multiple criteria can be applied and on different columns, but each new line of AutoFilter intersects the results - that is, each new line will only “see” the results of the previous filter. When you’re testing, this may cause runtime errors and plenty of frustration. Keep this in your Immediate Window (shortcut Ctrl+G) to quickly reset the filters while testing your code: ActiveSheet.AutoFilterMode = False If you need multiple filters, check out our guide on how to properly apply multiple filters. As you can see, filtering a column in VBA is pretty easy, and you can even add some additional criteria without issue. You can filter on both numeric amounts and strings, and you can apply mathematical relations (like greater than). Excel also provides some built-in dynamic filtering, like xlTop10Percent, depending on your dataset Just keep in mind that AutoFilter only applies to one column at a time. You can filter multiple columns using it, but it requires multiple lines of code. For more VBA tutorials like this, subscribe using the form below.
>> Read more

VBA Filter Unique Values with AdvancedFilter (Fri, 06 Aug 2021)
When working with large datasets, there’s a good chance that one day you’ll need to find unique values, especially unique strings. It might not matter how many times a value or string repeats, only that it exists in the dataset. When you have 50,000 records with potentially hundreds of unique strings, data cleansing becomes easier when these unique records are collected in one place. The VBA .AdvancedFilter method is a very powerful tool for this. The method can retain old data, take worksheet-based criteria, and, most importantly for this tutorial, find unique values. The criteria portion itself is a powerful feature, but we’ll dive into that in another tutorial. Today, we’re going to show you how to collect unique values in one place. Setting the Range Unique Values Across Multiple Columns Output in the Same Place or a New One? The Action Parameter CopyToRange Parameter Finding Unique Values Is the Original Unique? Full Code Before we show you fully functional code, we’re going to step through the parameters of AdvancedFilter so you can make sure you’re using it correctly. Setting the Range The AdvancedFilter method operates on a Range object, just as any filter would. Set the range normally, but note that VBA always treats the first row as a row containing headers. If your data has no headers - that is, your first cell is a regular value - the first value may appear twice in the uniques list. This is easy to recognize and adjust, but it is a bit inconvenient if you’re not expecting it. Unfortunately, Microsoft does not give us a [SourceHasHeaders] type optional parameter here. Often we want to find uniques in just one column. If the values we want to filter are in Column C, then we’d operate on C like so Range("C:C").AdvancedFilter 'Option 1 - using a general Range object or Columns(3).AdvancedFilter 'Option 2 - using a column Range object This is just the non-functional base code - we’ll show you how to get it working properly soon. Also, notice the range can be a single Column from the Columns collection, or it can be a range object. .AdvancedFilter can operate on more than one column and its row range can be limited, as well, if you only want to filter a subset of the data. Unique Values Across Multiple Columns Filtering for unique values behaves properly across columns, too. For example, if Column A contains first names and Column B contains last names, we could use .AdvancedFilter to find unique First Name + Last Name combinations. This can be extended to any number of columns. Just use Range("A:B").AdvancedFilter to expand the filter target to both columns. Output in the Same Place or a New One? VBA AdvancedFilter can either filter in place, which hides any records that do not match the criteria, or it can output the results to a new location. While filtering in place has its uses, I recommend copying to a new location whenever you have columns on which you do not filter. This is good way to preserve your original data integrity. The Action Parameter The Action parameter tells .AdvancedFilter to output results in the same place as the original (xlFilterInPlace) or copy results to a new location (xlFilterCopy) Beware of xlFilterInPlace! This will filter the entire sheet and hide rows that do not match your filtering condition on your specified column. This can cause users to panic because they don’t realize the data still exists. All you need to do to unhide the filtered rows is select the “Clear” button on the Excel Data tab to reshow the hidden rows. Alternatively, you can run this code to do it programmatically: ActiveSheet.ShowAllData CopyToRange Parameter If you choose xlFilterCopy as your Action parameter, you need to specify where you want to place the copy. The CopyToRange parameter lets you do this. All you need to do is specify a single cell or you can specify an entire column. If the output range is too small to contain all the results, VBA just overflows the area. This means you can’t limit the output, so choose a column without values or with values you don’t mind overwriting. There’s another quirk of xlFilterCopy to be aware of, too. If two columns in the data you’re trying to filter have the same header title, the xlFilterCopy feature may copy the first column with that name twice to your destination column (CopyToRange). Finding Unique Values The last piece of the puzzle is the Boolean parameter Unique, which takes only TRUE or FALSE. To find unique values, set this to TRUE. To find each unique City in the dataset shown below and place the list of unique cities in Column H, we can use this code Range("C:C").AdvancedFilter xlFilterCopy, , Range("H1:H1"), True Don’t miss the blank CriteriaRange parameter. This is our output:

VBA AdvancedFilter Unique Values from Column A to H

Original Data in Column A and the Filtered Data in Column H Once you have the list of unique cities, you can further process your data if you’d like using VBA AutoFilter to alphabetize or filter further. Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide To find unique given-family name combinations, you could use Range("A:B").AdvancedFilter xlFilterCopy, , Range("H1:H1"), True Since our list is very short, there are no first-last name combination duplicates and the output matches the input. On the other hand, if we had 3 Samuel Adams, one each in Annapolis, New York, and Erie, we would only see a single instance in the output. Of course, we could also filter on Family Name + Given Name + City, which would preserve all three in that example. Is the Original Unique? Full Code You can check if your original data has any duplicates by counting the input and output from the AdvancedFilter method. If the number of values match, then your original data didn’t have any duplicates to begin with. One way to do this is to use the WorksheetFunction.Count method. The xlFilterInPlace approach can also apply here, but keep in mind the warning we talked about earlier. This fully functional code block tells you whether the data in Column A is unique already or not: Sub wasOriginalUnique() Dim beforeCount, afterCount As Integer Range("A:A").AdvancedFilter xlFilterCopy, , Range("B:B"), True beforeCount = WorksheetFunction.CountA(Range("A:A")) afterCount = WorksheetFunction.CountA(Range("B:B")) If beforeCount = afterCount Then MsgBox ("The original was unique") If beforeCount <> afterCount Then MsgBox ("The original had repeated records") End Sub To reinforce the value of the VBA AdvancedFilter method, let’s so you have values in Column A and you want to copy only unique values to a new column, column H. It only takes one line of code to do this: Sub uniquesToColumnH() Range("A:A").AdvancedFilter xlFilterCopy, , Range("H:H"), True End Sub All your unique values from column A now appear in column H. It’s as simple as that. Advanced filtering unique values provides a valuable alternative to the VBA RemoveDuplicates method we wrote about not too long ago. We’ve now shown you how to filter out unique records, both in single columns or for consecutive columns. You also know how to put the results in a separate space for later comparison and to keep all the data on one page (without hiding large portions of your original dataset). Once you have the unique records, you can alphabetize and filter further using AutoFilter, both as an entire table or as a single column. If you found this helpful, please subscribe using the form below and we’ll send you a couple more tips to make sure you’re getting the most out of VBA.
>> Read more

VBA Insert Rows on Worksheets and Tables (Wed, 07 Jul 2021)
Inserting a row in a spreadsheet seems like a straightforward task. If you don’t overthink it, you could probably guess right now how to .Insert a Row into the Rows collection without looking up anything else. However, one of my favorite Bertrand Russell quotes applies here: everything is vague to a degree you do not realize until you have tried to make it precise. In that spirit, what exactly is meant by row? Is it the row of a table? The row of the entire sheet? Can a single cell be a row if we constrain ourselves to a single column? Let’s take a look at the different ways to insert rows. The Guessable Method The Table Row The Shift The Entire Row Property Conclusion The Guessable Method I’m going to call this method the guessable method, because, well, even non-programmers could probably guess how it do it. Let’s say you want a new Row 2. Perhaps you need to load some data into it because you’re using a worksheet as a reverse chronological database for sales - against all advice, might I add. Tip: don’t use Excel as a database. VBA works in Access, too. To add a new Row 2, it really is as simple as Rows(2).Insert To be more precise, this accesses the Rows collection and uses the .Insert method for the second entry. .Insert pertains to many objects, but VBA interpolates you would like to insert a row when accessing the Rows collection. If you’d like to inject several rows, you need to put the range into quotes: Rows("2:4").Insert 'this inserts THREE rows (2, 3, and 4) This method inserts entire rows, from Column A all the way to Column XFD or whatever your rightmost column is. While this is the guessable method because it’s simple, simplicity is not the only method or even the preferred method for all use cases. The Table Row In spreadsheet terminology, “row” can be, believe it or not, a somewhat nebulous term. While Rows(1).Insert does indeed insert a row, this type of row spans the entire worksheet. This method isn’t all that useful if you only want to change part of a table or if you have two tables side-by-side and you only want to target one of them.

Let’s say you use a UserForm to gather data about orders, then have two subroutines that run: one injects new orders into the left table, while the other changes the inventory table on the right. Rows.Insert will not produce the desired result here because you don’t want to shift rows in the inventory table on the right while updating the order table on the left. To target a row within a table, you can target the top row of the table using the range in two ways. One way is specify the columns and rows in your statement: Sub InsertRow() Range("A2:E2").Rows.Insert 'target the row directly End Sub In fact, you could even drop the .Rows part from this and get the same result, because Excel understands this range is indeed a single row. Alternatively, if you prefer to use relative locations: Sub InsertRow2() Range("A1:E2").Rows(2).Insert 'target the second row in the range A1:E2 End Sub The second method is useful if you already have a named range for the table or have a looping process. This version does indeed require the .Rows part to specify which part of the range to change. Otherwise, Excel wouldn’t know whether you wanted to insert rows or columns. With all the macro examples in this section, we’re not inserting an entire row across your whole spreadsheet, like we did in our first example. We’re simply inserting cells in a row within a defined range. Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide The Shift The .Insert method takes two optional inputs: Shift and CopyOrigin. Since they’re optional you don’t need to use them, but they can be useful in some instances. If you’re targeting a range, such as the first row of the Inventory table, normally you would shift the cells down. However, you can also shift the targeted cells to the right. This is precisely why Excel asks this question:

Excel GUI Prompt for How to Shift after Insert Cells In most cases, you’ll probably be using xlShiftDown, which is the default for .Insert when referring to rows. However, sometimes you might want to copy to the right, say for a visual check after the subroutine runs: Range("H2:J2").Insert xlShiftToRight This will produce the following result, where the range H2:J2 was inserted and the original values were moved to the right:

Cells Shifted to Right The other optional parameter chooses where to copy the format of the inserted cells from. The default is xlFormatFromLeftOrAbove. In our table example, if we insert at "A2:E2", we copy the cell formatting from "A1:E1". Since this is our header, the inserted cells are initially set as bolded font. In order to ensure we have non-bold font, you should explicitly specify the CopyOrigin parameter: Range("A2:E2").Insert CopyOrigin:=xlFormatFromRightOrBelow This ensures that the inserted cells use the format of the cells below the range before the insertion instead of what’s above (the header format). The Entire Row Property The Excel GUI prompt above also allows you to insert an Entire Row. From our example earlier, this would affect the order table on the left and all other cells on the sheet, including the inventory table on the right. It’s equivalent to the Guessable Method, except the initial reference is a range of cells rather than the Rows collection directly. To replicate the GUI’s Insert Entire Row from this example, you’ll add the the EntireRow property of the Range object: Range("H2:J2").EntireRow.Insert This is most useful if you only have a cell location (especially a relative location, so you don’t know the true location) and you want to insert a full row. Conclusion Now you can insert rows across the entire sheet or only for specific ranges using VBA. In the latter case, you can now also choose whether to shift the cells down or to the right of the newly-inserted cells. And finally, you can choose the format of the new cells from the surrounding, existing cells. You also learned a little about the EntireRow property, which can be useful in same instances, especially when programming complex macros where you don’t know the exact cell reference.
>> Read more

How To Use VBA GetAttr To Gather File Information (Sat, 12 Jun 2021)
Sometimes you may find it useful to know basic file attributes of the files or filepaths with which you’re interacting. If you read and then write back to a file, it’s important to know if the original is read-only, in which case your write will fail, or if it’s a system file, in which case the write will probably also fail but if it succeeds, you could damage the system. Other times, you may just want to check that a string indeed points to a valid file or a directory. The VBA GetAttr function will, unsurprisingly, return basic attributes of a file. Keep in mind, this is a function so it outputs a value; it is not a property of a file object! You cannot write to GetAttr to change the attributes of a file. Likewise, you can’t use this function to make files read-only or hide files. GetAttr works in a rather clever way, so we’ll outline bitwise operations to help our understanding. Don’t worry, though, you can skip that section if you prefer. The Possible Attributes The Output Unmixing Attributes Decomposing Bitwise AND The Possible Attributes There are 7 possible attributes and more than one may be true for a given filepath, though there are some mutually exclusive ones. Here are the attributes, straight from the Microsoft documentation (literally - this is their table): Constant Value Description vbNormal 0 Normal. vbReadOnly 1 Read-only. vbHidden 2 Hidden. vbSystem 4 System file. Not available on the Macintosh. vbDirectory 16 Directory or folder. vbArchive 32 File has changed since last backup. Not available on the Macintosh. vbAlias 64 Specified file name is an alias. Available only on the Macintosh. The first column is the VBA name, which is a human-understandable name for each of the constants in the second column. In your macros, you can use the vbXX name interchangeably with the number itself. The Output So what exactly does the GetAttr function output? The only output is a single integer value equal to the sum of all the true attributes. Whichever attribute is true for your particular input will appear in the function output. The simplest case is one where the file satisfies only one attribute. Let’s say myFile is hidden and this is the only attribute from the above list. Then the statement GetAttr(myFile) will evaluate to 2. Slightly more complicated is a hidden system file. Here we have to include 4 and 2 in the sum, so the output is 6: GetAttr(myFile) = vbHidden + vbSystem = 6. What’s the value for a hidden directory? vbHidden + vbDirectory = 2 + 16 = 18. A hidden read-only system file? vbReadOnly + vbHidden + vbSystem = 7. The reason the numbers are successive powers of 2 is twofold: every combination of attributes will always give a unique number and the mechanics of binary addition make bitwise operation easier. This clever technique means multiple attributes can be represented by a single number without losing any information, like multiple dimensions collapsing into one. Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Unmixing Attributes You could look at a table of all of the possible outputs and know which attributes are present. However, most of the time we are only interested in one attribute. Possible values for read-only inputs are 1, 3, 5, 35, and others. But it would be cumbersome to test each of these. To make it easier, we can rely on the cleverness of binary bitwise operations. To see whether a particular attribute is present, you’ll need to use the AND operator and set the resulting value to an integer: iReadOnly = GetAttr(myFile) And vbReadOnly If the output is zero, vbReadOnly is not an attribute of this file. If the output is anything other than zero, vbReadOnly is present. Here’s a full macro example to test the read-only attribute using a VBA If-Then statement: Sub VBA_GetAttr_Demo() Dim myFile As String Dim iReadOnly As Integer myFile = "C:\Users\Public\MySpreadsheet.xlsm" iReadOnly = GetAttr(myFile) And vbReadOnly If iReadOnly <> 0 Then 'File is read-only Else 'File is not read-only End If End Sub To drive the message home, let’s run through a second example to test whether a a path is a directory or a file: Sub VBA_GetAttr_Demo_Dir() Dim myPath As String Dim iDir As Integer myPath = "C:\Users\Public" iDir = GetAttr(myPath) And vbDirectory If iDir <> 0 Then 'A directory was selected Else 'Not a directory End If End Sub This will work whether or not you have trailing slash at the end of your file path. Testing is as easy as that. To use GetAttr you don’t need any more information, but if you’re curious how we can break down the sum, read the next section. Decomposing Bitwise AND So how can we test whether a number is really part of the sum? By using bitwise AND. Every number in computing is represented by a string of bits, which can be on/true or off/false, usually represented as 1 and 0, respectively. There are two operations we can do with these bits, AND and OR, which come from mathematical logic. For this article, the important point is that both sides of an AND operation must be true/on/1 for the output to be 1. If one or both sides is false/off/0, then the output is 0. When we look at the bitstring representation of 2, we get 10. Leading zeros can be added indefinitely, so 10 = 00000010 = 0010. The important part is the trailing zeros (and 1s). The bitstring representation of 4 is 100 and 8 is 1000. It is best to read these as “one zero zero” and “one zero zero zero” rather than “one hundred”, “one thousand”, etc. 6 is 2 + 4, or 110 = 010 + 100. Here are the bitstrings of the attribute values above: 00 = 0000000 6543210 <-- raise 2 to this power if 1 appears in the column 01 = 0000001 02 = 0000010 04 = 0000100 08 = 0001000 16 = 0010000 32 = 0100000 64 = 1000000 Because each value in the table above is a power of 2, all bits in the bitstring representation will be zero except one of them. For this reason, adding any of these numbers together will never “flip a bit” and carry over to the next column, as each number is fully represented in its own column. To see whether 4 is “part of” 6, we can check whether each 1-bit in 4 has a corresponding 1-bit in 6. Remember this is bitwise operation, so we need to go column-by-column: 06 = 0000110 AND 04 = 0000100 xx = 0000100 <-- bitwise output where bits in top and bottom row are BOTH 1 In the 3rd column from the right, which is four’s designated column, we have a 1-bit in 6. The bitwise AND operator output produces a non-zero number, and hence 4, vbSystem, is part of 6. If we have 14 (2 + 4 + 8), can you work out how the calculations look to determine whether vbArchive is present? This trick does not work if the possible inputs for the sum are not powers of 2. The VBA GetAttr function provides information about the attributes of a file using a summation technique that provides a unique number for each combination of attributes. Again, the VBA GetAttr function is a function, not a file property, so you can’t use it to change file attributes. You can only use it to determine which file attributes are present. Whether a particular attribute is present can be determined using the bitwise AND operator. Bitwise operations go bit-by-bit rather than taking the bitstring as a whole, and the gaps in the decimal representation of the attributes make bitwise AND able to detect whether an attribute is true or not. Bitwise operations can be complicated, but the GetAttr function, and VBA in general, don’t have to be. If you want to learn VBA without all the unnecessary coding complications, subscribe using the form below. We’ll walk you through a free step-by-step training program to making mastering VBA a breeze.
>> Read more

Controlling Your Spreadsheet With VBA UsedRange (Fri, 07 May 2021)
The VBA UsedRange property is a neat property to add to your VBA repertoire. It’s commonly found in internet threads for finding the count of rows and columns on a sheet - and by proxy the iteration counts for for-loops - and it’s certainly useful for that. UsedRange has some other interesting properties, too, and there are a handful of warnings to keep in mind. Let’s check it out. UsedRange Object The Shape of the Range Working with Rows and Columns Using the .Address Property First Row and First Column Counting Rows and Columns Last Row and Last Column Counts for For-Loops Properties and Methods Clearing the Range Navigating Ranges Hidden Changes UsedRange Object UsedRange is a special Range property that specifies the range of cells on a worksheet that have been, well, used. Truth is, certain modifications to a cell also designate it as “used” according to VBA. We’ll talk about this more in a few minutes, but a better description of the VBA UsedRange property is a range of cells, on the specified sheet, that have content, formatting, comments, or certain other modifications. It’s a great way to gain better control of your spreadsheet’s data. Since UsedRange is a property of the Range object, all of the properties and methods available to Ranges are also available to UsedRange. This includes column/row counts, selecting, clearing, formatting, and range navigation. Moreover, you can set it as a named object for more convenient reference, like so: Dim rng As Range Set rng = Sheets("targetSheet").UsedRange where targetSheet is the name of the sheet you want to work with. Setting a named object, like rng, lets you access Intellisense, an invaluable tool. Be aware that objects are not updated on-the-fly, so you need to set it again to get updated properties! If you set rng then change anything, like applying the .Clear method, your variable rng will NOT update automatically. You’ll need to set it again. To demonstrate what VBA UsedRange actually does, add a couple values to an empty spreadsheet and run this short macro: Sub SelectUsedRange() ActiveSheet.UsedRange.Select End Sub This macro selects the used range on your spreadsheet. You can see that VBA attempts to calculate the first cell and the last cell with data on your spreadsheet and selects everything in that range. Notice in the image below that any empty cells in the used range are also included when using the UsedRange property. Even though you might think it would, the VBA UsedRange property doesn’t just capture the union of all cells containing data.

The Shape of the Range Like we just demonstrated, VBA does not give you a choice in the shape of the range covered by UsedRange. It always produces a rectangle and starts in the upper leftmost corner and fans out to capture the lowest row and the rightmost column. Even though they define the boundaries of UsedRange, the upper left and lower right cells may not actually contain any values. For example, the UsedRange in this image is B1:E25, even though the four cells in the top corner, including cell B1, contain no data or formatting. Neither does cell E25. The same is true for the four bottom left cells. Importantly, the blank row between the top ten and next ten entries, row 13, is also counted - keep this in mind when using VBA UsedRange as the basis for your macro’s row and column counting!

UsedRange with Unfilled Cells Make powerful spreadsheets with our free VBA Developer's Guide This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy to your email address below. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Working with Rows and Columns UsedRange is handy for finding the first and last rows and columns used on a worksheet. By extension, it can be used to count rows and columns to help iterate through for-loops. Using the .Address Property One way to get the first row/column pair and last row/column pair is to use the .Address property of the UsedRange: Dim rng As Range Set rng = Sheets("targetSheet").UsedRange Debug.Print rng.Address This returns the string $B$1:$E$25, which can be parsed into the necessary pieces for the first row (1), the first column (B), the last row (25), and the last column (E). Recall that Debug.Print prints to your VBA Immediate Window. First Row and First Column You can also easily find the first row and first column as a number using the .Row and .Column properties: Dim rng As Range Set rng = Sheets("targetSheet").UsedRange Debug.Print rng.Row Debug.Print rng.Column This simply returns the first row number and first column number of the range, (1 and 2 in our example), which corresponds to the $B$1 cell that we talked about earlier. The number for the last row and column requires a bit more work. One way to get the last row and column of a UsedRange is by using the count property. Counting Rows and Columns To get the count of rows and columns in the range, you can use .Count Dim rng As Range Set rng = Sheets("targetSheet").UsedRange Debug.Print rng.Rows.Count Debug.Print rng.Columns.Count This returns 25 and 4 in our example above. Last Row and Last Column If the first cell in your UsedRange is $A$1, finding the last used row and column is simple since the .Count of the rows and columns equals your last used rows and columns. Dim rng As Range Set rng = Sheets("targetSheet").UsedRange lastRow = rng.Rows.Count '25 lastCol = rng.Columns.Count '4 This often isn’t the case, though. If your data does not start at $A$1, this method will not work! As can be seen in our example image, the last row is indeed Row 25. The last column, however, is not Column 4 (D). It’s Column 5 (E). To be more robust, you’d need to to add the first row/column numbers to the row/column counts. Dim rng As Range Set rng = Sheets("targetSheet").UsedRange firstRow = rng.Row firstCol = rng.Column numRows = rng.Rows.Count numCols = rng.Columns.Count 'remember to subtract 1 to not double-count the first row/column lastRow = firstRow + numRows - 1 lastCol = firstCol + numCols - 1 Now we get our expected {25, 5} instead of {25, 4} result. Remember to subtract 1 from the final result, otherwise you’ll double-count the first row and column. Now that we’ve demonstrated the logic for calculating the last used row and column in a UsedRange, let’s show you a much easier way. To record the last used row and column in a Used Range, simply use the following shortcut: Dim rng As Range Set rng = Sheets("targetSheet").UsedRange lastRow = rng.Rows(rng.Rows.Count).Row lastCol = rng.Columns(rng.Columns.Count).Column Counts for For-Loops Notice our continued use of the .Count property. The .Count property is also useful when constructing your for-loops. Use the first row/column to start, then process an entire range down to the last row/column. For a simplified approach that only loops through the rows, let’s say a sheet only contained numeric data in Column A. You could store the running total of the numbers in Column B (Column 2) using a macro like this: Sub RunningTotal() Dim FirstRow As Integer, LastRow As Integer, iRow As Integer Dim rng As Range Set rng = ActiveSheet.UsedRange FirstRow = rng.Row LastRow = rng.Rows(rng.Rows.Count).Row For iRow = FirstRow To LastRow If iRow = FirstRow Then Cells(iRow, 2) = Cells(iRow, 1) 'start off the running total Else Cells(iRow, 2) = Cells(iRow, 1) + Cells(iRow - 1, 2) 'add previous running total to new entry End If Next iRow End Sub You would need two nested loops to loop through both the rows and the columns in your used range, like this: Sub LoopThroughUsedRange() Dim FirstRow As Integer, LastRow As Integer Dim FirstCol As Integer, LastCol As Integer Dim iRow As Integer, iCol As Integer Dim rng As Range Set rng = ActiveSheet.UsedRange 'store the used range to a variable FirstRow = rng.Row FirstCol = rng.Column LastRow = rng.Rows(rng.Rows.Count).Row LastCol = rng.Columns(rng.Columns.Count).Column For iCol = FirstCol To LastCol For iRow = FirstRow To LastRow Debug.Print Cells(iRow, iCol).Address & " = " & Cells(iRow, iCol) Next iRow Next iCol End Sub If you only want to process data to cells in the UsedRange that are not empty, apply the VBA IsEmpty function. Properties and Methods Once you know how to navigate a UsedRange, applying properties using VBA is a breeze. Properties let you do things like change the entire range to bold font. Just note that the properties will be applied to the entire range, even if cells are blank. It would be tedious to go back and fix formatting for an entire sheet because of a single poorly thought out line of code. Dim rng As Range Set rng = Sheets("targetSheet").UsedRange rng.Font.Bold = True This macro snippet changes all cells on the worksheet containing data (or formatting plus empty cells between used cells) to bold. You can do the same thing with colors, cell fill colors, number formats, etc. Again, be aware the property will be applied to the entire used range, so if you have a nicely formatted table with 5000 cells, make sure you want all 5000 cells to be bold before running those lines of code! Clearing the Range Another very common use of UsedRange is to clear the contents of a sheet. When using UsedRange, you don’t need to find the last row/column like we did above. After setting rng as a named object, simply execute: Dim rng As Range Set rng = Sheets("targetSheet").UsedRange rng.Clear 'or rng.ClearContents to just clear data Again, make sure you want to clear the entire worksheet of formats and data. It’s a little like using sudo rm -r in Linux: powerful and convenient, but risky. Make powerful spreadsheets with our free VBA Developer's Guide This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy to your email address below. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Navigating the Range Ranges allow access relative to the range itself as opposed to relative to the worksheet. For example, in our table above, the first column is actually B, not A. If we knew the column layout of the table - axis name, rank, country, population - we could reference population relative to the sheet or relative to the range. population column relative to the sheet = 5 population column relative to the used range = 4 Let’s imagine a user shifts the table to a different position on your worksheet for some reason. If you hardcoded your macro to grab population relative to the sheet, you may now be inadvertently grabbing the wrong data. It would be more robust to use the table’s own structure as the point of reference: Dim rng As Range Set rng = Sheets("targetSheet").UsedRange rng.Columns(4).Font.Bold = True If the user moves the table’s starting point to $H$15, for example, we can still bold the population column because the population column will still be the 4th column in our UsedRange. Hidden Changes There’s at least one caveat to using UsedRange and it’s an important one to consider. The caveat deals with unseen changes. Cells that have been changed invisibly, such as with number formatting, font changes, or even comments, will be included in the coverage of UsedRange. What that means is if a user accidentally italicizes a cell without any data, that cell still counts for the “used range” so your UsedRange might be expanded beyond what you thought it would be. This expanded range would show up when selecting UsedRange and it would be included in the .Count and .Address properties. This isn’t always a problem, but it can present easily-overlooked issues. In our RunningTotal example, if only the first 10 cells have data, one would expect to get the total up to Row 10. If, however, the user accidentally italicizes cell $D$23 with a slip on the keyboard and Ctrl+i, we’ll get this strange output in column B, which is bound to confuse the user:

Format Changes Impacted VBA UsedRange There is no obvious change to cell $D$23, but UsedRange considers the formatting change and includes the entire area from $A$1 to $D$23. UsedRange makes it easy to find the first row/column and to apply formatting to any useful cells on a sheet. It also makes finding the last row/column pretty easy, and, most importantly, it helps you set up your for loops. If you found this tutorial helpful, subscribe using the form below and we’ll share similar VBA tips to help you get the most out of the programming language.
>> Read more

Using the VBA FileDateTime Function (Fri, 09 Apr 2021)
It may be useful to know the last time a file was modified or, if never modified, when the file was created. You could use this information to run an update script, avoid unnecessary processing, check the existence of a file, and even avoid overwriting important data. The VBA FileDateTime function is a simple function requiring only one input, but this simple function is so powerful it can be used in a variety of applications. VBA FileDateTime Function Another “Output” Applications Avoiding Overwrites Timing Overwrites Saving Time VBA FileDateTime Function The FileDateTime function is a built-in VBA function with only a single input parameter, PathName. It doesn’t matter how you enter the PathName argument, as long as it’s a string. It can be chosen by a file picker, deduced by the current script, or painstakingly typed out by the user. FileDateTime(PathName as String) The output is a Date variable, which can then be fed into any number of date-manipulating functions, like the DateAdd function. The output provides the last modified date/time or, if the file was never modified, the date/time of file creation. When displayed, it takes the form of the locale settings of the current system, so it’s safer to store the Date than to convert it to a String until necessary. The Date type will not confuse 03-03-03 with 03-03-03 or 03-03-03, while a String most certainly will. That’s mm-dd-yy, dd-mm-yy, yy-mm-dd, by the way - or is it? Programming with dates is confusing since different regions use different date formats. That’s actually why we created an entire tutorial on VBA date formatting. Anyway, VBA can implicitly convert Dates to Strings when you want to display the date to the user. That said, to be rigorous, apply a CStr conversion to the Date only when you’re truly ready to use the String version. As far as inputs and outputs go, they are not much simpler or more intuitive than that. Input a file path, output a date. Another "Output" As long as the file specified in PathName exists, there will be a Date output. If you get a Run-time error ‘53’, it means the file could not be found. Depending on the input capture method (file picker, typed out, deduced), the script should call the appropriate error handler. Run-time errors are usually discouraged, but a meaningful error saying a file does not exist could be really useful. For example, if your code is clean, you may consider applying On Error Resume Next to signal the file doesn’t exist and thus the script is free to create a file with that name. However, I only recommend doing this if you are absolutely certain there is no bug in the PathName capture method. Applications So why would you care about the last modified date or the creation date of a file? Many decisions in life are sensitive to timing, and the FileDateTime function provides some information to help us determine what we should do next. As is common in information communication theory, the presence of the Run-time error 53 error also provides some information. Avoiding Overwrites It’s an awful feeling to realize you just overwrote an old file that had data you wanted to keep. It is infuriating to find out that a program didn’t alert you but instead blindly overwrote a file. Luckily, Windows and most modern major applications have precautions built in, such as appending numbers to a download if a pre-existing file is found. When you write your own VBA scripts, no such precautions are in place unless you put them in place. Take a look at our Export Charts tutorial, for example. The .Export method in that tutorial writes the exact name the user provides. If there is already a file with that file path (name and folder), the method overwrites the existing one. For periodic scripts, this could leave you with only the latest chart, while for iterative scripts, you will only see a single chart (the last processed) instead of the entire set. Using the FileDateTime is a quick way to check if a file exists (though it’s worth mentioning the VBA Dir Function is the preferred way to check if a file exists): Sub FileDateTimeDemo() Dim lastModTime As Date 'initializes as 00:00:00 On Error GoTo handler 'when file not found, handle the error lastModTime = FileDateTime(targetFile) 'resets initial 00:00:00 to found time On Error GoTo 0 If lastModTime <> 0 Then 'code for when targetFile already exists Else 'code for when targetFile did not exist (may exist now, depending on error handler code) End If End Sub Importantly here, note that lastModTime initializes as 00:00:00. If the file exists, lastModTime is rewritten for the file date/time. In the erroring case, lastModTime remains 00:00:00 (unless your error handler changes it). If the function does throw an error because the specified file does not exist, one potential error handler could enter the subroutine responsible for creating the file then return control to the original subroutine. This code flow is outside the scope of this article, but I encourage you to read our VBA error handling tutorials to get a grasp on this type of logic flow. Make powerful spreadsheets with our free VBA Developer's Guide This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy to your email address below. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Timing Overwrites Overwriting is not always a bad thing. In fact, if we never overwrote any data, the world would be filled with hard drives and hard drives of useless data. One case may be to overwrite a file that is older than a certain threshold. Perhaps there are 7 files in a folder, each named for a day of the week. In our imagined scenario, the data need only be stored for one week, so the programmer could cycle through the folder checking for the file more than 6 days old. Once found, we know it’s the oldest file in our set (i.e., the one from one week ago), and that is the one to overwrite. Inversely, we may want to overwrite the latest file but only on the same day. The script may output a file, do some checks that take a variable number of minutes, and reschedule the script for an hour later if the checks succeed. Each day has its own file, resetting at midnight. One way to schedule the script, relative to the time of file modification (not the time of the completion of the checks), is to use FileDateTime and DateAdd functions: newScriptTime = DateAdd("h", 1, FileDateTime(outputFile)) Once midnight arrives, a new outputFile can be created, which the script might determine by using FileDateTime and Now together. Saving Time Yet another scheduling example may be to check that a file has not been created or modified within the last day in a folder. Your macro could loop through all files in a folder and if a file from the same day exists, we could tell the user to only run the script once per day. Alternatively, we could warn the user such a file was already created for the day and ask if they really want to continue to reprocess the data and/or overwrite the same-day file. The FileDateTime is a simple function with intuitive inputs and outputs. It’s quite useful for scheduling and determining file order, including the earliest and latest files in a set (which we often target). Naturally, you may have other applications for FileDateTime. Whatever they be, we wish you bug-free code and low frustration levels. For more VBA tips like this one, please subscribe using the form below. We publish VBA tutorials on topics no one else has covered and we strive to help you get the most out of your macros.
>> Read more

VBA Export Charts as Images (Fri, 19 Mar 2021)
In other tutorials, we’ve described how to create charts in VBA and even how to create scatterplots with VBA. In this tutorial, we’ll show you how to export a chart as a picture using VBA. We’ll be using a line chart with quarterly sales per branch throughout this tutorial. Truth is, you could make a bar chart or line chart but since this is data over time and across four branches, a line chart is cleaner and makes visualization easier. Exporting charts as images is useful when attaching charts as images to an email. Images are immutable, so while you can embed a chart in PowerPoint, if you want to ensure no one changes the chart, an image may be a better solution. To practice, you can download the CSV of the sales data (all fictitious, of course). Identify the Correct Chart Irresponsible Naming The Export Method File name (and File path) Optional Filter Name Resizing the Chart Exporting Your Chart(s) Identify the Correct Chart First you need to find the right chart to export. Most people create a chart directly on the worksheet that contains the data. This is technically an embedded chart, which must be wrapped by a ChartObject container to differentiate it from the underlying worksheet object. If you’re exporting charts made by a GUI-user, you’ll need to find the right ChartObject first (which contains the chart itself). As is standard in VBA, collections of objects are the pluralized name of the object: ChartObject is the individual object, and ChartObjects is the collection. Each Sheet has its own ChartObjects collection, so there’s a bit of layering here. Let’s say your user responsibly names their charts and sheets, so you have a sheet Quarterly Sales per Branch and a chart named Sales NCW for Sales North, Central, West.

Screenshot of Sheet and Chart, with the chart name in the upper left There are a couple options for identifying this chart using VBA, but here’s one way using the Chart name, found in the upper left of the screenshot above: Sheets("Quarterly Sales per Branch").ChartObjects("Sales NCW") Irresponsible Naming If your user does not logically name things, it may take some guesswork to programatically determine which chart to export or you can create a dropdown list of all the chart names detected. One thing you could do is try ActiveSheet.ChartObjects.Count, which counts the chart objects on the active sheet. With some luck, the user may want to export the sole chart on the active sheet. If that’s the case, the count is 1 and you can just export that chart. If the count isn’t 1, you’ll probably need to iterate through the ChartObjects in the collection, store their names (hopefully not Chart 1, Chart 2, etc.), and provide a custom selector box for the user. Painful for the end-user, but doable. Our tutorial describing how to print all charts in a workbook demonstrates how to loop through the charts like this. The Export Method Now that you have identified the chart or charts you want, you can export it as an image. But first, if you’d like to do any other chart manipulation, it would be wise to create a named object to identify your chart, like this: Dim salesChart As ChartObject Set salesChart = Sheets("Quarterly Sales per Branch").ChartObjects("Sales NCW") This way you can manipulate salesChart in multiple ways before exporting and you can rely on the Intellisense to see all the methods and properties VBA offers for controlling your chart object. File name (and File path) You’ll save your chart as an image using the .Export method. Of the three parameters for .Export, only the filename is required. It’s a good idea to include the full path instead of just the name you want to give your image. Otherwise, the user may have trouble finding the exported image later. You can either hardwire the path, or customize it at the time of execution using strategies, like these: ThisWorkbook.Path, ActiveWorkbook.Path, or some other way to find the file path of the workbook you’re currently working on. Use a folder picker dialog to allow your users to select their own path using a graphical interface. Use an InputBox or Application.InputBox to let your user manually copy and paste a path of their choosing. If the chart is responsibly named, you can just use salesChart.Name for the name itself (here .Name is Sales NCW, not our VBA object name salesChart!). You’ll also need to provide a file extension or it will be saved as a generic file, leaving less savvy users confused on how to open it. JPG, GIF, and PNG are all common filetypes. Optional Filter Name The Export method gives you the ability to choose a FilterName, which, according to the Microsoft documentation, identifies the “graphic filter as it appears in the registry”. These are the file extensions. VBA is smart enough to know which filter you want from the filename, though, so this is rarely necessary. There’s a final optional parameter for showing a dialog box to choose the FilterName. Unfortunately, when I set this to TRUE, my machine doesn’t seem to care and I’ve never been able to reliably use this parameter. Make powerful spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Resizing the Chart When Excel exports a chart as an image, it will retain the current size of your chart in pixels. You’ll need to resize the chart before exporting if if you want your images saved with certain dimensions. This is where our salesChart object variable comes in handy. This chart object has two convenient read-write properties, .Height and .Width, which you can use to programmatically resize the chart before exporting it as a picture. I emphasize these properties are read-write because it’s bad form to resize a user’s chart, especially since they’ve likely spent a lot of time getting it just right. The best practice would be to store the original height and width to variables, resize the charts to the size you want to export them, then restore the original the chart to its original height and width. The height and width properties take values in points, where one point is 1/72 of an inch. To ensure sufficient resolution and readability when exporting without distorting the original chart, you may want to include some code like this to capture the original dimensions before resizing the chart. That way, you can restore the original dimensions after exporting the chart image at the size you want. 'capture original dimensions origHeight = salesChart.Height origWidth = salesChart.Width 'resize chart salesChart.Height = 500 salesChart.Width = 500 Exporting Your Chart(s) So far you’ve identified your chart, your path, and your filetype and you’ve determined an appropriate size for your chart. Now you’re ready to export. Assuming you’ve stored the file path into a string, your full export macro may look like this: Sub exportCharts() 'dimension and set objects Dim endFileName As String Dim salesChart As ChartObject Dim origHeight As Integer, origWidth As Integer Set salesChart = Sheets("Quarterly Sales per Branch").ChartObjects("Sales NCW") 'capture original dimensions origHeight = salesChart.Height origWidth = salesChart.Width 'resize chart salesChart.Height = 500 salesChart.Width = 500 'build file path and name 'make sure to concatenate the backslash or you will land in the 'parent folder with the target folder in the filename endFileName = ThisWorkbook.Path & "\" & salesChart.Name & ".jpg" salesChart.Chart.Export endFileName 'restore original dimensions salesChart.Height = origHeight salesChart.Width = origWidth End Sub If you have multiple charts on a single worksheet, iterate through all the ChartObjects to save them all at once using a loop, like this: For Each cht In ActiveSheet.ChartObjects endFileName = ThisWorkbook.Path & "\" & cht.Name & ".png" cht.Chart.Export endFileName Next cht Note: don’t forget to concatenate the backslash to the Path, otherwise your target folder will be part of the filename, not the parent folder. Now you know how to export embedded charts as images, like jpg, png and gif. Once they’re exported, you can attach the results to emails or embed them in presentations as immutable objects. We’ve also shown you how to loop through the ChartObjects collections to help you export multiple chart images at once. The .Export method really is a simple method. In fact, the hardest parts are building the path and identifying the actual chart you want to export. Once you master that, you’ll be rapidly exporting charts in no time. If you found this tutorial helpful, I hope you’ll subscribe using the form below. We’ll send you tips and tutorials to make sure you’re exercising VBA to the fullest. It can do a lot of powerful things, and we’ve only just scratched the surface with this tutorial.
>> Read more

VBA Err Object and Error Handling (Thu, 11 Feb 2021)
In our third and final installment on VBA error handling we’ll dive into the Err object, some of its properties, and how to use it to raise errors. We’ll also take a brief look at how errors can cascade through subs. If you are new to VBA error handling, start with our tutorial on error handling blocks with GoTo then continue to our Resume error statements tutorial. The Err Object Properties The .Description Property The .Number Property The Source and Help Properties The Err Object Methods Raising Errors The .Clear Method Cascading Errors The Err Object Properties Every error instance in VBA populates the Err object with some useful properties designed to help handle the error. The Err object also has two useful methods, .Clear and .Raise, which become invaluable when debugging or providing more error details to your users. The .Description Property As you might expect, this property provides a description of your errors. If the error thrown is part of the common set of system errors (divide by zero, overflow, subscript ranges, type mismatches, etc.), a pre-determined description will already be set by the system. These descriptions are probably familiar to you, because they are the same ones that appear when you are testing in the VBA editor (VBE). If you run code that divides by zero, you’ll know because of the dreaded run-time error popup:

The dreaded runtime error message box The Division by zero string is the pre-determined .Description property of the Err object. .Description is read/write, so you can set your own description if you prefer. Generally this is done to help users fix the problem, and it is especially practical if you’ve written your own VBA classes and are raising errors (more on that later). The .Number Property The description is a string and human-readable, but computers operate with numbers. Using numbers makes it much easier to set up Select Case and IF-statement tests. It’s also more succinct, though most users will not know which numbers correspond to which errors. It’s still valuable to know the number when you’re Googling how to fix a run-time error. In the above screenshot, the Run-time error ‘11’ part shows the error number. As a little trick, the number property is the “default property” of the Error object, which means you can print the number in two ways: Debug.Print Err.Number Debug.Print Err Combining the .Number and .Description properties, you might tell your users not to divide by zero, overwriting the system default: Sub div_by_zero_from_input_error() Dim x, y As Integer On Error GoTo myHandler x = InputBox("Enter numerator") y = InputBox("Enter denominator") MsgBox "Your ratio is " & x / y On Error GoTo 0 Exit Sub myHandler: If Err.Number = 11 Then Err.Description = "You can't divide by zero, dummy" MsgBox Err.Description End If End Sub If you don’t understand why we have On Error GoTo 0 or how to reach myHandler, see our first VBA Error Handling article. The Source and Help Properties The Error object has three more properties. First is .Source, which can help you pinpoint what triggered the error. Again, this is a read/write property, so you can write whatever you want to it. You can set it to the name of the triggering subroutine or code block to help you find issues. The other two are related to help files. When a system default error occurs, like Subscript out of range, the .HelpFile will (ideally) point you to a help file on the system where you can find information about the error you encountered. Since some help files can be very large, .HelpContext will place you in the correct spot in the file. For example, when I have a Type mismatch error, I will get the following information from the Immediate window: ?err.Number 13 ?err.Description Type mismatch ?err.HelpFile C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm ?err.HelpContext 1000013 You can create your own help files to guide your users, but it’s not as simple as just pointing to a plain text file. If you’ve written any custom classes or your code is being shipped to thousands of users, consider going through this extra effort, though, especially if you are a one man or one woman show. This can help you avoid answering 500 emails. There are plenty of resources on the internet to help you write these files, which take the .chm extension. With a valid one, when the user taps Help in the error message box, they’ll go straight to your file. Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide The Err Object Methods The Error object methods, .Raise and .Clear, can greatly help you in debugging and telling users how to fix issues. Raising Errors Note: If you’re a long-time reader of our site, you might recall we published a detailed tutorial about raising custom errors with the VBA Err.Raise method. We’re going to touch on that again here. A system often automatically raises run-time errors, like Subscript out of range. If you have two sheets in a workbook, Inputs and Outputs, and you ask for a sheet named Intermediates, the system cannot conjure up an Intermediates sheet out of thin air. You can also raise errors yourself. In our Divide by zero code block above, instead of letting the system raise the error, you could’ve done it yourself. The .Raise method accepts all five properties we talked about earlier, so you can set the description, source, and help information in one go: Sub div_by_zero_from_input_error2() Dim x, y As Integer x = InputBox("Enter numerator") y = InputBox("Enter denominator") If y = 0 Then Err.Raise 11, "output ratio sub", "Denominator is zero", "C:\Help With Ratios.chm" outputRatio = x / y End Sub Now while debugging, checking the Err.Source property will tell you the error was encountered in the "output ratio sub". You can put whatever description you want in here. The user will be able to see the description of “Denominator is zero” if a runtime error window pops up and it’ll even link to a custom help file Help with Ratios.chm, if one actually existed. If the error is unique for your project, you can even set your own types of errors with custom numbers. The range 0 to 512 is reserved for system errors and valid numbers above 512 can be used for custom error numbers. The .Clear Method The other Error object method is .Clear, and, as its name suggests, it clears the current error. Recall that only one error can be active at any time. Further errors will be fatal. One way to reset the error handler is to use a Resume statement or exit the subroutine (or function). Technically this automatically calls the .Clear method in the background without any interaction from the programmer. To explicitly clear an error, use Err.Clear. This is quite useful when deferring errors with On Error Resume Next, especially if there’s an iteration during deferment coupled with an IF-statement, like this: x = 5 On Error Resume Next Do While x > 0 y = z / (x - 4) x = x - 1 If Err.Number <> 0 Then MsgBox ("Oops. There was a problem") Loop On Error GoTo 0 Here, on the first iteration, no problem. On the second, however, we get 0 in the denominator. This raises an error, which never clears during the loop. Hence, the message box appears every time, even though the error is only pertinent to the second run. On run 3, x = 3, so the operation is valid again, but the uncleared error still triggers the IF-statement. Use .Clear to solve this (illustrative) problem: x = 5 On Error Resume Next Do While x > 0 y = z / (x - 4) x = x - 1 If Err.Number <> 0 Then MsgBox ("Oops. There's a problem") Err.Clear Loop On Error GoTo 0 Cascading Errors Error handling must be activated with an On Error statement. If this is missing and the current subroutine is top-level (i.e., it was not called from another sub), then there will be a fatal error and the dreaded runtime error message appears. However, if the current sub was called from another sub, and the other sub has an active error handler, the error will “cascade backwards” to the calling sub’s error handler. This can help centralize all your error handling into a single sub for easier maintenance, especially when you have been zealous in modularization and each sub does a very simple task. In this example, you can easily write a single Select Case in your centralHandler to take care of potential issues in called subs rather than copying code to each sub. This illustrates the traditional tradeoff between code bloat (copying) and the readability of modularized code. If you rely on cascades, make sure to document well! Sub calling_cascade_back() On Error GoTo centralHandler 'revenues = called_cascade_1 'some code 'currCapital = called_cascade_2 magicNumber = called_cascade_3 '200 more lines of code On Error GoTo 0 Exit Sub centralHandler: 'error handling code for whole project Select Case Err.Number Case 6 'handle Overflow Case 11 'handle Divide by zero Case 13 'handle mismatches End Select Resume 'use RESUME to return to the errored-out function or sub! 'Resume Next ← GOES TO NEXT LINE IN **calling_cascade_back**!! End Sub Function called_cascade_3() 'calculates user-defined ratio 'all errors handled centrally from calling sub userInput = InputBox("Enter your magic number for stock valuation") called_cascade_3 = 500 / userInput End Function Even though an InputBox was called in the function called_cascade_3, if a user inputs 0 for userInput, then control will move to centralHandler in the main subroutine. The Resume statement will cause the InputBox to keep appearing until a valid number is entered. This concludes our 3-part VBA Error Handling tutorial. Now you know how to use the Error object, how to check and set properties, and how to use its methods for debugging. You also saw how to centralize error handling through cascades. The system has plenty of defaults to guide programmers and users, and programmers should use them liberally. It’s very useful to set your own errors using .Raise, especially in large programs, so you can find error-triggering sections more easily. In production code, you can even avoid many customer service calls if you handle errors well and guide the users with clear descriptions or even full help files. If you found our series on VBA error handling helpful and you’re serious about learning VBA, please subscribe using the form below for more free lessons.
>> Read more

VBA Error Handling with Resume Next, GoTo 0 (Fri, 01 Jan 2021)
This tutorial focuses on the meaning and implementation of Resume Next, GoTo 0, and GoTo -1. These critical components add structure to the VBA On Error statement and any explicit handlers using GoTo [label]. In brief, Resume Next goes to the next executable line, GoTo 0 turns off the currently enabled error handler, and GoTo -1 turns off the current exception. Of course, these things need a bit more explanation, otherwise we wouldn’t have written an entire tutorial about them! Basic Error Handling Overview How to Use Resume Next Defer Handling Return Execution to the Main Block Hidden Role Using GoTo 0 and GoTo -1 Enable and Disable Handlers Clearing Exceptions Basic Error Handling Overview For a more thorough exploration of basic error handling techniques, read our article dedicated to the On Error GoTo statement. Error handling in VBA is disabled by default, so it must be turned on with the On Error statement. If you want to explicitly handle errors, label a section of code and direct the execution flow to that section: On Error GoTo fixErrors 'some code that might trigger an error 'On Error GoTo 0 'uncomment to disable fixErrors and enclose the section Exit Sub fixErrors: 'code to fix the error 'Resume Next 'uncomment this to return to original code End Sub The first line in the snippet turns on (enables) the fixErrors handler, and if an error occurs in that section, execution flow moves to the part of the code with the label fixErrors:. To shut off (disable) the active handler, use On Error GoTo 0. Doing so will close off the code block that uses that handler. Alternatively, exit the subroutine using Exit Sub, which automatically turns off the handler. Calling another subroutine does not exit the current subroutine so any active handlers will be stacked in the new subroutine! Error handling code must be placed before the End Sub statement, so in order to avoid it running with the regular code, it should come after an Exit Sub statement. It’s wise to check inputs for the correct data types, formatting, and common issues like a divisor of zero. This reduces the prevalence of runtime errors before they can even arise. How to Use Resume Next Resume Next plays a dual role: as a stand-in for a custom error handler that is marked by a label to return execution flow to the main body of the code after a custom handler completes We’re going to talk about each of these roles in the next two sections. Defer Handling You can defer handling errors by using On Error Resume Next, like in this snippet: On Error GoTo fixErrors 'turns on fixErrors handler On Error Resume Next 'Defers error handling 'code that might error If you replace GoTo [label] with Resume Next, you can defer error handling. If an error is raised, execution simply skips that line of code and goes to the next one. If the next line raises an error, execution just skips that line, too. This definitely has some benefits, and we use it quite a lot on this site, like when checking if a file exists, but you need to be careful when applying it. If used to ignore a specific deficiency you know exists, you could mistakenly skip over an entire section of code without realizing it. Using Resume Next to defer handling has its purposes, such as eventually running code that forcibly rectifies the offending variables and objects. But I’d strongly recommend either explicitly handling errors with GoTo [label] or avoiding errors by checking inputs with IF statements, where appropriate. The main problem with the “ignore the errors” method is that users will not notice that the code is broken. It will run as long as deferment is active. However, some expected result will simply be incorrect or not be displayed at all. This leads to frustrated users trying to figure out what they did wrong when it was really a runtime problem with your code. Even worse, they may just accept the erroneous output, no matter how outlandish. Resume Next to defer errors has its place, but use it wisely. A good habit is to warn others (including your future self) that you’re using it. Clearly state this in the comments at the top module and describe why you’re doing it. This habit at least makes others aware that unnoticed errors may be lurking in the code. Return Execution to the Main Block Resume Next is not, however, without a positive side. The same statement can return control to the main code block after entering a custom handler. This means you don’t have to end a subroutine every time a custom handler is employed. On Error GoTo fixErrors students_per_class = num_students / num_rooms MsgBox ("You'll have " & students_per_class & " students per class") On Error GoTo 0 Exit Sub fixErrors: If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") students_per_class = num_students / num_rooms End If Resume Next This code snippet predicts that a divide by zero error may be triggered and fixes it with some user interaction. Once the input is received and the new result calculated, the Resume Next statement returns execution to the line immediately following the line that triggered the error. If you’d like to return to the same line that triggered the error, you can simply use Resume, like this: On Error GoTo fixErrors students_per_class = num_students / num_rooms MsgBox ("You'll have " & students_per_class & " students per class") On Error GoTo 0 Exit Sub fixErrors: If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") End If Resume Using Resume instead of Resume Next has the added benefit of naturally looping until the user inputs a valid divisor for num_rooms. Hidden Role Resume and Resume Next actually play a third role when in the error handling block: once executed, they reset the current exception to Nothing, meaning the active error handler can be used again. Think of the error handler as a single-use tool, which must be reset before it can be used again. We know that ending and exiting the subroutine resets the handler. What this hidden role means is that Resume and Resume Next also resets the error handler. Using GoTo 0 and GoTo -1 With VBA, only one error handler can be active at a time. This makes sense, as an error will immediately trigger the active handler, and VBA cannot guess between multiple handlers. To VBA, an error is an error is an error. You can distinguish errors using the Err object and a Select Case statement, but that’s for our next tutorial. Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Enable and Disable Handlers Use On Error GoTo 0 to completely turn off error handling in a subroutine. This effectively resets the error handling in that sub to the default, which is no handling at all. You may decide to do this when you only have one handler, like fixErrors above, which would not make sense with a different type of error. Alternatively, you may want VBA to warn the user so they can relay error codes to you. Note that you do not need to disable a handler before enabling another. The new one simply takes over. However, for clarity, I like to turn off a handler when its section of code ends. In this code snippet, it’s very clear which handlers go with which parts of the code. On Error GoTo colorPicker Range("A1:A30").Font.Color = colorChoice1 Range("B1:B30").Font.Color = colorChoice2 On Error GoTo 0 On Error GoTo fixErrors students_per_class = num_students / num_rooms MsgBox ("You'll have " & students_per_class & " students per class") On Error GoTo 0 Clearing Exceptions A particular error instance is called an exception. In the following code, there is one type of error but two exceptions, because each instance of the error throws an exception: Range("A1:A30").Text.Color = vbRed Range("B1:B30").Text.Color = vbBlue 'Range("B1:B30").Font.Color = vbBlue 'this is the correct object property When the first error is raised, VBA directs flow immediately to the active error handler. The single error handler rule applies even in the error code! Once an error has been raised, the error handler is completely “full”. In order to handle another error, the handler must be emptied first. Normally this is done by exiting the handler with End Sub or Resume Next. However, there is another method, which uses On Error GoTo -1. Let’s return to our divide by zero handler fixErrors. You could try some calculations in the handler: fixErrors: If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") students_per_class = num_students / num_rooms End If Resume Next But what happens if the user inputs zero again? The full error handler cannot take another exception and leads to a fatal error, killing the program. You can reset the handler - that is, remove the current exception - by using On Error GoTo -1: fixErrors: On Error GoTo -1 'clears triggering divide by zero error If num_rooms = 0 Then num_rooms = InputBox("Re-enter number of rooms") students_per_class = num_students / num_rooms End If Resume Next However, there is a flaw here! The GoTo -1 clears the current exception, and the Resume Next statement sets off an infinite loop. Execution does NOT return to the original code section. Since using GoTo -1 can be extremely confusing, I’d recommend not using it at all. If you are adamant about GoTo -1, use it as a last resort and finish the error handling code with End Sub. Now you can use On Error GoTo 0 and Resume or Resume Next effectively. Unless absolutely necessary, I’d recommend steering clear of On Error GoTo -1, as it can greatly complicate the logic of your program. I’d also recommend judiciously employing On Error Resume Next deferment, because it can mean segments of your code just don’t do anything at all.
>> Read more

VBA Error Handling with On Error GoTo (Thu, 17 Dec 2020)
This tutorial is an introduction to the most basic forms of VBA error handling. We’ll show you how to handle predictable errors using GoTo, how to properly implement and place error handling code, and how to turn handlers on and off. The importance of error handling cannot be understated, especially for production code that goes out to clients. Each error you handle is one less coworker to annoy you when they break your code. If that’s not motivation to practice good error handling techniques, I don’t know what is! Do You Need to Error Handle? Types of Errors Turn on the Handler with On Error Two Main Methods Skipping with Resume Next Handling with Custom Code and GoTo Set Up the Error Handling Code Turn Off the Error Handler with GoTo 0 Placement of the Handler An Alternative Error Handling Approach Conclusion Do You Need to Error Handle? Yes. But as with most things in life, the real answer is more nuanced than that. Do you need to error handle for a quick script you threw together to automate something once? Probably not. If you’re scripting something for yourself, you need to weigh whether or not error handling is necessary. Moreover, you have access to your own code, so you can diagnose and debug on the fly. On the other hand, error handling is vital for any code that you send to other people, especially if you have no contact with those people (so you cannot be on-site to fix it) or you’re selling the code. No one wants to pay for code that constantly throws errors. That’s a surefire way to receive negative reviews and no more revenue. You can reduce the time you spend developing such error handling by maintaining a list of common error handlers. Errors like divide by zero can often easily be rectified with a user prompt or, though dangerous, a pre-coded revaluation of the variable. A warning to the user is useful and informative, so create an error-handler template and add it to every program. Throughout the next few months, we’re going to publish a couple more tutorials on error handling: one for properly using Resume Next and another for using Err object and cascading. To get notified when these are published, subscribe using the form below: Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more. Sure, I'll take a free VBA guide. Programming Language VBA Python Get my free guide Types of Errors There are three types of errors in coding: Syntax Logical Runtime Syntax errors occur because of unfinished lines of code. If you’re using a modern VBA editor, you’ll know right away when a syntax error has occurred. The editor will tell you immediately if there’s a problem and highlight the offending line in red:

Missing closing parenthesis syntax error in VBE Logical errors are algorithmic. These are flaws in your thinking. If you want to calculate the interest for 4 months based on an annual rate, you need to write something like partial_interest = (months_elapsed / 12) * annual_rate But if you write partial_interest = (months_elapsed * 12) / annual_rate then you’ll have a logical error. These are harder to detect since they’re often caused by errors your own logic. Runtime errors occur when the code starts to run. Generally these occur because a variable or object is not as expected. Somewhere in the code a variable assignment breaks the logic. Perhaps a user input zero for available_rooms in this snippet, causing a divide by zero runtime error: students_per_class = num_students / available_rooms An error like this can only occur at runtime and only because some inputs are invalid for the logic. This kind of error is the topic of this tutorial and our next few tutorials. When unhandled, these errors stop your code and start the visual basic debugger. You’re probably familiar with runtime error dialog boxes. They look like this:

Turn on the Handler with On Error By default, there is no error handling in VBA. Runtime errors halt execution and an error message pops up to the user. This is normal behavior and VBA even allows you raise your own custom runtime error messages. Computers cannot think, so they cannot handle errors without instructions. In a section of code that may admit errors - this is particularly common when dealing with variables and objects that have been assigned values by users - you must turn on error handling if you want any preemptive handling at all. To turn on the handler, all you need do is include the On Error statement. What comes after the On Error words depends on which type of error handling you’d like to implement. We’ll dive into this in a few seconds. A bit of terminology: an error handler catches an error and directs execution to code that will rectify the problem (or ignore the problem). An exception is the error instance itself, such as a particular Divide by Zero attempt. Two Main Methods There are two main ways to deal with errors in VBA: skipping runtime errors altogether using Resume Next explicitly handling the errors with GoTo and a custom error handler Skipping with Resume Next Skipping errors with Resume Next is a risky practice, because it basically erases part of your algorithmic logic. On Error Resume Next tells VBA to simply skip to the next line of code when an error occurs. If the next line relies on the previous one and you still have the error handler turned on, VBA will skip the new line, too. And so part of your algorithm disappears into nothingness. You are ignoring the problem. Next month’s article will discuss this in more detail. Handling with Custom Code and GoTo You can also handle the error directly by turning on a specific handler with the GoTo qualifier. This qualifier tells VBA to jump to a specific section further down in the code block. Which handler you turn on is specified by the label, as in GoTo [label], where the label is just a line of code with that name. Let’s walk through an example. Set Up Error Handling Code Let’s say you want print the output of some code into a sheet, and the user can choose which sheet to place it on. If you were being robust you might create a userform with a dropdown menu listing all of the sheets in the workbook, an option to add new ones, and then either jump to the sheet or create the new sheet. But maybe you’re feeling lazy or have a short deadline, so you handle it with errors: Sub output_to_sheet() sheet_name = InputBox("Enter the name of the sheet on which to place output") On Error GoTo noSheetHandler 'turn on the noSheetHandler handler Sheets(sheet_name).Activate On Error GoTo 0 'turn off the noSheetHandler handler 'code to fill in the sheet Exit Sub noSheetHandler: MsgBox ("No such sheet! Try again") 'maybe some other code to handle the error differently 'Resume Next 'uncomment this line to return to the line after the one which threw the error End Sub You don’t turn on the error handling until the line On Error GoTo noSheetHandler. Before this, any errors will halt execution and warn the user. Once enabled, any errors will push execution to the code under the label noSheetHandler: near the bottom of the macro. This example simply ends the subroutine, and the user will have to run it again. It would be wise to perform all the calculations in another subroutine then call output_to_sheet. The calling sub can simply loop until the user inputs the name of an existing sheet. To return to the main section of code after the error handler completes, use Resume Next, which returns execution to the line after the one that threw the error. Note the handler noSheetHandler: is still enabled until you get to the line On Error GoTo 0. Turn Off the Error Handler with GoTo 0 GoTo 0 simply turns off the currently enabled handler (noSheetHandler). Errors no longer push execution flow to that handler. You’ll either have to enable another handler or run with no active handlers, so the code is haulted with each new error raised. Remember to turn off a handler once it is no longer relevant. If you don’t turn it off, a different error that occurs later would automatically try to go to your old label. This old label might be an irrelevant handler to the new error encountered. That makes diagnosing downstream problems very frustrating. Placement of the Handler It’s important that error handling code is placed within the subroutine. For that reason, your label (here noSheetHandler:) must come before the End Sub statement. However, the VBA compiler cannot distinguish what is error handling code. You can, if desired, use labels in other ways. For example, you could do something like this (not recommended for your own sanity): If amount > balance Then GoTo balance_lines 'other code 'other code 'other code balance_lines: 'balance code This is not error handling code at all, but to the VBA compiler it’s all just code. Thus, if you don’t want to run error code every time a subroutine runs, you must place it after an Exit Sub statement. Every error handler should come before the End Sub but after the Exit Sub statement. An Alternative Error Handling Approach You can deal with errors by using On Error, but it can quickly become confusing if you’re using this method for many different types of errors and have many labeled handlers. An alternative method is to check for valid inputs as early as you can to reduce the number of downstream error handling situations that will arise. For example, in the interest example above, it would be possible to use IF statements to check if a user inputs zero and rectify the issue before any runtime error was raised. This is preferred way to handle errors. Error prevention is always better than error mitigation. Checking input variables like this can reduce the amount of error handling you need to write using GoTo. Line jumping with GoTo can give you quite the headache when your code is hundreds of lines long and you’re struggling to debug. Conclusion This was a very basic overview of error handling in VBA using On Error GoTo [label]. Using the label allows you to create custom code to handle expected errors that only runs if an error is raised in its section of code. You now know how to turn on error handling, how to guide execution flow to error handling code, and how to turn off the handler with GoTo 0. Next month we’ll publish an article on properly using the dual-role Resume Next, which is another very popular way to handle errors. Check inputs as often as possible in the code to reduce the number of potential errors in the first place. Checking with IF statements, forcing strings to integers, and similar techniques can help prevent errors without resorting to handling actual runtime errors. To make sure you don’t miss our next couple error handling tutorials, subscribe using the form below and share this article on Twitter and Facebook.
>> Read more

Daily Dose of Excel

Macros with 32-bit API Calls (Sun, 30 May 2021)
I have a fairly slow running macro (~20 seconds) that works fine on one computer, but never finishes on another. If finally decided to figure out why. (Big Daddy is the nickname of the guy who started this golf league way before my time. I am not Big Daddy, although maybe I should be.) First […]
>> Read more

Name Manager updated (Mon, 14 Dec 2020)
Do you use Range names in Excel? Today I have updated my 100% free Excel Name Manager add-in with two new features: – Edit the name’s comment – Filter Lambda names https://jkp-ads.com/officemarketplacenm-en.asp Here’s a screen-shot: Regards, Jan Karel Pieterse jkp-ads.com
>> Read more

RefTreeAnalyser: Two updates (Fri, 04 Dec 2020)
Hi There, It’s been a while since I last posted here. Today I have released an update of my RefTreeAnalyser utility. The tool now allows you to add a Table Of Contents to your workbook which contains lists of all sheets, all charts and all Pivottables: In addition I have added a Feedback button to […]
>> Read more

Opening a File from a Userform Disables Ribbon (Wed, 09 Sep 2020)
In Excel 2019 16.0.10361.20002 32-bit, when I open a file from a userform (which for me is always), the hotkeys on the Ribbon don’t work. Here’s how I reproduce: In a new workbook, add a userform named UserForm1. Add a commandbutton to the userform. Paste this code in the userform’s code module. [crayon-6149f3e14e49f177337473/] Add a […]
>> Read more

Excel Virtually Global (Fri, 17 Jul 2020)
https://excelvirtuallyglobal.com/ Tue 21 July to Thu 23 July 2020 Microsoft’s Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. This virtual conference, now in its fifth year, presents […]
>> Read more

Excel VBA Masterclass (Mon, 27 Apr 2020)
Hi there. Hope you and yours are well in these difficult times! This is just a very short announcement that I’ll be doing an on-line version of my Excel VBA Masterclass. The training is scheduled for May 18, 20, 26, 28, June 2, 4 and I’ll be using Microsoft Teams to deliver it to your […]
>> Read more

International Keyboard Shortcut Day 2019 (Mon, 04 Nov 2019)
The first Wednesday of every November is International Keyboard Shortcut Day. This Wednesday, people from all over the world will become far less efficient for a short time in an effort to be far more efficient the rest of the year. “WASD V2 88-Key ISO Custom” by Bitboxer is licensed under CC BY-NC-SA 2.0 This […]
>> Read more

Combinations, Combinations. And the winner is… (Thu, 19 Sep 2019)
It’s been a great competition and now it is time to announce the winners. Check out my new Excel challenges page to find out which solutions I received and who won the prize for best solution! I hope you enjoyed this challenge. If you have more ideas for challenges, let me know! Regards, Jan Karel […]
>> Read more

Putting Statistics (Mon, 19 Aug 2019)
Because of my recent terrible putting, I decided to keep track of every putt to see what I can see about it. Here’s how I compiled the stats. I wrote down how many paces each putt was and assumed one step was 2.5 feet. The Made formula in the table is [crayon-6149f3e14f3ec404558752/] For that date […]
>> Read more

Combinations, Combinations. A challenge (Tue, 06 Aug 2019)
In case you’re bored stiff during your holiday, here’s a nice Excel challenge for you all. In my newsletter for May 2019 I showed you a trick to get all possible combinations of two lists. This time I have a related problem. Suppose I have 10 ballot tickets. My job is to draw 5 random […]
>> Read more

P3 Adaptive

Calculation Groups to the Rescue! (Wed, 31 Mar 2021)
To set the stage, I need you to travel back in time with me a bit. The year was 2019, and none of us had ever heard of COVID-19…those were simpler times. I was working on a data model that had A LOT of dates in the data table. By a lot, I mean 10 different dates in a single transaction table. I was trying to build a model that would be able to answer any question that the users would ever ask, so I ended up including all 10 different dates. The problem came when I started building out my relationships between my data table and my calendar table. Power BI would only let me have 1 active relationship between the data table and the calendar table…but I had 10 dates that I wanted to relate to the calendar table. As all good developers do, I went to the inter-webs for help.i (In the back of my mind, I knew the answers I was going […]
>> Read more

We’re Changing Our Name (and Nothing Else!) (Sun, 28 Mar 2021)
An overdue change for which there was never a good time In the Fall of 2009, I created PowerPivotPro.com in order to begin blogging about this new technology called Power Pivot. I’d seen what it could do, and knew it was going to change the world. People needed to know about it! So, the blog was born. Separately I knew that sooner or later, I was going to launch a business around this new tech, but hey, that would be a separate site, and PowerPivotPro seemed like a great “call sign” for a blogger, so… “don’t worry about it,” I told myself. “Business URL comes later!” Heh. Famous last words. Here we were, twelve years later, with a name that we outgrew long ago. Good problem to have, really, but we’ve known for many years now that we needed to make a change. WHEN to make the change turned out […]
>> Read more

P3 Adaptive Jobs: Time to Come Home? (Mon, 25 Jan 2021)
Welcome Home: It’s not just a bombastic prog-metal song Continuous hiring mode In P3’s history, we’ve had a bit of a “reactive cyclical” hiring model. Demand for our services would grow, stretching us to the limits of staffing, so we’d add 1-2 new consultants. And those new consultants would instantly take us from “short of capacity” to “excess capacity,” so we’d kinda pause hiring for awhile. Then the cycle would repeat. I think that era is over, and we’re now going to be continually hiring. The value proposition we bring to clients is second to none, and that reputation has a way of getting around over time. Plus we now have a full-time marketing department (I have a team reporting to me again!), and they’re very good at what they do. Apply Here The Best Place to Work If you’re keen on the Power Platform, I think we are THE […]
>> Read more

Planning at the speed of COVID: P3 Adaptive and Power On (Tue, 01 Dec 2020)
It’s not adversity which defines you, but how you respond to it. GPS and Steering Wheel Power BI = GPS. Power On = Steering Wheel. In 2020 we’ve been talking a lot about how important it is to be both intelligent AND agile in the face of unpredictable business conditions. During “normal” times, you can often get away with guessing that tomorrow will resemble yesterday, and simply following your well-worn business map. But 2020 has thrown all of that out the door, and even though 2021 is hopefully kinder, we all know that it will be far from a return to normal. To survive and thrive in chaos, you have to SEE changing conditions clearly and quickly, but then also ACT – decisively. And taking action is a team sport – everyone needs to know their new targets. They need to believe in them. And they need to know how […]
>> Read more

Free Workshop From Microsoft and P3: Modern Excel Analyst In A Day (Mon, 30 Nov 2020)
A FREE Course By P3, In Partnership With Microsofton Tuesday December 8th, 2020 9am-5pm EST Do you have Excel Analysts spending hours wrangling data week after week to provide updated reporting and analytics? Do you have Analysts starting to explore how Power BI can help but they’re in need of instructor-led training & hands-on workshop to gain experience? Are you unsure how Power Pivot, Power Query, Power BI, and Power Platform are all used together with O365, MS Teams, and SharePoint? If this sounds familiar, our team can help! First come, first scheduled in this FREE event, and seating is limited to 50! Experience how Excel, together with Power BI, can empower your analysts to transform repetitive tasks into fully automated, action-driven solutions. Modernize and empower your organization while driving critical business impact. Did we mention that it’s a free workshop? This workshop is perfect for Excel analysts, Power BI […]
>> Read more

Hiring Three New Roles: Copywriter, Designer, and Web Dev! (Thu, 12 Nov 2020)
P3 Adaptive continues to grow, and in the process, our needs grow, too – and not just in the Power Platform department. In particular, we’re an opinionated crew, and we have some things to tell the world. Things that we believe to be valuable. And we’re kinda bottlenecked right now – unable to project those ideas at the appropriate pace and reach. So we’re leveling up our Digital Marketing department, by adding three new full-time positions: Digital Designer Web Developer Marketing Copywriter We’re a fun place to work and there’s a LOT of opportunity for growth and stimulation. If you or someone you know is interested, please apply ASAP. We’re looking to fill all three positions before the end of 2020. All 100% Remote The jobs are listed as Indiana, but pay no mind to that, because all that matters to us is timezone. You don’t even need to be […]
>> Read more

Look mom, we have a Power Platform Podcast! (Tue, 13 Oct 2020)
Data with the Human Element. Three episodes dropped today! Raw Data by P3 Adaptive – “Data With the Human Element” ***UPDATE*** The podcast is now also live on Apple Podcasts We overhauled our website in April of this year, and daaaaamn do I love it. It’s sharp, it’s modern, it’s just a touch edgy – and that represents us more accurately to first-time visitors than our old site did. Our core mission is to disrupt and revolutionize a stodgy old industry, we’ve proven our approach works, and the redesign reflects that side of us quite well. It’s just good branding and marketing to lean into your differentiating characteristics. No complaints. But the human element is ALWAYS the most powerful force in any situation – tech included – and we’re doing the community (and ourselves) a disservice if we neglect it. There is SO MUCH VALUE to be “harvested” on the […]
>> Read more

How Do You Know Your Power BI Security Roles are Right? (Wed, 26 Aug 2020)
You need insights from your data, but you don’t want the wrong people seeing data that’s confidential or outside their responsibilities. In the Power BI service, there’s a robust way of ensuring that the right people get the data that they need for their purposes: row-level security (RLS). It’s a set of filters that only gives people those rows that they need according to their role. These roles are creating in Power BI Desktop and mapped in Power BI Service to users, or better, security groups. I don’t want to talk about the technical aspects of setting up RLS. There’s plenty of resources online on how to set this up (I’ve learned a lot from Reza Rad, for example). Instead, I’d rather open up a discussion about auditing security in Power BI reports. If you can ask, how do we know that we are improving, you should also be able […]
>> Read more

Power BI and Paginated Reports: Better Together (Tue, 16 Jun 2020)
Wise Professionals See the Big Picture Bringing it Home Today we’ll be concluding our 3-part series on Analysis versus Reporting, Power BI versus SSRS and Paginated Reports, and looking closely at our habit of using the word “versus” so liberally: Part One summarized the history of the industry, its long-stagnant roots in Reporting, and the recent “triumph” of Analysis. Part Two examined the relative strengths of Power BI reports and Paginated (SSRS) Reports, and why you need both. Today, we’ll explore how you can use both Power BI and Paginated Reports together, as part of a wholistic system. What Does “Triumph” Mean? I’d like to start by clarifying what I mean when I say Analysis has “won.” Even today, traditional reporting is STILL the majority of the “biomass” in the BI world. For example, right now around the world, there are probably three people using a Traditional report for every […]
>> Read more

Paginated Reporting and Power BI: Why You Probably Need Both (Wed, 10 Jun 2020)
Recap of Part One If you missed Tuesday’s history lesson AKA Part One, don’t worry, here’s the summary: For a long time, Reporting was the dominant force in BI, with Interactive Analysis a distant second This wasn’t because Reporting was better – it was just because Reporting was easier Reporting only provided the business with a pinhole-sized field of view This led to a proliferation of reports AND an overwhelming dependence on Excel Tableau was a breakthrough of Interactivity for the business, expanding the field of view considerably – but still insufficient Power BI marries Interactivity with an underlying Analytical Model, providing the most comprehensive field of view This is a Good Thing, But… Yes this represents tremendous progress! The purpose of BI is to see what is going on, and then use that vision to drive improvement. Pinhole-sized fields of view (Reporting) don’t cut it, and neither do spotlight-sized […]
>> Read more

The Excel Charts Blog

Alberto Cairo’s How Charts Lie: an Alt-Disappointed Book Review (Thu, 31 Oct 2019)
To tell you the truth, I don’t like the word “lie”: it feels obvious and unsophisticated. I prefer something like “reframing truth”, “alternative facts” or an English word I recently discovered, “paltering” (lying with the truth). Wanting to improve my skills in that area, I had great expectations about Alberto Cairo’s most recent book, How ... Read more The original post is titled Alberto Cairo’s How Charts Lie: an Alt-Disappointed Book Review , and it came from The Excel Charts Blog .
>> Read more

Excel: sort + COUNTIF() = utter mess (Tue, 29 Oct 2019)
I’m still in shock. Such a stupid Excel mistake. I should know better, but it was Monday, so… Let me tell you about it. It’s as mistake as old as the hills, but it never goes away, and you are not immune to it. I’ll exemplify with a simple data set. Here is a list ... Read more The original post is titled Excel: sort + COUNTIF() = utter mess , and it came from The Excel Charts Blog .
>> Read more

Horizon charts in Excel [bonus file] (Mon, 18 Mar 2019)
A single horizon chart is easy to make in Excel using overlapping columns or areas (the trick is to structure the data the right way). But the horizon chart is a variation of small multiples, so what makes sense is to stack them to compare multiple entities. That’s problematic in Excel. But many charts can ... Read more The original post is titled Horizon charts in Excel [bonus file] , and it came from The Excel Charts Blog .
>> Read more

Comparing Tableau and PowerBI visuals (Mon, 25 Feb 2019)
I need to learn PowerBI, as soon as possible (per client request). So, I spent much of last week using it. I wrote about the depressing experience on Twitter. I also commented on this post, and its author, Vitali Burla, invited me to show an example of a chart that can be done in Tableau ... Read more The original post is titled Comparing Tableau and PowerBI visuals , and it came from The Excel Charts Blog .
>> Read more

Data visualization: beautiful Paris? (Mon, 18 Feb 2019)
When I saw Paris for the first time I was like, meh. Not Paris’ fault. This was the second leg of a trip that started in Prague, and I was still in a process of digesting the city’s overwhelming beauty. After a couple of days, I was able to enjoy Paris, not in full, but ... Read more The original post is titled Data visualization: beautiful Paris? , and it came from The Excel Charts Blog .
>> Read more

Excel user’s guide to make charts in Tableau (Mon, 21 Jan 2019)
How do Excel and Tableau compare when actually making a chart? I couldn’t find such post, so I wrote one. I’ll create a simple chart, a population pyramid, and comment on the process. To make it a bit more interesting, we’ll compare a certain population in 1986 with the estimates for 2050. The Data Let’s ... Read more The original post is titled Excel user’s guide to make charts in Tableau , and it came from The Excel Charts Blog .
>> Read more

Wordless instructions for making charts: Tableau Edition (Thu, 10 Jan 2019)
After creating wordless instructions for making charts in Excel, here is the Tableau version. This post discusses similarities and differences between both tools. Check out the e-books at the bottom! How to make a chart To make a chart, you must select the data, encode the data into visual objects, format those objects, and add text ... Read more The original post is titled Wordless instructions for making charts: Tableau Edition , and it came from The Excel Charts Blog .
>> Read more

[New ebook] Wordless instructions for making charts in Excel (Wed, 10 Oct 2018)
An ebook that uses visual instructions to show how to make charts in Excel. Non-English speakers should be able to follow these instructions. The original post is titled [New ebook] Wordless instructions for making charts in Excel , and it came from The Excel Charts Blog .
>> Read more

12 ideas to become a competent data visualization thinker (Fri, 27 Oct 2017)
It began with a tweet: Data tweeps: Help! I need to become a competent data viz thinker, well, immediately. Are there “must-read” sources that y’all can suggest? — Lindsey Leininger (@lindsleininger) September 27, 2017 In spite of being a notorious Excel Brute Forcer (thanks, Elijah!), I was invited for a presentation at JMP and was working on ... Read more The original post is titled 12 ideas to become a competent data visualization thinker , and it came from The Excel Charts Blog .
>> Read more

A companion post to my NTTS2017 presentation (Tue, 14 Mar 2017)
This post summarizes a few key points in my NTTS2017 effective data visualization for statistical offices The original post is titled A companion post to my NTTS2017 presentation , and it came from The Excel Charts Blog .
>> Read more

ADVANCEd Excel Access databases VBA macros SQL scripts BI Reports data integrity big data data science data mining data mapping data profiling data visualization data modeling financial analysis SCORE CARDS BENCHMARKS PERFORMANCE KPI METRICS

Print Sitemap

Logout Edit page