Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link .xls cell formula to .xla function
Excel 2000 xla problem:
Hi, I would like to encapsulate all code from a .xls file into an .xla (complementary macro) file. My ultimate goal is to simplify the maintainability of my code: I do not want to ask users to update their thousands of .xls files when I do a tiny change in the code. The .xla file seemed like a good solution: the user's .xls files would not be affected if I would modify the code, only the .xla file would need to be replaced. Using this technique, subs seem to work fine, but functions don't. Is there a way to also encapsulate functions code outside of the .xls files? This is what I attempted: 1) I created a Code.xla file with a single module that has this code inside: Public Function Crap(ByVal psName as String) As String Crap = "Your name is " & psName End Function 2) I created a new empty ExcelWorkBook.xls file. 3) I referenced the Code.xla file in Excel's menu Tools/Complementary Macros... 4) I put the following formula into a cell in ExcelWorkBook.xls: =Crap("me") From all this I get #Name? as the cell formula result. Which is strange since the "Formula Zone" pannel that appears when you click on the "equal" button from our formula cell seems to recognize the Crap function parameters... Pointers anyone? (hehe) Thanks, C. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link .xls cell formula to .xla function
Load your *.xla file! Or Open your *.xla file
File with your macro have to be open! To open it, there are 4 ways: 1) Save it in XLStart folder. Excel automaticly open all workbooks from this folder (C:\Program Files\Microsoft Office\Office(version)\XLstart\ 2) Load *.xla file manualy: Tools-AddIns-... 3) Load *.xla from code 4) Open *.xla file from link -----Original Message----- Excel 2000 xla problem: Hi, I would like to encapsulate all code from a .xls file into an .xla (complementary macro) file. My ultimate goal is to simplify the maintainability of my code: I do not want to ask users to update their thousands of .xls files when I do a tiny change in the code. The .xla file seemed like a good solution: the user's .xls files would not be affected if I would modify the code, only the .xla file would need to be replaced. Using this technique, subs seem to work fine, but functions don't. Is there a way to also encapsulate functions code outside of the .xls files? This is what I attempted: 1) I created a Code.xla file with a single module that has this code inside: Public Function Crap(ByVal psName as String) As String Crap = "Your name is " & psName End Function 2) I created a new empty ExcelWorkBook.xls file. 3) I referenced the Code.xla file in Excel's menu Tools/Complementary Macros... 4) I put the following formula into a cell in ExcelWorkBook.xls: =Crap("me") From all this I get #Name? as the cell formula result. Which is strange since the "Formula Zone" pannel that appears when you click on the "equal" button from our formula cell seems to recognize the Crap function parameters... Pointers anyone? (hehe) Thanks, C. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link .xls cell formula to .xla function
You should be able to use the function even without creating the reference.
Make sure the Function is in a general/standard module in the addin and not in a worksheet/thisworkbook/userform module. Did you try entering it using the function wizard? -- Regards, Tom Ogilvy "Conceptor" wrote in message ... Excel 2000 xla problem: Hi, I would like to encapsulate all code from a .xls file into an .xla (complementary macro) file. My ultimate goal is to simplify the maintainability of my code: I do not want to ask users to update their thousands of .xls files when I do a tiny change in the code. The .xla file seemed like a good solution: the user's .xls files would not be affected if I would modify the code, only the .xla file would need to be replaced. Using this technique, subs seem to work fine, but functions don't. Is there a way to also encapsulate functions code outside of the .xls files? This is what I attempted: 1) I created a Code.xla file with a single module that has this code inside: Public Function Crap(ByVal psName as String) As String Crap = "Your name is " & psName End Function 2) I created a new empty ExcelWorkBook.xls file. 3) I referenced the Code.xla file in Excel's menu Tools/Complementary Macros... 4) I put the following formula into a cell in ExcelWorkBook.xls: =Crap("me") From all this I get #Name? as the cell formula result. Which is strange since the "Formula Zone" pannel that appears when you click on the "equal" button from our formula cell seems to recognize the Crap function parameters... Pointers anyone? (hehe) Thanks, C. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link .xls cell formula to .xla function
I used the method 2) in my initial try: didn't work. I
tried putting it in the XLStart folder without avail. your methods 3) and 4) are new to me. But if the .xla file was not open, I could not access sub calls either. But I can. So I assume that the .xla file should be "open". Only the function call causes problems. Could you detail your other methods to open the xla file? Thanx -----Original Message----- Load your *.xla file! Or Open your *.xla file File with your macro have to be open! To open it, there are 4 ways: 1) Save it in XLStart folder. Excel automaticly open all workbooks from this folder (C:\Program Files\Microsoft Office\Office(version)\XLstart\ 2) Load *.xla file manualy: Tools-AddIns-... 3) Load *.xla from code 4) Open *.xla file from link -----Original Message----- Excel 2000 xla problem: Hi, I would like to encapsulate all code from a .xls file into an .xla (complementary macro) file. My ultimate goal is to simplify the maintainability of my code: I do not want to ask users to update their thousands of .xls files when I do a tiny change in the code. The .xla file seemed like a good solution: the user's .xls files would not be affected if I would modify the code, only the .xla file would need to be replaced. Using this technique, subs seem to work fine, but functions don't. Is there a way to also encapsulate functions code outside of the .xls files? This is what I attempted: 1) I created a Code.xla file with a single module that has this code inside: Public Function Crap(ByVal psName as String) As String Crap = "Your name is " & psName End Function 2) I created a new empty ExcelWorkBook.xls file. 3) I referenced the Code.xla file in Excel's menu Tools/Complementary Macros... 4) I put the following formula into a cell in ExcelWorkBook.xls: =Crap("me") From all this I get #Name? as the cell formula result. Which is strange since the "Formula Zone" pannel that appears when you click on the "equal" button from our formula cell seems to recognize the Crap function parameters... Pointers anyone? (hehe) Thanks, C. . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Link .xls cell formula to .xla function
Yes the function is in a standard module. Function
wizard: that's what I meant by formula zone ( I have a french version of Office...). The function wizard finds the Crap function and even displays the correct parameter names. It returns the #Name? value even then. You should be able to recreate this "bug" by following the outline I wrote down. Let me stress that the problem is about function calls, not sub calls. Thanks C. -----Original Message----- You should be able to use the function even without creating the reference. Make sure the Function is in a general/standard module in the addin and not in a worksheet/thisworkbook/userform module. Did you try entering it using the function wizard? -- Regards, Tom Ogilvy "Conceptor" wrote in message ... Excel 2000 xla problem: Hi, I would like to encapsulate all code from a .xls file into an .xla (complementary macro) file. My ultimate goal is to simplify the maintainability of my code: I do not want to ask users to update their thousands of .xls files when I do a tiny change in the code. The .xla file seemed like a good solution: the user's .xls files would not be affected if I would modify the code, only the .xla file would need to be replaced. Using this technique, subs seem to work fine, but functions don't. Is there a way to also encapsulate functions code outside of the .xls files? This is what I attempted: 1) I created a Code.xla file with a single module that has this code inside: Public Function Crap(ByVal psName as String) As String Crap = "Your name is " & psName End Function 2) I created a new empty ExcelWorkBook.xls file. 3) I referenced the Code.xla file in Excel's menu Tools/Complementary Macros... 4) I put the following formula into a cell in ExcelWorkBook.xls: =Crap("me") From all this I get #Name? as the cell formula result. Which is strange since the "Formula Zone" pannel that appears when you click on the "equal" button from our formula cell seems to recognize the Crap function parameters... Pointers anyone? (hehe) Thanks, C. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to link a few cell to Calculate | Excel Worksheet Functions | |||
Naming a Formula, using Link to Cell containing that Formula | Excel Discussion (Misc queries) | |||
Link a cell from another worksheet as a result in a function? | Excel Worksheet Functions | |||
EXCEL LINK CELL FORMULA | Excel Worksheet Functions | |||
formula and cell link movements | Excel Worksheet Functions |