CODEWITHSUNDEEP
excelvba
Isra Shaikh
Isra Shaikh

Reputation: 151

VBA Dot notation

Hi so im new to programming specifically with VBA. Worked basics with Matlab previously so understand the for and if loops etc. However I do not understand how the dot notation works. Meaning ActiveCell.Range etc

Where do we use it and in what context.

Thanks

Upvotes: 2

Views: 3323

Answers (1)

Cyril
Cyril

Reputation: 6829

If I'm understanding this correctly, you're curious about how VBA defines what is happening in a line of code.

The VBA notation for working will be dependent on what you're doing, meaning how deep you will need to go.

ActiveWorkbook.Sheets("Sheet1").Range("A1").Formula=""

This would be a pretty common display for a single line of code if you wanted a formula hard coded into A1. You specify the workbook, the sheet, the cell/range to be worked in, then what you want to happen.

You can shorten some of this if you want to work do multiple things in the, arbitrarily, sheet (sheet1). You can use a With statement which will have the following nomenclature:

With ActiveWorkbook.Sheets("Sheet1")
     .Range("A1").Formula=""
     .Range("B1").Formula=""
End With

The use of the "." is what ties you do what is being operated on. Think of it as part of a name. Your name is Isra Shaikh (based on username). If we want to punch you, and not your brother named Herb, we could want to specify:

SurName("Shaikh").PreName("Herb").Punch

If we wanted to punch both of you:

SurName("Shaikh").PreName("Isra").Punch
SurName("Shaikh").PreName("Herb").Punch

or more combined:

With SurName("Shaikh")
    .PreName("Isra").Punch
    .PreName("Herb").Punch
End With

Notice how the With statement doesn't end with a ".", but what happens inside the With is preceded with the "."; this is how VBA knows what all is being worked on. Also note that you start from the widest, to the narrowest, component (workbook -> cell).

Hoping that is enough of a description!

Upvotes: 4

Related Questions