Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
One thing I often found frustrating with VBA is the lack of any easy way to see the values in a range object. I often use Debug.Print when debugging my VBA and I'd love to just be able to Debug.Print a range like you can with other variables. But because a range is an object, …
Normally variables cease to exist once your Sub or Function has finished executing. Static variables allow you to preserve a value after your code has finished. Let's say you want to keep track of the number of times something happens, like how many times someone clicks on this button they are not supposed to.
You might want to save your charts as images that can then be used in other applications, like email or displayed on a website. Selecting a single image then copying/pasting or saving it, isn't too much bother. But if you want to do that for several images it quickly becomes tiresome. I've written some VBA, …
Following on from Mynda's post last week on Dependent Data Validation, I've written a little VBA that enhances the functionality. What this code does is to clear any cells dependent on the value of another data validation list. I'll call these downstream cells. So if I change the Country, I'm going to clear the values …
In my previous blog post I created a progress bar using a userform. We can use this progress bar to communicate to our Excel user that our VBA code is still running. In this post I’m going to look at using the Status Bar to do the same thing, and create something like this. Status …
If your code runs for any noticeable amount of time, you might find that using some sort of visual feedback is useful for your users. Letting them know just how long something will take to complete, and just as importantly, letting them know that Excel hasn't hung, reassures them that the code is working and …
If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you'll see a default error message like this: But you can change this and instruct Excel to allow your code to deal with run-time errors. NOTE : I’m going to use …
In a previous post I looked at using the FileSystemObject to search through folders on your computer's disk and create a hyperlinked list of files. Clicking on the hyperlink would open the file. FileSystemObject (FSO) can also be used to retrieve meta data from a file, like the creation date, last modified date or file …
If you are using data validation (and you should) to help you with your data entry, then you might find that the list you are using doesn't have all the values you want. For example, if you were using a list to assign a priority to some work, your list may consist of 'Not Started', …
“Funny” days versus funny “Days” Guest post by Roger Govier, Microsoft Excel MVP Do you ever have “Funny” days? You know, the days when you start thinking about a problem, overcomplicate it and get carried down a long and spurious path where your vision becomes totally tunnelled and you can’t “see the wood for the …
If you've been following the previous posts in this series you'll know that by now we have a user form that allows us to enter data into a table, and allows us to update and delete existing records. If you haven't read the other posts about Excel Forms, here are the links. Excel Forms Create …
Carrying on with the Excel form I created in an earlier post, I’m adding code to allow us to insert new records into any row of the table (not just at the bottom of the table), delete records and update existing records. Related Posts: This is the last part of a series of posts I've …
In my previous post about Excel forms the date picker I used was a 32 bit ActiveX control. If mentioning 32 bit ActiveX controls makes your eyes roll back in your head, then this post is for you 🙂 This date picker is implemented as a VBA class. Which means all you need to do …
Excel forms, or Userforms, can be used to collect information from a user, validate it, then enter that data into your worksheet. In this post I am going to show you how to create a form in Excel that gathers data, then populates a table for tracking expenses. This is meant as a simple example …
When you select a cell, or cells, in Excel, the row and column headers change color to indicate what you have selected. As you can see here we have selected B2. Or is it 2B? Hmm, 2B or not 2B? If you have a busy sheet though, you may want a more obvious indication of …
What Are Events? An Event occurs when certain actions are performed by the user or VBA. For example when you open a workbook the Workbook_Open event is triggered. When you (or your VBA) saves a workbook, the Workbook_BeforeSave event is triggered. These events are built into Excel VBA and are executed automatically by Excel. You …
One of our students (hi Leah 🙂 ) sent me a question last week asking how she could change the color of words (sub-strings) in text. She was trying to visually indicate where words were on the sheet. She supplied some code that she got somewhere on the net. I'm not sure where from so …
I recently had a client who has a requirement to protect and unprotect a lot of sheets. This was something they didn’t do very often, but when they did, they described the process of doing it manually as cumbersome. I’m sure that once you get more than a few sheets, protecting and unprotecting each one …
You can customize the Ribbon in Excel so that you add your own tabs and icons to run commonly used tasks and tools, or run your macros. NOTE : This applies to Excel 2010 and 2013 only. Modifying the Ribbon in Excel 2007 is a different process which I will cover in another post. Just …
You can customize the Quick Access Toolbar (QAT) in Excel so that you add icons to run your macros, or commonly used tasks and tools. Just follow the 8 steps below, it shouldn't take you more than 5 minutes, if that. You can also modify the Ribbon to show custom tabs, groups and icons for …
What You Can Do With Macro Enabled Templates This is up to you. Using a template allows you to design a workbook layout to look how you want. Using macros gives you the power to customize that template. Let's say you are creating invoices and each invoice has a new, unique number. VBA can keep …
I’m sure most of us have experienced frustration at Excel’s refusal to allow us to unhide multiple worksheets. This is no April Fools' joke! I know it seems illogical to allow the hiding of multiple sheets but not the un-hiding. Ho, hum....here are a couple of easy fixes: 1. Custom Views A custom view is …
When you create a macro in Excel, by default, the macro works only in the workbook that contains it. But if you need to use a macro in multiple workbooks what do you do? You can use your own personal macro workbook, called PERSONAL.XLSB. This is a hidden workbook into which you can store macros …
Barcode Types & Uses There are LOTS of barcode types used for a multitude of things. Various internationally recognised standards exist (and some not standardised) for things like retail stock tracking, mail and parcel tracking, ISBN numbers, warehousing, pharmaceutical packaging and many other uses. Like creating secret spy messages to leave pinned up around the …
We get requests now and again asking how to convert numbers to words (or convert currency) e.g. 123.45 becomes One Hundred Twenty Three Dollars and Forty Five Cents. Excel doesn’t provide a function to do this so I’m going to write my own. Microsoft does provide some VBA code which I will use as my …
So you’ve got some code and you want to be able to run it, but opening up the list of macros (ALT+F8) and choosing the one you want just isn’t quick enough. Or if you have a lot of macros it can get tiresome looking through your list to find the right macro before running …
The Developer tab in Excel gives you access to features and tools that help you to write and run macros. It’s not enabled by default so this is what you do to get access to it. Excel 2007 Click the Microsoft Office button (the round button at top left) Click on Excel Options Click Popular …
Real World Scenario One of my customers (thanks Tony) creates invoices in Excel for his clients and at the end of each month he wants to email these invoices to each of those clients. Each invoice is on a separate worksheet. If he has a lot of sheets doing this one by one will quickly …
The Scenario Let’s say that you have a lot of sheets in your workbook and you want to merge all the data onto a single worksheet. If you have your data laid out in the same way on each sheet then this piece of VBA will do the trick for you. Maybe you have sales …
The Problem You have a sequence of numbers, and you want to find the numbers that are missing from that sequence. Let's use some VBA. Watch This Video - It Explains All This video explains how the code works, how to use it, and how to get the code and put it into your own …
