Home

Complex VBA Excel - Best Practice (KISS)- Visual Diagrams-Wireframes or Recommendations

%3CLINGO-SUB%20id%3D%22lingo-sub-757894%22%20slang%3D%22en-US%22%3EComplex%20VBA%20Excel%20-%20Best%20Practice%20(KISS)-%20Visual%20Diagrams-Wireframes%20or%20Recommendations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-757894%22%20slang%3D%22en-US%22%3E%3CH6%20id%3D%22toc-hId-1006506998%22%20id%3D%22toc-hId-1006507000%22%20id%3D%22toc-hId-1006507000%22%20id%3D%22toc-hId-1006507000%22%20id%3D%22toc-hId-1006507000%22%3EFrustrated%20quickly%20when%20getting%2040%20lines%20of%20VBA%20down%20to%20learn%20it%20can%20be%20written%20In%202%20lines%20going%20a%20different%20route.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ERecommendations%20for%20the%20best%20practices%20on%20CLEANIEST%20way%20possible%20for%20Excel%20VBA%20objects%2C%20properties%2C%20and%20methods%20being%20applied.%20Simple%20right.%20Excel%20allows%20the%20nesting%20%26gt%3B64%20levels%20within%20a%20function%2C%20everything%20become%20complex%20at%20a%20point%20%3A)%20MUST%20use%20Excel%20VBA%20per%20requirement.%3C%2FH6%3E%3CH6%20id%3D%22toc-hId--1545649963%22%20id%3D%22toc-hId--1545649961%22%20id%3D%22toc-hId--1545649961%22%20id%3D%22toc-hId--1545649961%22%20id%3D%22toc-hId--1545649961%22%3ETrying%20to%20locate%20the%20simplest%20%22diagram%2Fmapping%2Fstructure%22%20reference%20guide%20to%20assist%20with%20'order%20of%20operation'%20when%20it%20comes%20to%20using%20VBA%20within%20Excel%20data%20types%20are%20stored%20procedures%2C%20Access%20tables%2C%20and%20Excel%20worksheets%2Fbooks!%3C%2FH6%3E%3CH6%20id%3D%22toc-hId-197160372%22%20id%3D%22toc-hId-197160374%22%20id%3D%22toc-hId-197160374%22%20id%3D%22toc-hId-197160374%22%20id%3D%22toc-hId-197160374%22%3EExample%3A%3CBR%20%2F%3E-%20Workbook%20with%2015%20worksheets%20(SOME%20cells%2Fcolumns%2Frows%20of%20data%20are%20being%20used%20in%20a%20variety%20of%20functions)%20from%20them%20all%3CBR%20%2F%3E-%20Different%20arrays%2C%20formatting%2C%20tables%2C%20charts%2C%20functions%2C%20starting%20points%2C%20on%20each%20worksheet%20in%20workbook%3CBR%20%2F%3E-%20Additional%20workbook%20with%20some%20additional%20data%2C%20functions%2C%20calculations%20to%20be%20leveraged%3CBR%20%2F%3E-%20Create%20%3CSTRONG%3EReport%2FResult%3C%2FSTRONG%3EWorksheet%20file%20in%20location%20'X'%3CBR%20%2F%3E-%20Title%20%3CSTRONG%3EReport%2FResult%3C%2FSTRONG%3EWorksheet%20'______'%3CBR%20%2F%3E-%20Protect%20%3CSTRONG%3EReport%2FResult%3C%2FSTRONG%3EWorksheet%20from%20being%20broken%20by%20end%20user%20that%20NEEDS%20the%20excel%20version!%3CBR%20%2F%3E-%205000%2B%20lines%20of%20VBA%20code%3CBR%20%2F%3EExcel%20is%20powerful%20trying%20to%20keep%20it%20SIMPLE%2C%20ORDERLY%20is%20the%20goal!%3CBR%20%2F%3EVisual%20-%20Mappings%20of%20Best%20Practice%20-%20Order%20of%20Applying%20all%20the%20check%2C%20applying%20references%20correctly%2C%20looping%20as%20to%20not%20cause%20performance%20hit%20when%20loading%3C%2FH6%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-757894%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EVBA%20in%20Excel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-757911%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20VBA%20Excel%20-%20Best%20Practice%20(KISS)-%20Visual%20Diagrams-Wireframes%20or%20Recommendations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-757911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302322%22%20target%3D%22_blank%22%3E%40aes35%3C%2FA%3E%26nbsp%3BWhat%20is%20your%20question%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758022%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20VBA%20Excel%20-%20Best%20Practice%20(KISS)-%20Visual%20Diagrams-Wireframes%20or%20Recommendations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20I%20was%20asking%20for%20references%20visual%20learning%20aids%20for%20some%20aspects%20of%20the%20VBA%20(Objects)%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Is%20there%20any%20%3CSTRONG%3Erecommended%20method%20or%20order%20to%20adding%3C%2FSTRONG%3E%3A%26nbsp%3BIf%20you%20have%20multiple%20Date%20and%2For%20CboBox%20options%20for%20individuals%20to%20select%20data%20to%20populate%20Excel%20VBA%20worksheet%20coming%20from%20variety%20of%20data%20sources%20leveraging%20VBA%20functions%2C%20actions%2C%20etc.%3C%2FP%3E%3CP%3Efunctions%2C%20forms%20and%2For%20modules...%3CBR%20%2F%3EFunctions%20within%20functions%20within%20functions%3CBR%20%2F%3EIfs%20within%20Ifs%20with%20in%20Ifs%3C%2FP%3E%3CP%3EOr%20is%20this%20basically%20what%20your%20preference%20is....%20I%20am%20trying%20to%20identify%20issues%20that%20could%20cause%20the%20code%20to%20CRAWL%20thru%20some%20looping%20and%20functions%20crossing%20active%20workbooks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
aes35
New Contributor
Frustrated quickly when getting 40 lines of VBA down to learn it can be written In 2 lines going a different route. 

Recommendations for the best practices on CLEANIEST way possible for Excel VBA objects, properties, and methods being applied. Simple right. Excel allows the nesting >64 levels within a function, everything become complex at a point :) MUST use Excel VBA per requirement.
Trying to locate the simplest "diagram/mapping/structure" reference guide to assist with 'order of operation' when it comes to using VBA within Excel data types are stored procedures, Access tables, and Excel worksheets/books!
Example:
- Workbook with 15 worksheets (SOME cells/columns/rows of data are being used in a variety of functions) from them all
- Different arrays, formatting, tables, charts, functions, starting points, on each worksheet in workbook
- Additional workbook with some additional data, functions, calculations to be leveraged
- Create Report/Result Worksheet file in location 'X'
- Title Report/Result Worksheet '______'
- Protect Report/Result Worksheet from being broken by end user that NEEDS the excel version!
- 5000+ lines of VBA code
Excel is powerful trying to keep it SIMPLE, ORDERLY is the goal!
Visual - Mappings of Best Practice - Order of Applying all the check, applying references correctly, looping as to not cause performance hit when loading
2 Replies

@Ingeborg Hawighorst 

 

1. I was asking for references visual learning aids for some aspects of the VBA (Objects) 

2. Is there any recommended method or order to adding: If you have multiple Date and/or CboBox options for individuals to select data to populate Excel VBA worksheet coming from variety of data sources leveraging VBA functions, actions, etc.

functions, forms and/or modules...
Functions within functions within functions
Ifs within Ifs with in Ifs

Or is this basically what your preference is.... I am trying to identify issues that could cause the code to CRAWL thru some looping and functions crossing active workbooks.