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.
Category Archives: Macros
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.
May the Macros Be With You
May is Macros month.
You can learn how to save time and effort by replacing repetitive or time consuming tasks with a macro. Macros make delegation easier.
Four live Excel webinars to get you started with macros. The first one is free. Over 5 hours of training.
Buy the Essential Macros Bundle for AU$60 and be registered for all the live May sessions (see below for dates) plus get access to the online courses including bonus macro content.
Essential Macros Bundle 2024Excel 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.
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.
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.
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.
VBA Color Constants
Simplify using basic colours
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 Hiding Sheets Macros
These ones work with the selected sheets
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.
Outside Borders in Excel
How to apply to a range
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.
Macro to Format a Spill Range in Excel
VBA and spill ranges can work together
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.
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.
Stop Excel Control being Clicked
Textbox hack
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.
Hiding Multiple Sheets in Excel Using xlVeryHidden
Flexible solution
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.
Excel VBA and the TRUNC Function
Excel has a TRUNC function that truncates numbers. For example =TRUNC(15.75) returns 15. It doesn’t round it just removes the fraction leaving the whole number. Excel VBA does not have the TRUNC function. It does have the Fix function that works the same.
Excel VBA to Get to the Top Left of the Screen
Sometimes when you are using Excel VBA you need the screen to always display at the top, left of the sheet. Here is how you do it.
VBA Window Split
I just found out you can split the VBA code window. See images below.
Use the small icon above the right side scroll bar.
This can be useful if you have a long block of code and need to look at separate parts together.
Thanks to Bob Umlas the Excel MVP for sharing.
You can double click the split bar to remove it.
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.
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.
Excel VBA Sort Sheet Tabs by Colour
Get your sheet tabs organised
Using colours on your sheets can help you navigate and organise your file. If you want to sort your sheets by colour you can use a macro to speed up the process.
Excel File is Open VBA Function
Useful function
When working with files in VBA code, you may need to know if a file is already open. A function can return TRUE if a file is currently open and FALSE if it isn’t.
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.
Sheet Exists Function for Excel VBA
A function macro to help another macro
Often when you are working with Excel VBA you need to confirm if a sheet exists based on the tab name. This Function can be used to do that. It works with the active file (workbook).
Outlook macro to stop sending an email without an attachment [VIDEO]
Automatic attachment check via VBA
I don’t do many Outlook macros, but this one is really useful and I have used it for a long time. It looks at your outgoing email and checks to see if you have the word attach in it and then checks to see if you have an attachment. It warns you if you don’t.
Excel Data Validation Blind Spot
Macro to fix it
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.
Excel VBA to Navigate to Sheets
A shortcut menu makes it easy
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.
Excel Macro to Clean the Data
Before Power Query, this is how we cleaned data
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.
Old Macros XL4 macros
If you use the old Excel macro language – known as XL4 macros – you may need to update a setting to keep using them.
This is the macro language before VBA was introduced in the Excel 5 back in the 90’s.
Not many people use these macros any more but there a couple of techniques that they are used for.
Microsoft will soon disable them automatically and you will need to turn them back on if you want to use them.
The setting to update is in the Trust Center Macro settings – see image below.
Related Posts
Keyboard Shortcut to Record a Macro
You need the Developer ribbon tab visible to record a macro, or do you?
This old-fashioned keyboard shortcut will open the record macro dialog. Pressed in sequence, not held down.
Alt T M R
You can also use the little icon next to the Ready at the bottom left corner of the screen.
Once you start recording the small square icon to stop recording appears in the same spot.
Clearing Unlocked Cells in Excel
A macro makes it easy
People always ask, how to do you lock Excel? In fact you have to unlock Excel and then protect the sheet. The default setting for all cells is locked, so you need to unlock input cells.