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: VBA Macro to Create Power Query Connections for All Excel Tables - Excel Campus (Thu, 27 Aug 2020)
[…] you're new to Power Query, I recommend checking out this overview, and then using this tutorial to get it installed […]
>> Read more

By: Data Import Now Possible From PDF Documents For Office 365 Users (Tue, 11 Aug 2020)
[…] Many identify  Excel as a spreadsheet tool that is ideal for numbers, but in fact, it is commonly used to create text grids or lists as well. In the past, users complained about not being able to import table data from Adobe Portable Document Format (PDF) files into their Excel spreadsheets. All that changed on August 5, 2020, when Microsoft announced the launch of a new FROM PDF connector that could be connected to PDF files, enabling data to be transported into Excel spreadsheets easily. In some scenarios, you may wish to import a range of pages from one or more PDF documents. If so, you can simply specify your Start and End pages in the  Power Query Editor’s M formula. […]
>> 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 to Sort a Column and Sort Multiple Columns (Sat, 05 Sep 2020)
Why do we use spreadsheets? One reason is organization. We like to organize our data so we can make intelligent calculations on it. That’s the essence of a spreadsheet. One of the most common ways to organize your data is by sorting, which happens to also hold the prestige of being a fundamental problem in computer science. When people talk about “sorting columns” or “column sorting”, they are conflating two ideas: sorting full columns (Rows 1 to 65,000+) and sorting a range using a column as a key. People conflate the two ideas because they’re very similar. Due to their similarity, we’ll cover both. For this tutorial, we’ll use a business-oriented example data set. Naturally, you can use whatever dataset you have. As long as the data comes in columns and rows and you want to sort it, this tutorial is for you. The Example Data The Sort Function Decide Which Range to Sort Apply the Sort Function Do we need to specify which columns? When xlGuess Fails Final word The Example Data You run a business and have an Accounts Receivable sheet for the day’s orders made on credit. The orders are added as the day goes by, so they are in ascending chronological order. Before committing this sheet to your database - and you do use a database, I hope - you like to check the amounts owed and from which cities most orders came. You’re old-school, so you just like to eyeball these numbers, but you need to sort the data first. If you want to practice, you can download this CSV file of the sample data and follow along. If you’d rather just use the image, check out the screenshot. Screenshot of sample data Sample Data Do you recognize any names? The Sort Function Yes, we know we just called it a function, but .Sort is really a method. The .Sort method in VBA is pretty straightforward, though it can seem daunting at first if you have Intellisense turned on: Intellisense for Sort Function Scary-long Intellisense for Sort You don’t need to use all these parameters, though. We’ll only look at keys, orders, and headers. You can play with the others if you’d like. For better guidance in your explorations, these bullets very briefly explain what they other parameters do. Keys are which data to sort on Orders are ascending or descending Header includes or excludes the first row OrderCustom uses a separate data structure that stores your own sort criteria (like days of the week M, T, W, Th…) Orientation defaults to Rows, meaning rows “move” up and down. Set to xlSortColumns to “move” columns left and right SortMethod is only applicable to Hanzi (Chinese characters) DataOptions correspond to their respective Keys and default to treating text and numeric data separately. Set to xlSortTextAsNumbers to mix them Decide Which Range to Sort Normally, people want to sort entire columns (rows 1 to the end). This can be achieved by using a subset of the Columns collection. This code block will sort all data in Columns A, B, C, and D using default sorting options: Columns("A:D").Sort Sometimes, you may have formulas or other information in rows after your table of data. In that case, you’d want to sort on a “shorter” range, say rows 1 to 50. From Row 51, maybe you have another set of data that should not be sorted. Alternatively, maybe you have analytics at the top of the sheet above the data that should be sorted. For example, perhaps the sortable data only starts at row 20. In that case, you’d use a range object. This snippet will only sort the square block from cell A20 to cell D100, leaving your analytics that are entered in A1 to D19 untouched: Range("A20:D100").Sort You can use the Range approach and cover a large area, too, like Row 20 down to Row 20,000. Here are some general rules of thumb for using .Sort: Use Columns.Sort if you only have sortable data in the columns and do not know exactly where it ends Use Range.Sort if you have unsortable data in the same columns as sortable data. Apply the Sort Function Now that you’ve decided the proper range, let’s get sorting! First, we must assess our needs for our hypothetical business situation: We've decided we need to sort by City and Amount We've determined our data has headers We don't have any data that shouldn't be sorted We don't know how many orders we'll have each day Points 3 and 4 mean we can use the Columns collection, and in fact we should, since we don’t know how many orders will come in a day. If we use the range, we might accidentally miss orders that fall outside the range. Point 2 means we should set the Header parameter to xlYes or xlGuess. The latter lets Excel guess whether or not headers exist. Since we already know we have headers, it’s better to be explicit and set Header to xlYes rather than relying on the logic of Excel’s guessing algorithm. Since cities are duplicated, we want to sort first on cities, then on amounts. This approach will gather all of the cities together, ordered alphabetically, then sort by the amount within each city group. What we’re left with are ordered amounts, grouped by city. So, for Point 1, we want to use the City column as the first key and Amount as the second key. For SortOrder, xlDescending corresponds to 9 → 0 and Z → A order. Normally we would use A → Z for cities. For amounts, we want the biggest at the top of its City group, so let’s use xlDescending for the Amount and xlAscending for City. We can sort columns A through E all together, so let’s specify the Columns collection as Columns("A:E"). All together, it looks like this: Sub SortData() Columns.Sort key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("E"), Order2:=xlDescending, Header:=xlYes End Sub Phew! That’s a long VBA expression, but it all works out as we expected: Screenshot of sample data sorted Sample data sorted by City (A-Z) and Amount (9-0) The red box shows the A → Z sorting of cities, while the blue and green boxes highlight descending amounts in Annapolis and Baltmore respectively. Make powerful macros with our free VBA Developer's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's Kit Programming Language VBA Python Get my free kit Do we need to specify which columns? In the code above, we used Columns("A:E") to specify we wanted to sorted only columns A, B, C, D, and E. If you simply use the Columns collection, all columns on the sheet will be sorted. If you’re uncertain whether there’s data that shouldn’t be sorted somewhere on your spreadsheet, I recommend explicitly specifying the columns to sort. Sometimes spreadsheet creators store static data in Column AA or a similar column far off to the right. If you use the entire Columns collection, without specifying which columns, this static data will be rearranged too. When xlGuess Fails A very easy way to see how the default header detection algorithm, xlGuess, fails is to try this code: Columns.Sort Columns("A") Header sorted with data xlGuess guessed incorrectly The data is sorted by the Family Name Key in Column A, but since Excel didn’t guess correctly about headers, the header was sorted as if it were also a name. This is very common when your header data type, String in this case, matches the data type of the data in the column. Final word Use .Sort on a Range object or Columns collection to sort data based on the columns in question. A macro for sorting can be as simple as specifying a single Column as a key (Columns.Sort Columns(1)) to as complex as layering keys for groups (group Cities then sort Amounts descending) while narrowing the data by calling out specific ranges (Columns("A:C").Sort). If you start to get into custom orders and orientation, it can be even more complicated. You need to assess the situation and choose which parameters to include and how to set up the code for your use case. After all, that’s what programming is all about. If you were wondering: after architect Walter Gropius, the names are the signatories of the United States Declaration of Independence. If you haven’t already done so, I encourage you to subscribe below for a structured curriculum designed to help you learn more VBA tricks like this one. We use this curriculum to guide you through more advanced sorting algorithms, like these custom algorithms for sorting data inside an array: VBA Bubble Sort VBA Quicksort
>> Read more

