Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pull valid arguements out of a custom excel function using VBA

Posted on 2011-09-14
7
Medium Priority
?
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 total points
ID: 36536332
>>But how to do it with code?

You can't.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

715 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