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: Descubra como o Power Query pode mudar a sua vida (Fri, 30 Oct 2020)
[…] Para lhe explicar como funciona o Power Query, não há melhor do que este exemplo que encontrei enquanto fazia a minha pesquisa para escrever este artigo o mais completo possível. Pode ver a explicação completa aqui. […]
>> Read more

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

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

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

Prepare Your Data in a Chart Staging Area (Mon, 19 Oct 2020)
You can spend five minutes fixing up your data, or five hours working on a chart with the wrong data. A user on the Mr Excel forum asked about creating a chart from unsuited data. He asked, “Is there a way to do this without modifying the data table?” My reply started with “I know […] The post Prepare Your Data in a Chart Staging Area appeared first on Peltier Tech Blog.
>> Read more

Precision Positioning of XY Data Points (Wed, 14 Oct 2020)
This tutorial shows how to calculate coordinates for positioning of XY Scatter data points on an Excel clustered column chart. The post Precision Positioning of XY Data Points appeared first on Peltier Tech Blog.
>> Read more

Welcome to the Day of Data! (Tue, 13 Oct 2020)
Microsoft is partnering with NASA to bring you the Day of Data, where you can explore how data powers astronauts, space missions, and our world! Join us over the next few weeks to celebrate World Spreadsheet Day and 20 years of humans living and working aboard the International Space Station. This is the place to find space-themed trivia, an […] The post Welcome to the Day of Data! appeared first on Peltier Tech Blog.
>> Read more

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

The VBA Tutorials Blog

