Hard to Find Excel Tidbit

A short post this week. I recalled that there was a syntax for Excel ranges in VBA. I searched online, but couldn't find much, so here is the tidbit: Ranges can be specified using square brackets. If you used named ranges a lot, this turn out quite nice. So if you have a named range called ImportantInfo, you can refer to it as ... [ImportantInfo]! And it may not provide the method list when you type a full stop after it, but it IS a range object, so you can use it like this:

    Answer = [ImportantInfo].Offset(RowCount, ColumnCount).Text
    MsgBox "The cell Sheet2!E3 contains """ & [Sheet2!E3] & """."
    Dim Cell As Range
    For Each Cell In [B1:B3]
        MsgBox Cell.Text
    Next Cell

One of the reasons I think it's neat when used for named ranges is that with most named ranges in square brackets, you don't have to specify the sheet to disambiguate it. That makes for shorter, and thus easier to read code.

Enjoy!