Bottom line: Learn two keyboard shortcuts for displaying the Intellisense menu, so you can autocomplete words in the VB editor.
Skill level: Intermediate
Accessing the Intellisense Menu
I'd like to share two of my favorite keyboard shortcuts with you that are really helpful when working with VBA and the VB editor. Both of them bring up the Intellisense menu, but they are used at different times.
The first keyboard shortcut is Ctrl+Space. It shows the Intellisense menu so that you can autocomplete words that you've already started to type.
The second shortcut is Ctrl+J. It brings up the menu when it has disappeared and you want to view it again.
What Is the Intellisense Menu?
The Intellisense menu is the drop-down menu that appears after we type a period “.” in the VB Editor. The drop-down contains a list of all the members of the active references/libraries (Object Model). This includes objects, properties, methods, constants, variables, etc.
If you're familiar with the autocorrect feature on a smartphone, you have a sense of how this helpful tool works. Intellisense anticipates potential words that you might want, based on what you have already typed. When you select the option that you intend, it automatically completes the word for you.
This feature is great, not only because it can save you a bit of time typing, but because it ensures you don't misspell words and cause problems in your code from typos. Once you start taking advantage of this autocomplete feature, you'll find yourself using it all the time.
The Ctrl+Space Keyboard Shortcut
In the VB Editor, you can begin to type any word, reference, or variable and then press Ctrl+Space to bring up the Intellisense menu.
You'll see all the options that begin with the letters you've already typed. To select the option you want, you can either
- Click on it with your mouse, or
- Use the Down Arrow key ? until your selection is highlighted, and then press the Tab key to select it.
As you type more and more of the word, the menu is filtered down to fewer and fewer options. If you've typed so much of the word that there is only one option left to complete the word, then when you use the Ctrl+ Space keyboard shortcut, the word will automatically complete and the menu will NOT appear.
The Ctrl+J Keyboard Shortcut
Now, let's say we accidentally selected the wrong option and we want to bring back the Intellisense menu. We don't have to backspace through half the word and start over. We can simply click anywhere on the word we want to change and choose Ctrl + J to call back the Intellisense menu.
You can then make a selection from the list and press Tab. The existing reference will be replaced with the one you selected.
Alternatives to the Keyboard Shortcuts
You can select also select these options from the Edit menu in the VB Editor.
Those same options are available if you right-click on a word anywhere in the VB Editor.
Intellisense for Variable Names
I like to prefix my variable names based on data type. This is typically referred to as Hungarian notation. Some programmers don't like this, but I find it much easier to reference variables in my code using Intellisense.
If the variables are prefixed with something like “rng”, “i”, “str”, or “var”, then you can start typing those letters and hit Ctrl+Space to see all the variables that start with the prefix in the Intellisense menu. Select the one you want and hit Tab to fill it.
This has two advantages:
- It prevents errors and typos in our code.
- It allows us to use longer variable names that are more descriptive, without having to type out the entire name. This saves time and makes the code easier to read.
Some Related Topics
If these keyboard shortcuts are right up your alley, I suggest you check out my post 18 Excel VBA Macro Shortcuts for 2018 to add some more to your toolbelt.
And if you haven't yet made your own Personal Macro Workbook to store all of the macros you create, I've got a four-part video series that explains the process, step by step. You can access that post here: How to Create a Personal Macro Workbook.
The macro used in this post is from my post & video on 3 Ways to Delete Entire Blank Rows.
Leave a note in the comments if this post is helpful, and as always, feel free to ask questions there as well! Thanks! 🙂
Free Webinar on Macros & VBA
If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”. It's running all next week, and it's absolutely free to register.
During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started. I jump into Excel and the VB Editor and walk through how to write and run our first macro. Even if you have been using VBA for awhile, I'm sure you will learn some new tips.