VBA Excel "Target" highlighted as "Compile Error: Sub or function not defined"

Question

Maybe I'm dim but I can't see why I'm getting a "Compile Error: Sub or function not defined" error on the following code. The compiler highlights the term "Target" as though I am supposed to define it. What am I missing here? I thought it was a library missing but they seem to be okay. Googling hasn't helped.

Option Explicit
Sub Employee_Entered()
Dim Employee_name As String

Employee_name = Target.Value

Target(0, 2) = Employee_name

End Sub

(Obviously I've cut out the rest of the code. But this still gives me the error described.)

I'm using Excel 2010, Version 14.0.7165.5000.

Thanks Tim

Seems like Target should be a range, then you want to repeat the value in that range into the cell 2 columns to the right? — TheGuyThatDoesn'tKnowMuch
You have Option Explict on so anything not that's not global needs to be defined with a Dim statement or it will throw up a compile error. Maybe you're looking for Selection. That doesn't need to be defined ahead of time. — Sobigen
I think you're getting confused with the Worksheet events such as Private Sub Worksheet_Change(ByVal Target As Range) which does contain a Target variable. In any other code you'll have to define Target yourself, or use Selection as Sobigen commented. — Darren Bartrup-Cook

Gary's Student Gary's Student · Accepted Answer · 2016-03-07T13:29:28

Modify the Set to suit your needs:

Option Explicit

Sub Employee_Entered()
    Dim Employee_name As String
    Dim Target As Range

    Set Target = Range("A1")

    Employee_name = Target.Value
    Target.Offset(0, 2) = Employee_name
End Sub

VBA Excel "Target" highlighted as "Compile Error: Sub or function not defined"

1 Answers