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
– TheGuyThatDoesn'tKnowMuch, Commented Mar 7, 2016 at 13:20
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
– Sobigen, Commented Mar 7, 2016 at 13:27
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
– Darren Bartrup-Cook, Commented Mar 7, 2016 at 14:20

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

0

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

answered Mar 7, 2016 at 13:29

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related