Use VBA SendKeys to send keystrokes anywhere (Fri, 06 Nov 2020)
Most VBA applications are designed to work within Excel and other Microsoft Office programs, like Powerpoint and Excel. VBA offers a way to interact with web requests and deal with HTTP responses (however clumsy it may be) through APIs and web scraping, too. But sometimes you want to interact with non-MS Office programs that are not web content. In that case, you can use SendKeys. Admittedly, SendKeys is also somewhat clumsy and it’s not the most reliable, but it can get the job done in a pinch. VBA is really designed and optimized for MS Office applications, hence Visual Basic for (Microsoft) Applications, but SendKeys is useful when you want to deal with other programs. SendKeys and The Active Window Sending Keyboard Shortcuts Special Characters Opening and Activating Other Programs Opening other applications with Shell Switching to open windows with AppActivate Note on Shell and AppActivate Automate Some Tasks SendKeys and The Active Window Many tutorials start off with a warning on using SendKeys, and they have a good reason. SendKeys literally sends the indicated keystrokes to whatever element is active. Ideally this is the place you want the keystrokes to impact, such as a cell in a worksheet, an open notepad, or a browser URL bar. However, this can cause some problems. If the wrong window is active, you’ll be sending keystrokes to the wrong window. Moreover, the active element at exactly the time SendKeys executes receives the keystrokes. Thus, you cannot step through your code to verify its correctness. In fact, you can’t even run some bits of code from the VBA Editor, as the VBE will still be active! To illustrate: open the VBE, input this code, and run the macro with F5. What happened? Sub sendKeysTest() Cells(1, 1).Select SendKeys "Test value" End Sub You’ll end up with something like this, which probably is not what you intended: Incorrect result Sent keys to the wrong place! Despite you trying to activate Cell A1, the VBE was still active at the time of running, so SendKeys placed the keystrokes at the cursor in the VBE instead of in your desired Cell. It’s imperative to first activate whatever element you want SendKeys to impact. If the workbook is your target, run your code from the Macros selection box in the GUI. This will ensure the workbook is active. If you want to send keystrokes to another program, you’ll need to activate it. We’ll talk more about how to do that in a bit. Sending Keyboard Shortcuts Technically, you can use SendKeys to send keyboard shortcuts to MS Office programs. If you want to save a workbook after making modifications via some subroutine, it’s theoretically possible to send the Ctrl+s keyboard shortcut to save. I’d strongly recommend against using SendKeys to do this, though. It’s far more robust to use “pure VBA” to save the workbook with a Workbook.Save. This method permits precise naming of a specific workbook, thereby ensuring it’s exactly the workbook you want to save. Moreover, .Save executes as “pure VBA”, without the awkward injection of an I/O device (keystrokes) designed for us humans. Nonetheless, if you’d like to send keyboard shortcuts, it’s possible to do so with SendKeys. You just need to take care that the workbook is the active window. Special Characters Pressing Ctrl+S to save a file uses the control key, which is part of the special character set for SendKeys. You can send any key you want, but if it’s a non-alphanumeric character, you’ll need to reference it in a special way. The three most common special characters you’ll use, especially for shortcuts, will be control, alt, and shift. To send these as compound keystrokes (i.e., keystrokes sent concurrently), use the following substitutions: SHIFT = + CTRL = ^ ALT = % So, to send the keyboard shortcut for save, we’d use SendKeys "^s". Now, sometimes you might actually want to display a plus-sign or a percent-sign. To do so, you’d need to place it in curly braces, { }, so VBA knows you want that character, not its special double-role as a compound key combination. So, to send 10% to a cell, we’d use SendKeys "10{%}". The % is “escaped” by the curly braces, and VBA knows you want the actual symbol instead of the ALT key. Finally, there are several keys that have other functions (like the function keys F1-F16), tab, arrows, and so forth. These should be placed inside the curly braces, and they take the values below: Special Character Table SendKeys Special Character Table Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit If you’d rather be able to copy and paste the correct VBA SendKeys special character codes, here’s a plain text version of the list: BACKSPACE = {BACKSPACE}, {BS}, or {BKSP} BREAK = {BREAK} CAPS LOCK = {CAPSLOCK} DEL or DELETE = {DELETE} or {DEL} DOWN ARROW = {DOWN} END = {END} ENTER = {ENTER} or ~ ESC = {ESC} HELP = {HELP} HOME = {HOME} INS or INSERT = {INSERT} or {INS} LEFT ARROW = {LEFT} NUM LOCK = {NUMLOCK} PAGE DOWN = {PGDN} PAGE UP = {PGUP} PRINT SCREEN = {PRTSC} RIGHT ARROW = {RIGHT} SCROLL LOCK = {SCROLLLOCK} TAB = {TAB} UP ARROW = {UP} F1 = {F1} F2 = {F2} F3 = {F3} F4 = {F4} F5 = {F5} F6 = {F6} F7 = {F7} F8 = {F8} F9 = {F9} F10 = {F10} F11 = {F11} F12 = {F12} F13 = {F13} F14 = {F14} F15 = {F15} F16 = {F16} Opening and Activating Other Programs Sending keystrokes inside Excel to Excel is not all that useful, especially when more robust methods exist. Including SendKeys in your macro is more useful when you want to interact with other applications. First, you need another program open. We’ve written several tutorials explaining how to open and activate external programs with VBA using different methods, including VBA Shell and AppActivate. We’re going to spend a few minutes refreshing your memory on these topics. Opening other applications with Shell You can use Shell to open other applications. It only takes two arguments, the filepath for the app and an attribute for the opened window. For our purposes, let’s open Notepad, a favorite app of mine for storing a few quick notes: Shell "notepad.exe", vbNormalFocus Notepad will open a new, normal-sized instance with focus already on it. You may need the full filepath for non-standard programs. Focus is important, as this means Notepad will already be active and SendKeys can work right away. Let’s copy and paste the range A1 to C10 from our spreadsheet to a new notepad file: Sub copyAndPaste() Range("A1:C10").Copy Shell "notepad.exe", vbNormalFocus SendKeys "^v" End Sub Run this macro and you’ll see a notepad window with the contents of your cell pasted inside. Is it clean? No. Is it quick and does it get the job done? Yes. You could even bring up the save prompt for your new notepad file with "^s". Granted, there are much better ways to write to a text file with VBA. Switching to open windows with AppActivate If you run the macro above 3 times, you’ll end up with 3 separate notepads with the same text. If you want to switch to an open application, you can use the AppActivate statement, instead. AppActivate requires the TITLE of the application. If you have multiple programs with the same name, it will switch to the last active one. If your program title has changed (such as after having saved the notepad), you can use the exact name to specify which window to open. For instance, I have a plaintext file name Snippets.txt, and I can single it out like this, even if 5 other notepads are open: AppActivate "Snippets.txt - Notepad" Note on Shell and AppActivate Technically, Shell returns the PID (process ID) of the opened application. Unfortunately, AppActivate uses the window title, not the PID. You can look up the title of a window with its PID, but you must do so through the Windows API. That is way outside the scope of this tutorial, but I wanted readers to be aware that it is possible to cross-reference. Automate Tasks Why spend time learning about SendKeys? Well, to save time later, of course! I’ve found SendKeys useful in opening multiple URLs in a browser all at once. This example combines SendKeys with Application.Wait to ensure smooth execution between keystrokes transmissions. Sub openURLSAutomatically() AppActivate "Mozilla Firefox" For i = 1 To 10 targetURL = Cells(i, 1) SendKeys "^t" Application.Wait Now + TimeValue("00:00:01") SendKeys targetURL & "~" Application.Wait Now + TimeValue("00:00:01") Next i End Sub The .Wait statements ensure Firefox has time to actually open new tabs, focus on the URL box, and execute the Enter command before opening the next new tab. Application.Wait only lets you pause in 1-second increments, but you can use VBA sleep for finer control. SendKeys sends keystrokes to the active element in the active window. It’s clumsy, unreliable and only recommended as a last resort. That said, it can be helpful in a pinch. If you must use SendKeys, I’d also recommend doing as much processing as you can before invoking the statement. Concatenate, calculate, and error correct before sending the keys to reduce the likelihood of process-related hiccups. I hope you found this tutorial helpful. We’ve cross-linked to a number of related VBA posts throughout this article. If you like all these guides, please subscribe using the form below.
>> Read more