Send an email through Gmail using VBA (Fri, 28 Aug 2020)
Sending an email through Gmail with VBA is easy once you know the correct Gmail SMTP server and port information. After onfiguring your message using the Microsoft CDO library, your macro will be ready to send. If you have 2-step verification (2-factor authentication) enabled on your Gmail account, there’s an extra step you’ll need to take. I’ll walk you through the whole process in this tutorial. Example - VBA Gmail Send Email through Gmail with VBA 'For Early Binding, enable Tools > References > Microsoft CDO for Windows 2000 Library Sub SendEmailUsingGmail() Dim NewMail As Object Dim mailConfig As Object Dim fields As Variant Dim msConfigURL As String On Error GoTo Err: 'late binding Set NewMail = CreateObject("CDO.Message") Set mailConfig = CreateObject("CDO.Configuration") ' load all default configurations mailConfig.Load -1 Set fields = mailConfig.fields 'Set All Email Properties With NewMail .From = "youremail@gmail.com" .To = "recipient@domain.com" .CC = "" .BCC = "" .Subject = "Demo Spreadsheet Attached" .Textbody = "Let me know if you have questions about the attached spreadsheet!" .Addattachment "c:\data\testmail.xlsx" End With msConfigURL = "http://schemas.microsoft.com/cdo/configuration" With fields .Item(msConfigURL & "/smtpusessl") = True 'Enable SSL Authentication .Item(msConfigURL & "/smtpauthenticate") = 1 'SMTP authentication Enabled .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com" 'Set the SMTP server details .Item(msConfigURL & "/smtpserverport") = 465 'Set the SMTP port Details .Item(msConfigURL & "/sendusing") = 2 'Send using default setting .Item(msConfigURL & "/sendusername") = "youremail@gmail.com" 'Your gmail address .Item(msConfigURL & "/sendpassword") = "yourpassword" 'Your password or App Password .Update 'Update the configuration fields End With NewMail.Configuration = mailConfig NewMail.Send MsgBox "Your email has been sent", vbInformation Exit_Err: 'Release object memory Set NewMail = Nothing Set mailConfig = Nothing End Err: Select Case Err.Number Case -2147220973 'Could be because of Internet Connection MsgBox "Check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description Case -2147220975 'Incorrect credentials User ID or password MsgBox "Check your login credentials and try again." & vbNewLine & Err.Number & ": " & Err.Description Case Else 'Report other errors MsgBox "Error encountered while sending email." & vbNewLine & Err.Number & ": " & Err.Description End Select Resume Exit_Err End Sub Make powerful macros with our free VBA Developer's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's Kit Programming Language VBA Python Get my free kit Tutorial - VBA Gmail Late Bindings vs Early Binding I set this macro up with late binding, which means you don’t have to enable any external libraries. All you have to do is copy and paste the macro, and it will be ready to use. The downside is you won’t get auto-complete (IntelliSense) when you’re typing. To enable auto-complete, you need to transform the macro to Early Binding. Go to Tools > References in your VBA editor and check the box beside Microsoft CDO for Windows 2000 Library. Then, when initializing your variables, replace everything above the “load all default configurations” comment with the following block of code: Dim NewMail As CDO.Message Dim mailConfig As CDO.Configuration Dim fields As Variant Dim msConfigURL As String On Error GoTo Err: 'early binding Set NewMail = New CDO.Message Set mailConfig = New CDO.Configuration Configuring your Gmail Before you can programmatically send an email through your Gmail account using VBA, or any other programming language, there are a few steps you need to take. The steps you take depend on whether or not you have 2-step verification set up on your Gmail account. We’ll walk you through both options here. Configuring Gmail without 2-step verification If you don’t have 2-step verification set up on your account, you may need to enable “less secure apps” in your Gmail mailbox settings in order to get your macro to work. To enable less secure apps, follow these steps: Navigate to your Google Security Settings in your Google Account Settings On the bottom of the page, in the Less secure app access panel, click Turn on access. If you have 2-step verification enabled, you’ll see a message like This setting is not available for accounts with 2-Step Verification enabled. Such accounts require an application-specific password for less secure apps access. If you got this message, we’ll walk you through how to configure your application-specific password in the next section. Configuring Gmail with 2-step verification 2-step verification is just a second step you take when logging into your Google account, like entering a code or security key. If this is how you log into your account, you’ll need to create a unique app password specifically for sending emails with VBA. Here’s how you do that: Navigate to your App Passwords page in your Google Account Settings. If this page doesn’t work for you, most likely 2-Step Verification isn’t set up for your account or Advanced Protections is enabled. Once on the App Password page, scroll to the bottom. Under Select app, choose Mail. Under Select Device, choose Windows Computer. Click Generate. Generate App Password This should lead you to a screen with a 16-character App Password. Remember this password! This 16-character code is what we’ll put for our account password in your VBA macro in the next section. Google App Password for VBA Note: You may be able to select “Other (Custom Name)” in the Select app dropdown in Step 3, above. From there, you could give your app password a more meaningful description, like “VBA SMTP.” This is my preferred choice but Google has restrictions on how many “Other” passwords you can have activated, so that’s why I defaulted to the “Mail > Windows Computer” option. They both work the same way. If you choose Other, your App Password screen will look like this: Google App Password for VBA (Other) Configuring your Macro Customizing your Gmail Message Once you have your Google Account set up, you’re ready to configure your macro. There are a couple fields you need to change to get the macro to work for your account. The With NewMail code block has parameters you can change to control your message. With NewMail .From = "youremail@gmail.com" .To = "recipient@domain.com" .CC = "" .BCC = "" .Subject = "Demo Spreadsheet Attached" .TextBody= "Let me know if you have questions about the attached spreadsheet!" .AddAttachment "c:\data\testmail.xlsx" End With The .From parameter should be your gmail address, but you can configure the other settings however you want. If you’d rather your email display your name instead of your email address in your recipients inbox, remove your email address from the .From parameter and add the following code, instead: .Sender = "youremail@gmail.com" .From = "Your Name" To send emails to more than one email address, comma-delimit the .To field with multiple email addresses, like this: .To = "recipient1@domain1.com, recipient2@domain2.com" To attach more than one attachment to your email, just add multiple .AddAttachment lines, like this: .AddAttachment "c:\data\spreadsheet.xlsx" .AddAttachment "c:\data\rawdata.pdf" Updating your Configuration Fields The With fields section is where you need to adjust the SMTP settings specific to your Gmail account. With fields .Item(msConfigURL & "/smtpusessl") = True 'Enable SSL Authentication .Item(msConfigURL & "/smtpauthenticate") = 1 'SMTP authentication Enabled .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com" 'Set the SMTP server details .Item(msConfigURL & "/smtpserverport") = 465 'Set the SMTP port Details .Item(msConfigURL & "/sendusing") = 2 'Send using default setting .Item(msConfigURL & "/sendusername") = "youremail@gmail.com" 'Your gmail address .Item(msConfigURL & "/sendpassword") = "yourpassword" 'Your password or App Password .Update 'Update the configuration fields End With Here’s some information you need to know about this section. First, the smtpserver field is smtp.gmail.com and the smtpserverport field is 465. These fields are defined by Google and tell the macro to send your email using Gmail’s servers. Second, the sendusername field should be the full email address associated with your Gmail account. It’s the same username you use to login. Finally, let’s talk about the sendpassword field. If you don’t have 2-step verification enabled, you simply put your account password here and you’ll be ready to run your macro. It’s worth noting Google has been cracking down on exposing your password like this, so I highly recommend enabling 2-step verification and using an App Password, instead. Speaking of App Passwords, remember that 16-digit code you generated earlier (if you had 2-step verification enabled)? That 16-digit code is what you need to paste into the sendpassword field if you’re using 2-step verification on your Google account. Once you update these fields, you’re ready to run your macro. VBA Email successfully sent through Gmail That’s all there is to it! Once you have your Gmail set up to support sending emails from external codes and have your macro set up to properly link to your account, you’re ready to run the SendEmailUsingGmail macro. If it runs successfully, you’ll get a message box saying Your email has been sent. Your email will show up in your Sent folder in Gmail. Checking your Sent folder is a good way to verify your email was successfully delivered: Email sent through Gmail with VBA Application Ideas You can use a macro like this to make a custom mail merge application to mass send emails to a list of email addresses. You’ll be able to quickly loop through a list of email addresses in your spreadsheet and send to multiple recipients, though I recommend disabling the msgbox warnings if you’re sending in a loop! One thing to keep in mind is Google places limits on the number of emails you can send per day. For example, there’s a G Suite limit of 2000 messages per day. Keep this in mind and review your bulk sending practices to avoid being blocked or sent to spam. I have a free VBA developer’s kit I want to send you as part of my Write Better Macros in 7 Days challenge. To see how this dev kit can take your VBA to the next level, please subscribe using the form below:
>> Read more

