Ray Erden M.A. Business Economics Sr. Data/Business Systems Analyst Business Intelligence Reporting  
                                           Ray Erden                              M.A. Business Economics                 Sr. Data/Business Systems Analyst                                                        Business Intelligence Reporting                                           

Select Excel Feeds

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.

 

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

By: carol (Fri, 19 Jun 2020)
my labtop was using Microsoft Office 365 ProPlus. How come i still do not see the option for me to select "PDF" under Data-> Get Data -> From File
>> Read more

By: Saravanan (Thu, 28 May 2020)
What do I do when I click the refresh button on the load data, it gives an error message - "The key didn't match any rows in the table"?
>> 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 Blog

Use Power Query to Manipulate Your Data (Thu, 07 May 2020)
Power Query (Get & Transform) is a powerful yet easy technique for manipulating data in Excel. This example shows how to prepare data for a box plot. The post Use Power Query to Manipulate Your Data appeared first on Peltier Tech Blog.
>> Read more

Dynamic Arrays, XLOOKUP, LET – New Excel Features (Tue, 05 May 2020)
In this tutorial, learn new Office 365 features to help prepare data for charting. Dynamic Arrays, XLOOKUP, and the new LET function make data manipulation easy. The post Dynamic Arrays, XLOOKUP, LET – New Excel Features appeared first on Peltier Tech Blog.
>> Read more

Watching my Weight with SPC (Statistical Process Control) (Tue, 28 Apr 2020)
In this tutorial I use records of my weight to illustrate statistical process control techniques (I charts and MR charts) for tracking data. The post Watching my Weight with SPC (Statistical Process Control) appeared first on Peltier Tech Blog.
>> Read more

Assign Chart Series Names or Categories with VBA (Wed, 12 Feb 2020)
This tutorial shows VBA code to find ranges or allow a user to select ranges that have series names or category labels and apply them to the active chart. The post Assign Chart Series Names or Categories with VBA appeared first on Peltier Tech Blog.
>> Read more

Hour by Hour Flow Chart (Mon, 03 Feb 2020)
This article shows two ways to create a flow chart that displays how many visitors enter and leave a museum during an hour alongside hour-by-hour totals. The post Hour by Hour Flow Chart appeared first on Peltier Tech Blog.
>> Read more

Jon’s Toolbox – A New Utility from Peltier Tech (Thu, 23 Jan 2020)
Jon's Toolbox is a simple Excel VBA add-in that provides a handful of useful functions. I use it when I am preparing or teaching classes, writing blog posts, and working on projects. The post Jon’s Toolbox – A New Utility from Peltier Tech appeared first on Peltier Tech Blog.
>> Read more

Bar lengths on a chart, what do they even mean? (Thu, 09 Jan 2020)
In this article I analyze unusual bar lengths in a newspaper chart, create a chart with appropriate bar lengths, then propose a few better alternatives. The post Bar lengths on a chart, what do they even mean? appeared first on Peltier Tech Blog.
>> Read more

Order of Series and Legend Entries in Excel Charts (Tue, 31 Dec 2019)
In Excel charts, series and legend entries appear in a particular order. This article explains this order and how to make it work the way you want. The post Order of Series and Legend Entries in Excel Charts appeared first on Peltier Tech Blog.
>> Read more

Invert if Negative Formatting in Excel Charts (Thu, 05 Dec 2019)
In Excel column and bar charts, positive and negative data can be formatted differently using a feature called Invert if Negative. The post Invert if Negative Formatting in Excel Charts appeared first on Peltier Tech Blog.
>> Read more

Geography and Maps in Excel (Wed, 06 Nov 2019)
Excel has introduced Geography Rich Data Types and Filled Maps, making it easy to acquire, analyze, and visualize geographical information, as shown here. The post Geography and Maps in Excel appeared first on Peltier Tech Blog.
>> Read more

The VBA Tutorials Blog

VBA MsgBox Yes No Options (Tue, 30 Jun 2020)
Binary yes and no responses are often give you all the information you need from your users. The built-in VBA MsgBox with Yes or No options is designed for this exact scenario. There is one caveat with binary choices, though, which we will cover in this tutorial, too. VBA MsgBox vbYesNo or vbYesNoCancel Reading the Output of the MsgBox Function The Value of Yes, No and Cancel Functional Programming With MsgBox VBA MsgBox VBA’s built-in MsgBox function provides a method for user input in a predefined dialog box. You can create your own userforms for this purpose, and if you are designing a professional-grade branded dashboard, I’d recommend doing so. Once you’ve designed a custom userform, you can control when to show the userform in your program. Most applications don’t need fully customized userforms for basic yes/no questions. If you just need simple, quick solution, the built-in VBA MsgBox dialogs are the better option. They give the popup a natural feel since the MsgBox is a native VBA function, and you know the visuals will work on the first try. VBA message boxes take 5 parameters, but all except the first one are optional: Intellisense for VBA MsgBox function Prompt is the text which appears on the popup box. Since this tutorial focuses on yes or no questions, we’ll assume your prompt is going to take on a structure that requests a yes or no response. Is the entered name correct? There are 87,000 records; do you want to proceed? You can put any string in the Prompt field. Often you’ll use concatenation with a variable, like we demonstrated with our VBA Concatenate article. If you want Yes or No to be the choices for your users, you must also pass the Buttons parameter. Enter vbYesNo in the Buttons parameter to display Yes and No buttons. Let’s try it with this code: MsgBox "This process will take about 15 minutes. Do you want to proceed?", vbYesNo Tip: You can use the VBA immediate window (shortcut Ctrl+G from the VBE) to quickly run this macro. VBA Yes No MsgBox Example Now the user can decide whether to proceed right away or delay their response. Notice, there are only two possible choices. The “X” in the upper right of the window is disabled so the user can’t click it. The best free VBA training on the web I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free. Let me join the wellsrPRO VBA Training program for free Programming Language VBA Python Join us (for free) vbYesNo or vbYesNoCancel When you ran the code above, you probably noticed that there is no way to “X” out of the dialog box. The user must answer Yes or No, and they have no alternative. The only other choice is to delay the process by not choosing Yes or No, but eventually an answer must be chosen. They could also kill the Excel instance, then complain that Yes and No are not adequate for all scenarios… So, if you want to force your users to make a binary Yes No decision, then you can use vbYesNo as your Buttons parameter. Most of the time, this is acceptable. If you are verifying the content of an inputbox this way, the user may answer No to the prompt and the code could fetch the input again until the answer is Yes. Alternatively, a No response may stop execution entirely and exit the subroutine. Passing vbYesNoCancel to the Buttons parameter provides a third option for the user. It adds a Cancel button and enables an “X” in the upper-right of the dialog box. Clicking the “X” does the same thing as choosing the Cancel button. Sometimes users feel more comfortable pressing “X” than “Cancel” so both options are available. MsgBox "This process will take about 15 minutes. Do you want to proceed?", vbYesNoCancel Yes No Cancel MsgBox Reading the Output of the MsgBox Function In VBA, subroutines don’t have an output value but functions do. Because MsgBox is a function, it also has an output value. Intuitively this makes sense: if there were no output values, how would the program know which choice was made? The output values weren’t captured in the codes mentioned above. VBA received the value chosen (Yes or No (or Cancel)), stored it in memory for a brief moment, then ended the subroutine and threw the value away. To store the value, assign the function to a variable. The code (enforcing Integer typing) will look like this: Sub vbYesNoDemo() Dim userResponse As Integer userResponse = MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNo) End Sub Make sure to use the parentheses to encapsulate the MsgBox function parameters on the right side of the assignment. The Value of Yes, No and Cancel The output from the native MsgBox function is neither a string nor a binary value, as one may suspect, but an integer. This makes it easier to process programmatically, and it is simpler to remember just three values than an exact string (is it “Yes” or “yes” or “y” or “YES”?). It also allows MsgBox to have multiple outputs other than just Yes and No (for which a Boolean value would suffice). The values of the three buttons here are: Yes = 6 = vbYes No = 7 = vbNo Cancel = 2 = vbCancel Using the “X” on the vbYesNoCancel version also produces 2. Technically, the vb Buttons parameter itself also represents a numeric value, but it’s much easier to use the proper vb Names (vbYesNo and vbYesNoCancel. By capturing the output of the MsgBox function like we did in the example above, you can use userResponse as you would any other integer. You can compare its value in if statements, use it in Select Case blocks, or even add numbers together if you want. Functional Programming With MsgBox Instead of storing the value in a specific variable, it’s perfectly acceptable to use the output value as a temporary memory value, which is thrown away as soon as a specific line of code is finished being evaluated. This is called functional programming, because what is normally stored as a variable becomes the input to another function, like a series of nested functions. It is not always the clearest code, but it is certainly compact and reduces the need to name and store variables. For instance, instead of using this code block: Sub vbYesNoDemo2() Dim userResponse As Integer userResponse = MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNo) If userResponse = 6 Then 'proceed Else 'retry End If End Sub You could shorten it a bit like this: Sub vbYesNoDemo3() If MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNo) = 6 Then 'proceed Else 'retry End If You wouldn’t ever need to think about how to type the output value (Integer, Boolean, etc.). Conveniently, if you are writing the condensed form and have Intellisense enabled, you’ll even get the proper vb names of the potential outputs of the MsgBox function. VBA MsgBox Output Values You can use these vb names in your conditional statements instead of remembering the integers, if you prefer. Here’s an example: Sub vbYesNoDemo() Dim userResponse As Integer userResponse = MsgBox("This process will take about 15 minutes. Do you want to proceed?", vbYesNoCancel) If userResponse = vbYes Then 'Yes button clicked ElseIf userResponse = vbNo Then 'No button clicked Else 'Cancel button clicked End If End Sub Binary responses from users are very commonly needed by programs that take user input. For example, they can verify previously input information and they can allow users to stop long processes before they start. The VBA MsgBox Yes No examples in this tutorial provide quick dialog boxes for scenarios like this without the need to build your own userforms from scratch. If you haven’t already done so, consider subscribing using the form below to get more VBA tips like this sent to your inbox.
>> Read more

