index function in vba

O

oercim

Hello, I have a problem in vba. I am trying to use index function(which is an excel function) in vba. Let the function to be:


Function myfunc(a As Range)
mayfunc = Index(a, 1, 2)
End Function

However I get such an error "sub of function undefined" by highlighting "Index". In vba isn't index function defined? If not, what is its counterpart in vba? Thanks a lot. Best regards.
 
C

Claus Busch

hi Oercim,

Am Sat, 9 Mar 2013 10:24:12 -0800 (PST) schrieb oercim:
Function myfunc(a As Range)
mayfunc = Index(a, 1, 2)
End Function

try:
myfunc = WorksheetFunction.Index(a, 1, 2)


Regards
Claus Busch
 
R

Ron Rosenfeld

Hello, I have a problem in vba. I am trying to use index function(which is an excel function) in vba. Let the function to be:


Function myfunc(a As Range)
mayfunc = Index(a, 1, 2)
End Function

However I get such an error "sub of function undefined" by highlighting "Index". In vba isn't index function defined? If not, what is its counterpart in vba? Thanks a lot. Best regards.

If you want to use an Excel function within VBA, you need to be specific (in computer talk). Not all Excel functions are useable within VBA.

Try WorksheetFunction.Index(...)
 
P

plinius

Il 09/03/2013 19:24, oercim ha scritto:
Hello, I have a problem in vba. I am trying to use index function(which is an excel function) in vba. Let the function to be:


Function myfunc(a As Range)
mayfunc = Index(a, 1, 2)
End Function

However I get such an error "sub of function undefined" by highlighting "Index". In vba isn't index function defined? If not, what is its counterpart in vba? Thanks a lot. Best regards.

You can use directly range(row_number, column_number)

Function myfunc(a As Range)
mayfunc = a(1, 2)
End Function

Hi,
E.
 
H

Harry Flashman

Il 09/03/2013 19:24, oercim ha scritto:







You can use directly range(row_number, column_number)



Function myfunc(a As Range)

mayfunc = a(1, 2)

End Function



Hi,

E.

One other point I would add to this, if you want to use worksheet functions in VBA you need to preface the function with WorksheetFunction or Application for example:

Sub IndexFunctionInVBA()
x = WorksheetFunction.Index(Range("C17:C32"), 1, 1)
MsgBox x
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top