CODEWITHSUNDEEP
vbaexcel
eeeccc17
eeeccc17

Reputation: 55

defining code on vba excel to simplify code writing process

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.

Upvotes: 1

Views: 75

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

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

Upvotes: 0

Samuel Hulla
Samuel Hulla

Reputation: 7089

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

Upvotes: 1

Related Questions