defining code on vba excel to simplify code writing process

Question

I am attempting to reduce the amount of clutter on my code by creating "shortcuts" if you will

For instance, I always have to type

ThisWorkBook.ActiveSheet.Range

Is there a way for me to define the above to create a less wordy macro? I have tried convert to range and string and the former returns an error (but I could still get intellisense recognize and attempt to autofill) while the string version doesnt work.

'Wordy' code equals faster execution time and fewer errors. You just have to write good code. — , Jun 13 '18 at 08:40

score 1 · Answer 1 · answered Jun 13 '18 at 08:16

Just like in any programming language, you can use variables to store data For example:

Dim myrange As Range: Set myrange = Sheets("Sheet1").Range("B5")

Alternatively, if you will be working with the same object multiple times, you can use the With keyword For example. instead of writing you want to work with table every time on every new line you can do

With Sheets("Sheet1").ListObjects("Table1")
   .ListRows.Add
   .ListColumns(2).Range(3) = "Hello World!"
   ' ... and so on
End With

Also, please on a sidenote: Avoid using Select/ActiveSheet/ActiveWorkbook and so on!
More info on how to here

score 0 · Accepted Answer · answered Jun 13 '18 at 08:31

You can create functions or customized properties, which are always evaluated when called

Property Get pARng As Range
    Set pARng = ThisWorkBook.ActiveSheet.Range
End Property

Function fARng As Range
    Set fARng = ThisWorkBook.ActiveSheet.Range
End Function

'Usage
Sub xxx

    '...

    pARng.Rows(1).Activate
    'Same as ThisWorkBook.ActiveSheet.Range.Rows(1).Activate

    fARng.Rows(1).Activate
    'using function instead achieves same result
End Sub

defining code on vba excel to simplify code writing process

2 Answers2