Micro-Efficiency: Duplicating Line(s) of Code in VBA

I obsess over tiny details of efficiency way more than any healthy person should.

Keep that in mind as you read today's tip. If you walk away at the end puzzled, thinking that you must be missing something, I assure you that you are not. If you adopt today's tip into your workflow as a full-time VBA developer, you could save seconds–dare I say, dozens of seconds–every single month that you write code.

But I obsess over the little things. So here goes. (Don't say I didn't warn you.)

The Problem

Making an exact copy of a line of code in VBA is annoying.

It's not hard. And it doesn't even take particularly long. But it's annoying.

It's annoying because many modern development environments have shortcuts to duplicate a line of code:

VS Code: [Shift] + [Alt] + [↓] (down arrow)
Notepad++: [Ctrl] + [D]
SSMS: [Ctrl] + [D] (requires configuration)
SSMS pre-18.0: [Ctrl] + [C], [Ctrl] + [V] (if no text is selected, [Ctrl] + [C] in SSMS copies the entire line to the clipboard)

The nice part about the first three solutions is that they don't affect the existing clipboard. The SSMS alternative replaces your clipboard contents, but it's still quite fast to use.

The Solution Workaround

There is no "duplicate line" shortcut in VBA.

For a long time, I did this instead:

[Shift]+[End] to select the line of text I wanted to copy
[Ctrl]+[C] to copy it to the clipboard
[Down] arrow key to move to the next line
[Ctrl]+[V] to paste the clipboard contents

Use [Shift] + [End] to select the line of code, then press [Ctrl] + [C] to save it to the clipboard.

Press the down arrow to move to the next line.

Press [Ctrl] + [V] to paste the line of code.

It's that third step that's the killer. Moving my hand off the main keyboard. Desperately groping for the down arrow key. Occasionally having to look at the keyboard to find it. (OK, I never have to look. Now I'm just being dramatic.)

Here's my point. In step 2, I'm holding down the [Ctrl] key with one finger while another finger is on the [C] key. You know what's right next to the [C] key? The [V] key. You know what's not right next to the [C] key? The down arrow key. I mean, the whole reason [V] is used as the default shortcut for Paste and not [P] is because they are next to each other on the keyboard. It was done for efficiency!

And here I am. With one finger on the [Ctrl] key, another finger coming off the [C] key, and the [V] key just sitting there. Tantalizingly close to the [C] key. But I can't touch it yet. Nope. I have to interrupt this potential efficiency nirvana to go searching for a different key.

And that's not even the worst part!

I also have to release the [Ctrl] key. And what if I don't? What if I'm so fast that I press the [Down] arrow key before I release the [Ctrl] key? What then? VBA takes me to the next procedure down in the code window! It could be a few lines. It could be a few screen lengths! [Editor's note: It better not be a few screen lengths.]

So I release the [Ctrl] key. I press the [Down] arrow. Everything is going according to plan. But you know what I have to do now? Press the $#!@#% [Ctrl] key again! Wasn't I just doing that?!?! Fine. Whatever. I press the [Ctrl] key. I hold it down while I press the [V] key. My line of code has been duplicated.

I mean, it is faster than using the mouse. So I guess there's that.

A Very Slightly Faster Way

I've since optimized the above process, as pressing the down arrow on the keyboard after copying the text was costing me precious deciseconds every time I did it.

Now I do this instead:

[Shift]+[Down] to make my initial selection
[Ctrl]+[C] to copy the selection
[Ctrl]+[V] to move the cursor to the end of the selection
[Ctrl]+[V] to paste the selected text

Let me explain.

I use [Shift]+[Down] to make my initial selection, so that it includes the line break character. [Editor's note: Didn't you just spend three paragraphs lamenting the arduous journey to locate the [Down] arrow key?] Then, I press [Ctrl]+[C] to copy the selection. Now here's the exciting part... I press [Ctrl]+[V] while the text is still selected. This replaces the selected text with itself. It also moves the cursor to the end of the selected block. I press [Ctrl]+[V] a second time, et voilà!, I have duplicated my selected text.

Use [Shift]+[Down] to select the line of code and line break, then press [Ctrl]+[C] to save it to the clipboard.

Pressing [Ctrl]+[V] pastes the just-selected text over top of itself. More importantly, though, it moves the cursor to the end of the text.

Pressing [Ctrl]+[V] a second time duplicates the previously selected text.

Note that this also puts the cursor in the next line down, so it makes it very fast to copy the same line multiple times.

It's easy to quickly create multiple copies of the same line.

Trust me, I did that fast. 👆

Duplicating Multiple Lines at Once

Another advantage to this technique is that you can use it to duplicate multiple lines at a time:

Hold down [Shift], then press [Down] four times to select all four lines. Press [Ctrl]+[C] to copy the text to the clipboard.

Press [Ctrl]+[V] to paste the text over top of itself and move the cursor to the next line.

Press [Ctrl]+[V] to paste the copied text a second time. And perhaps a third time, a fourth time, ...

Try doing that with VS Code or Notepad++ or SSMS!

No, really, it's just simple copy and paste, so the technique works on pretty much any text editor anywhere. Even on plain old Notepad, which may be the only development environment with fewer features than VBA.

Final Thoughts

Now that you've read the whole article, I've got good news and bad news for you.

The good news is that you (may) have a new technique to improve your efficiency while writing code. A technique that could save you valuable seconds every week.

The bad news is that it will take you years of using that technique to recoup the investment you just made reading this article.

But hey, I tried to warn you!