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)