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!
--
Cheers,
Ryan

Try this
Dim ws As Worksheet
Set ws = Worksheets("Labor")

With ws
.Range("C4").Value = _
.Range("B4").Value * .Range("C6").Value
End With

"Ryan H" wrote:

> 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!
> --
> Cheers,
> Ryan

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 H wrote:
>
> 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!
> --
> Cheers,
> Ryan

--

Dave Peterson

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


"Dave Peterson" wrote:

> 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 H wrote:
> >
> > 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!
> > --
> > Cheers,
> > Ryan
>
> --
>
> Dave Peterson
> .
>

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 H wrote:
>
> Thanks for the replay Dave. Just curious, how do you know it is slower with
> the brackets?
> --
> Cheers,
> Ryan
>
> "Dave Peterson" wrote:
>
> > 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 H wrote:
> > >
> > > 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!
> > > --
> > > Cheers,
> > > Ryan
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson