Engineering Planning for VBA programming

Read a lot of people write VBA code, a regiment of a regiment, a little planning is not, in order to VBA programming more engineering, here to discuss, and list their own recommendations:
0. Define a name for the VBA project instead of directly using the default name-"VBAProject"-to facilitate future cross-VBA engineering coding
1. Define a standard module named "O" Phonetic pronunciation of "o" in Pinyin, meaning "I", which defines all global objects, manages the code and data of this project, the main API:
[1] About (Optional showdetail as Boolean = False) function: All aspects of the project of the Readme, convenient to view the various types of information on the project, you can set up an information switch parameters, such as
Here are the showdetail parameters, such as adding showcodelinescount parameters
[2] Public Property Get Project () as Vbide. VBProject, a reference to the VBA project of this project
[3] Public Property Get Vbatype () as Vbatype, defines the VBA type for this project, such as in Excel or Word, where Vbatype is a custom enum
[4] Initialize () function: Initializes all content that needs to be initialized for this project
[5] Terminate () function: Destroys all that is required to destroy
[6] Haslib (Byval libname as String) function: Check whether this project has a reference to a COM, mainly check o.project.references collection, such as O.haslib ("Scripting"), convenient
Dynamic automatic encoding that may be required
[7] HasModule (Byval modulename as String) function: Check if there is a module in this project
[8] Property Get/let Nexterrornumber: Used to plan the project custom error number, so that each error number is unique, the custom error is thrown as follows:

The second parameter is in the Set Err.Source property, in the form: VBA project name + module name + method/function/property name, "If it is a property, the attribute name is also suggested to add a suffix #get/#Let/#Set, in order to indicate the error of the more specific source ' use ' # ' instead of ' _ ', It is because "_" is a valid identifier character, which may bring meaning confusion err.raise-2147221406, "Nutix.Output.Format", "The values parameter contains a number that is not equal to the number of formatted identities in the TXT parameter"

[9] Bake () function: Make a backup of the project
[10] Move () function: Migrate all code from this project to another VBA-enabled file
[11] Activate (Byval modulename as String) function: Opens the Code window for a module
[12] The declaration of various global objects, it is recommended to take the new keyword directly in the declaration, which avoids many set statements, and because the new keyword is when the corresponding object variable is actually accessed,
To actually perform the corresponding new operation, so if necessary, in the Initialize () function above, define the initialization of some objects that must be initialized immediately; Focus on this module declaration
can also facilitate the management of global variables and access, such as the object variable name is very long, direct o.xxx will be more convenient input, after all, intelligent member hints, and O module name only one word
Character. In addition, the centralized initialization and destruction can also avoid leakage operation.
[13] Common third-party feature objects:
(1) Scripting.FileSystemObject object, globally named FSO
(2) Vbs_regexp_55.regexp object, globally named re
(3) Msforms.dataobject object, globally named clip
(4) Shell32.shell object, globally named sh
(5) Iwshruntimelibrary.wshshell object, globally named WSH
(6) Mscomdlg.commondialog object, globally named CD
2. Define a standard module named Enums, which holds all the custom enums
3. Define a standard module named types, storing all the custom type structures
4. Define a standard module named constants, which holds all the custom constants
5. Define a standard module named API that holds all of the Windows API claims and extensions
6. Define a named main standard module, as the work module of this project, all the coding, testing in this module, the general test is also stored in this module for easy access to understand the characteristics of VBA, especially the non-commonly used objects, you may have learned to use its API some time, but not long, then forget, If the original test code, well named, and saved in this module, it will be convenient for you to familiarize yourself with this object again.
7. Define a series of X_XXX class modules for functional enhancements to the standard types of VBA, or to referenced third-party types, or VBA-intrinsic objects
[1] For example, COLLECTION/ARRAY/DICTIONARY/VB (this refers to the VBA library, because VBA and VB similarity, written here vb instead of VBA)/VBE (you write VBA code that window)
/designer (VBE form Designer)/math/string/regexp/errobject/filesystemobject type or object enhancement, respectively defined as X_collection/x_array
class module of/x_dictionary/x_vb/x_vbe/x_designer/x_math/x_string/x_regexp/x_errobject/x_filesystemobject;
[2] in the O module, respectively, define a global instance object of the class module, respectively named Xcollection/xarray/xdictionary/xvb/xvbe/xdesigner/xmath/xstring/xregexp

