Reduce typing with variables in Excel VBA

Use a Constant variable

Sometimes when writing code you use the same text string in multiple places in your code. To make things easier you should capture that text string in a variable.

Variables make writing and maintaining VBA code much easier.

In this case let’s say we need to enter the word None in a cell if something doesn’t have a value.

Assuming r is a row variable and c is a column variable you could write something like this

Cells(r, c).Value = "None"

But if you need to use this word in multiple places in your code then this may be a better solution

Sub test()
Dim r, c
Const sNone As String = "None"
  For r = 2 To 10
    For c = 1 To 10
      If Cells(r, c) = 0 Then
        Cells(r, c).Value = sNone
      ElseIf Cells(r, c) = "" Then
        Cells(r, c).Value = sNone
    Next c
  Next r
End Sub

(I know the If statement could use an Or statement but this is only an example)

The line

Const sNone As String = "None"

Declares sNone as a constant which means it doesn’t/can’t change during the code execution.

If you needed to change the word that went in to the cell you only need to change it in one place

Const sNone As String = "Nothing"

Reduced typing

Also when creating the code you can type sn and then press Ctrl + Space to input sNone. And you avoid using quotations marks.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.