VBA IsNull to Trap Errors and Find Unfilled Values (Fri, 05 Jun 2020)
As developers, a smooth-running program is our ultimate goal. If we can write a macro such that the program runs with minimal input and handles its own errors, we have succeeded - of course, assuming the output is what we wanted. A frequent mistake is not catching easy-to-anticipate errors, like a null variable being passed into a function and throwing an error or outputting unexpected results. Let’s explore the Null value, its uses, and a couple applications for your own programs. What is Null? What Data Types can be Null? Catching Unfilled Values with IsNull Catching Data Variation with IsNull Catching Errors with IsNull What is Null? Null is used to indicate “invalid data” or unknown values. This is generally applied in the context of databases, where a field may be left blank when it’s optional. This differentiates that value from 0, an empty string "" or “nothing” values. As we know, 0 represents some information. It’s numeric, it non-negative (and non-positive), and it has a particular value (precisely between -1 and 1 on the integer spectrum). However, an optional field may change as more information becomes available. To indicate this potential for change, Null acts as a placeholder until more information surfaces. In VBA, some functions or properties may output Null as well. This is the “invalid data” aspect of Null. For example, the following line of code normally outputs the color value of the highlight range: rangeFontColor = Range("A1:D5").Font.Color If all the cells have the same font color, rangeFontColor will give you that value. Conversely, if some of the cells have font of a different color than others, this property does not know what to return. If some cells have black font while others are red, should this property return 0 for black or 255 for red? Surely we do not want the property to produce an array of values, as this would be very difficult to anticipate. It could be a 1-element array, a 3-element array, or any other number of elements. Thus, in this example, the data is “invalid”, and the property will read Null. WhatData Types can be Null? In VBA, there’s a single variable type that accepts Null as a value: Variant. If you try to assign Null to any other variable type (Integer, String, etc), you’ll receive an error. I don’t recommend typing all your variables as Variant, though. Since Variant can take on any data type, it must have all the structures available to it and thus has a larger memory footprint than a type like Boolean. More importantly, though, narrowly typing your variables can help you enforce code integrity and find mismatched data types at compile time to avoid runtime errors. With that said, Variants have their place in code and can be useful for finding unfilled fields in databases (including those implemented in Excel sheets). Catching Unfilled Values with IsNull For this first example of Null, let’s use the customer spreadsheet from our Find and Replace Cells with VBA post. You can download the CSV here. In this sheet, though, let’s assume a customer made an order by phone and it was undecided which shipping method would be used. Because of that, the final Amount column has been left blank until the customer confirms the preferred method tomorrow. For this example, edit the CSV file to take out a customer’s payment amount in Column E, like this: VBA IsNull on Empty Cell Now, let’s say you want to write a macro that does a basic data integrity check on your data to see if any bits of information are missing. We can assume all orders must have an Order Number in column A, but other fields are optional. One way to do this is to simply count the number of entries for each column and assign a separate variable to be Null if it has fewer entries than the Order Number column, which by definition must be filled. Then, because IsNull will return TRUE if any part of an expression is Null, you can test them all at once. The full code block looks like this: Sub basicDataIntegrityCheck() Dim orderCount, nameCount, methodCount, amountCount As Integer Dim oC, nC, mC, aC As Variant orderCount = WorksheetFunction.CountA(Columns("B")) nameCount = WorksheetFunction.CountA(Columns("A")) methodCount = WorksheetFunction.CountA(Columns("D")) amountCount = WorksheetFunction.CountA(Columns("E")) If nameCount < orderCount Then nC = Null If amountCount < orderCount Then aC = Null If methodCount < orderCount Then mC = Null If IsNull(aC + mC + nC) Then MsgBox ("Some fields are empty") End Sub The best free VBA training on the web I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free. Let me join the wellsrPRO VBA Training program for free Programming Language VBA Python Join us (for free) Now, why would you use this method instead of simply writing an if-statement for each column, like this? If nameCount < orderCount Then MsgBox ("Some names are missing") With the variables aC, mC, nC, you can now use these variables in other parts of your macro. Perhaps you want to send these numbers off to another module to do some calculations. It’s advantageous to write the assignment once then use stored values rather than having to re-evaluate the CountA functions in another module. Catching Data Variation with IsNull The other major source of Null values may fall out of the evaluation of built-in VBA functions or properties that are not producing expected results - hence the “invalid data” piece of the VBA IsNull function. Using the same data set from above, let’s say you instruct employees to highlight any tentative details, such as the shipping method, with a different background color. To check whether any such highlights exist, you can write a simple one-liner to test for variation in cell background colors: If IsNull(Range("A:G").Interior.ColorIndex) Then MsgBox ("Some data is tentative. Check manually") Conditional statements like If IsNull are probably the most common way people use the VBA IsNull function. Notice in this case you don’t have to assign the output to any variable at all and can simply feed the output of the .ColorIndex property to the IsNull function. Catching Errors with IsNull Sometimes our programs aren’t built to anticipate what a user will do, so they error out when encountering something odd. Catching errors is very important for user experience, though, even if it is a thankless job. For example, if we wanted to write the color of the A:G range from our spreadsheet, the .ColorIndex property seems like the perfect way to identify it. Consider the following code and think about why it’s not advised: Dim bColor As Integer bColor = Range("A:G").Interior.ColorIndex If any cell in columns A through G have a different background color than all the other cells, your program will throw a “Run-time error ‘94’: Invalid use of Null” error. This happens because the computer is trying to assign Null to an integer-typed variable. Remember, only Variants can be set to Null. It’s certainly useful to type bColor as an Integer, but withour a robust code, your user may call you up asking why there’s an error. To avoid that call and the associated debugging, use error trapping with IsNull and handle the issue in the background without ever notifying the user. Sub IsNullErrorTrapping() Dim bColor As Integer If IsNull(Range("A:G").Interior.ColorIndex) Then 'code to handle issue Else bColor = Range("A:G").Interior.ColorIndex End If End Sub To recap, the Null value in VBA is used for missing or invalid data, and it has close connections to database entries. You can only assign Null values to Variant data types. Otherwise, you’ll get a “Run-time error ‘94’: Invalid use of Null” error. Properties and many functions can also result in Null values, and that makes the VBA IsNull function useful for trapping errors and handling them in the background to avoid interrupting the user. As with most programming tasks, the use of Null can require a little creativity, and thinking in this way will improve your program user experience (UX). To get more free VBA training tips like this, join us using the form below.
>> Read more

Find and Replace Cells with VBA (Fri, 08 May 2020)
Some spreadsheets contain only a single sheet of data in a few columns and rows. Other spreadsheets contain tens or even hundreds of thousands of datapoints. The latter type often act as miniature databases for smaller businesses or teams within companies where building a full database is unnecessary. Often the datapoints are automatically populated, and they are not always checked for integrity before loading. Sometimes things just change and we need to globally update the information in a spreadsheet. Whatever the reason, Find and Replace is an invaluable tool for anyone working with spreadsheets. The Excel GUI provides an easy-to-use dialogue to find and replace content throughout a spreadsheet, for both the data and the formulas. But what if, somewhere in the pipeline between origination of the data and its final residence in the spreadsheet, a flaw in the data is algorithmically introduced? No one wants to do exactly the same Find and Replace operation every time data is loaded or entered. This is a programming blog, and around here we like to automate tasks, no matter how simple. It helps cut down on unnecessary, tedious work, but it also ensures our data is manipulated reliably. So, today let’s look at how to programmatically apply Find and Replace with VBA. Our Dataset The Inefficient Approach The .Replace Method Expanding the Range The Other Parameters Lookat MatchCase Other Parameters Our Dataset Throughout this tutorial we’ll manipulate the data in this screenshot. You can download the CSV file here, if you’d like. It isn’t necessary to have all the data locally in your own workbook, but it serves as a good visual aid: Screenshot of Dataset A screenshot of the dataset The Inefficient Approach I want to start off with an inefficient approach, which is really just brute-force string manipulation. This is certainly not the most efficient, but by using looped string manipulations and if statements you could check every cell explicitly. For example, let’s say you import customer information daily, and the currency label resides in Column F. For some reason, the supplied information used Australian dollars (AUD) instead of Canadian dollars (CAD) for your Canadian orders. Most orders are in US dollars (USD), but you do have some Canadian customers. You do not ship to Australia, so you don’t accept AUD. In this case, to ensure data consistency, you want to replace every instead of AUD with CAD. This is a very straightforward task, and you can write and apply this three-liner in about five seconds: Sub naiveApproach() For i = 2 To 5000 If Cells(i, 6).Value = "AUD" Then Cells(i, 6).Value = "CAD" Next i End Sub However, as your dataset grows, this method can become time consuming. It also makes it harder to expand to multiple columns and rows. Fortunately, there is a simpler and more elegant solution. The best free VBA training on the web I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free. Let me join the wellsrPRO VBA Training program for free Programming Language VBA Python Join us (for free) The .Replace Method One of the methods attached to the Range object is the .Replace method, which does exactly what you would expect. Be wary that this function is the GUI equivalent of “replace all” within the range, not just “replace the first instance”. In our currency example, we want to replace all instances anyway, so this is fine. Instead of using a loop, you can set the entire range first and Excel will use its optimized internal code to most efficiently find and replace the errant AUDs. The code is a simple one-liner: Range(Cells(2, 6), Cells(5000, 6)).Replace "AUD", "CAD" And in the simplest terms, this searches the sixth column between rows 2 and 5000, replacing any instance of “AUD” with the letters “CAD”. In letter-reference notation, you could use: Range("F2:F5000").Replace "AUD", "CAD" It’s important to recognize the Range.Replace method is different from the VBA Replace function, which is used to replace substrings within a string. Expanding the Range Of course, if you don’t know where in the spreadsheet certain information occurs, you can expand the range to anything you want. Perhaps your friend called you and said they accidentally entered “AUD” instead of “CAD” for every Canadian purchase. They need your help to fix the mistake! Moreover, they can’t show you the spreadsheet because it’s proprietary. Well, you could tell them to write this line of code, which covers a gigantic area, captures every instance of AUD, and turns it into a CAD: Range("A1:ZZ200000").Replace "AUD", "CAD" Note: if your friend’s spreadsheet is bigger than this, they need to switch to a real database… But alas! This has also changed “Audio” into “CADio”! What a disaster. The Other .Replace Parameters One pitfall of the .Replace method is that it is non-case-sensitive and replaces any matching string, regardless of whether that string fully matches. Thus, Audio is converted to CADio by mistake, because the three letters A-U-D are matched non-case-sensitively. You can use the other parameters in the .Replace method to avoid issues like this, even when searching large areas. In this section, I’ll use the named parameter method (:=) of setting them, since there are so many parameters and multiple empty commas is unsightly and a bit confusing. LookAt Optional Argument The first optional argument is LookAt, which accepts two options: xlWhole or xlPart. The function defaults to the latter, so when we searched for AUD, it rewrote any string that contained those three letters in sequential order. One way to avoid the debacle for our friend is to ensure this parameter is set to xlWhole: Sub VBA_Replace() Range("A1:ZZ200000").Replace What:="AUD", Replacement:="CAD", LookAt:=xlWhole End Sub Now the full word must match for a replacement. Since “Audio” is not exactly the same 3 letters as “AUD”, we don’t end up with “CADio”. If we had an option Audio Audi, xlPart will match both “aud” strings in both words, giving us CADio CADi. Luckily, you can easily avoid this now with LookAt:=xlWhole. MatchCase Optional Argument Another approach in this situation could be to match cases. Since we are searching for all uppercase currency codes, and we know the Item Type column is proper case, we could force the case to precisely match to get the same outcome: Range("A1:ZZ200000").Replace What:="AUD", Replacement:="CAD", MatchCase:=True A quick quiz for you: if we truly did not know the stylization of the data, would it be better to use LookAt or MatchCase? Answer: LookAt, which forces the entire string to match. There is less chance that non-currency entries will be exactly the three letters A-U-D in that particular sequence, while it is very possible “AUD” might appear in multiple words, whether they’re capitalized or not. Had the Item Types used capital letters - as in AUDIO, PERIPHERAL, etc. - using only the MatchCase parameter would still have left us with CADIO. Other Parameters For finding and replacing strings in ranges, the other parameters are not important. Whether you choose to search by columns or by rows in SearchOrder is irrelevant for .Replace, because it will replace whatever it finds in all columns and all rows, regardless. Moreover, formatting is related to a completely different function and is not necessary for finding and replacing strings as we’ve explored here. Replacing all instances of a string of text can be quite useful, particularly to fix mistakes or to update spreadsheets. However, it is important to think through how optional parameters might affect your output, because there is no simple undo button when using VBA. You certainly don’t want to make a mistake that affects 200,000 cells and cannot easily be undone. If that worries you, I recommend saving your workbook before using .Replace. Or at least test it on a smaller range. You can remind your user to save, too, by running some code to programmatically initiate a Save As Dialog, if you want. To learn more VBA tips like this one, subscribe using the form below.
>> Read more

