×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Compile Error: Sub or Function not Defined
2

Compile Error: Sub or Function not Defined

Compile Error: Sub or Function not Defined

(OP)
Hi,

I am new to VBA programming and trying to write some code in VB editor of excel to look up table,find value and display in the TextBox on the userform.

I wrote the following code.But when I run it,it send me
Compile Error:
Sub or function not defined with Vlookup highlighted.

Could anyone tell me why?

[code]
Private Sub CommandButton1_Click()
TextBox1 = VLookup(1, "A2:B8", 2)
End Sub

Thanks,

RE: Compile Error: Sub or Function not Defined

VLookup is an Excel Worksheet function, and not a VBA function.  You might try including the various Excel objects into your application by including a reference to the Excel Object Library, and through the objects and methods from those objects may be able to build the vlookup function.

RE: Compile Error: Sub or Function not Defined

Hello,

Try using the MACRO RECORDER, inserting the VLOOKUP in a cell, then edit the code as necessary.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: Compile Error: Sub or Function not Defined

Use Worksheetfunction.vlookup, not just vlookup. After you type period after worksheetfunction the editor will show you the list of all available worksheet funcions you can use in VBA.

RE: Compile Error: Sub or Function not Defined

You'll need to reference the Excel object as well.

cExlObject.WorksheetFunction.VLookup

RE: Compile Error: Sub or Function not Defined

I'm having the exact same problem. I understand everything that has been written so far, but i have one question. What object has to be referenced? Actually i'm not even sure i know what the definition of an object is, because i am very new at this. Is it like the text box i need to refernce. I'm just not sure. If anyone could help that would be great.

Lesli

RE: Compile Error: Sub or Function not Defined

I would suggest that you start your own thread, and describe the situation you're in, and ask a specific question.

RE: Compile Error: Sub or Function not Defined

It's very simple!
All the Excel-functions are in VBA also, all what you have to use is:

e.g.
Application.WorksheetFunction._
VLookup(X, Range("Vlookup_Range"), 2, False)

.:JJ:.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources