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
Highlighted

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies