Solved

Pull valid arguements out of a custom excel function using VBA

Posted on 2011-09-14
7
270 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 35

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

792 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