Different Ways To Reference Ranges

  • Thread starter Thread starter Ryan H
  • Start date Start date
R

Ryan H

I need a very short way to refer to single cell ranges in a worksheet. I
have a calculator that I use in VBA and it references cells in many
worksheets. So I want to keep the code as clean as possible. For example,

'this is what I currently do
Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value

Is there a shorter way to write this? I think I have seen people use [B4] *
[C6] or something like that, but how can I include the worksheet?

Thanks in Advance!
 
Try this
Dim ws As Worksheet
Set ws = Worksheets("Labor")

With ws
.Range("C4").Value = _
.Range("B4").Value * .Range("C6").Value
End With
 
You could use:

Worksheets("Sheet1").[a1]
or even
['Sheet 99'!x88]
or if you know the code name for that sheet
Sheet1.[a1]

But it turns out, that the [] notation is slower than the range(...).value
syntax.

Personally, I find the range(...).value easier to read/debug/write--even though
there are more characters that I have to type.

Ryan said:
I need a very short way to refer to single cell ranges in a worksheet. I
have a calculator that I use in VBA and it references cells in many
worksheets. So I want to keep the code as clean as possible. For example,

'this is what I currently do
Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value

Is there a shorter way to write this? I think I have seen people use [B4] *
[C6] or something like that, but how can I include the worksheet?

Thanks in Advance!
Click to expand...
 
Thanks for the replay Dave. Just curious, how do you know it is slower with
the brackets?
--
Cheers,
Ryan


Dave Peterson said:
You could use:

Worksheets("Sheet1").[a1]
or even
['Sheet 99'!x88]
or if you know the code name for that sheet
Sheet1.[a1]

But it turns out, that the [] notation is slower than the range(...).value
syntax.

Personally, I find the range(...).value easier to read/debug/write--even though
there are more characters that I have to type.

Ryan said:
I need a very short way to refer to single cell ranges in a worksheet. I
have a calculator that I use in VBA and it references cells in many
worksheets. So I want to keep the code as clean as possible. For example,

'this is what I currently do
Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value

Is there a shorter way to write this? I think I have seen people use [B4] *
[C6] or something like that, but how can I include the worksheet?

Thanks in Advance!
Click to expand...
Click to expand...
 
This is not the first discussion for this topic. A few people who tested it,
reported their results.

(Even if it were faster, I wouldn't use it. I find the other syntax easier to
read.)


Ryan said:
Thanks for the replay Dave. Just curious, how do you know it is slower with
the brackets?
--
Cheers,
Ryan

Dave Peterson said:
You could use:

Worksheets("Sheet1").[a1]
or even
['Sheet 99'!x88]
or if you know the code name for that sheet
Sheet1.[a1]

But it turns out, that the [] notation is slower than the range(...).value
syntax.

Personally, I find the range(...).value easier to read/debug/write--even though
there are more characters that I have to type.

Ryan said:
I need a very short way to refer to single cell ranges in a worksheet. I
have a calculator that I use in VBA and it references cells in many
worksheets. So I want to keep the code as clean as possible. For example,

'this is what I currently do
Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value

Is there a shorter way to write this? I think I have seen people use [B4] *
[C6] or something like that, but how can I include the worksheet?

Thanks in Advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top