Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Before VBA there were XLM macros. VBA was introduced in Excel version 5, so these Excel macros are also known as Excel 4 macros. Even though these XLM macros are quite old they still work in Excel 2016. To use them you call them like a regular function e.g. =GET.CELL(64,A1) but entering this into a …
The VBA Shell function runs a command in the operating system shell. The shell refers to the interface, typically where you type commands, to run programs. This is called a command line interface or CLI. In Windows, the shell is commonly known as the Command Prompt. To access it, click on the Windows button and …
VBA SELECT CASE allows you to test an expression against multiple conditions, and then carry out a series of commands when a matching condition is found. It's like using lots of IF statements, but easier and clearer to understand. Here's the basic structure of SELECT CASE Select Case Expression Case Condition_1 Code Block Case Condition_2 …
The VBA IF THEN ELSE statement lets you test a condition, and then carry out other commands if the test result is true (or equivalent to true - more on that later). Excel has an IF function that you use in the worksheet, but this is a different beast and does not work the same …
Way back, when Excel 4.0 was around, it used a macro language called XLM. VBA was introduced in Excel 5.0. These XLM macros are still in Excel and you can use them to do certain cool things. One of these macros is FILES. Now I call these macros, but when you use them you just …
After my last post on debugging VBA I received a few comments with other tips or commands people use for debugging. I'm going to have a look at these here. Option Explicit The first thing is using the Option Explicit statement. There are a number of Options you can use in VBA, and Option Explicit …
When you write VBA, or any programming language, you are going to encounter errors in it, or should we call them unintended features? Basically you can't write any substantial amount of code without needing to fix errors and make sure that it works as it's supposed to.
If you are working with strings (a sequence of letters, numbers or other characters) in VBA then you are going to need to know the functions you can use to manipulate those strings. This post looks at the VBA string functions only, not the functions you can use in the worksheet. What's a String? Why …
This post is going to look at how to return an array from a udf. My last post looked at how to return a range from a UDF and in that, I included a small, bonus function which gave you the interior color of a cell. I'm going to modify that function so it becomes …
I've previously written a UDF to count colored cells and then perform various maths functions on the values in those cells, like SUM, AVERAGE or COUNT. In this post I'm going to rewrite that code so that we can return a range from a UDF. This range contains all the cells that match the reference …
We often get asked if we can count colored cells on a worksheet, and yes you can. You could count cells using a combination of conditional formatting, filters and the SUBTOTAL function, but I want to show you how writing a UDF (user defined function) can let you count, sum and average colored cells, in …
In a previous post I discussed storing your UDF's in PERSONAL.XLSB so that you could use them in any workbook. You do run into a problem with this though in that unless you create a reference to PERSONAL.XLSB, every time you want to use your UDF, you need to precede the function name with PERSONAL.XLSB!. …
You've created your own PERSONAL.XLSB and want to use it to store VBA that you are going to use all the time. Sounds cool. You are really keen to know what the cube of numbers are so you write this function and place it into a module in PERSONAL.XLSB. Function cube(number As Long) As Long …
My last post looked at creating a simple user defined function (UDF). This post looks at a little more advanced subject of combining multiple functions into one multi function UDF. Sometimes you’ll write code to do similar jobs. Maybe one function to remove alpha characters (letters) from a string, and another to remove non-numeric characters. …
Any user of Excel will have used its functions. From the humble SUM to VLOOKUP, INDEX and MATCH, OFFSET and myriad others. Excel also allows you to create your own functions, these are known as user defined functions, or UDF’s. By creating a UDF you can expand the functionality of Excel and get it to …
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 …
After you’ve written some VBA, or created your own form, or you’ve found some useful code on the internet, you might want to put this into your PERSONAL.XLSB. If you leave the code/form in the workbook it was created in, then that workbook needs to be open every time you run the code or use …
You have a workbook with lots of sheets and you want to do things like hide sheets, delete some sheets or just sort them. By using a very simple userform and some VBA we can do these things with a few clicks of our mouse. The Form The forms consist of one ListBox and four …
Prompted by a couple of questions on our Excel forum I thought I'd write about how to record a macro in Excel, and show how simple it can be. Excel contains a tool called the Macro Recorder, and what it does is to create VBA code for the actions you perform in Excel. So you …
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 you want to maximise Excel on a single monitor you can just click on the maximize button That's ok but this just maximizes it on the current monitor. If you have multiple monitors you may want to maximize it across all of your monitors, but Excel doesn’t provide any quick way of doing this. …
Imagine that you are preparing data for a pivot table, and you want to make sure that all the cells contain the correct data type. We know that a date or number entered as text can mess things up so if we had a quick way to identify these problems, it could save a lot …
You can create very complicated and powerful VBA routines in Excel, but not all VBA code has to be like this. VBA is here to make your life easier. Even simple, little things can speed up what you do. Whether you use the macro recorder, or write your own code, they are not hard to …
If you want to limit the choices available for the values that can be entered into a cell, you could use a data validation list. In the example image below, we are creating a roster for the teachers that are on playground duty during the week. The data is laid out as a table, 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 want to save data from one Excel session to another, you can store this in the Windows registry. Using the registry is good for saving things like user preferences or program configuration data. There are other means of saving this type of data like external files, hidden sheets and defined names, but I'm …
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', …
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 …
