Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Table in my VBA User Defined Function
Hi,
I am making a UDF that is an engineering equation. This equation requires looking up data in a set table. I want to be able to use this in several of my spreadsheets, without having to copy the table into each one. Is there a way to incorporate it into my UDF so that it can reference the table or do I have to add a sheet to my personal.xlsb and then do it that way? I want to make this UDF into an add-in so that it is easier to share with collegues. Thanks! Jen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Table in my VBA User Defined Function
On Jul 14, 12:44*pm, Jen wrote:
Hi, I am making a UDF that is an engineering equation. *This equation requires looking up data in a set table. *I want to be able to use this in several of my spreadsheets, without having to copy the table into each one. *Is there a way to incorporate it into my UDF so that it can reference the table or do I have to add a sheet to my personal.xlsb and then do it that way? *I want to make this UDF into an add-in so that it is easier to share with collegues. *Thanks! Jen Hi Jen: In this example the table is an array. The array (table) is first filled by running the Starter sub. The UDF can then be used in the worksheet: Dim tablee(1 To 2, 1 To 7) As Integer Sub starter() For i = 1 To 7 tablee(1, i) = i Next tablee(2, 1) = 7 tablee(2, 2) = 11 tablee(2, 3) = 13 tablee(2, 4) = 17 tablee(2, 5) = 19 tablee(2, 6) = 23 tablee(2, 7) = 29 End Sub Function engineering(inp As Integer) As Integer engineering = 0 For i = 1 To 7 If inp = tablee(1, i) Then engineering = tablee(2, 1) Exit Function End If Next End Function If this does not help, try posting he http://social.answers.microsoft.com/...0-047e4781dcf4 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Table in my VBA User Defined Function
If you're going to share this with others, don't put it into your personal.xls*
workbook. They may have their own version and you'll have to make them choose what workbook to open (since excel can't have two files with the same name open at the same time in the same instance). Instead, you could create an addin -- either specific for this situation or something that could contain all your UDFs and macros. Name/save it as: JensUtils.xlam (or .xla if you have to support earlier versions of excel) Then you could create a UDF that uses data on a sheet in that addin. The addin will be hidden from the user, so they won't see that sheet via the user interface. But you could even hide that sheet within your addin to make it safer (or at least make it feel safer) from prying eyes/fingers. It's always better to pass the ranges that the UDF needs to use to the UDF via the formula in the cell: =vlookup(a1,[jensaddin.xla]tablesheetname!a:b,2,false) (for instance) Excel looks at those ranges in the function to determine when it should reevaluate the formula. If you don't pass the ranges, then excel doesn't know for sure when to recalc. But I bet in your case, the table doesn't change very often. But if you find that it does, you may want to either provide the range in the formula -- or give the users a way to recalculate all their formulas before they trust anything! ======= Saved from a previous post: This makes updates a lot easier (well, somewhat easier). If a macro changes, you fix your master copy of the addin and plop it back onto the common network drive where the users find the addin. (More later if you have to share individual files with each user (if they don't have access to the common network share).) But (there's always a but!)... The users need a way to access those macros. The macros don't show up in the tools|macro|macros dialog. So you need a nice way for them to run them. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003 and xl2007. http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx ========================= More about if you have to share those individual macro workbooks with users. If your macro workbook (addin) contains any UDFs (userdefined functions) located in cells in a worksheet, then if the users put that addin in any path that they want, you're going to be hit with a lot of phone calls--especially when your users share workbooks with each other. All my functions that used any of the UDFs turn to NAME errors and I get asked to point to a file to update links. What's going on??? Those UDFs will point to the drive/folder/filename that was used by the original developer of the formula (not you as the developer of the addin. Instead of saying put this addin on your local harddrive, tell them explicitly to create a new folder on their C: drive. Call it ExcelAddins. And file the addin in that folder as: C:\exceladdins\JensUtils.xla (or xlam) Since everyone will be using the same location and same name, excel will not have any trouble with links or NAME errors. On 07/14/2010 11:44, Jen wrote: Hi, I am making a UDF that is an engineering equation. This equation requires looking up data in a set table. I want to be able to use this in several of my spreadsheets, without having to copy the table into each one. Is there a way to incorporate it into my UDF so that it can reference the table or do I have to add a sheet to my personal.xlsb and then do it that way? I want to make this UDF into an add-in so that it is easier to share with collegues. Thanks! Jen -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dcount function within user-defined worksheet function | Excel Programming | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
How to access MSSQL table in a user defined function | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |