Solved

Pull valid arguements out of a custom excel function using VBA

Posted on 2011-09-14
7
261 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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:teylyn
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now