/xerrobject/xfilesystemobject
[3] When the corresponding object is to be used, the uniform is referenced in the form of o.xxx, especially when the object name is very long.
[4] Although only one such object is required, it is recommended to use a class module rather than a standard module, which is to avoid naming pollution, because defining too many of the standard module's global functions will name
Make a mess, sometimes there is a mutual shielding phenomenon, and as TypeName this VBA standard has been used in the name, if redefined in the standard module, then it will be obscured, created
Success can be confusing, obviously want to call Vba.typename but call the custom TypeName member of a standard module
8. Define a series of TOOL_XXX class modules for functional extension of VBA-enabled files
[1] For example, doc/.xls/.mdb/.dwg/.ppt, the corresponding definition of the Tool_doc/tool_xls/tool_mdb/tool_dwg/tool_ppt class module, to encapsulate the word/excel/access
The design of the function code of the/autocad/powerpoint file.
[2] Only one of these types of global objects is defined in the O module, and is named Tdoc/txls/tmdb/tdwg/tppt for easy access
[3] Property Get/set App Properties: used to define the corresponding VBA host object, that is, the Application object, depending on the O.vbatype property to decide whether to create a new object, or to directly reference a ready-made object,
For example, if you want to invoke the O.txls.app property, and the current document is a Word document, then accessing it requires creating a new Application object, and if it is an Excel document,
Can be set directly to the current application object
[4] Property Get/set Doc attribute: Used to define the corresponding Document object, Word is type, Excel is workbook, ...
[5] Other function codes
9. Define a standard module named Txtdata to store engineering data for this VBA project, such as the above O.nexterrornumber data, in the form of XML text saved, you can borrow ThsiWorkbook.VBProject.VBComponent.CodeModule.Lines ()/addfromstring ()/replaceline ()/insertlines ()/deletelines () and other APIs to complete the reading and writing of data
10. Define a class module named Checker, which is used to store all common judgment APIs in this VBA project.
[1] The return value is always Boolean, the member is suggested to be named Isxxx form, still only in the global definition of one such object instance, named Chk, may often use the following functions
[2] Isallintype (Obj as Object,byval TypeName as String) method: Check an array/collection whether each element type of an object containing many elements is a type
[3] Isallintypes (Obj as Object,paramarray typenames () as Variant) method: Check the array/collection for each element type of an object that contains many elements is a certain number of
One of the types
[4] Isintypes (Obj as Object, ParamArray typenames () as Variant) method: Used to check whether a variable is one of several types
[5] Hasattr (Obj As Object, Byval procname as String) method: Used to verify that an object has a member
[6] Isalltrue (ParamArray Values () as Variant) method: Used to verify that some variables are all true, and if only one variable is passed, the variable is treated as a multi-element variable, and the
All of its elements to perform this operation
[8] Isanytrue (ParamArray Values () as Variant) method: Used to test certain variables, whether one is true, if only one variable is passed, the variable will be treated as a multi-element variable
and perform the operation on all of its elements
[9] Issubset (Items1 As Variant, Items2 As Variant) method: Used to test whether the former is a subset of the latter
[10] IsIn (item As Variant, ParamArray items () As Variant) method: Used to verify whether the former is an item in the latter, if only one variable is passed, the variable will be treated as a multi-element
Variable and verifies whether item is one of the variables in the element
11. Define a class module named Createor, encapsulating all NEWXXX operations to facilitate initialization of objects
[1] Only one such object instance is still defined globally, and the global variable is named CRT
[2] implementation of some standard or third-party type object instantiation, and initialization, such as a new ErrObject object
[3] implementations of certain second-party types (which are your own custom types) are instantiated, and initialized, such as some type struct, or some custom class
12. Define a class module named Convertor, encapsulating all conversion actions
[1] Only one such object instance is still defined globally, and the global variable is named CVT
[2] Various objects of the string format method, named Str_+typename, convenient for the formatting of the object
13. Define a class module named caller, encapsulating all of the cluster calls, to a certain extent, to achieve functional programming, packaged well, can reduce the use of circular statements
[1] Only one such object instance is still defined globally, and the global variable is named cal
[2] ForEach (Objs As Variant, ByVal procname as String, ByVal CallType as Vbcalltype, ParamArray Args () As Variant) method: For each of the OBJS Object Access Pairs
Member to be
[3] Filtrate (Objs As Variant, ByVal procname as String, ByVal CallType as Vbcalltype, CompareTo as Variant, ByVal Getwhenequa L as Boolean, _
ParamArray Args () as Variant method: Filter objects from the Objs object set
14. Define a class module named dialogs that encapsulates the dialog box that may be used
[1] Only one such object instance is still defined globally, and the global variable is named Dlgs
[2] GetColor (Optional Dlgtitle as String) method: Invoke the function of the MSCOMDLG library to implement color selection
[3] GetFont (Optional Min As Integer =-1, Optional Max As Integer =-1, Optional Dlgtitle as String) as Nutix.mscomdlgfont method: Call the Mscomdlg library.
function, which implements the setting of the font, where Nutix.mscomdlgfont is the type of the custom type struct, because you are not able to directly new out a font object's
[4] GetSaveFileName (Optional Filter as String = "All Multiple pieces (*. *)", Optional filterindex as Integer = 1, Optional DefaultExt as Str ING, _
Optional Initdir As String, Optional Dlgtitle As String) method: Call the function of the Mscomdlg library to get the file name and path to save
[5] GetOpenFileName (Optional Filter as String = "All Multiple pieces (*. *)", Optional filterindex as Integer = 1, Optional DefaultExt as Str ING, _
Optional MultiSelect as Boolean = False, Optional initdir As String, Optional Dlgtitle As String) method: Call the function of the Mscomdlg library to get the Filename
and path
15. Define a type module named system that is used for functional operations that encapsulate this operating system
[1] Only one such object instance is still defined globally, and the global variable is named SYS
[2] Hastasknamed (ByVal name as String) method: Invoke the related functionality of WbemScripting Library (WMI) to verify that a process with a name is already running on the operating system
[3] Getcliptext () Method: Invokes the function of Msforms.dataobject to enable reading of the text of the system clipboard
[4] Setcliptext () Method: Call the function of Msforms.dataobject, implement the contents of the system Clipboard to set
16. Define a class module named Vbs/js, which encapsulates calls to Vbs/js code, and uses the Msscriptcontrol library to implement
[1] Only one such object instance is still defined globally, and the global variable is named VBS/JS
[2] The code is saved in the above mentioned in the VBA project data module, that is, the Txtdata module
[3] Property Get the This () as Msscriptcontrol attribute: Used to expose the inner central object outward, since all functions are built on the Msscriptcontrol object, so to speak
[4] Addcode (Byval code as String) method: Used to add code to this, code data will also be synchronized into the Txtdata module
17. Define a class module named output, which encapsulates various commonly used string formatting operations
[1] Only one such object instance is still defined globally, and the global variable is named out
[2] WriteLine (Byval line as String) method: Defines the write operation of the current class, all other writes are based on this method; internally provides write operations to the Immediate window/text file in two directions
[3] Singlesepline (Optional length as Integer) method: Outputs a "-" (minus sign) of the specified length, which is the output of a single split line
[4] Doublesepline (Optional length as Integer) method: Outputs a "=" (equal sign) of the specified length, that is, outputs a double split line
[5] Namedsepline (ByVal name As String, Optional ByVal Char As String = "*", Optional length as Integer) method: Outputs a named centered, specified length,
A line consisting of a char string
[6] FileName Property: Used to change the direction of output to a text file
[7] ObjectName Property: The name of the object used to define the output of the object, formatted with the following federated completion object
[8] Pobjself (Obj as Variant) method: Used to output the object itself, the object name is defined by 7, the output form is: ObjectName + "=" +object string
[9] Pobjcall (Obj as Object, ByVal procname as String, ByVal CallType as VBA. Vbcalltype, ParamArray Args () as Variant) method: Used to output an object
Member, the output form is: ObjectName + "." + procname + "=" + Object.proc value
[10] Pobjproperties (Obj As Object, ParamArray procnames () as Variant) method: Used to output multiple properties of an object, output form as above, each attribute, corresponding to an equation
Output line
[11] Ptypevalue (Obj as Variant) method: Output Variable type and value, Output form: Objtypename + "= +" + ObjValue string
18. Define a series of form modules named Frmxxxtool, encapsulating the interface operation for various VBA-enabled files
[1] corresponds to. doc/.xls/.mdb/.dwg/.ppt, the name of the form module is Frmdoctool/frmxlstool/frmmdbtool/frmdwgtool/frmppttool
[2] Do not define global above form objects because the VBA system has created one such object by default, which is named the same as the form module name
19. Define the class module named Coder, which encapsulates the object encoding function for this VBA project
[1] Only one such object instance is still defined globally, and the global variable is named cod
[2] Reference management function
[3] Code statistics function
[4] Code additions and deletions replacement function
[5] Dynamic programming function based on fixed mode

20. Finally add a piece of code, as the main block of work code "Main is to pre-declare a large number of possible variables and arrays, to avoid each re-declaration"

Public Sub aaa_00000000_aaa () "[VBA] Data type variable declaration Dim Byt As Byte, Byt1 as Byte, Byt2 as Byte, Byt3 as Byte Dim Bln A  S-Boolean, Bln1 as Boolean, Bln2 as Boolean, Bln3 as Boolean Dim Itg as Integer, Itg1 As Integer, Itg2 as Integer, ITG3 As Integer Dim Lng As Long, Lng1 as long, Lng2 as long, Lng3 as Long Dim Sng As single, Sng1 as single, Sng2 as Sin GLE, Sng3 as Single Dim Dbl As Double, Dbl1 as double, Dbl2 as double, Dbl3 as Double Dim Str As String, str1 as Str ing, str2 As String, Str3 As String Dim Dt As Date, Dt1 as date, Dt2 as date, Dt3 as Date Dim Var as Variant, Var1 A S Variant, Var2 as Variant, Var3 As Variant Dim Obj as Object, Obj1 as Object, Obj2 as Object, Obj3 as Object ReDim Byts (0) as Byte, Blns (0) as Boolean, ITGS (0) as Integer, Lngs (0) as Long ReDim SNGs (0) as single, Dbls (0) as Double, St RS (0) as String ReDim Dts (0) as Date, Vars (0) as Variant, Objs (0) as Object Dim I as Long, j as long, K as Long, RE As New vbscript_regexp_55.REGEXP Dim C As new Collection, C1 as new Collection, C2 as new Collection, C3 as New Collection "[Scripting] Data type Variable declaration Dim d As New Scripting.Dictionary, D1 as new Scripting.Dictionary, D2 as new Scripting.Dictionary Dim Key as Va Riant, Key1 As Variant, Key2 as Variant "[VBSCRIPT_REGEXP_55] Data type variable declaration Dim m as Vbscript_regexp_55.match, MS as VBSC Ript_regexp_55.matchcollection ' [vbide] Data type variable declaration Dim vbc as Vbide. VBComponent, CP as Vbide. CodePane, CM as vbide.    CodeModule ' [Excel] Data type variable declaration Dim rng as Excel.Range, rng1 as Excel.Range, rng2 as Excel.Range, rng3 as Excel.Range Dim sht As Excel.Worksheet, sht1 as Excel.Worksheet, sht2 as Excel.Worksheet, SHT3 as Excel.Range Dim WB as Excel.wor Kbook, wb1 as Excel.Workbook, wb2 as Excel.Workbook, wb3 as Excel.Workbook Dim shp as Excel.shape, Ole as Excel.oleobje CT ' [Word] Data type variable declaration Dim Doc as New MSXML2. DOMDocument60, E as MSXML2. IXMLDOMElement, a as MSXML2. Ixmldomattribute ' [MSXML2] Data type variable declaration DimCData as MSXML2. Ixmldomcdatasection, NL as MSXML2. IXMLDOMNodeList, N as MSXML2.  IXMLDOMNode ' msforms related variable declaration Dim win As Msforms.userform, grp as Msforms.frame Dim CTLs As Msforms.control, ctl as  Msforms.control Dim btn As Msforms.commandbutton, rbtn as Msforms.optionbutton Dim sbtn As Msforms.spinbutton, tbtn As Msforms.togglebutton Dim cbb As Msforms.combobox, lst as Msforms.listbox Dim ckb As Msforms.checkbox, IMG as MSF Orms. Image Dim lbl As Msforms.label, txt as Msforms.textbox Dim mp As Msforms.multipage, pg as Msforms.page Dim ts As Msforms.tabstrip, TB as Msforms.tab Dim SCB as Msforms.scrollbar ' <AAA_00000000_AAA_WorkingCode> ' < /aaa_00000000_aaa_workingcode>end Sub

================================================================================
As above is my VBA project--"VBA toolset. xlsm" coding planning, shared in this, I hope to give you love the VBA of the altar friends to help.
If you feel that my plan is functional or unreasonable, please give your suggestion.

Engineering Planning for VBA programming