Dividing one workbook into two: modules and data (add-in?) 2

[krinid]

Looking for some advice in splitting up an Excel workbook. Currently it has VBA code, and worksheets contain user-entered data. I'm looking to separate the code from the user data - what's the best way to do this?

I'm aiming at saving the user data in the worksheets in a workbook separately from the code so that when users save their data the workbook contains only their data. The reason is that the entire book is pushing over 2MB, whereas the data itself is only about 50KB per sheet, and it's a waste of time and disk space resaving the code with each workbook.

My first intuition is an Excel Add-in - but as I've never delved into Add-ins, can anyone provide any guidance or point out any pitfalls I might come across in doing so, or point out an alternative?

 

[fredericofonseca]

Add-in is the way to go.

Code changes you "may" need to do is related to how you reference excel objects.
E.g. if you do lots of
"thisworksheet.blabla" you may need to change this to
dim oThisworkbook as worksheet
Set oThisworkbook = blabla

and then reference the new object reference


Apart from this type of things I have never come accross any problem in moving from an normal Excel worksheet to a add-in.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[krinid]

Good point.

ThisWorkbook from inside an add-in refers to the add-in itself, is that right?

ActiveWorkbook will return, as expected, the active workbook and never the add-in, is that right? Because an add-in can never be active, can it?

My code refers a lot to the worksheets in ThisWorkbook, b/c they were always together. I'll have to make a lot of changes before I can make it add-in! I'll have to create a way to recognize the data workbook and data sheets so that the operations are only carried out on relevant sheets.

 

[fredericofonseca]

The Activeworksheet CAN be the add-in.

Getting the correct objects.
public wMacroWorksheet_base_values as worksheet
public wDataWorksheet as worksheet

....

set wMacroWorksheet_base_values = Workbooks("myadd-in.xla").Worksheets("base_values")

set wDataWorksheet = Workbooks(sWorkbook).Worksheets(sWorksheet)

Where sWorkbook and sWorksheet are populated by you depending on which workbook/worksheet your user was working at the moment.

More is involved but you get the idea


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[krinid]

I've been looking around for some tips on add-ins and found a decent site

http://www.fontstuff.com/vba/vbatut03.htm

- anyone know of any other good sites regarding creating add-ins? (I've got lots of questions still, but I'm sure there's a lot of answers out there already...)

Frederico,
How can an add-in be the activeworksheet? (I believe you that it can be, but don't see how it's possible yet... doesn't 'active' mean it has to have a physical worksheet portion which is active? I thought add-in's were only code modules...?)

 

[fredericofonseca]

Just as an example.

I have a production add-in that is structured as follows.

Sheet 1
work worksheet to hold the list of CTL files to be processed.
This is populated from a list of several folders, created dinamicaly when the macro is run.


sheet 2
Work worksheet to hold the CTL files records being processed
Each CTL file listed on 1 is opened, some records are extracted and added to this worksheet.
When process is finished they will be ordered by date.

sheet 3
When sheet 2 is ordered each group of files under the same day are loaded, and it's records are added to this sheet.
These will be sorted, titles added, grouped and totalized.
When done these will be added to the final workbook on a worksheet with the day as the name.

Sheet 4.

This holds some variables required for the add-in to run, such as production users authorised, production folders, development folders.
Depending on the user, and using vlookup, some cells are populated with either the production or the development information.


And then there is a module with all the code.
In some parts of the code there are some pieces like


wWork_sheet.Cells.SpecialCells(xlLastCell).Select
iRow = wWork_sheet.Cells.SpecialCells(xlLastCell).Row
iCol = wWork_sheet.Cells.SpecialCells(xlLastCell).Column

wWork_sheet.Range(Cells(1, 1), Cells(iRow, iCol)).Select
wWork_sheet.Range(Cells(1, 1), Cells(iRow, iCol)).Copy
wWork_values.Activate
wWork_values.Select
wWork_values.Cells(iWorkingRow2, 1).Select

where wWork_sheet is one of the loaded daily files, and wWork_Values is worksheet 3 mentioned above.

a LOT MORE can be done with a add-in. Almost everything you can do with VB.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[krinid]

Frederico,
Thanks for the info - I see add-ins are more flexible than I previously imagined.

 

[krinid]

Can you view the worksheets of an add-in as objects?

I have data on the worksheets that I need to change. How can I accomplish that? Or should I only change the corresponding XLS file and then resave it as an XLA when changes are finished?

 

[PHV]

Or should I only change the corresponding XLS file and then resave it as an XLA when changes are finished?
It's my option

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[krinid]

PHV,
Thanks for the response. In that case, what's the best way to test the functions of an add-in during the development phase? (so that it's tested under the same conditions that the add-in would be used - ie: I want to avoid the case where the XLS file works fine but the XLA file has errors).

 

[PHV]

Take care of the difference between ThisWorkbook and ActiveWorkbook, use systemwide unique functions, subs and global variables names.
Test the xls, save it and resave it as xla, quit excel and retest with the new xla.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[krinid]

Is there a way to reference the forms (etc) of the XLS workbook containing the main code that I'm referencing from my data sheet XLS workbook? Or will I have to make interface functions/subs for each method for each form I'm currently using?

WORKBOOKCODENAME.ThisWorkbook.VBProject.VBComponents("formName")
allows me to get at it but only as a component, not as a userform object (ie: I can't make it visible, hide it or access it's variables, etc).

 

[krinid]

For now I've put public form variables in a module in the code workbook, and will call a sub to assign these variables to the actual forms themselves. Is there a better way?

 

[krinid]

Do I need to reference the add-in in the VBE for each project that will reference the add-in? It seems I can reference the add-in's public functions from a worksheet, but not from VBA code (without referencing it).

 

[krinid]

Just found a site that answered the question for me (actually the one I myself listed above). The answer is YES, you need to reference it in the VBE reference list.

 

[krinid]

Follow up comment:
If referenced via VBE, it isn't necessary for it to be installed as an Add-In via the main Excel application.