Hiding Sheets with VBA xlSheetHidden and xlSheetVeryHidden (Fri, 03 Apr 2020)
In keeping with our recent theme of making workbooks easier to navigate (hiding columns, deleting columns), this post will take a look at the .Visible property of sheets and its three possible values. This property lets you hide and unhide sheets with its three options: True, xlSheetHidden, and xlSheetVeryHidden. Knowing how to hide sheets with VBA is valuable if you need to clean up the user interface or protect calculations to prevent accidental modification. Boolean Properties The Unhide Dialogue Hiding Sheets More Thoroughly Protecting from Prying Eyes A Note on Security The Usual Boolean Property You may have noticed that many VBA object properties are Booleans. This means they can only be True or False. Common Boolean properties include .Hidden for columns/rows, font properties like Font.Bold, and text properties like Cells.WrapText. These can only take Boolean values, and that intuitively makes sense. How can the Bold property of a font be any value other than True or False? To determine whether a property is likely Boolean, you can ask the simple question “Is the object property?” For example, “is the font bold?” or “is the column hidden?”. If the answer is naturally yes/no, then it’s most likely a Boolean property. The [Sheet].Visible property surely fits this heuristic: is Sheet1 visible? It is natural to answer this question with a yes or no. To complicate it even more, the values True and False can certainly be assigned to the sheet object’s .Visible property. You can hide a sheet using the [Immediate window] (press Ctrl+g in the VBE to open it) by executing this code: Sheet1.Visible = False and Sheet1 will be hidden from view in the tabs at the bottom of the Excel window. To show it again, set the property to True: Sheet1.Visible = True and Sheet1 will reappear. If you have Intellisense enabled - which I recommend for everyone, but especially those learning VBA - you might have noticed that [Sheet].Visible does not display True and False as its options, though. The Unhide Dialogue One reason to hide sheets is to clean up the interface so it’s easier for users to navigate. This applies to complex workbooks wherein a single sheet handles inputs and a single sheet for results suffices to satisfy user needs, while perhaps 5 sheets store useful historical data and 3 set up and perform calculations. The 8 data and processing sheets are not really necessary for the user, and it can clutter up the display or even confuse inexperienced users. By right-clicking on the sheets group at the bottom of the Excel window, users can look at hidden sheets. That is, they can see a list of sheets whose .Visible property is set to False. They can then unhide those sheets to look at or change them. Unhide Dialogue The Unhide Dialogue, which lists hidden sheets and allows the user to re-display them What if you want to make sheets more hidden? For example, if you want to prevent users from accidentally - or maliciously - changing formulas. To do this, we can use the third legal value for the .Visible property of sheets, xlSheetVeryHidden. Hiding Sheets More Thoroughly To avoid issues with curious users who may want to see what sheets are hidden, you can use VBA to obscure especially sensitive sheets from a GUI-user’s view. Instead of setting the .Visible property to False (which is the same as setting it to xlSheetHidden), you can set it to a third value: xlSheetVeryHidden. This makes the sheet invisible not only on the tab tray but also in the Unhide Dialogue, so users can’t even unhide it that way. Hide the sheet graphically in VBE with the Properties window (usually shortcutted via F4 while the VBE is open). Change the Visible property manually via the red box here: Properties Window for Sheet1 The Properties Window for Sheet1 with Visible property highlighted Or, of course, you can set the .Visible property in code: Sub HideSheetExample() Sheet1.Visible = xlSheetVeryHidden End Sub This example uses the VBA CodeName Sheet1 to hide the sheet, but you could also spell out the sheet name, like Sheets("Sheet1"). The only way for a Very Hidden sheet to be unhidden is through the VBA editor. For less-skilled users, they might not even know how to enable the Developer tab, while others may not know how to use VBA. This certainly is not a secure solution against an adept attacker, but it will likely keep most coworkers and clients from unhiding and inadvertently breaking sheets. There are shorter ways to make a sheet very hidden, though. The value xlSheetVeryHidden is the same as xlVeryHidden, just like a value False is the same as a value of xlSheetHidden, which is the same as a value of xlHidden. You can use whichever nomenclature you like! Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit Protecting from Prying Eyes If you want, you can set passwords on VBA Projects through the Tools > VBAProject Properties... menu item (blue box below). On the Protection tab, add your password and check the Lock project for viewing checkbox (red box). When you close the Excel file and re-open it, the project will be collapsed in the Project Explorer window and no one will be able to view the object properties or code modules without entering the password. Password Protection Window with VBAProject Properties Password Protection Window with VBAProject Properties A Note on Security If you’re worried about users maliciously changing the sheets or stealing intellectual property, using a password to protect the project is not very secure. If you have highly sensitive data on hidden sheets, I’d recommend another approach. If you are simply comparing inputs, at least store hashed data instead of plaintext. If you need to operate on the data, consider a database connection that Excel can query every time it needs to operate on the data. For most office applications, a very high level of security is not necessary, and password protecting your VBA project will suffice. If you need more security on your spreadsheets, I typically recommend Unviewable+. It’s not invincible, but it does a good job protecting against the most common hacking techniques. Hopefully this tutorial taught you how to hide, unhide, and make sheets very hidden using VBA. It may have surprised you that .Visible is not a Boolean property, since most properties are. With a Very Hidden sheet, you can stop some curious users from seeing sensitive information or accidentally changing formulas on sheets for calculations. If you want, you can even password protect your VBA projects now, but bear in mind that a weak password may be easily broken. And if you have to protect data from truly malicious actors, I would recommend another approach. The average curious office user, though, will likely be thwarted by password protection and Very Hidden sheets. To learn more VBA tips like this one, subscribe using the form below.
>> Read more

