How to use the Target parameter in a standard module?

Question

I am trying to figure something out. I really don’t have any code so far that is of any use to look at, other than I am playing with this one line. Why can’t I use “Target” under ThisWorkbook or a module for VB Script in Excel?

Here is the line of code that I am trying to use:

ActiveWorkbook.ActiveSheet.Range("A1").Value2 = ActiveWorkbook.ActiveSheet.Cells(Target.Row, Target.Column)

If I was to put this exact line in a sheet it works without issues. I tried many variations of the use of “Target” and if it is in a module or under ThisWorkbook, it will not recognize it and give me an error. Now if I were to use a specific location instead of target, such as what I have below, it will work without issues.

ActiveWorkbook.ActiveSheet.Range("A1").Value2 = ActiveWorkbook.ActiveSheet.Cells(10, 1)

Is there something that I can do similar to the use of "Target" that will recognize the text of the current cell I am on, regardless of the sheet or which cell is active. In other words, so I don't have to specify a specific location? I would need this to work in ThisWorkbook.

Answer 1

Target is a parameter name when using Events.

Ex.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)

There is no reference to the Target unless you declare it somewhere in your code (note: regular code module)

Sub Main()

    Dim Target As Range
    Set Target = ActiveCell

    MsgBox Target.Address & vbNewLine & Target.Row & vbNewLine & Target.Column

End Sub

Instead of declaring the Target as a Variable, you can also use the ActiveCell object, which acts the same way as the Target as they both are Range objects.