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

Using Built in Functions in VB Code
 

Hi Jim

Use it like this

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


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



"Jim Conrady" wrote in message ...
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

Using Built in Functions in VB Code
 

Hi Jim, Ron,

A dot after WorksheetFunction

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ron de Bruin" wrote in message ...
| Hi Jim
|
| Use it like this
|
| Application.WorksheetFunctionVLookup(............. ................)
|
|
| --
| Regards Ron de Bruin
| http://www.rondebruin.nl
|
|
|
| "Jim Conrady" wrote in message ...
| 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
|
|
|

Using Built in Functions in VB Code
 

Oops

Thanks for the correction Niek



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



"Niek Otten" wrote in message ...
Hi Jim, Ron,

A dot after WorksheetFunction

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ron de Bruin" wrote in message ...
| Hi Jim
|
| Use it like this
|
| Application.WorksheetFunctionVLookup(............. ................)
|
|
| --
| Regards Ron de Bruin
| http://www.rondebruin.nl
|
|
|
| "Jim Conrady" wrote in message ...
| 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
|
|
|

Using Built in Functions in VB Code
 

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


--

Dave Peterson