12

I have a workbook that uses a certain vba function which doesn't appear in the Insert | Insert Function... dialog box list of available functions.

How do I find it's source (to see what it does)?

0

4 Answers 4

21

I hope I understand your question... From reading other answers I see that it is not a recorded macro. It's safe to assume then that someone wrote it in VBA. Try the following:

  1. Press Alt+F11 to open your VBA editor
  2. Click View -> Project Explorer (it may already be visible.) (or: CTRL+R)
  3. You should see "VBAProject(your workbook name.xls). Click that.
  4. Click one of the sheets under 'Microsoft Excel Objects' in the Project explorer.
  5. Click View -> Code
  6. Click Edit -> Find
  7. Type the name of the function in the box
  8. Ensure 'Current Project' is selected.
  9. Click Find Next

That should take you to where the function is written.

2
  • Try this: 3a. Click one of the sheets under 'Microsoft Excel Objects' in the Project explorer. 3b. Click View>Code Now try Step 4 in the original answer.
    – Sux2Lose
    Mar 30, 2010 at 14:53
  • cheers that found it
    – citronic
    Mar 31, 2010 at 12:37
3

Press ALT + F11 to see the VBA project window. You should be able to find the code for the custom function in there.

1

Microsoft has written a documenation for VBA. It is sorted for objects, not for functions, but if you don't need to type an object's name before the function, it is likely to be a function of an object you are currently working with, like the current workbook. This documentation can be read here. http://msdn.microsoft.com/en-us/library/bb149081.aspx Try looking for the function in objects like Workbook, Sheet, etc., and if it isn't there, use the search option on the site (warning: this search used to be bad, now it seems to be a bing search, so maybe it is better now). You won't be able to see the source for the function, but there is a comprehensive API there.

If you cannot find the function there and think that it is not a standard Excel function, there is the question of where did Excel get it from in order to execute it. Try debugging a macro calling the function in question, and use the "step into button". If you see the source, you've solved your problem. If it isn't there, it may come from an external application communicating with Excel. Go to the Button "Office", there choose Excel options, there go to Add-Ins. You can look at the addins installed and hunt down their documentation.

If all else fails and/or is too cumbersome, you can always google something like "[name of function] + Excel + VBA +Documentation]". Maybe you'll find some online documentation.

1
  • the function is definitely not a standard Excel function. As far as I know I can see all functions available via the Insert | Function... dialog then show all categories. the function name does not show up.
    – citronic
    Mar 29, 2010 at 17:24
0

The function you're referencing is probably a "macro" recorded in Excel.

If you're using Office 2007, click on the View tab and then click the drop-down arrow beneath the Macros button. Select View Macros from the list to view Macros recorded for that spreadsheet.

If you see the name of the function you're interested in listed, select it and then click the Edit button to view it.

3
  • thanks for response but I only see two macros here and neither is the function I'm interested in
    – citronic
    Mar 29, 2010 at 16:38
  • What's the name of the function? Mar 29, 2010 at 16:56
  • name is MarketPrice
    – citronic
    Mar 29, 2010 at 17:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .