index function in vba
 

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.

index function in vba
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

index function in vba
 

On Sat, 9 Mar 2013 10:24:12 -0800 (PST), oercim wrote:

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(...)

index function in vba
 

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.

index function in vba
 

On Wednesday, March 13, 2013 9:16:04 PM UTC+11, plinius wrote:
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.

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