How to use named ranges in VBA?

Suppose you have a table that looks like this:

Fruit Apples Oranges etc

John (numbers)

Bill

Sue

etc



and you insert > name > create the names in the top row & L column,
then in a formula you can say things like

= John Apples

or

= Apples + Oranges

Meaning the values in that row. Please is there any way to do the equivalent
in VBA? If (in VBA) you have option explicit and you say

x = John( 2)

you get an undefined variable error.

Sorry if this is a FAQ. I searched. Only thing I could find that looks relevant
was is 2006 . Somebody said " use the INDEX function" but I didnt
say how to do this. INDEX requires numerical arguments, so it cannot use
the defined names .

Thank you.

If you have a range named 'John', you can refer to in in VBA as

Please Login or Register  to view this content.

thank you for replies. I tried to attach a file.
In it I created the names in row 1 & col A & in col G are some formulae that
refer to cells in these ranges.

This will give you the number of apples for John:

Please Login or Register  to view this content.

and this will give apples and pears

Please Login or Register  to view this content.