VBA Boilerplate

What is the Boilerplate:

An Excel binary file with VBA code in it, which can be used for every new VBA project as a boilerplate.

About the Boilerplate project:

Building a boilerplate, which is to be used by as a start point for every VBA project was long in my mind. Somewhere in 2016 I have decided to put all the useful VBA code that I am using in a single repository. The repository is  https://github.com/Vitosh/VBA_personal, and up to now it has 30 stars in GitHub and just 1 contributor except for me. The reason for this is that it probably looks a bit unstructured and I am the only one who can somehow find his way among all these files. Anyway, I am having some time, thus I have decided to restart the project again.

How can I use the boilerplate:

Simply download it and use it! Or, if it is against your rules to download and open binary workbook from github (this is something that I completely understand!), import the modules to your own binary workbook. Safety first, this is the internet :)!


How can I propose / improve the boilerplate:

Make a git pull request. It would be great, if you put write some tests before making the PR.

Video tutorial for the boilerplate:

  • Part 1 – ExcelAdditional, ExcelDates, ExcelFormatCell, ExcelLastThings

  • Part 2 – ExcelPrintToNotepad, ExcelStructure, tblInput, xl_main

  • Part 3 – ExcelVBE, ExportModules, ImportModules, Forms (the old way), Forms (the good way)

  • Part 4 – Test Driven Development and Tests

What is inside the boilerplate:

In the current version 8th version, the code is structured as follows:

  • ConstantsAndPublic
    • The module provides a list of the used public constants in the whole project. Including one public variable, which is used to build the error report
  • ExcelAdditional
    • Various useful procedures are here. They somehow do not belong anywhere else so far:
      • FreezeRow
      • UnfreezeRows
      • SumArray
      • ChangeCommas
      • BubbleSort
      • IsArrayAllocated
      • RangeIsZeroOrEmpty
      • MakeRandom
      • IsRangeHidden
      • ColumnNumberToLetter
      • IsValueInArray
      • Rgb2HtmlColor
      • NamedRangeExists
      • GetRgb
      • CopyValues
      • OnEnd
      • OnStart
  • ExcelDates
    • Dates were always tough for Excel users. These were tested for quite a long time.
      • GetLastDayOfMonth
      • GetFirstDayOfMonth
      • AddMonths
      • AddMonthsAndGetFirstDate
      • DateDiffInMonths
  • ExcelFormatCell
    • Formatting a cell in Excel can be done in various ways. These are some quick ones:
      • FormatAsDate
      • FormatAsPercent
      • FormatAsCurrency
      • FormatAsEurProM2
      • FormatRedAndBold
      • WhiteRows
      • WhiteCell
      • FormatFontColorToGrey
  • ExcelLastThings
    • Last row, last column, etc… in Excel are a must, when you are working with VBA. Make sure that you are aware, that some of the code ignores hidden ranges:
      • LastColumn
      • LastRow
      • LastUsedColumn
      • LastUsedRow
      • LocateValueRow
      • LocateValueCol
      • Increment
      • Decrement
  • ExcelPrintToNotepad
    • Printing to a .txt file is a feature that everyone needs. The file is in ThisWorkbook.Path & "\Info  folder.
      • PrintToNotepad
      • CodifyTime
      • MakeAllValues
  • ExcelStructure
    • Changes in the structure of Excel are found here. Named ranges, printing PDFs, working with comments, styles, resetting and unlocking stuff is found here
      • LockScroll
      • StyleKiller
      • DeleteName
      • CoverRange
      • PrintActiveSheetPDF
      • PrintPage
      • DeleteDrawingObjects
      • UnhideAll
      • UnprotectAll
      • HideNeededWorksheets
      • AddCommentToSelection
      • PrintArray
      • PrintAllNames
      • DeleteAllNames
      • DeleteCommentInSelection
      • SelectMeA1RangeEverywhere
      • HideShowComments
      • ResetAndUnlock
      • EnableMySaves
      • DisabledCombination
      • DisableShortcutsAndSaves
  • ExcelVBE
    • Be careful here. In general, this one could be dangerous, as far as it has one sub named ImportModules. It imports all the modules from a given folder to a given workbook. The “problem” is that before importing these, it deletes all other modules there. Just make sure that you know what you are doing, before using any of the subs from there.
      • PrintAllCode
      • PrintAllContainers
      • ListProcedures
      • ExportModules
      • GetFolderOnDesktopPath
      • CreateFolderOnDesktop
      • ImportModules
      • DeleteAllVba
  • FormExample
  • FormSummaryPresenter
  • FrmExample
  • FrmInfo
    • The above four a combined together.  To run the form, call “ShowMainForm”. It does the rest. The forms are built, as in the article here – the perfect userform
  • tblInput (Input)
    • There is 1 sub for selection_change in this one. It checks the Zoom.
  • tblSettings (Settings)
    • Nothing in this one. It is by default xlVeryHiddenIts idea is to put some data inside, avoiding the data in ConstantsAndPublic.
  • TddMain
  • TddSpecDefinition
  • TddSpecExpectation
  • TddSpecInlineRunner
  • TddSpecSuite
    • The 5 modules and classes above are a framework taken from here, with some small changes. TddMain is where the tests are.
  • VersionsAbout
    • Well, this is #VBA. I have seen lots of projects, where the versioning is inside, hidden in a module. This is probably not a good practice (again!). But so these stay there.
  • xl_main
    • Workbook_BeforeClose
    • Workbook_BeforeSave
    • Workbook_NewSheet
    • Workbook_Open
Tagged with: , ,