I use a New Project workbook whenever I’m starting, wait for it, a new project. The idea is that I always have the same stuff in my projects, so why set it up from scratch. At first, it contained the minimum stuff – stuff that was guaranteed to be in every project. Then I learned that it’s easier to delete stuff than add it, so I included a Toolbar module even though not every project will have a toolbar.
Every module has Option Explicit and a private string constant called msMODULE. MErrorHandler comes straight from PED. Some of the other modules look like this:
Private Const msMODULE As String = “MGlobals”
Public gbDebugMode As Boolean ‘true if debug file exists – used in error handling
Public Const gsAPPNAME As String = “NewProject”
Public Const gsCBTOPLEVEL As String = “TopLevelMenu”
Public Const gsCBTAG1 As String = “AppTag1”
Public Const gsCBTAG2 As String = “AppTag2”
Do you think “NewProject” has shown up on some message boxes? Well, I haven’t forgotten to change it yet, but I’m sure it will happen someday.
Private Const msMODULE As String = “MOpenClose”
Sub Auto_Open()
Const sSOURCE = “Auto_Open()”
Application.EnableCancelKey = xlErrorHandler
On Error GoTo ErrorHandler
gbDebugMode = Len(Dir(“C:Test_Datadebug.ini”)) > 0 ‘sets the debug mode for error handling
ErrorExit:
Application.EnableEvents = True
Exit Sub
ErrorHandler:
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub
Sub Auto_Close()
On Error Resume Next
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.CellDragAndDrop = True
End Sub
Private Const msMODULE As String = “MVersion”
Public Const lAPPVER As Long = 1
Public Const lAPPBUILD As Long = 1
MToolbar creates a menu item, a command button, and a popup. I’ve never bought into the table driven approach, and I’m not sure why. MToolbar does get a little unwieldy, but once it’s set up, I change it so rarely.
I usually end up adding a module called MProcess that holds the business logic. I don’t like the name but haven’t come up with a better one. Yeah, I know MBusinessLogic.
You can download NewProject.zip. What’s in your new project template?
Dick,
I’ve just started building a similar construct called AppCore – just like it sounds, it is what all my other apps are built around. As well as similar stuff to your examples, I’m including standard security and access stuff, standardized interfaces to databases, standard logging, version and release control, debug tools, etc.
Biggest challenge is retrofitting changes to AppCore “2.0? into AppCore “1.0?. I haven’t constructed AppCore as an addin yet, and I’m not really looking forward to it.
I tend to include an modFunctions for generic functions like fUsedRange (which finds the true used range based on cell content), IsIn (which tells you if an items belongs to a collection) and things like that.
And then I have modUtilities, which contains all sorts of small routines to do things like tidying up of styles, listing styles, checking template integrity regarding range names and lots of other stuff. This module only gets included on files where I do heavy lifting Excel work as opposed to plain vanilla VBA programming: e.g. when I build reporting templates.
I don’t have a project, but I have some modules & classes that have sets of functionality, I just drag them across as i need them, it’s not like they add much to the workbook size, so even if i want just one thing from a modual, i will put the whole lote there – lazy, but easy! I also try t get the standard modules to work like classes – i.e. replace able in any project, but it never works! Mainly because I go ” oh well i go do this properly, but just for this project I do it like this”!
I’ve done the same thing for years here at work. I call my “new project” BaseMacro.
Like you said, it’s easier to delete stuff, than to add.
I also make use of the document properties to use for the application name, manager names, version and install dates. Very handy.
My projects usually entails using a 3rd party program called Monarch, so I have modules and forms that deal with setting folders up for input files, models, and output.
Modules for menus and command bars.
A library module that contains several general routines that almost every project uses.
Sever different user forms, depending on the type of input needed (some projects may require several files, others just one). I just delete the forms that are irrelevant.
I try to make my projects not look like Excel at all, so there tends to be several hidden sheets that many contain user defined data that can only be accessed via menus. Also just a main screen with the company logo and title of the project. If the title says Base Macro, I know that I haven’t set the document properties up yet. This gives all the macros for the company a standard look and feel.
I do have the Professional Excel Development book, just haven’t had the time to peruse it in depth yet. I’ve always wanted to set up a general error handler. I have a simple one made up, it just doesn’t have a very polished look to it.
Like everyone else… the new project book builds up with time.
some of the most commonly used modules
a) A Control Sheet – with buttons linking to other sheets
c) A Parameter Sheet – where the user selects paths to databases – A Browse for File Module Tied
d) The Kick_butt_find Func – Thanks to Aron T Blood -xl-logic
e) A real LastRow Function
f) A Constants Module – For Application Headers, Stardard Messages etc
g) Help Sheet – Tied to a UserForm – Thanks to J.Walk
h) Sheets with Code names Sheet01, Sheet02 etc so that they line up nicely
I stopped using Menus / Toolbars – for user inputs – 2007 is a sad eventuality….some day and 2009 looks no different
Jan Karel –
Could we impose to ask you to share your IsIn function over in the Euler Problem 23 thread I just started? I used an approach I found on StackOverFlow, and would appreciate seeing another way.
…best, mrt