Object variables are the variable types that have their names written in black (right hand side) when you define them – see example image below for Range, Worksheet and Workbook. These are the most common objects used. There are two important things to know about using object variables.
Tag Archives: vba
Useful Excel VBA Range Technique
Titles made simple in VBA
It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process.
Excel VBA BeforePrint Issue
Not all Print Previews are Equal
Excel has a BeforePrint event which enables you run VBA code before a document is printed. This event can also be triggered by Print Preview. But not all Print Previews are the same.
Excel VBA and Variables
Using Option Explicit
Variables can speed up your code and make maintenance a lot easier. You should always declare or Dim (technical term) your variables, here’s why.
VBA – Easy Way to Handle TRUE/FALSE Tests
Using a boolean variable
Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.
VBA to Open CSV and Avoid Date Error
You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
Getting Excel to Recognise an Email Address
Converting it into a hyperlink
Sometimes when Excel imports email addresses they are not recognised as emails and are not hyperlinks. They are two ways to fix this.
Running an Excel Macro from a Graphic
Its easy to set up
These days running a macro off a control button seems to be old school and many people have started running macros off graphics.
Excel Hiding and Unhiding Error Fix
Sometimes when you hide or unhide, rows or columns, you can get an error message saying that Excel can’t move objects off the sheet. The solution is in the macro below.
VBA macros that run other macros
When you become more advanced with macros and VBA programming (Visual Basic for Applications), you realise that you can create re-useable macros.
Unhide all sheets in Excel
In Excel if you need to hide data or workings sheets you can hide multiple sheets in one action. Unfortunately Excel won’t let you unhide multiple sheets in one step (even in the latest version). To get around this limitation you can use a macro that unhides all the sheets.
Restricting the Scroll Area in Excel
Non-VBA and VBA solution
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
Excel and the VBA Editor
Make the VBA editor screen your friend
When you first start out using VBA and writing VBA (macro) code it is useful that the VBA Editor helps you quickly identify when you have made a mistake. If a line of code has an error it will display a dialog box that explains the error and turns the line of code red.
Excel and Hyperlinks
Sometimes you want them, sometimes you don't
Hyperlinks are a great tool as they allow you to speed up and simplify navigation within a file. Sometimes hyperlinks can be frustrating. See how to remove some of those frustrations below.
Speeding Up Excel Macros
Tips and tricks for faster macros
There are two commands you can insert at the top of your code to speed up your macros.
Handling sheet passwords with Excel VBA
Protect and Unprotect quickly
When you are using sheet passwords in Excel it can be handy to have a macro to unprotect all the sheets in one step. This can make maintenance easier.
Protecting Your Excel VBA Macro Code
Applying a password to VBA
The more you use macros the more important they become and the more you want to make sure the VBA* code doesn’t get changed by someone who shouldn’t change it. You may also want to stop people viewing your code.
Fixing Excel’s Pivot Table headings
Say goodbye to Sum of
Pivot Tables are incredibly powerful and easy to use. Unfortunately their headings can include the terms “Sum of” or “Count of”. This is not always what you’d like to present to users. A macro to the rescue.
Unhide all sheets in a file macro [VIDEO]
Adding functionality with a macro
Excel allows you to easily hide a group of sheets BUT frustratingly, it won’t let you unhide a group of sheets. You have to unhide them one sheet at a time.