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.

Related Posts

Page Breaks in Excel

Turn them off

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 and pasted into the code window.

If you want to turn page breaks off for all the sheets in the file use the code below.

Sub TurnOffAllPageBreaks()
 
Dim ws
 
For Each ws In Worksheets
 
  ws.DisplayPageBreaks = False
 
Next ws
 
End Sub

 

I hope these code snippets are useful.

Speed up a macro

Just saw an Excel newsletter post from Kevin Jones from https://www.dataautopros.com/about-us/

He found that turning off the VBA interactive setting can speed up some macros. I tested it on a one minute macro and it cut it down to 40 seconds.

Worth a try if you have a longer running macro. You can add it to your opening and closing routines.

Code to turn it off

Application.Interactive = False

And then turn it back on at the end of your code.

Application.Interactive = True

Thanks Kevin for sharing.

Related Posts

Missing Chart Data in Excel

Another macro to the rescue

The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.

Save and Close in Excel

Let's create a macro

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.

Increase Font Size in VBA Window

Easy change

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

See below for a comparison between 10 and 14 point.

Much easier to read!

Free Webinar Recording – Recorded Macros Tips Tricks and Traps

Getting started with recorded macros

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 in a series of webinars dedicated to macros. Future paid sessions this month (August 2019) will expand on the techniques taught in this session. Click here to register.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Download Materials