Solved

Pull valid arguements out of a custom excel function using VBA

Posted on 2011-09-14
7
266 Views
Last Modified: 2012-05-12
I have an excel template with some third party functions.  The function accepts an old code as an arguement and returns a new code. so:

=GETNEWCODE("21E") returns 800B which is the new code.  There are only a finite number of old codes the function GETNEWCODE will recognize.  I want to use VBA to find out what old codes GETNEWCODE will accept.

So is there anyway to "look inside" a custom excel functioin using VBA to pull out the acceptable arguements?
0
Comment
Question by:atprato
7 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 36536112
Within the VB Editor, you can try to right-click on the function and hit "Definition" and see what comes up.
0
 

Author Comment

by:atprato
ID: 36536308
But how to do it with code?  I need my code to know the possible arguements of a given function.
0
 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
ID: 36536332
>>But how to do it with code?

You can't.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 34

Expert Comment

by:Gary Patterson
ID: 36536961
This is purely up to the developer of the third-party library.  Did they implement a mechanism for retrieving all valid codes?  (ListValidCodes, or something like that).  If not, perhaps you can write your own - look at the GETNEWCODE function to see where it finds the valid old codes.

If there is a limited number of possible "old codes", perhaps you could just write a loop in your program that rips through all possible "old code" combinations, and calls GETNEWCODE for each, keeping track of the valid old codes it finds.

If the list of "old codes" is static, then perhaps you could just do this once, manually, and populate a static list in your code.

Clearly, the list of "old codes" is stored somewhere.  Odds are it is in a table or similar structure, or coded statically in the library someplace.  If it is in a database table, it should be trivial to figure out how to obtain a list of old codes if you can determine the name and location of the table.

Post the code for the GETNEWCODE function for more specific recommendations.

- Gary Patterson
0
 

Author Comment

by:atprato
ID: 36537217
The code of GETNEWCODE() code is not available.  It is like an excel function, you can't see how it was written.  I was hoping somehow I could grab application.worksheetfunction.GETNEWCODE and somehow get some info out of it.  Mayb not.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37412239
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question