A long spiel from a septuagenarian novice struggling with Excel VBA...


I have a workbook with several data-entry worksheets (each with a different layout, to facilitate input of possibly repetitive financial data, and variable amounts of data). For each of these sheets, other sheets transcribe the data into tabular blocks laid out for printing, adding things like Brought Forward, Carried Forward, subtotals etc. Some of these latter sheets contain the full input data; some remove personal details and only show total amounts. Finally a "Print" sheet merges the full data onto several pages of paper, deciding (inter alia) where to put page breaks. A "Digest" sheet does the same for the de-personalised data. The user fills in all the data-entry sheets, then prints the "Print" and "Digest" sheets.
The problem is that this is achieved using some extremely complicated cell formulae, which (although they work) are not easily maintainable - including conditional formatting to put horizontal or vertical lines, or boxes, around various fields on the printout.
I would like to use VBA macros to collect the data in public arrays, and use code with copious comments to do the reorganisation of the data. Typically this should occur in a routine called on exiting from each sheet to the next one. [Also, I would like to declare some preset arrays, e.g. the value of each coin/banknote denomination.] But I haven't discovered how and where to declare these arrays. If I try doing it at the head of the Workbook code, I can't "do the presetting outside a sub"; if I try it within a sub, it doesn't like the public declaration.

Attached Files

Cash analysis 3.00 wip+minimal data.xlsm (367.9 KB, 10 views)

Public variables have to be declared outside all routines, but set within one. You can either use public Functions that simply return the array you need, or use public Constants that are delimite strings, and then Split them whenever you need an array.

Thanks, Debaser. I think I am starting to get sorted out with my array and const declarations. Two quick follow-ons from this, though:

I want to call a sub (or a function?) when I try to leave a worksheet, which does some analysis of the data on that sheet, and leaves the results in various arrays.

What seems to work is the following coding...

In worksheet "Income" (aka "Sheet2"):
Private Sub worksheet_deactivate()
Call ThisWorkbook.Income_Deactivate
End Sub

In ThisWorkbook:
<various array and constant declarations (I'm happy with these now)>
Public Sub Income_Deactivate()
<sort the data into the arrays, maybe generate error messages>
End Sub

I seem to need the double-layer call in order to get my "deactivate()" code invoked when (say) the user clicks on the tab for another worksheet, and then do the processing where the arrays etc. are in scope. But is there a better way of achieving this?
In the event that my analysis finds an error, can I (maybe by using functions instead of subs) force the user to stay on the outgoing worksheet and fix the error?

The public declarations don't really belong in the ThisWorkbook module - they should be in a regular module. If you have them in ThisWorkbook, you need to access them as members of that object from other modules - i.e. use ThisWorkbook.variable_name and not just variable_name.

You could turn your routine into a function that returns True if everything is ok and False otherwise. Then the deactivate event can check the return value and simply reactivate the sheet if need be.

Thanks, Debaser. I haven't started to use modules yet, but I think I am now able to work out for myself how to do it.
You may consider this thread solved, and once again many thanks.