Excel Macro for Yesterday
I frequently use the shortcut Ctr + ; to enter today’s date in a cell. I thought it would be useful to be able to enter yesterday’s date in a cell. I wrote a one-line macro to do it for me.
(more…)Updating the Excel Header with a Cell Value
A common request over the years for Microsoft has been to have the ability to link the print header or footer to a cell. As at the time of writing this functionality doesn’t exist, but we can add a one-line macro to fix that.
(more…)Excel VBA – Please Don’t Do This
I recently read an Excel VBA book that recommended using numbers instead of VBA constants for message boxes. Please don’t do that. Here’s why.
(more…)Excel VBA Mod Operator Problem
Excel has a MOD function that returns the remainder after a division. Excel VBA also has the Mod operator. Unfortunately, they return different results when it comes to negatives.
(more…)Excel VBA and the # Symbol
The # symbol is used in Excel sheets for hyperlinks and dynamic arrays. It is also used in VBA for date definitions.
(more…)Colour Constants Updated
Thanks to Rick Rothstein MVP for sharing a LinkedIn article he wrote a while back about colour constants. He gave me permission to share the contents in this post. Making your own colour constants.
(more…)
VBA Color Constants
In Excel colours are colors. If you want to used basic colours you can use the built-in VBA color constants. This simplifies the code and makes it more descriptive.
(more…)More Hiding Sheets Macros
It is easy to hide a sheet, you right click the sheet tab and select Hide. Unfortunately, it is just as easy to unhide that sheet once hidden. If you want to hide sheets and make it harder to unhide them, then these macros may help.
(more…)Outside Borders in Excel
I use the thin all borders format a lot. But there are times when I need to use the thin outline (outside) borders. This border is not as straight forward to apply to a range.
(more…)Macro to Format a Spill Range in Excel
Currently spill ranges do not spill formats. Hopefully Microsoft will add this functionality soon. In the meantime, here is a macro that will copy the format from the top left cell of the spill range to the rest of the spill range.
(more…)Slope Chart Macro
About 10 years ago I did a short blog post on Slope charts. I was reviewing my 10 year old posts and remembered I had written a macro to simplify the process of creating a Slope Chart. So here it is.
(more…)Stop Excel Control being Clicked
In a protected worksheet users can still click on checkbox and option button controls. A warning message will pop up if the control’s linked cells are locked. There is a technique you can use to stop users clicking on these controls. This involves a macro that you can run just before you protect the sheet.
(more…)Hiding Multiple Sheets in Excel Using xlVeryHidden
It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.
(more…)Excel VBA to Sort Sheets by Name
In an earlier post I shared some VBA code to sort sheets by their colour. In this post I have tweaked the code to sort the sheets by their name.
(more…)Quotation Marks in Excel VBA
Sometimes when you are building a text string in VBA you may need to insert quotations marks. That poses a problem because quotation marks are used to surround the text you want to join and using them within the text is problematic. There is a solution.
(more…)Switching Between a Line and a Column Chart
Keeping charts simple is a good rule to live by. Two of the simplest charts are a column chart and a line chart. People have their preferences. Here is a short macro that lets you switch between the two chart types.
(more…)Excel Data Validation Blind Spot
One of the problems with Excel’s Data Validation is that it is possible to have an invalid entry in a data validation cell. This can be caused by Paste Special Values or linked drop downs that don’t update if an earlier drop down is changed. To easily identify invalid cells you can use a macro.
(more…)Excel VBA to Navigate to Sheets
If you right click the arrows on the left of the sheet tabs at the bottom, left of the Excel screen you can see a list of all the sheets in the file. You can use VBA to show this list anywhere.
(more…)Excel Macro to Clean the Data [video]
Yes, I know you should use Power Query to clean data and I demonstrated how to do that in my previous post. Sometimes it is easier to record a macro because a macro can clean the data in place.
(more…)Page Breaks in Excel
When you set your print area or use the Page Break Preview View, Excel will show you the page breaks on the grid. If this annoys or distracts you, here is how to remove them. It’s a one-line macro that turns off the page breaks in the current sheet. Sub TurnOffPageBreaks() ActiveSheet.DisplayPageBreaks = False End Sub If you are new to macros then this blog post can take you through how to use them. The above code can be copied…
Save and Close extra
In my previous post I created a macro from scratch that saved and closed the current file. The macro required that the file had been saved before and wasn’t read only. This post handles those two situations so you can run the macro on any file and it will only work when required.
(more…)Save and Close in Excel
I started using Excel in the late 80’s on a Mac. It had a Save and Close button. When I discovered VBA in Excel on the PC, the very first macro I ever made was save and close.
I thought this would be a good example to take you through creating a macro from scratch and sharing a technique to make it easy to use.
(more…)Extract Email from Copied Outlook Address
I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<jsmith@email.com>. To be used as an email I need to extract from between < and >. To do that in a single cell is tedious, so I wrote a macro to do it for me.
(more…)Increase Font Size in VBA Window
Yes, you can make the text in the VBA window easier to read. It is also great for training. (This tip may not work on 4K monitors.) To open the VBA Window press Alt + F11. Click the Tools menu and then Options - see image below. Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below. Click OK and the font size will now be increased in the…
Free Webinar Recording – Recorded Macros Tips Tricks and Traps
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file. Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names. Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel. This is the first…
First and Last Cell Address in a Range
Following on from my last two VBA posts here is how you can identify the cell addresses of the first and last cells in a range.
(more…)VBA to Find the Row and Column Number Limits of a Range
In my last blog post I found the last used row and column numbers on the active sheet. This post lets us find the row and column extremities of a specific range.
(more…)Excel VBA to Find the Last Used Row or Column
When writing VBA code it is common to need to know the last used row and/or the last used column. These values provide the edges of the sheet contents.
(more…)