How to Delete Columns with VBA (Fri, 06 Mar 2020)
We recently showed you how to hide columns (and rows), which is helpful for making spreadsheets more readable. However, sometimes you may want to completely delete a column instead of just hiding it. In this post, we’ll teach you how to remove columns entirely. You may find yourself wanting to delete entire columns if they contain outdated data or bad formulas. Deleting columns can also be used to move objects, such as charts, to the left as columns disappear. This may be applicable if you create a chart programmatically far off to the right of the data then remove all of the data. Instead of repositioning the chart directly, you can bring it closer to left side. Note that if columns containing a chart are deleted, the chart will be reduced in width equal to the deleted column, which can delete the chart entirely if a single column underlays the entire chart. Clear vs. Delete Deleting a Single Column Deleting Multiple Columns Letter-reference Style Build a Range The Optional Shift Parameter What Happens to References Clear vs. Delete The first question to ask before using the .Delete method on a column (or any range, really), is whether .Clear might be more suitable. Deleting columns is more likely to lead to errors in formulas and cause unintended consequences as objects and data shift left across the sheet. The VBA Clear method can be applied to data, formulas, or formatting individually, whereas deleting simply removes all traces of that column, its data, its formatting, and any references to it. Moreover, clearing source data will leave the scaling of a chart intact, while deleting a source column destroys the underlying series for the chart and can lead to undesired results. In many instances, .Clear will do what you need and it’s less likely to wreak havoc on your sheet. However, .Delete has legitimate uses and there are certainly a few situations out there that favor deleting a column over clearing it. One such case may be removing all intermediate columns before delivering the final results to a client. Deleting a Single Column It’s important to recognize the difference between a single Column object and a collection of Column objects. The singular term Column refers to a single column, while the plural Columns refers to the set of all columns on any particular sheet. To reference any column, you must specify its index in the collection. Logically, the index in the collection equals the placement on the page, so Column C, the third column, will have an index of 3. Thus, to delete a single column, say Column Z, the 26th column, simply reference it in the collection and use the .Delete method: Columns(26).Delete Fortunately, you don’t have to convert column letters to numbers each time you want to delete a column. It’s also possible to reference Column 26 by its associated letter: Columns("Z").Delete Make powerful macros with our free VBA Developer Kit 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 Kit full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit Deleting Multiple Columns Since referencing an index in the Columns collection specifies a single Column object, we cannot use single indices to reference more than a single column. Separating indices by a comma will throw an error: Error Message for Comma Separated Columns Error message when attempting to separate columns with commas There are two ways to reference more than a single column for deletion: one is to use letter-reference style and the other is to create an overarching range object. Letter-reference Style This is the quickest method if you know exactly which columns to delete, the columns are adjacent, and they do not need to change programmatically. It’s not as easy to change a letter-referenced column using VBA as it is to change a numerically-referenced column. If you have a certain range that will always need to be deleted, though, it’s quite simple to hardcode the columns letter-reference style: Columns("B:E").Delete Build a Range If you need to programmatically determine the columns to delete, it’s not that useful to hardcode the columns in letter-reference style. In that case, it’s better to use number-reference style. However, you cannot simply list the column numbers in the Columns collection call, as that produces the error above. Instead, you must build a range and call .Delete on that range. To build the range, just wrap the Column objects in a Range line: Range(Columns(firstColumn), Columns(lastColumn)).Delete where firstColumn and lastColumn are integers to be set before this line is processed. Range() will always envelope an entire region, so if you need to delete Columns 1 through 3 and Columns 6 through 8, you’ll need two Range objects, one for the first set and one for the second set. You can combine the ranges using VBA Union so your code can still be written with one line, like this: Sub DeleteNonAdjacentColumns() Union(Range("2:4"), Range("6:7")).Delete End Sub or Sub DeleteNonAdjacentColumns2() Union(Range("B:D"), Range("F:G")).Delete End Sub The Union command is the way to delete non-adjacent columns using VBA. The Optional Shift Parameter There is an optional parameter for the .Delete method: [Shift]. When a specific cell is deleted, the surrounding cells must either shift upwards to fill in the space or left to fill in the space. For example, if the cell B3 is deleted, either the cells in Column B should move up or the cells in Row 3 should move left. In this case, the programmer can choose which occurs with xlShiftUp and xlShiftToLeft. For Columns().Delete, however, this is irrelevant. Since the entire column is deleted, the only option for Excel is to shift the other columns beyond it to the left. Thus, when you delete a Column (or a Row), there is no need to assign a value for Shift. What Happens to References Deleting a column removes any trace of the original, which is replaced with columns from the left. When you move cells containing formulas around the page through the GUI, cell references are re-calculated to remain accurate with the new arrangement. However, with .Delete, this doesn’t happen. If a formula references a column that is subsequently deleted, the formula breaks and throws a #REF error, as its original reference cell no longer exists. For this reason, it is always prudent to consider using .Clear before jumping straight to .Delete, as the latter can easily break formulas. For complex models, this can destroy the entire workbook as the #REF error cascades through it! Even worse, this #REF error cannot be reversed by inserting new columns for the deleted ones, and the Undo command does not apply to VBA-initiated actions. Once a #REF error is thrown, it can be difficult to fix, especially if the deleted column is weaved into formulas throughout the workbook. This reference problem also occurs with charts. If the columns connected to a chart are deleted, there’s no way to repopulate the chart because the original columns are now nonexistent and the original series cannot be recovered. The bottom line is you should proceed with caution when using .Delete. You don’t want to destroy a 10-sheet workbook and have to go through the file recovery process with your fingers crossed. To learn more VBA tips like this one, subscribe using the form below.
>> Read more

