Document Stability

[AndrewKent]

Hi there,

I am currently building a program in Excel, which can carry out numerious tasks. Having over 15 userforms, countless macros and several worksheets of caculations in the background, basically all geared towards capturing customer data, producing a application form and submitting the data to an ".mdb" file.

Now everything is going well so far with little in the way of problems and the document is 2.5Mb in size (users centrally access this Read Only). I'm conducting a daily Clean Project on this but I have a few questions...

1) how far can I push an Excel workbook in terms of size/content before it becomes unstable
2) are there any tips that can be offered for program stability
3) Has anyone built anything on this scale before where they would be able to offer advice on the two questions above

...the reason I ask is the program could potentially double in size over the next 6 months depending on the volume of application types it is able to produce. I want to make sure I have considered stability. Many thanks,

Andy

 

[RoryA]

2.5MB is not big at all. (In fact it's tiny by comparison to a lot of the workbooks I use! )
As long as you clean out your project and compile it regularly, I can't see any problems if it doubles, though I would question why you are building an Excel interface with userforms to put the data into an mdb? I take it there is a reason you can't use Access itself? (or VB)

 

[AndrewKent]

That's the best bit of news I've had all day.

Okay, the question about why not use Access forms. Well, our company policy is that there can only be three users per department allowed to use the Access program. However Excel is unlimited. Therefore, submitting data to an ".mdb" file from Excel gets round this problem (we have a user base of 60+) and gives us all the benefits of Access. I (and two others) build the program and administer the database from Access.

I have the Clean Project add-on for Excel and run this proir to putting updates live, when you are talking about compiling the program, what are you referring to? Is this another add-on that I should consider?

Again, your help is proving invaluable to me (as it has in the past!) so I'm open to any advice you can offer (within the contraints of our working environment of course :D )

 

[RoryA]

I just mean the Debug-Compile option in the VBE!

 

[AndrewKent]

I'm in VBA through Excel, is that option available through here?

 

[AndrewKent]

Nevermind, found it...

 

[AndrewKent]

Okay, what does it actually do? Stupid question I know but it's the end of the day and I'm tired

 

[RoryA]

It's mostly just a quick way of checking that all your syntax is valid.

 

[AndrewKent]

Okay, I found it and ran it...very quickly, so I can gather from that there's nothing wrong with my syntax.

Appart from here of course, is there any decent websites out there for program design in VB(VBA)? Again this is really just to confirm I'm going about things the right way.

Andy

 

[RoryA]

Websites, I don't know about, as I learn better from books. If you plan to develop in Excel, then Professional Excel Development is, IMO, about the best book there is. Additionally, the Visual Basic Language Developers Handbook is my VBA bible - it also comes with the VBA SDK on disc.