Using Built in Functions in VB Code

[Guest]

I apologize if this is some trivial question that has an easy answer, but I
have been through help and 2 different Excel VB books and cannot get this to
work.

Problem: In a VB Function, I would like to do the following:

Pos = VLookup(InValue, NamedTable, 2, FALSE)

The debugger won't let me use VLookup. I also tried Average, etc., and the
debugger pops on whatever built-in function I use.

Is there a flag I can set, or a technique I need to use to make this work?

I really know excel, but am a novice at VB. I can get around it nesting
vlookups in the spreadsheet, but it is really ugly. Being able to do this
would enable me to clean up my code a bunch. I would welcome feedback.

Regards,

Jim

 

[Ron de Bruin]

Hi Jim

Use it like this

Application.WorksheetFunctionVLookup(.............................)

 

[Niek Otten]

Hi Jim, Ron,

A dot after WorksheetFunction

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Jim
|
| Use it like this
|
| Application.WorksheetFunctionVLookup(.............................)
|
|
| --
| Regards Ron de Bruin
| http://www.rondebruin.nl
|
|
|
| >I apologize if this is some trivial question that has an easy answer, but I
| > have been through help and 2 different Excel VB books and cannot get this to
| > work.
| >
| > Problem: In a VB Function, I would like to do the following:
| >
| > Pos = VLookup(InValue, NamedTable, 2, FALSE)
| >
| > The debugger won't let me use VLookup. I also tried Average, etc., and the
| > debugger pops on whatever built-in function I use.
| >
| > Is there a flag I can set, or a technique I need to use to make this work?
| >
| > I really know excel, but am a novice at VB. I can get around it nesting
| > vlookups in the spreadsheet, but it is really ugly. Being able to do this
| > would enable me to clean up my code a bunch. I would welcome feedback.
| >
| > Regards,
| >
| > Jim
| >
| > --------------
| > Jim Conrady
| > (e-mail address removed)
|
|

 

[Ron de Bruin]

Oops

Thanks for the correction Niek

 

[Dave Peterson]

I like to do it this way:

Dim Pos as Variant 'could return an error
dim inValue as Variant 'string, number, what???
dim namedtable as Range

with worksheets("somesheet")
set namedtable = .range("sometablerangehere")
end with

pos = application.vlookup(invalue, namedtable, 2, false)

if iserror(pos) then
'not found
else
'found, rest of code goes here
end if

This assumes that invalue and namedtable are variables in your code.