Set Target.Offset as a function/constant

[Fredrik1987]

Hi!

I'm using the function target.offset(, 1).value quite often in a worksheet script, so I was thinking that it would been alot easier if it was possible to asign this function to a specific letter, e.g.:

set a1 = target.offset(, 1).value

so instead of writing "target.offset()... = someValue" I could just write "a1 = someValue".
I'm not sure how to assign this property to a function, do I have to create a module or something?
I would much prefer if it was possible to implement this directly in the sub.

Basically, what I want to do is:

Sub Worksheet_Change(ByVal Target As Range)

set a1 = target.offset(, 1).value
set a2 = target.offset(, 2).value
'etc etc

end sub

 

[Norie]

How/where exactly are you using Target.Offset(,1).Value etc?

 

[Fishboy]

Hi!

I'm using the function target.offset(, 1).value quite often in a worksheet script, so I was thinking that it would been alot easier if it was possible to asign this function to a specific letter, e.g.:

set a1 = target.offset(, 1).value

so instead of writing "target.offset()... = someValue" I could just write "a1 = someValue".
I'm not sure how to assign this property to a function, do I have to create a module or something?
I would much prefer if it was possible to implement this directly in the sub.

Basically, what I want to do is:

Sub Worksheet_Change(ByVal Target As Range)

set a1 = target.offset(, 1).value
set a2 = target.offset(, 2).value
'etc etc

end sub

Hi Fredrik1987, try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a1 As Range, a2 As Range
Set a1 = Target.Offset(0,1)
Set a2 = Target.Offset(0,2)

If Target.etc etc etc Then
[COLOR=#ff0000][B]Application.EnableEvents = False[/B][/COLOR]
a1.Value = Whatever
a2.Value = Whatever
[COLOR=#ff0000][B]Application.EnableEvents = True[/B][/COLOR]
End If
End Sub

It is VERY important to remember to disable and re-enable events at the start of / end of making your changes or you risk causing an infinite loop and filling the rest of the row with your offset values.

 

[Fredrik1987]

Thank you for your input! I think my question was unclear;
The script is quite long and refers to several target ranges (Application events, as Fishboy shows above), the script works fine, but it's cumbersome to write a line target.offset(, 1).value = 1 again and again.

To save time I want to assign "a1" as target.offset(, 1).value.
So my question is: Is it possible to assign this property at the top of the script, or do I have to write it as you suggested Fishboy?

 

[RoryA]

You can declare a range object variable and use that:

Dim a1 as Range
set a1 = target.offset(, 1)

and then just use a1.Value instead of target.offset(, 1).value

 

[mikerickson]

"A1" is a bad name for a variable, it is too easily confused with a cell address.

Having said that, assigning a range variable is a good idea if that particular range is going to be used repeated in the same procedure.

Dim OneCellToTheRight as Range

Set OneCellToTheRight = Target.Offset(,1)

If you are looking to write a function or property or something to act as a shorthand for .Offset(,1), the machinery that requires won't save you keystrokes.

You could use a With construction to save on typing.

Instead of

Target.Offset(,1).Value = "x"
Target.Offset(,2).Value = "y"
Target.Offset(,.3).Value = "z"
' ...

use

With Target
    .Offset(,1).Value = "x"
    .Offset(,2).Value = "y"
    .Offset(,3).Value = "z"
    ' ...
End With

Plus the fact that when you come back to this routine, for updating and improving, 6 months from now. All that tiresome typing now will help you remember what the routine is doing.

 

[Fishboy]

Thank you for your input! I think my question was unclear;
The script is quite long and refers to several target ranges (Application events, as Fishboy shows above), the script works fine, but it's cumbersome to write a line target.offset(, 1).value = 1 again and again.

To save time I want to assign "a1" as target.offset(, 1).value.
So my question is: Is it possible to assign this property at the top of the script, or do I have to write it as you suggested Fishboy?

I am a little confused. Does my suggestion do what you are asking here..?

so instead of writing "target.offset()... = someValue" I could just write "a1 = someValue".

Unfortunately time saving features only start saving you time once you have put some initial work in. To prevent the need to keep typing target.offset() by using shortcuts instead you will have needed defined what your shortcuts are. Firstly by Dim a1 As Range and so on so Excel / VBA understands that your shortcut a1 refers to a cell. Secondly you need to have Set a1 = Target.Offset(,1) etc so Excel knows which cells are being referred to. Excel is many things, but psychic is unfortunately not one of them

 

[Fishboy]

"A1" is a bad name for a variable, it is too easily confused with a cell address.

To be honest this was my first thought when I started testing, but was pleasantly surprised that it worked anyway.

Still I suspect it is best practice not to have variables confusingly similar to cell references regardless...

 

[Fredrik1987]

Thanks for the help!

English is not my native language so I think I'm having some difficulty making myself understood
I've use Matlab previously, so I'm just trying to understand how VBA works.
But the solution presented by ericson was the one I was looking for

Perhaps a bit off topic, a1 was just a variable name I chose from the top of my head. But I'm wondering what you mean by:

A1" is a bad name for a variable, it is too easily confused with a cell address.

?

Isn't that what the range() property is for? for example Range("A1") = A1 shouldn't confuse Excel if i have, say set A1 = 3 earlier.
This is jsut something I'm curious about, so I don't expect an answer if you don't have the time

From a more practical view, I completely agree that it's easy to confuse the variable a1 with the cell reference A1, but so would the variable A_1 i suppose.
http://www.mrexcel.com/forum/members/mikerickson.html

 

[Fredrik1987]

Nevermind, I misread

Thank you so much for your help!