How to Hide and Unhide Columns with VBA (Fri, 14 Feb 2020)
Spreadsheets can have a lot of information stored on them that users do not need to see, like helper columns. It’s often better to hide this information automatically so users don’t accidentally make any changes. It can also make the information easier to understand if columns are hidden, especially if there are several intermediate columns between the user-changeable columns and the final output columns. Hiding and unhiding columns is very simple in VBA, so this tutorial will be less in-depth than some of our other ones, like the API one or the scraping one, which required many interdependent parts. The Column Object and its .Hidden Property The .Hidden Property Hiding Multiple Columns Toggle Hidden Columns The Column Object and its .Hidden Property In VBA, the Column object accesses a specific column in a Sheet object. It’s important to recognize that Column objects reside on specific sheets. The expression Columns(1) referenecs different columns on different sheets, just like Range("A1") references a different cell on different sheets. Column A on sheet Sales is obviously different than Column A on sheet Customers. Thus, when making a call on a Column object, you must either specify the sheet or expect the currently active sheet to be used. Again, this is no different than working with VBA Range objects. The .Hidden Property Every Column object has a .Hidden property, which is of type Boolean. This property can only be TRUE or FALSE. Note that this is a property, not a method. This means you must set it equal to something, which in this case would be True or False. You don’t call .Hide() to make the column hidden. Similarly, you also don’t call .Unhide() to make the column reappear. If we think about this for a second, it makes sense to implement hidden-ness as a property instead of as a method. If a column is already hidden and we were to call Hide() on it, it would not do anything. Moreover, there would be no way to check whether any specific column is hidden or not. We would only ever be able to blindly hide and unhide columns. So, to hide a column, simply set its .Hidden property to True: Columns(1).Hidden = True If you want to use letter notation, you can do so with quotation marks: Columns("A").Hidden = True There’s nothing more to it. As you might expect, setting the property to False unhides the column. If your column number can change, you might find out VBA column number to letter function useful for converting column numbers to letters. Hiding Multiple Columns with VBA Hiding more than one column requires a different approach. If you follow our site, you’ll know that a Column object refers to a single instance while the plural Columns will refer to a collection. If you don’t follow our posts, you should consider it: Make powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit In VBA, there isn’t a Column object exposed to the programmer, but Columns(1) references a single Column object - the first one - in the Columns collection. To reference several columns at once, you must group them together in a range: Range(Columns(1), Columns(3)).Hidden = True This macro hides columns 1, 2, and 3. If you know the letter, you could also hide Columns A, B, and C with quotations instead of grouping them into a range: Columns("A:C").Hidden = True Toggle Hidden Columns With VBA, it’s easy to check if columns are hidden it’s governed by a property of the Column object. Because this property is Boolean, you can toggle it with the Not modifier. To the user, it’s obvious that columns are hidden or not, and they will want a single button to hide/unhide rather than two buttons (one to hide and a separate one to unhide). Compare these two ways to toggle hidden columns, perhaps connected to a button that a user can press. One uses Not to toggle, and the other complicates the issue with if statements: Sub toggleHiddenWithIfs() If Columns("A:C").Hidden = True Then Columns("A:C").Hidden = False Else Columns("A:C").Hidden = True End If End Sub Sub toggleHiddenWithNot() Columns("A:C").Hidden = Not Columns("A:C").Hidden End Sub Both macros work, but the second one is much cleaner. If you are learning to program, I recommend trying to use logic like in the second macro rather than brute-forcing it with explicit If statements (i.e., checking the possible conditions and acting accordingly). Short, elegant code makes the macro cleaner and more readable. In the example above, there isn’t a problem with the code, but when you have thousands of lines of code, being overly explicit and testing cases can lead to bloat. Of course, be judicious. If your code becomes confusing because of your cryptic logic, it might be better to make the code more verbose - or at least use comments (VBA comments are declared using an apostrophe '.). No one wants to hunt down a and b in this example, especially when they were set 500 lines earlier: Sub crypticToggleHiddenWithNot() a.Hidden = Not b End Sub The variable a could be a column on the active sheet or it could be a row in another workbook. The variable b is definitely a Boolean, but who knows where it came from. These kinds of instances require comments (or, better yet, more verbosity). Hiding and unhiding columns is rather straightforward. Perhaps the only slightly non-intuitive part is that we set the propery of .Hidden on the column objects rather than using a method like .Hide() to accomplish the goal. Hiding and unhiding columns with VBA can really help your users visualize what’s going on in complex spreadsheets. This is especially true if the results column is far from the input column with several intermediate “helper” columns. Imagine a user adding input to Column A and this input is combined with information in Columns B through M and you don’t get to the output until in Column N. It might be better to hide columns B through M using your macro, unless someone needs to edit those fields. That’s when a VBA hide/unhide toggle button, like the one we showed earlier, might come in handy. When you’re ready to take your VBA to the next level, subscribe using the form below.
>> Read more

Faster Alternatives to VBA PageSetup (Sun, 02 Feb 2020)
Editing properties in the VBA PageSetup object is notoriously slow because the object sends a call to the print drivers each time it wants to update a PageSetup property. Even if you disable events and screen updating, PageSetup is still slow. It’s not until you set Application.PrintCommunication = False before you start noticing considerable improvements in performance.. You don’t have to use the slow PageSetup method, though. There is another way to update your page setup properties without relying on any of the sluggish Worksheet.PageSetup statements. This alternate solution lies in a really old version of Excel… The Excel 4.0 compatible macro method, Application.ExecuteExcel4Macro, has a PAGE.SETUP function that is much faster than changing properties using the slow Worksheet.PageSetup object. The speed differences aren’t as significant if you disable the time-wasting events, which we’ll talk about below, but it’s still a bit faster. This tutorial will help you if you’re searching for ways to speed up your VBA PageSetup statements and nothing you’ve tried has worked. VBA PAGE.SETUP Routine Private Sub PageSetupXL4M( _ Optional LeftHead As String, Optional CenterHead As String, Optional RightHead As String, Optional LeftFoot As String, _ Optional CenterFoot As String, Optional RightFoot As String, Optional LeftMarginInches As String, Optional RightMarginInches As String, _ Optional TopMarginInches As String, Optional BottomMarginInches As String, Optional HeaderMarginInches As String, Optional FooterMarginInches As String, _ Optional PrintHeadings As String, Optional PrintGridlines As String, Optional PrintComments As String, Optional PrintQuality As String, _ Optional CenterHorizontally As String, Optional CenterVertically As String, Optional Orientation As String, Optional Draft As String, _ Optional PaperSize As String, Optional FirstPageNumber As String, Optional Order As String, Optional BlackAndWhite As String, _ Optional Zoom As String) Const c As String = "," Dim pgSetup As String Dim head As String Dim foot As String If LeftHead <> "" Then head = "&L" & LeftHead If CenterHead <> "" Then head = head & "&C" & CenterHead If RightHead <> "" Then head = head & "&R" & RightHead If Not head = "" Then head = """" & head & """" If LeftFoot <> "" Then foot = "&L" & LeftFoot If CenterFoot <> "" Then foot = foot & "&C" & CenterFoot If RightFoot <> "" Then foot = foot & "&R" & RightFoot If Not foot = "" Then foot = """" & foot & """" pgSetup = "PAGE.SETUP(" & head & c & foot & c & _ LeftMarginInches & c & RightMarginInches & c & _ TopMarginInches & c & BottomMarginInches & c & _ PrintHeadings & c & PrintGridlines & c & _ CenterHorizontally & c & CenterVertically & c & _ Orientation & c & PaperSize & c & Zoom & c & _ FirstPageNumber & c & Order & c & BlackAndWhite & c & _ PrintQuality & c & HeaderMarginInches & c & _ FooterMarginInches & c & PrintComments & c & Draft & ")" Application.ExecuteExcel4Macro pgSetup End Sub Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit How to Call PageSetupXL4M I first came across this subroutine in an article from 2004. It’s a great VBA routine but without an explanation for how to use it, it can be a bit intimidating. The routine organizes each argument into the old Excel 4.0 PAGE.SETUP function style. The PAGE.SETUP function in Excel 4.0 is complicated and requires the arguments be entered as one massive string organized in a specific manner. The syntax varies based on whether you’re adjusting a worksheet or a chart sheet, but we’re going to focus on worksheets in this tutorial. In short, you have to call the PageSetupXL4M routine with a really long call statement, like this: Sub FasterPageSetup() Call PageSetupXL4M(Orientation:="2", _ LeftMarginInches:="0.25", _ RightMarginInches:="0.25", _ TopMarginInches:="0.5", _ BottomMarginInches:="0.5", _ HeaderMarginInches:="0.3", _ FooterMarginInches:="0.3", _ Zoom:="{2,1}", _ CenterVertically:="False", _ CenterHorizontally:="True") End Sub This lengthy statement adjusts the page setup properties on the active sheet, and is equivalent to this slower (but newer) method: Sub SlowPageSetup() With ActiveSheet.PageSetup .Zoom = False .Orientation = xlLandscape .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .FitToPagesWide = 2 .FitToPagesTall = 1 .CenterHorizontally = True .CenterVertically = False End With End Sub The first thing you’ll notice is that each property is entered as a string in the Call statement of the FasterPageSetup routine. You’ll also notice I didn’t use all of the available properties in my Call statement. All the arguments are optional and the PageSetupXL4M macro organizes them in the proper format for the PAGE.SETUP function. This makes your job a lot easier, because the PAGE.SETUP function is just as finicky as the Worksheet.PageSetup method is slow. The syntax for some of the PAGE.SETUP properties is unusual, especially for the Zoom and Orientation properties. If you’re unfamiliar with Excel 4.0 macro statements, it’s hard to know the different ways to enter each string. To make it easier, I’ve compiled a description of all the accepted properties, examples of how to use them, and their corresponding Worksheet.PageSetup equivalent methods: Detailed PageSetupXL4M Argument Explanations PageSetupXL4M Argument Description .PageSetup Equivalent Example(s) LeftHead Text you want displayed in the upper left of your page .LeftHeader LeftHead:="Header Text" CenterHead Text you want displayed in the top center of your page .CenterHeader CenterHead:="Header Text" RightHead Text you want displayed in the upper right of your page .RightHeader RightHead:="Header Text" LeftFoot Text you want displayed in the lower left of your page .LeftFooter LeftFoot:="Footer Text" CenterFoot Text you want displayed in the bottom center of your page .CenterFooter CenterFoot:="Footer Text" RightFoot Text you want displayed in the lower right of your page .RightFooter RightFoot:="Footer Text" LeftMarginInches Left page margin in inches .LeftMargin (with InchesToPoints) LeftMarginInches:="0.25" RightMarginInches Right page margin in inches .RightMargin (with InchesToPoints) RightMarginInches:="0.25" TopMarginInches Top page margin in inches .TopMargin (with InchesToPoints) TopMarginInches:="0.5" BottomMarginInches Bottom page margin in inches .BottomMargin (with InchesToPoints) BottomMarginInches:="0.5" HeaderMarginInches Header margin in inches .HeaderMargin (with InchesToPoints) HeaderMarginInches:="0.75" FooterMarginInches Footer margin in inches .FooterMargin (with InchesToPoints) FooterMarginInches:="0.75" PrintHeadings Toggle to print Row and Column letters/numbers .PrintHeadings PrintHeadings:="TRUE" PrintGridlines Toggle to print gridlines .PrintGridlines PrintGridlines:="FALSE" PrintComments Toggle to print comments .PrintComments PrintComments:="FALSE" PrintQuality Print quality in dots per inch. Horizontal and vertical values can be different and must be entered as an array, like "{90,72}" .PrintQuality PrintQuality:="{72,90}" PrintQuality:="90" CenterHorizontally Center on page horizontally .CenterHorizontally CenterHorizontally:="TRUE" CenterVertically Center on page vertically .CenterVertically CenterVertically:="FALSE" Orientation Numeric value representing page orientation "1" is portrait, "2" is landscape .Orientation Orientation:="2" Draft Toggle for draft quality printing .Draft Draft:="TRUE" PaperSize Numeric value representing the desired page size. Typically an integer from 1 through 26. 1 Letter, 2 Letter (small), 3 Tabloid, 4 Ledger, 5 Legal, 6 Statement, 7 Executive, 8 A3, 9 A4, 10 A4 (small), 11 A5, 12 B4, 13 B5, 14 Folio, 15 Quarto, 16 10x14, 17 11x17, 18 Note, 19 ENV9, 20 ENV10, 21 ENV11, 22 ENV12, 23 ENV14, 24 C Sheet, 25 D Sheet, 26 E Sheet .PaperSize PaperSize:="9" FirstPageNumber Page number to start numbering pages from .FirstPageNumber FirstPageNumber:="12" FirstPageNumber:="AUTO" Order Specifies whether pages are printed top-to-bottom, then right ("1") or left-to-right, then down ("2") .Order Order:="1" BlackAndWhite Toggle to print pages in black and white .BlackAndWhite BlackAndWhite:="FALSE" Zoom Can be a logical toggle to to fit the printed area on a page ("TRUE"), a percentage to scale the page ("75"), or an array defining how many pages you want to print ("{2,1}" for 2 pages wide, 1 page tall) .Zoom .FitToPagesWide .FitToPagesTall Zoom:="{2,1}" Zoom:="75" Zoom:="TRUE" Performance Comparison To prove how much faster the ExecuteExcel4Macro version of PAGE.SETUP is, I ran the following macros on a workbook with 30 spreadsheets and recorded the timing differences. Sub SlowPageSetup_Loop() For Each sht In ActiveWorkbook.Sheets With sht.PageSetup .Zoom = False .Orientation = xlLandscape .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .FitToPagesWide = 2 .FitToPagesTall = 1 .CenterHorizontally = True .CenterVertically = False End With Next sht End Sub Sub FasterPageSetup_Loop() For Each sht In ActiveWorkbook.Sheets sht.Select Call PageSetupXL4M(Orientation:="2", _ LeftMarginInches:="0.25", _ RightMarginInches:="0.25", _ TopMarginInches:="0.5", _ BottomMarginInches:="0.5", _ HeaderMarginInches:="0.3", _ FooterMarginInches:="0.3", _ Zoom:="{2,1}", _ CenterVertically:="False", _ CenterHorizontally:="True") Next sht End Sub The FasterPageSetup_Loop macro uses the old Excel 4.0 PAGE.SETUP function and finished in 8.91 seconds. This still isn’t fast, but it’s much faster than the SlowPageSetup_Loop which took a whopping 37.29 seconds. Disabling Application Flags You can improve the performance even more by disabling certain Application flags. The difference between the PAGE.SETUP and Worksheet.PageSetup approaches isn’t nearly as dramatic once the following properties are disabled: With Application .ScreenUpdating = False .EnableEvents = False .PrintCommunication = False End With I highly recommend you set these to False at the beginning of your macro. You can set them back to True right before the end. When I disabled these time-wasting events, the PAGE.SETUP method finished in 4.87 seconds and the .PageSetup method finished in 5.42 seconds. Both methods show considerable speed improvements, but the .PageSetup method improvement is downright staggering. The time reduced from 37.29 seconds down to 5.42 seconds. Of the three disabled properties, the PrintCommunication property is the one that had the biggest impact on macro speed. This was especially true for the slow .PageSetup macro. Between the two optimized codes, the PAGE.SETUP routine performed about 10% faster for me, but your results may vary. I actually had some test cases where the results were slower with PAGE.SETUP. Although the timing results with these two macros are closer, you have to keep in mind the “fast” VBA PAGE.SETUP method wastes time by selecting the sheet you want to edit, whereas the slow VBA .PageSetup method doesn’t have to activate each new sheet before adjusting the properties. Recognizing this fact, it’s likely the actual page setup portion of the “fast” code is a bit faster, even with the the Application events above disabled. Either way, this tutorial shows you should at least disable ScreenUpdating, EnableEvents and PrintCommunications before adjusting your page setup properties. The PrintCommunications property alone can easily cut your macro execution time in half. By playing with alternative methods, we were able to speed up the VBA PageSetup execution time from 37.29 seconds down to 4.87 seconds. That’s still not very fast, but it’s a big improvement! I hope you enjoyed this little experiment with VBA page setup speeds. When you’re ready to take your VBA to the next level, subscribe using the form below.
>> Read more

Refreshing Pivot Tables with VBA (Sat, 04 Jan 2020)
Pivot Tables are powerful tools, but they’re only as strong as the data in them. If the data is outdated, then they are no longer useful for understanding the current state of things and they’re certainly not useful for forward projections. Updating your Pivot Table data when you open your Excel file is very simple through the Excel GUI, but you can easily overlook the need to update regularly. In that case, automated updates might be a better idea. We’ve published a tutorial on scheduling macros before, so once you know how to update Pivot Tables programmatically using VBA, you can always automate the entire process, too. Tables and Caches Refreshing Pivot Table When Tables Share a Cache When Tables Do NOT Share a Cache Getting Table Names and Iterating Refreshing a PivotCache Refreshing a single PivotCache Refreshing all PivotCaches Refreshing on Changes Pivot Tables and Pivot Caches There are three main components to a Pivot Table: the original data, the Pivot Cache, and the table itself. A PivotCache is an object that lives at the workbook level, so it can be accessed by any Pivot Table on any worksheet. A PivotTable is a sheet-level object, as it must exist on a particular sheet (otherwise you wouldn’t be able to see it!). Pivot Caches prepare the original data to be used in a Pivot Table, and one cache can be used for many different tables. Importantly for this tutorial, if two Pivot Tables share the same cache, any refresh of one table will first update the underlying cache and then update all tables associated with that cache. This means updates can very efficiently flow through a workbook, but that is not always desirable. In order to update tables individually via VBA, it is necessary that the tables to be refreshed independently rely on separate caches. You can see which cache any table relies on through its .CacheIndex property. Refreshing Pivot Table The easiest way to refresh a table is simply calling the .RefreshTable method on it. If you know the name and location of the table, it is a simple one-liner. From our previous VBA pivot table tutorial, let’s say the table we want to refresh lives on Pivot Sheet1 and the name of the table is AQI for CBSAs 2019. Our single line of code would be: Sheets("Pivot Sheet1").PivotTables("AQI for CBSAs 2019").RefreshTable Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit When Pivot Tables Share a Cache Importantly, if any other tables share the same Pivot Cache - that is, their .CacheIndex values are equal - then all associated tables will also update. This is a very efficient implementation because you only need to know the name of one table, and you only need to write one line of code. The underlying programming logic and processing is already optimized by Microsoft and the VBA language developers for us. So, if a workbook is full of Pivot Tables all built off a single cache, then this approach will actually update the entire workbook without any other coding required. When Tables Do NOT Share a Cache Sometimes it is important to separate the underlying caches in order to accomplish your needs. One drawback of using one cache for all tables is it becomes impossible to separate tables on certain dimensions, such as groupings. In such cases, it is better to separate caches. If tables do not share a cache, then they can be refreshed in the same way as above, but it will need to be done over the entire collection. This can be accomplished via nested loops, which is shown in the next section. Getting Names and Iterating If you know the name of the sheet but not the name of the table, you can always check the .Name property of all the Pivot Tables on that page, which will live in the PivotTables collection. To clarify, PivotTable refers to a single table, while the plural PivotTables actually refers to the collection of tables. Using debug.print and the Immediate window (keyboard shortcut is Ctrl+g), you can check all the names: Sub check_table_names() For Each tbl In Sheets("Pivot Sheet1").PivotTables Debug.Print tbl.Name Next tbl End Sub You don’t have to stop here, though. You could nest for-loops and do this for every worksheet and every table (tables on different sheets can have the same name, though!): Sub check_table_names_all_sheets() For Each sht In ThisWorkbook.Sheets For Each tbl In sht.PivotTables Debug.Print tbl.Name, sht.Name 'to print table and the sheet where it resides Next tbl Next sht End Sub and now you have the name of every table and the sheets on which they reside, so you can easily target specific Pivot Tables to be updated. If you have separate Pivot Caches for each table, you can use this nested for-loop method to update every table. Simply replace the debug.print line above with tbl.RefreshTable, like this: Sub refresh_pivot_tables_all_sheets() For Each sht In ThisWorkbook.Sheets For Each tbl In sht.PivotTables tbl.RefreshTable Next tbl Next sht End Sub Using this method, each table in the entire workbook can be updated independently, regardless of its dependence on the same Pivot Cache. There’s another way to refresh all the pivot tables in a workbook. Instead of iterating through each worksheet and pivot table, you can simply call the .RefreshAll method of the workbook. Here’s how your simplified macro might look if you wanted to refresh all the pivot tables in your workbook: Sub refresh_all_pivot_tables() ThisWorkbook.RefreshAll End Sub Refreshing a PivotCache Earlier we talked about the relationship between Pivot Tables and Pivot Caches. Instead of refreshing a pivot table, you could choose to refresh the pivot cache directly and all pivot tables linked to this cache will automatically be updated. Let’s show you how that looks. Refreshing a single PivotCache What if you wanted to update a specific PivotCache rather than a specific pivot table? The code is similar to the first code snippet we presented in this tutorial, but you have to drill down one more level to get to the PivotCache. Take a look: Sheets("Pivot Sheet1").PivotTables("AQI for CBSAs 2019").PivotCache.Refresh Notice how this version of the code uses the .Refresh method instead of the .RefreshTable method. That’s because this method refreshes the entire cached data, whereas the .RefreshTable method refreshes the PivotTable from the source data. It’s a subtle difference, I’ll admit. Refreshing all PivotCaches Instead of looping through and refreshing all the pivot tables, you could choose to loop through and refresh all the underlying Pivot Caches. Here’s how you would to that: Sub Refresh_All_Pivot_Table_Caches() Dim PCache As PivotCache For Each PCache In ThisWorkbook.PivotCaches PCache.Refresh Next PCache End Sub The downstream pivot tables are automatically updated when the underlying pivot cache is refreshed, so this macro essentially does the same thing as the refresh_all_pivot_tables macro we presented earlier. Refreshing on Changes Another nifty feature of Excel is its worksheet events. Worksheet events are similar to workbook events, but they’re triggered by events on a specific sheet. These events can be used to update Pivot Tables whenever the original data is changed (assuming the data lives in the same workbook as the Pivot Tables). The original data is often updated by hand to fix errors. In those cases, the original datasheet can be monitored for changes with the Worksheet_Change event. Whenever a change occurs to the original data worksheet, a macro can be triggered to update the Pivot Tables. In order to access worksheet events, you need to open the worksheet’s dedicated code module: Dedicated Code Module for Worksheet with Highlighting The Dedicated Worksheet Code Module Double click the red box to get the dedicated code for that worksheet. For our purposes, use the worksheet that contains the editable original data. Then, change the orange box to Worksheet from General, and finally, choose Change from the events dropdown. This will fill in some code that will trigger when changes are detected on that worksheet. Now, you can add your Pivot Table refresh code in this private subroutine, and any time a change is committed (someone presses enter for new data), the code here will be triggered. This is particularly useful to ensure tables are always updated, since any change to the original data will flow over to the tables immediately. This frees users from updating the tables manually; they don’t even have to remember to do so, since it will automatically work in the background. A caveat: this method is only practical for singular changes! If many changes are being made or the pivot table is on the same sheet as the data, Excel will become unusable as it continually updates tables for every single commit. If several changes are to be made to the original data at once, consider another method, such as a button users can press once they finish making their changes. Alternately, you can disable events in your worksheet_change code by toggling Application.EnableEvents, like this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ThisWorkbook.RefreshAll Application.EnableEvents = True End Sub Updating the information in a Pivot Table is essential for accurate data analysis, and in Excel, it is very easy to do programmatically. While it can be done through the GUI, it is often better to automate the process using a worksheet event. If you’d rather the data only be updated once the user is ready, you could have a button the user presses after changes have been made to the source data. I hope you enjoyed this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.
>> Read more

Create and Manipulate Pivot Tables with VBA (Fri, 06 Dec 2019)
The Data Revolution is upon us, and everyone wants to gather and analyze as much data as possible. Spreadsheets like Excel offer one of the best ways to engage in data analysis for the average worker - that is, those without the need for distributed systems or supercomputers. Within spreadsheets, one of the most powerful tools for analyzing data and extracting information is a Pivot Table. In this tutorial, we’ll automate pivot tables using VBA so you can easily set up new ones or hand off your files to a coworker. Like my VBA API tutorial, this one may be a little complicated due to the number of functions and concepts involved. However, just like APIs, Pivot Tables are immensely powerful and it is worthwhile to learn the concepts here. Data is the future, and if you can programmatically create Pivot Tables to analyze that data, you are ahead of the curve. Your Data Source Creating Pivot Tables Select the Data Create New Sheet Cache the Data Place the Table Add Fields and Filters Add Calculated Values Using the Data Switching Orientations Your Data Source First, you’ll need a data source. If you already have the data in your spreadsheet, great. If not, you can find plenty of open data sources online. Most governments and international bodies collect and publish massive amounts of data, and many companies also produce open data sets. For this tutorial, I will use the Air Quality Index for all United States CBSAs (Census Bureau Statistical Areas). This is the air quality for every city/town in the country, and it is accessible on the EPA’s website here. The following attributes, among others, are included in the dataset: CBSA (town or city area) AQI (air quality index) Category (descriptive term of quality, such as “good” or “hazardous”) Particulate type (PM2.5, PM5, PM10, SO2, etc.) The particular set I retrieved had about 110,000 instances, so there is quite a lot of data here - perfect for Pivot Table analysis. From the snapshot below you can see that it populates Columns A to H: Simply an image of the first few rows of the dataset 2019 Dataset from the EPA for Air Quality Index (AQI) Creating the Pivot Table You’ll probably want to preserve the original data, so it’s best to put your Pivot Table on a new sheet, which can easily be accomplished with the Sheets.Add function in VBA. This function adds a new Sheet object to the Sheets collection of your workbook Select the Data First, grab the data you want to manipulate in the table. Since our data populates Columns A through H, you can identify and set a Range object with a simple column selection: Set ODRange = Range("A:H") If you use another dataset, you will need to find the bounds independently - this can be done programmatically. There are several ways to do this, but it’s important to capture the entire column since the number of rows in your table may change. One common way is to set your Range object equal to the entire used range, like this Set ODRange = ActiveSheet.UsedRange.EntireColumn Create the Sheet I recommend assigning the new sheet to a named Object. This line creates the new Sheet object, which you can later reference (in the same macro) by calling PTSheet (short for Pivot Table Sheet). I also recommend giving it a label: Set PTSheet = Sheets.Add PTSheet.Name = "Pivot Sheet" Cache the Data VBA requires you to cache the data before supplying it to the Pivot Table creation function. This ensures Excel knows where to find the source when you want to refresh the table. It basically creates a map in the computer’s memory between the Pivot Table and the Source Data, so changes can easily flow between the two. To cache, you need to add it to the workbook’s aptly name PivotCaches collection: Set PTCache = ThisWorkbook.PivotCaches.Create(xlDatabase, ODRange) Create and Place the Table Now just put the table in the right place and give it a title: Set PT = PTCache.CreatePivotTable(PTSheet.Cells(1, 1), "AQI for CBSAs 2019") If you visually inspect PTSheet, you’ll see a blank Pivot Table named AQI for CBSAs 2019. Your Pivot Table is in place and ready to be set up. Sometimes you can get a run-time error 5, Invalid procedure or argument, when establishing your Pivot Table with VBA. Older version of Excel can be picky and require the SourceData argument in the PivotCaches.Create function to be delivered as an R1C1 style string instead of as a Range argument. You can also get the run-time 5 error if the optional Version argument of the PivotCaches.Create function doesn’t match the DefaultVersion argument in the CreatePivotTable function. We didn’t declare any of these parameters when we made our PivotTable, but it’s something to keep in mind if you run into trouble. Make powerful macros with our free VBA Developer Kit There’s a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer Kit below. It’s full of tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit Adding Fields and Filters Of course, the point of Pivot Tables is to manipulate data, so we’ll need to populate the table with the correct data. It is a relatively straightforward process if you already know how to manipulate Pivot Tables graphically (i.e., using a mouse in Excel). Let’s add the CBSAs as a row and AQI as a calculated value. Let’s also filter by Category and Defining Parameter (which has the name particulate type). Rows are probably the easiest to understand and add. Simply find the name of the field in our new, blank Pivot Table and assign it the xlRowField orientation: To add row fields: PT.PivotFields("CBSA").Orientation = xlRowField Filters are just as easy. Find the name of the original field in the data and assign it the xlPageField orientation: To add filters: PT.PivotFields("Category").Orientation = xlPageField PT.PivotFields("Defining Parameter").Orientation = xlPageField If you want to add columns, follow the same steps, but use xlColumnField as the orientation. Adding calculated values For calculated values, there is one extra step. Excel needs to make some calculations, so we will need to supply a function. Use the AddDataField function and feed it the field to operate on. Here, we will average the AQI field using the xlAverage argument. To add a (calculated) Values field PT.AddDataField PT.PivotFields("AQI"), "Average AQI for 2019", xlAverage Using the Data At this point, you should have something like this on Pivot Sheet: Image of resultant Pivot Table The Pivot Table we just created Notice there is a problem with the statistical analysis here, though. All numbers for all particulate types are being averaged together. While this can give you some indication of pollution levels, it is probably better to view each particulate individually. You can do this by filtering for each Defining Parameter, but it is better to see everything at once. That means we need to adjust our Fields: Switching Orientations The Defining Parameter field is set as Orientation type xlPageField, but we can set it as a column instead to see the breakdown for each city. We’ll need to access the table, find the field, and change it. Remember that we named the table AQI for CBSAs 2019, our new sheet name is Pivot Sheet, and the associated field is Defining Parameter. Worksheets("Pivot Sheet").PivotTables("AQI for CBSAs 2019").PivotFields("Defining Parameter").Orientation = xlColumnField and now you can see the particulate size average by city along with the average of all particulates: Full Pivot Table with Particulate Type The new Pivot Table with particulate type broken down Because the only calculated DataField is the average, switching the orientation of Defining Parameter automatically calculates the average per type, as expected. If we had another function, say xlSum, we’d have a breakdown by average and by sum. To recap, we’ve found a data source, created a Pivot Table, added fields and filters, and changed orientations. This was a very basic overview, but it gives you the foundation for setting up Pivot Tables. At this point, you can easily create Pivot Tables in Excel and pass that file to your friends or coworkers. Or you can simplify some of your own tasks when analyzing data. Now go experiment! Sub create_full_table() Set ODRange = Range("A:H") Set PTSheet = Sheets.Add PTSheet.Name = "Pivot Sheet" Set PTCache = ThisWorkbook.PivotCaches.Create(xlDatabase, ODRange) Set PT = PTCache.CreatePivotTable(PTSheet.Cells(1, 1), "AQI for CBSAs 2019") PT.PivotFields("CBSA").Orientation = xlRowField PT.PivotFields("Category").Orientation = xlPageField PT.PivotFields("Defining Parameter").Orientation = xlPageField PT.AddDataField PT.PivotFields("AQI"), "Average AQI for 2019", xlAverage Worksheets("Pivot Sheet").PivotTables("AQI for CBSAs 2019").PivotFields("Defining Parameter").Orientation = xlColumnField End Sub If you liked this tutorial, we have a 20+ part free email series designed to help you learn more VBA tricks like this one. Check it out using the form below and share this article on Twitter and Facebook.
>> Read more

Adjusting Dates with the VBA DateAdd Function (Fri, 01 Nov 2019)
Mentally calculating a date a few periods from now is not hard: finding the date five days in the future, three months from now, or two years into the past is relatively straightforward arithmetic. When we start to cross over intervals, however, it becomes more difficult. Is 180 days from now exactly 6 months away, like March 28 → September 28? Or is it a little shifted due to differences in month length? What about 26 weeks? VBA has a built-in function to make calculations like these easier for both past and future dates. Let’s look at how the VBA DateAdd function can help you calculate dates and how we can manipulate the outputs to simplify the results for users. The Built-in DateAdd Function The Interval Parameter The Number Parameter The Date Parameter Loops with Pauses The Built-in DateAdd Function Let’s start by breaking down the function, which has three parameters, all of which are required: DateAdd(Interval, Number, Date) where, Interval as String Number as Double Date as Variant(Date) The DateAdd function returns a single Variant(Date) as the result. The Interval Parameter According to Microsoft’s own documentation, there are 10 intervals you can use for DateAdd. This parameter should be passed as a String to the function, with the following interpretations: Setting Description yyyy Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second For example, if you want to add seconds to a date, the Interval parameter should be "s". If you want to subtract years, pass "yyyy" as the Interval. Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit Okay, now you need to really pay attention. There are three types of days in the table above that do not have any bearing on the days added or subtracted: d, w, and y. Specifically, the w weekday parameter does not have a cycle of 5 days, as one would expect! Entering these 3 values in your Interval argument will not do anything to your original date. This quirk seems to arise because Microsoft uses the same interval set for different functions, like DateDiff and DatePart, where d, w, and y do carry different meanings. The Number Parameter The Number argument specifies how many of Interval you want to add or subtract. If your Interval is "yyyy" and your Number is 20, the output of DateAdd will be twenty years from the starting date (supplied in Date). To add time, use positive numbers. To subtract time, use negative numbers. One point to note is that, though Number’s data type is Double, which carries up to 15 decimal points, you cannot add or subtract fractional intervals. To illustrate, this code will always output the starting time, "10/23/2019 14:35:31", regardless of how big i is, because partial intervals (intervals with decimals) are not added or stored. Despite being a double data type, the Number argument only interprets integers. Sub partial_interval_added_iteratively() 'VBA DateAdd cannot add fractional time! y = "10/23/2019 14:35:31" For i = 0 To 100 y = DateAdd("yyyy", 0.5, y) Next i Debug.Print y End Sub Consequently there is no resolution below one second. Moreover, dates that result in years outside the range [100, 9999] (inclusive) will result in an error. The Date data type in VBA does not allow dates outside this range. No one ever claimed VBA is a scientific computing language. If you need better than one second resolution or dates outside this period, which may be common in many disciplines like Earth Sciences, Astronomy, and even Archaeology, you might need a different language for date calculations, like Python. I mean, VBA can do it if you’re willing to risk string manipulations and forced data type conversions, but that’s a dangerous game to play. The Date Parameter The variable type for Date is Variant, and you may use either a string date, such as "Oct 21 1992 12:34:23", "21-Oct-92", "10-2019", or any other common string format for dates. Since DateAdd also handles times, you can use a human-readable time expression, like "12:30:15" or the VBA internal representation, which is a Double indicating the fraction of the day elapsed. In VBA internal time representation, a few examples are: 12:00:00 = 0.5 21:00:00 = 0.875 (21/24th of the day) 00:00:01 = 1/(60 seconds * 60 minutes * 24 hours in a day) = 0.000011574 If you do not specify a day for a Date month-year entry, VBA will default to the first day. If you do not specify a time, VBA will default to midnight of that day. Thus Debug.Print DateAdd("s", 1, "Oct 2019") will print 10/01/2019 00:00:01, one second after midnight on October 1st. When specifying the date, it is advisable to provide as much information as possible to avoid forcing VBA to guess what you mean. Consider the following short dates: 10-2019 10-19 When seen next to each other, a human may automatically assume the pattern of October 2019 for both. However, the latter entry will be interpreted by VBA as October 19 of the current year. Above, 10-2019 references a month, and thus defaults to midnight on October 01, 2019, while 10-19 references a day and thus defaults to midnight on October 19, [current year]. The less ambiguity VBA must deal with, the less likely you are to run into an unintended outcome. For clarity, you can use the VBA cDate function to convert your strings to dates. Once you’ve added your dates using the DateAdd function, remember you can format dates using VBA so your output can be printed however you like. Loops with Pauses It’s possible to delay a loop’s progress in different ways. One way is to use DateAdd. The most common use here is for waiting a few seconds between iterations. People often do this to allow other processes to finish before continuing their macro. For a better approach to automation, especially over longer periods of time, see our tutorial on scheduling macros. The easiest solution is to add seconds to the current time, which is conveniently accessible with the built-in Now function. A skeletal example is shown below. Sub delayed_loop_name_guessing_game() Do Until user_input = "stop" Or user_input = "John" Or user_input = "Sarah" user_input = InputBox("Enter a name to play or 'stop' to stop the game", "Guess a Name Game") Application.Wait (DateAdd("s", 3, Now())) Loop If user_input = "John" Or user_inut = "Sarah" Then MsgBox ("You win!") End Sub This (admittedly excessively) basic game asks the user to guess a name then compares it to the winning names (John and Sarah). To give the user some time to think, it pauses the loop for 3 seconds. Of course, the setup also means evaluation is delayed for 3 seconds, so even if they win, they must wait 3 seconds for the game to conclude. Regardless of the triviality of the game, this code illustrates using DateAdd to pause execution for a time interval. If you’re interested in adding delays to your macro, check out our detailed tutorial about the Applicaion.Wait VBA method. The built-in DateAdd function is quite useful for finding dates in the future or past. There are a couple unfortunate characteristics, like the inability to directly calculate business days or partial intervals. If you liked this tutorial, we have a 20+ part free email series designed to help you learn more VBA tricks like this one. Check it out using the form below and share this article on Twitter and Facebook.
>> Read more

Daily Dose of Excel

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-5f08318895171536598123/] 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

Where’s my “Edit Measure” option from the Values pane? (Sun, 04 Aug 2019)
Why is it that I can do this from here…   …but it doesn’t let me do this from there? When I want to edit a PowerPivot measure, I generally want to edit a measure I’m *already* using.  I don’t want to scroll through a whole bunch of tables in the fields list or the […]
>> Read more

Australia Summit for Microsoft Business Applications. (Oh, and Excel) (Sun, 21 Jul 2019)
Excel occupies a special place in my heart. It also occupies a special place in the Power universe thanks to the superpowers (DAX and PowerQuery) it shares with its alter-ego PowerBI. Unfortunately this Power Universe is a lot like the Marvel one: An ever-expanding cast of new players with sometimes overlapping powers constantly has me […]
>> Read more

Keyboard Metrics III (Mon, 12 Nov 2018)
In the grand tradition of Keyboard Shortcut Metrics and More Keyboard Metrics, here’s this: Procedure 2016 2017 2018 Grand Total MakeComma 20.54% 18.67% 19.01% 19.19% WrapSheetsDown 13.80% 17.15% 9.93% 14.93% WrapSheetsUp 8.60% 15.36% 7.33% 12.16% FillVirtualScreen 10.50% 11.04% 8.42% 10.40% CopyPasteValues 14.00% 4.84% 2.20% 6.54% ShowFormatting 5.85% 5.13% 7.04% 5.68% IncrementDate 4.25% 4.08% 9.77% 5.23% […]
>> Read more