Use VBA Application.Caller to see how your macro was called (Fri, 09 Oct 2020)
If you’ve ever set up a user-defined function (UDF) or created shapes that trigger macros, you might have come across suggestions for Application.Caller. This application-level property has one purpose: It tells you where your VBA macro was called (or “triggered”) from. That information is valuable if you have slightly different procedures for the same macro depending on the context. We’ll demonstrate a couple ways to use Application.Caller to make your macros better. The 3 Variable Types Returned Common Use: Generalizing Interactive UDFs Common Use: Shapes Bonus Use Case: Debugging The 3 Variable Types Returned The Application.Caller property can return three possible variable types, so be careful using it when you’re explicitly dimensioning your variable types or using static variable types. The three types are String Range Error Strings occur when the caller of the VBA module has a name, usually a shape or a form control object. The Application.Caller property itself will be a string and will return the name of the object, so there is no need to access any .Name property of .Caller subproperty. If you have a named Shape that calls a macro, you can set a variable callingShape like this to return the name of the shape that called the macro: callingShape = Application.Caller The property can take on a second variable type for ranges. Here, it becomes a full range object, including the .Row, .Column, and, importantly, .Worksheet properties. The range can be either a single cell or an array depending on whether the UDF is placed in a single cell or is applied to the sheet as an array function. You can set the callingCellRow, callingCellColumn, and callingCellSheetName variables like this: callingCellRow = Application.Caller.Row callingCellColumn = Application.Caller.Column callingCellSheetName = Application.Caller.Worksheet.Name The Row and Column variables will be of type Long (a numeric) and the worksheet’s name is a String. You can return the Address of the cell that called your UDF by using Application.Caller.Address, which will give you both the Row(s) and Column(s) involved as a String (for example, $G$2). The third variable type that Application.Caller can take on is an Error, which generally occurs if the command returns neither a string nor a range object. The error will be Error 2023. If you test your code and get 2023 errors, they’re likely coming from a bad Application.Caller property somewhere in your code. Common Use: Generalizing Interactive UDFs Excel and VBA are powerful and extensive, but we don’t always find that perfect function for our needs. In those cases, we usually end up writing our own functions to handle whatever custom application we’re building. Furthermore, I sometimes find it easier to script a few lines of VBA than try to figure out how to nest built-in formulas to achieve the result I want, especially when I need to start working with nested ANDs and IF functions. Sometimes a very small tweak, based on which cell has called the macro, can help us generalize the UDF to more cases without duplicating code and writing separate functions. An example from busi\ness could be a function that is additive above the Gross Income line on an Income Statement, since each line item in that section of the statement adds revenue, while the same function would be subtractive below the Gross Income line, since each line item in that section is an expense. UDFs can also be more interactive with the worksheet if we use Application.Caller by leveraging the resultant Range object’s .Row and .Column properties. One example might be different insurance costs for different employees depending on the number of hours worked that week. Table with different rates for different employees Suppose the Rate < 50h and Rate > 50h are confidential, but employees should be able to edit the Special Hours Worked Function. To protect privacy, the employer hides the two Rate columns and then allows editing only of specific ranges on the sheet. Using the two hidden columns, protected ranges in the UDF could confuse users. To avoid confusion, you could import the rates using Application.Caller: If hoursWorked > 50 Then insuranceRate = Cells(Application.Caller.Row, Application.Caller.Column - 1) Else insuranceRate = Cells(Application.Caller.Row, Application.Caller.Column - 2) End If Now the rates can remain confidential and the inputs never appear in the UDF for the user. The function can be edited without confusion. This is a great, and pretty common, use case for the Application.Caller function in an Excel workbook. Make powerful macros with our free VBA Developer Kit There’s a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer Kit below. It’s full of tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit Common Use: Shapes Another quite common use case is to determine which Shape or Form Control Object called a macro. Building a visually-appealing dashboard in Excel often ends up with Shapes used as buttons, because Shapes are easy to customize with the Excel GUI - and userforms aren’t. If you have 3 buttons on a sheet, you can easily assign them to 3 separate macros, but this would be inefficient if most of the code is the same. There’s a principle in programming called DRY, which stands for Don’t Repeat Yourself. Having 3 buttons point to 3 macros that are almost identical, with only minor calculation differences, violates this principle. Instead, you could assign them all to the same macro and use the name of the calling Shape to determine which part of the macro to use. Three colored buttons for different calculations Three colored buttons for different calculations Let’s say your buttons are named rateButton, taxButton, and payButton and they all call the same macro. Now you can use a single macro rather than 3 separate ones with lots of repetition. The Select Case block might look like this: Sub ProcessButtons() callingButton = Application.Caller Select Case callingButton Case "rateButton" 'calculate rate Case "taxButton" 'calculate tax Case "payButton" 'calculate pay End Select End Sub Bonus Use Case: Debugging If you are given a very complex spreadsheet to debug, you may struggle to find the origin of a cascading #VALUE! error. If you check the VBA code and find a UDF, you could try using the Application.Caller.Worksheet.Name and Application.Caller.Address properties to locate the offending cell. From there, you may be able to figure out which inputs to the function are wrong based on their cell references. This method is particularly useful if you are given a spreadsheet with cryptic input names and zero context on the UDF inputs. In this case you may have to rebuild the original developer’s thought process, and without knowing where the original UDFs are entered into the sheets, it could feel impossible to figure out the various interactions and calculations taking place. To wrap things up, there are three variable types returned by Application.Caller: Strings, Ranges, and Errors. The Ranges are full-bodied objects and contain all the properties of a Range, including Row, Column, Address, and Worksheet.Name properties. You can use these to change the behavior of a user-defined function depending on which cell called the function or even which sheet the cell that called the UDF was on. Shape names are useful for determining which buttons on a visual dashboard have called the function. It helps you avoid one-button-one-macro code bloat. It can also be used to change your macro logic based on which form control radio button or checkboxes are checked. Application.Caller can also be useful for debugging complex spreadsheets. Play around with it until you get comfortable using it. Application.Caller isn’t used too often but it ought to be. It can shorten the amount of lines of code you write and it gives your VBA macros a polished look. If you found this helpful, I hope you’ll subscribe using the form below.
>> Read more

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 Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit 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 Kit There’s a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer Kit below. It’s full of tips and pre-built macros to make writing VBA easier. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit 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 Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit 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 Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit 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 Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit 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 Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now. Sure, I'll take a free VBA Developer Kit Programming Language VBA Python Get my free kit The .Replace Method One of the methods attached to the Range object is the .Replace method, which does exactly what you would expect. Be wary that this function is the GUI equivalent of “replace all” within the range, not just “replace the first instance”. In our currency example, we want to replace all instances anyway, so this is fine. Instead of using a loop, you can set the entire range first and Excel will use its optimized internal code to most efficiently find and replace the errant AUDs. The code is a simple one-liner: Range(Cells(2, 6), Cells(5000, 6)).Replace "AUD", "CAD" And in the simplest terms, this searches the sixth column between rows 2 and 5000, replacing any instance of “AUD” with the letters “CAD”. In letter-reference notation, you could use: Range("F2:F5000").Replace "AUD", "CAD" It’s important to recognize the Range.Replace method is different from the VBA Replace function, which is used to replace substrings within a string. Expanding the Range Of course, if you don’t know where in the spreadsheet certain information occurs, you can expand the range to anything you want. Perhaps your friend called you and said they accidentally entered “AUD” instead of “CAD” for every Canadian purchase. They need your help to fix the mistake! Moreover, they can’t show you the spreadsheet because it’s proprietary. Well, you could tell them to write this line of code, which covers a gigantic area, captures every instance of AUD, and turns it into a CAD: Range("A1:ZZ200000").Replace "AUD", "CAD" Note: if your friend’s spreadsheet is bigger than this, they need to switch to a real database… But alas! This has also changed “Audio” into “CADio”! What a disaster. The Other .Replace Parameters One pitfall of the .Replace method is that it is non-case-sensitive and replaces any matching string, regardless of whether that string fully matches. Thus, Audio is converted to CADio by mistake, because the three letters A-U-D are matched non-case-sensitively. You can use the other parameters in the .Replace method to avoid issues like this, even when searching large areas. In this section, I’ll use the named parameter method (:=) of setting them, since there are so many parameters and multiple empty commas is unsightly and a bit confusing. LookAt Optional Argument The first optional argument is LookAt, which accepts two options: xlWhole or xlPart. The function defaults to the latter, so when we searched for AUD, it rewrote any string that contained those three letters in sequential order. One way to avoid the debacle for our friend is to ensure this parameter is set to xlWhole: Sub VBA_Replace() Range("A1:ZZ200000").Replace What:="AUD", Replacement:="CAD", LookAt:=xlWhole End Sub Now the full word must match for a replacement. Since “Audio” is not exactly the same 3 letters as “AUD”, we don’t end up with “CADio”. If we had an option Audio Audi, xlPart will match both “aud” strings in both words, giving us CADio CADi. Luckily, you can easily avoid this now with LookAt:=xlWhole. MatchCase Optional Argument Another approach in this situation could be to match cases. Since we are searching for all uppercase currency codes, and we know the Item Type column is proper case, we could force the case to precisely match to get the same outcome: Range("A1:ZZ200000").Replace What:="AUD", Replacement:="CAD", MatchCase:=True A quick quiz for you: if we truly did not know the stylization of the data, would it be better to use LookAt or MatchCase? Answer: LookAt, which forces the entire string to match. There is less chance that non-currency entries will be exactly the three letters A-U-D in that particular sequence, while it is very possible “AUD” might appear in multiple words, whether they’re capitalized or not. Had the Item Types used capital letters - as in AUDIO, PERIPHERAL, etc. - using only the MatchCase parameter would still have left us with CADIO. Other Parameters For finding and replacing strings in ranges, the other parameters are not important. Whether you choose to search by columns or by rows in SearchOrder is irrelevant for .Replace, because it will replace whatever it finds in all columns and all rows, regardless. Moreover, formatting is related to a completely different function and is not necessary for finding and replacing strings as we’ve explored here. Replacing all instances of a string of text can be quite useful, particularly to fix mistakes or to update spreadsheets. However, it is important to think through how optional parameters might affect your output, because there is no simple undo button when using VBA. You certainly don’t want to make a mistake that affects 200,000 cells and cannot easily be undone. If that worries you, I recommend saving your workbook before using .Replace. Or at least test it on a smaller range. You can remind your user to save, too, by running some code to programmatically initiate a Save As Dialog, if you want. To learn more VBA tips like this one, subscribe using the form below.
>> Read more

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

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

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-5fbc585674316824864780/] 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-5fbc585674d36660840371/] 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

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

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

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

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

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

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

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.