Solved

Pull valid arguements out of a custom excel function using VBA

Posted on 2011-09-14
7
282 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 500 total points
ID: 36536332
>>But how to do it with code?

You can't.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

623 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