Incrementing Months and Years (Fri, 09 Nov 2018)
As you know, I love keyboard shortcuts. But I hate entering dates. So I created this little helper. In my new accounting system, they also have shortcut keys for incrementing weeks, months, and years. I don’t have much use for incrementing weeks, but I could kick myself for not thinking of the others. Plus Alt+semi-colon […]
>> Read more

Free Table tools add-in (Fri, 09 Nov 2018)
Do you want to be able to rename your Excel tables and columns without breaking existing PowerQuery queries? Do you always change the default name of a new table? Want to have quick navigation to your tables in your workbook? Use my free TableTools add-in Enjoy! Jan Karel Pieterse jkp-ads.com
>> Read more

P3

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

Power BI and Paginated Reports: Have We Come Full Circle? (Mon, 08 Jun 2020)
This is where we’re headed in Part Two. Today: taking stock of how we got here. (Click image for larger version) Summary of this 3-part piece: There’s always been a blurry line between Analysis and Reporting 20 years ago, BI was dominated by Reporting, with Analysis as an upstart Today, Analysis seems to have “won,” and this DOES reflect major progress! But I also think we’ve overcorrected quite a bit, and it’s time to take a fresh look Old habits die hard, and old use cases die even harder In an era of Action, we have the chance to hybridize in ways that were impossible before The Thick Grey Line There are few stories as tortured and twisted as my relationship with Analysis and Reporting. In 2002, as part of the Excel team at Microsoft, I was introduced to the concept of Business Intelligence. This is back when the term […]
>> Read more

Agile Reaction to Change: Cleaning Up with Bar Keepers Friend (Tue, 28 Apr 2020)
Crises Drive a Need for New Business “Maps” Over the years, I’ve written multiple times about how BI spending increases during hard times. When we were planning the Excel 2007 release at Microsoft for instance, we researched the spending trends of the dot-com crash and found that IT spending had declined in every single category except one – Business Intelligence – in which investment had INCREASED during the tough times. When a crisis hits, suddenly you can’t trust yesterday’s map, and you need to draw a new and accurate one quickly. And then, around the time I was starting P3 in 2010, I looked at IT spending during the recession following the 2008 crash. Again, spending went down in nearly every category, but this time there were TWO exceptions – BI and Cloud. A good omen for Power BI in the years to come. The common thread is that crises […]
>> Read more

Self-Documented Reports (aka the FAQ Page) (Mon, 20 Apr 2020)
Sometimes in the grind of data wrangling, slinging some DAX code, and laying down some sweet charts, graphs, and tables, we can forget that the softer “non-data stuff” can really drive usability and adoption.  It’s very easy for us (the report authors) to underestimate how much better we understand our reports than our users do. We just implicitly “get” the meaning of each metric, and are aware of each interactive capability. It’s important for us authors to try to step outside our own knowledge and history in order to see things from the naïve user perspective. A good data visualization is like a good joke – it shouldn’t need to be explained. I frequently use that phrase when teaching our students about report design. However, sometimes a little context about what a visualization is communicating is necessary. Let’s look at an example. Here’s a Sales Snapshot for Stores with the […]
>> Read more

A Completely New Site for a New Era (Sun, 12 Apr 2020)
Yep, it’s a big change. And a good one!(but don’t worry, we’re still in the stick figure business) “P3 version 3” Clarifies What We Can Do For You A lot has changed in the ten and a half years since we launched this site!  When I penned the first “hello world” post in 2009, I still worked for Microsoft, Power Pivot was still in beta, Power Query didn’t exist, and this thing called “Power BI” wasn’t even an idea yet. As the technology evolved and matured, so did we.  Five years ago, we began hiring in earnest, and have now grown to become the “new style” prototype firm I imagined many years ago.  We’re constructed, organized, and staffed in a manner which sets us apart from the BI firms of yesterday – because we needed to be. Some of the team in October. I’m proud to say that everyone at […]
>> Read more

Speed up your Power BI DAX formulas by up to 95% using the new OPTIMIZEDAX() function (Wed, 01 Apr 2020)
4/2/2020 Edit: This is an April Fools’ joke. OPTIMIZEDAX() doesn’t exist, but we hope the post gives you a laugh nonetheless. Have you ever seen the movie Limitless? In the film, Eddie Morra (played by Bradley Cooper) is a man whose life is in the dumps, but is turned around by a magic drug that enhances his mental acuity. With his new-found skills, he quickly rises to the top of Wall Street. If there were an equivalent of Eddie in the Power BI world, it would be the performance of your complicated DAX measures – in the dumps. Here’s usually how it goes – you first start to learn a bit of DAX and with your new-found superpowers, you start to build measures upon measures. But before you know it each click of your slicers takes 20 seconds… 30 seconds… 60 seconds?? Then you spend hours and hours tweaking a […]
>> Read more

Welcome to the New Normal (Thu, 12 Mar 2020)
We’re All Going to Keep Working, But We’re Going to be Smart About It Now is the Time I’ve been mentally working on this post for nearly two weeks.  “Post it too soon,” I told myself, “and you’ll look like an alarmist weirdo.”  But once we saw what was happening in China, Italy, etc., and once it was “in the wild” in Washington state, it was pretty clearly just a matter of time.  Put a ball at the top of a hill, give it a push, and it’s going to roll to the bottom while picking up speed.  Simple as that.  So here we are.  Let’s talk a bit about where “here” is. Short Term:  Social Distancing is a Must Folks, this thing is already everywhere.  In your recent commutes and workdays, it’s instructive to think that you’ve seen multiple undiagnosed people.  They’re out there.  Heck, some people reading this […]
>> Read more

ALL(‘the Bengal Ladies’) (Tue, 18 Feb 2020)
All the BENGAL Ladies… Today’s post goes out to all my DAX Tigress friends out there… put your hands up! AND, to my fellow meme enthusiasts – I have a meme trifecta for you today (cats + pugs + ???). Interestingly, I recently watched #CATS_THE_MEWVIE, a documentary on Nextflix, and learned about the history and evolution of cats on the internet – quite fascinating! The link to Google Trends & chart below does validate that cats do trend higher in interest over dogs! https://trends.google.com/trends/explore?date=today%203-m&geo=US&q=cat%20meme,dog%20meme Let’s talk about another STRANGE phenomenon that exists … Our Power BI classes are > 50% female. The best student in each class is also often female. Applicants for P3 Principal Consultants are > 95% male! Yeah. That’s how I feel! What gives? Well done Pugs! It’s impossible to know exactly why, but let me share MY story. First, let’s start with TODAY.  I’m one of […]
>> Read more

Just in Time for the Big Game: Install NFL Analytics by P3! (Fri, 31 Jan 2020)
Remember NoCheckdowns?  Well, we turned it into a template you can download from AppSource and install on your own Power BI tenant! Install it Here. Enjoy See you next week.
>> 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
>> 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
>> 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
>> 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
>> 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
>> 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
>> 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
>> 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.
>> 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
>> 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
>> Read more

Option Explicit VBA

10 hours left to get My Excel Dashboard Pro Course (Tue, 07 Feb 2017)
Hi, all. I was debating doing a post on this, but the books say to do it–so I will! (Who am I to second guess the wisdom of online marketing eBooks?) Hopefully, many of you have already signed for my free 3-part video series. (Once you put in your email, you’ll be emailed a page with the […]
>> Read more

FREE 3-part Dashboard Video Series (Tue, 24 Jan 2017)
Hi there, friends. In celebration of my new course for Excel.TV, I’ve released a free 3-part video series around building dashboards. The videos are as follows: Awesome Benefits of Building Dashboards With Excel 5 Rookie Dashboard Mistakes That Even the Experts Miss Excel Dashboard Questions Answered All you need to do is go to excel.tv/training/ […]
>> Read more

Excel, Access and VBA Problems?

Call or text at 612-701-6292


or send an email.

Print Print | Sitemap Recommend this page Recommend this page
© Ray Erden

This website was created using 1&1 IONOS MyWebsite.