0

I have been taking some VBA course from the Excel for Business web site and created a user defined function called ULookup. The function works fine in the spreadsheet and if I use the = sign, the Ulookup will show up. However, Control-Shift-A does not show the complete fill in hints like it does on a PC and if I go to the insert function menu, the function will not show up at all. Is this a bug in the Mac version of Excel or is am I missing something? Here is the code that I put in Module 1 of myWorkbook:

Option Base 1

Function ULookup(lookup_value As Range, lookup_range As Range, source_range As Range, Optional match_type As Integer = 0) As Variant()
' Performs an Index / Match Lookup.
Dim results_Array() As Variant
Dim lookup_index As Long
ReDim results_Array(1) As Variant

' Consider defining this as an array function

' Dim lookup_value ' Contains the value I want to find

' Dim lookup_range ' Range to search in

' Dim source_range ' Pull corresponding value from

' Dim match_type   ' Consider the type of match to perform, exact, lesser, greater


' Find lookup_value in lookup range
lookup_index = Application.WorksheetFunction.Match(lookup_value, lookup_range, match_type)


' Determine if lookup value was in the range.

' Get corresponding value from source range
results_Array(1) = WorksheetFunction.Index(source_range, lookup_index)

' Do not edit code beyond this comment
ULookup = results_Array

End Function
Comintern
  • 21,855
  • 5
  • 33
  • 80
Patrick Raiford
  • 3
  • 1
  • 2
    Not sure of the purpose of this - you can easily perform the same thing with a native formula that doesn't incur the horrendous performance penalty of using `Application.WorksheetFunction`. As for your specific question, take a look at [this question](https://stackoverflow.com/q/14731675/4088852) or [this one](https://stackoverflow.com/q/7760657/4088852). – Comintern Oct 26 '18 at 22:48
  • Thank you for your response. This is just a practice code that I am running through. My problem on the Mac side is that the intellisense or whatever you call it , is not listing out the definition when pressing CTRL-Shift-A. It is not listed in the functions unless you use the = sign and start typing the formula. It does not show up in the object browser window in VBA so I am thing its a major bug in the Mac version of Excel and Microsoft really needs to get on the ball on making a proper VBA for Mac. – Patrick Raiford Oct 27 '18 at 03:42

0 Answers0