Manual Calculations in Excel VBA (Fri, 07 Aug 2020)
When you work with very large workbooks that make complex calculations, Excel can slow down. If you’re using VBA to change cells, especially in an iterative manner (like a for-loop), Excel can slow way down. This happens because every change triggers another complex calculation in the background before the next iteration is executed. To run your code faster, you can turn off automatic calculation and manually calculate sheets. It’s still via VBA, though, so I am using “manual” quite loosely here. Manually calculating sheets is just one of several ways to speed up your macros. Alternatively, for interactive programs, you may want the user to double-check the inputs before performing any calculations at all. This could come in handy when using an API to load data. For example, the API loads the data, the user checks it within the workbook before calculations occur, then the input can be reverted or the calculation finalized. Whatever your use case, let’s learn how to make manual calculations with VBA. The First Step Choose What You’ll Calculate Calculate The Whole Workbook (or Workbooks) Calculate An Individual Workbook Calculate One Sheet Calculate Any Range (Cells, Rows, Columns) Beware of Sequence-Sensitive Formula Connections The Final Step Conclusion The First Step First, you need to turn off automatic calculations. By default, Excel uses automatic calculations, and very few users turn it off (including power users). That makes sense, because imagine how tedious it would be to constantly force formulas to calculate manually? Moreover, it’s part of coding best practices to set environmental variables explicitly at the beginning to ensure your processing environment does not invalidate any of your assumptions (this is another reason why strictly typing your variables is beneficial). Here, your environmental variable is the Application.Calculation property. Usually it will be set to xlCalculationAutomatic. To enable manual calculations, you’ll need to change it to: Application.Calculation = xlCalculationManual Even if the user has already set the property to manual calculation by themselves, there is no harm in adding this one line to ensure your program functions properly. Notice that this is an application-level property. It’ll be set for all open workbooks unless you’ve initialized a second instance of Excel (which is uncommon). If you want, you can even store the pre-program property value so you can change the calculation style back to the one the user initially had. This would be part of best practices for UX (user experience), since the user will never even know the property was changed. Generally, though, setting it back to xlCalculationAutomatic after your program finishes (or aborts from an error) is a safe bet. Choose What You'll Calculate You can use VBA to calculate the entire workbook (or all open workbooks), or you can be more selective in what is manually calculated. In this section we look at all the ways you can trigger a manual calculation for different parts of your workbook. Calculate The Whole Workbook (or Workbooks) The easiest and usually most robust method is to calculate all open workbooks. All the formulas in the open workbook(s) will be calculated at the application level, whether they are contained within a single sheet, spread across sheets, or even spread across workbooks. You can use either of these lines to force a calculation on the whole workbook: Application.Calculate Calculate We used the Application object, so all open workbooks within the scope of the Excel application will calculate. Generally, because the calculation happens once, unless you have very significant interaction between a large number of cells, the calculation will execute very fast. Of course, if you include this in an iterative piece of your code, Excel will calculate repetitively, possibly negating the benefit of manual calculation. For efficiency, try to calculate as infrequently as possible. Calculate An Individual Workbook If you have Intellisense enabled, you’ll see that the Workbook object does not have a .Calculate method. It is not possible to calculate a single entire workbook via VBA. You will need to either calculate all open workbooks or calculate all the sheets on a specific workbook. This will require iteration if there is more than one sheet, which is explained in the next section. The alternative to this is to create a new Excel Application instance with VBA and open your workbook in that instance, but that’s outside the scope of this tutorial. Calculate One Sheet The Calculate method lets you calculate formulas on individual sheets. In fact, in the Excel interface, there’s an option for calculating just the current sheet (check under the Formulas ribbon). This can be replicated in VBA. .Calculate is a VBA method that applies to multiple objects, including the Sheet (and Worksheet) objects. To calculate just a single sheet, reference that sheet. Here we’ll use a sheet named API Content: Sheets("API Content").Calculate Make powerful macros with our free VBA Developer's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's Kit Programming Language VBA Python Get my free kit While it’s not possible to calculate an entire workbook without calculating all workbooks, it is possible to calculate a single sheet. Thus, a simple solution to calculating one workbook but not another is to cycle through all the sheets in a specific workbook. For example, you might download data in the API Downloader workbook but store content in the Long Term Data workbook. If you want to calculate the entire API Downloader workbook only, you’ll have to do something along these lines with For Each loops or another iterative structure: Sub CalculateAllSheets() For Each s In Workbooks("API Downloader").Sheets s.Calculate Next s End Sub For an API example, you might run the API subroutine to make a GET call to the API endpoint, populate a worksheet with the new data, then ask the user to verify the downloaded content. Once verification is complete, you might work in some programmatic integrity checks, like a sum to ensure the total isn’t negative or an average to ensure there are no significant outliers, and then let Excel calculate the sheet. If there’s an error, identified either by humans or by code, you can roll back the changes without affecting any of the other sheets in the workbook. Calculate Any Range (Cells, Rows, Columns) You can get more granular with your calculations, too. Instead of an entire sheet, you can calculate a range. Just set your range and use the .Calculate method. Let’s say you have a set of formulas in B1:B5. If you’ve enabled manual calculations (by setting Application.Calculation equal to xlCalculationManual) and you want to just calculate the first two cells after each iteration of a loop, you can do it this way: Range("B1:B2").Calculate You can force a calculation for any of the following ranges without calculating anything else: Rows (Rows(1).Calculate) Columns (Columns("A").Calculate or Columns(1).Calculate) Cells (Cells("A1").Calculate or Cells(1,1).Calculate) Ranges (Range("A1:D5").Calculate) This is a good way to hone in on a few formulas to cut down recalculation times. Beware of Sequence-Sensitive Connections When using manual calculations, it is important to be aware of how your formulas are connected to each other. If Formula A flows into Formula B, make sure you calculate Formula A first. Otherwise, Formula B will not have the most updated data for its calculation. Calculating them out of order will cause Formula B to result in erroneous output. This is precisely why Excel defaults to automatic calculations. In very complex workbooks, this sequence-sensitivity is the reason many programmers simply calculate the entire workbook. It still allows for changes to occur without calculation, but when calculations are made, it is assured that all calculations are updated simultaneously. Let Excel handle the updates for the entire workbook if you’re not sure which formulas are connected to which data. The Final Step Of course, once you are finished with your calculations, make sure to return the Application.Calculation property back to its original state (for most applications, that will be the xlCalculationAutomatic value). For less tech-savvy users, forgetting to do this might cause them to think you broke their Excel application and complain! It’s also important to remember to set the calculation state back to automatic inside any error handling routines you write that could abort your code early. This is a common a mistake, and a common reason why people may think your macro messed up their worksheet. If you have a reason to calculate formulas manually in Excel, whether it’s to speed up calculations or to inject human interaction, it really is a straightforward process. You can force manual calculations for a variety of targets, including all open workbooks or any-sized range object, even single cells. It’s very important to be aware of how formulas rely on others, though, because overlooking the formula connections can cause unexpected output when manually calculating out of order. With automatic calculations, Excel tracks formula calculations to ensure all parts are up to date, but if you do it manually, you’ll need to take this into consideration yourself. For more VBA tips like this one, I hope you’ll subscribe using the form below.
>> Read more

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. Make powerful macros with our free VBA Developer's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's Kit Programming Language VBA Python Get my free kit 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 As Integer, nameCount As Integer Dim methodCount As Integer, amountCount As Integer Dim oC As Variant, nC As Variant Dim mC As Variant, 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 Make powerful macros with our free VBA Developer's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's Kit Programming Language VBA Python Get my free kit 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. Make powerful macros with our free VBA Developer's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's Kit Programming Language VBA Python Get my free kit 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's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's 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's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's 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's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's 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's 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's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer's 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 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

Daily Dose of Excel

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

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

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

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

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

Putting Statistics (Mon, 19 Aug 2019)
Because of my recent terrible putting, I decided to keep track of every putt to see what I can see about it. Here’s how I compiled the stats. I wrote down how many paces each putt was and assumed one step was 2.5 feet. The Made formula in the table is [crayon-5f730b4230d6f480743466/] 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

P3

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

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

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

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

The Excel Charts Blog

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

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

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

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

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

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

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

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

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

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

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.