Using Built in Functions in VB Code

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
jimconrady@hotmail.com

Hi Jim

Use it like this

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jim Conrady" <JimConrady@discussions.microsoft.com> wrote in message news:193F23BC-86D0-4208-80B4-855F210D9AF9@microsoft.com...
>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
> jimconrady@hotmail.com

Hi Jim, Ron,

A dot after WorksheetFunction

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:eZ9Joz5wGHA.4476@TK2MSFTNGP02.phx.gbl...
| Hi Jim
|
| Use it like this
|
| Application.WorksheetFunctionVLookup(.............................)
|
|
| --
| Regards Ron de Bruin
| http://www.rondebruin.nl
|
|
|
| "Jim Conrady" <JimConrady@discussions.microsoft.com> wrote in message news:193F23BC-86D0-4208-80B4-855F210D9AF9@microsoft.com...
| >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
| > jimconrady@hotmail.com
|
|

Oops

Thanks for the correction Niek



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Niek Otten" <nicolaus@xs4all.nl> wrote in message news:uxfzjC6wGHA.4280@TK2MSFTNGP04.phx.gbl...
> Hi Jim, Ron,
>
> A dot after WorksheetFunction
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:eZ9Joz5wGHA.4476@TK2MSFTNGP02.phx.gbl...
> | Hi Jim
> |
> | Use it like this
> |
> | Application.WorksheetFunctionVLookup(.............................)
> |
> |
> | --
> | Regards Ron de Bruin
> | http://www.rondebruin.nl
> |
> |
> |
> | "Jim Conrady" <JimConrady@discussions.microsoft.com> wrote in message news:193F23BC-86D0-4208-80B4-855F210D9AF9@microsoft.com...
> | >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
> | > jimconrady@hotmail.com
> |
> |
>
>

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.



Jim Conrady wrote:
>
> 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
> jimconrady@hotmail.com

--

Dave Peterson