Select Feeds in excel

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

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

Static Tables in Power Query, Power Pivot and Power BI (Wed, 31 Mar 2021)
Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you'd write a query to load it from an external source. But any time you have data that won't change (or changes rarely), you can use a static table. That is, a table that doesn't need a data source, it […] The post Static Tables in Power Query, Power Pivot and Power BI appeared first on My Online Training Hub.
>> Read more

Change Type Using Locale with Power Query (Thu, 25 Mar 2021)
One of the most common issues I help people fix is data imported to Excel or Power BI using Power Query where the regional settings of the PC differ from that of the source data. Usually, the problem arises with dates. For example, data formatted dd/mm/yyyy imported on a PC with a mm/dd/yyyy date format. […] The post Change Type Using Locale with Power Query appeared first on My Online Training Hub.
>> Read more

pythonpip.com

Python re match Example (Wed, 24 Mar 2021)
In this tutorial, you will learn search substring pattern using python with re.match method. The re.match() function will search the regular expression pattern and return the first occurrence. Python re match Example The match() takes two arguments- a pattern and a string. If they match, it returns the string otherwise returns None. The re.match() will […] The post Python re match Example appeared first on pythonpip.com.
>> Read more

How To Use Regex With Python (Tue, 16 Mar 2021)
This python tutorial help to understand regular expressions (RegEx) using python 3, We will use Python’s re module to work with RegEx. Regular expressions are widely used in UNIX world. Python Regex A Regular Expression (RegEx) is a special sequence of characters that defines a search pattern. This helps you match or find other strings […] The post How To Use Regex With Python appeared first on pythonpip.com.
>> Read more

Compucademy

Conditional Probability with Python (Tue, 13 Apr 2021)
In this post we are going to explore conditional probability with Python. Here’s a fun and potentially tricksome question about probabilities: In a family with two children, what is the probability that, if at least one of the children is a girl, both children are girls? First of all let’s state a couple of assumptions […]
>> Read more

Ransom Note HackerRank Challenge in Python (Mon, 29 Mar 2021)
This Python programming challenge is adapted from a challenge on HackerRank called Ransom Note, which is part of a collection involving hash tables. If you are unfamiliar with HackerRank, you can read about it here: Introduction to HackerRank for Python Programmers. The problem descriptions on HackerRank are sometimes a bit obscure, and one of the […]
>> Read more

P3 Adaptive

Blog - Excel Dashboards VBA

Excel Dashboard Charting with Shapes (Sat, 06 Mar 2021)
An Excel dashboard created using excel charts in the form of shapes. It compares data from prior periods with actual data in a beautiful final output.
>> Read more

Multi-National Excel Dashboard (Tue, 29 Dec 2020)
Excel dashboards are fantastic and a wonderful way to show a point in time snap shot of any number of corporate, customer, household positions. They convey high level information in a succinct one page data collection point
>> Read more

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

By: Peggy Wong (Sat, 10 Apr 2021)
Hi Jon, yes it is very good. hee... Jon cause I my learning step which quite a bit slow. May I have the step 1 step 2.. how to get the report done. Cause i was thinking if i want to pull report out in a diff folder. I am sure this is a very good and its save a lots of space in my PC. Can you share with me the step. Thank you Peggy
>> Read more

By: Vhena Upton (Sat, 10 Apr 2021)
Hi Jon, I've been watching your videos on YouTube about excel and power query. You made it easy to follow and learn. Thank you. I have a shared workbook on OneDrive used by multiple user. Each user need to do some investigation and mark the item resolve when everything is said and done. The users have been saying it is difficult to make updates when someone else is editing and filtering the file. They are on top of each other. I put this file on power query and created 3 queries for each team. The challenge I have is how do I connect the 3 queries to the master file so that each of the team's update is captured? How do I extract the Resolved items from the master file so that the master file only contains in progress and new cases. Can this be done in power query? Unfortunately, I can't give you sample data due to confidentiality concerns. I'm hoping you can provide brilliant insight on what might be the best tool to use for this type of situation. Thanks Jon.
>> 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

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

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

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

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

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

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

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

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

The VBA Tutorials Blog

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

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

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

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

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

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

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

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

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

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:

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:

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 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 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 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.

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:

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:

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")

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 configuring 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 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 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.

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.

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:

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:

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

Daily Dose of Excel

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

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

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

P3 Adaptive

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

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

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

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

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

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

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

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

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

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

The Excel Charts Blog

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

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

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

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

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

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

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

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

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

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

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

Print Sitemap

Logout Edit page