import existing module

[Guest]

How would I go about programmatically importing an exported module from a file?

Thanks!

 

[Dave Peterson]

Check out Chip Pearson's site:
http://cpearson.com/excel/vbe.htm

He shows how to write code that modifies code.

But there can be difficulties. If the workbook's project is locked or if the
user didn't allow any program access to the project, then you'll have trouble.

====
As an alternative, have you thought of creating a template (.xlt) that already
has the code in it?

 

[Guest]

Thanks for the link. I think I found right after posting (always works that
way!)

What I'm trying to do:
I have have a planning model with many sheets and custom code that takes
into account many different variables. The basic structure is the same for
every company that uses it. But for each company we do a certain amount of
customizing. The problem I have run into is that whenever I find something
that needs to change I have to contact all the companies and individually
change all of their files.

What I think I need to do is make the excel workbook a sort of shell. I can
store all of the user-added data in a text file and when I make updates to
the shell I'll just have to send each company the new shell.

The only problem is that the shell won't contain any of the customization.
What I am thinking is that I will create a sort of installer package using
another excel workbook for each company that stays fairly static. When I
send out the updated shell it will know to look for the installer for the
customization and open and run the macros.

Do you know of a better way to go about doing this?

Thanks

 

[Dave Peterson]

If possible, maybe you could separate the code into an addin. Then when the
code portion changes, you don't (or may not) have to update the data workbook.

If you're lucky, you may be able to build just a single addin and have code that
reacts to each type of workbook (per customer).

Thanks for the link. I think I found right after posting (always works that
way!)

What I'm trying to do:
I have have a planning model with many sheets and custom code that takes
into account many different variables. The basic structure is the same for
every company that uses it. But for each company we do a certain amount of
customizing. The problem I have run into is that whenever I find something
that needs to change I have to contact all the companies and individually
change all of their files.

What I think I need to do is make the excel workbook a sort of shell. I can
store all of the user-added data in a text file and when I make updates to
the shell I'll just have to send each company the new shell.

The only problem is that the shell won't contain any of the customization.
What I am thinking is that I will create a sort of installer package using
another excel workbook for each company that stays fairly static. When I
send out the updated shell it will know to look for the installer for the
customization and open and run the macros.

Do you know of a better way to go about doing this?

Thanks

 

[Guest]

Just to make sure I understand...

What would the add-in do? Would it be the shell or the customization?

The concern that I have is that oftentimes the customized portion is highly
sensitive information. Having had to crack into excel files before I don't
want to leave that open as a possibility. The customizations usually involve
extra worksheets that calculate specific items in the background that other
companies don't have.

 

[Guest]

I should add that the code, for the most part, is more static then the
formulas in the workbook. The customization isn't generally with the code
but with additional calculation requirements in the workbook itself.

 

[Dave Peterson]

The addin would contain the code that your customer needs to run your workbook.
If you have UDF's in cells in that workbook, that UDF could be moved to the
addin.

Then when the UDF has to change, the data workbook doesn't need to get updated
(directly). You just update the addin and send it out. The customer replaces
the addin, opens the data workbook (and the addin) and the formulas update
following the new algorithm.

If you've provided a subroutine that the customer needs to manipulate the data,
then that subroutine would be moved to the addin. So when that subroutine's
rules change, you change the addin and redeploy it to the customer. Again, the
data workbook won't need to be updated. Just install the new addin.

If you have proprietary (to each customer) data, then I wouldn't make a common
addin. You're right. That addin could be broken into and one customer would
have access to the information for all the other customers.

But another option would be to create one addin with all the code and another
set of addins--one for each customer.

Then each customer would get 3 files:
1. The "real" data workbook
2. The Code addin (with all the macros/UDFs)
3. Their own version of the proprietary data addin.

======
Just a note.

If you have workbook/worksheet events, you may want to make them application
events. So you can still put them in that code addin.

Then that 2nd workbook (the code addin) would do all the code work.

You can read a lot more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

Just to make sure I understand...

What would the add-in do? Would it be the shell or the customization?

The concern that I have is that oftentimes the customized portion is highly
sensitive information. Having had to crack into excel files before I don't
want to leave that open as a possibility. The customizations usually involve
extra worksheets that calculate specific items in the background that other
companies don't have.

 

[Dave Peterson]

But you may need code to put the results of the formulas where it's needed--or
not.

Sometimes, putting the calculation rules into a UDF may make it easier to
control things--but the downside is that UDFs are usually slower than worksheet
calcs.

I should add that the code, for the most part, is more static then the
formulas in the workbook. The customization isn't generally with the code
but with additional calculation requirements in the workbook itself.

 

[Guest]

I might be going about this the wrong way, but whenever I've done UDFs I've
had to trigger them with either an event procedure or a button. When I do
this I lose the ability for the user to undo previous actions, which is
something that is used a lot in the projection models to switch between
numbers (their pretty big and not much fun to write down everytime you want
to change).

That is why I let the workbook do all the calculating. Any thoughts?

Thanks for all the insight!

 

[Dave Peterson]

UDF's are functions that are placed in formulas in cells.

You can use something like:
=yourworkbookname.xls!myfunc(a1)
or if that workbook is an addin:
=myfunc(a1)

If your workbook is an addin, you'd use it just like a built in function.

I might be going about this the wrong way, but whenever I've done UDFs I've
had to trigger them with either an event procedure or a button. When I do
this I lose the ability for the user to undo previous actions, which is
something that is used a lot in the projection models to switch between
numbers (their pretty big and not much fun to write down everytime you want
to change).

That is why I let the workbook do all the calculating. Any thoughts?

Thanks for all the insight!
--
JNW

But you may need code to put the results of the formulas where it's needed--or
not.

Sometimes, putting the calculation rules into a UDF may make it easier to
control things--but the downside is that UDFs are usually slower than worksheet
calcs.

 

[Guest]

Thanks for refreshing me on UDFs. I was thinking of something totally
different!

And thanks for the insight. I think I still have some planning to do before
I can really continue.
--
JNW

UDF's are functions that are placed in formulas in cells.

You can use something like:
=yourworkbookname.xls!myfunc(a1)
or if that workbook is an addin:
=myfunc(a1)

If your workbook is an addin, you'd use it just like a built in function.

I might be going about this the wrong way, but whenever I've done UDFs I've
had to trigger them with either an event procedure or a button. When I do
this I lose the ability for the user to undo previous actions, which is
something that is used a lot in the projection models to switch between
numbers (their pretty big and not much fun to write down everytime you want
to change).

That is why I let the workbook do all the calculating. Any thoughts?

Thanks for all the insight!