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.

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.

.

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.

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.

.

.

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.



.