Choropleth Map of Myanmar in MS Excel VBA

Choropleth Maps
Choropleth maps are different from heat (sisarithmic) maps in that the former using pre-existing geographically defined areas while the latter just uses regions drawn according to the pattern of the variable...

VBA Project Info

Preamble

I was writing code for a Choropleth Map using msofreeform shapes in MS Excel.
I dropped it and picked up time and again according to my free time.
After several such attempts to finish that project, I became lost in the code flow.
I no longer remembered what subs/functions were there or how these were called by which sub/function.

A code visualizer was something that I want many many moons ago but these are either very expensive (for someone who just want to release open-source code or they don't incorporate that easily into our .xlsms.
Most such products require .bas or .frm file to be loaded into them to be analyzed.

As a first step in that direction (towards an incorporated codeflow diagram), I created a module/procedure lister based on the information provided by cpearson.com by the late but great Mr. Chip Pearson.

Currently, there are mainly 2 files (though the code from .bas module can be put into the userform module):

1. ".frm" file for userform module (with a complementary .frx)
2. ".bas" file for code module

1.".frm" userform module

".frm" userform module has 2 listviews (LVs) and 1 treeview (TV).
Users need to have MSCOMCTL.OCX in their windows/system32 or windows/syswow64 directories based on their system architecture.
This is required for the listview and treeview.
Please be reminded that these are ActiveX controls.
Why use the ActiveX controls?
Because I like them and I want to use them and because I can.
There are lots of codes on the web which would fill up the worksheet with the same information.
But I prefer to go through information inside a listview and view the hierarchical relationship of how and which procedures belong in which modules rather than a listbox or in the cells inside a table in a worksheet.
The controls on the userform were optimized for 1920x1080 resolution display.
Issues like partial display of label caption text (especially in the full version) might occur if different resolution were utilized.

How the userform was setup!
Please refer to the Releases section for screenshots.
At the top of the userform, there is a combobox which shows the list of References in the VBA Project.
A combobox was used in order to save space and also because this information is already easily accessible from the VBA Editor.
Below that, the main listview shows the component modules inside a VB Project in a workbook.
On the right of that LV, a treeview will show the procedures: the Subs/Functions inside that Module.
Below this LV and TV, there will be a more detailed LV showing the same information as TV but more information on Procedures.
*A .frx file is also included and it should be placed inside the same folder as the .frm.

2.".bas" standard module

".bas" module contains subroutines/functions required for collecting information regarding modules/procedures.
Most of these are based on information from Mr. Pearson's website.
For further information, please visit, http://www.cpearson.com/excel/vbe.aspx .

Releases

There will be 2 versions currently available for the users' perusal.

1. BareBone version which only shows a userform with 2 LVs and 1 TV.
   
2. Full version showing the different count values, buttons for reloading and exporting to table on worksheet.
   

IMPORTANT NOTEs on System Requirements

1. The VBA Project must be unlocked meaning it must not be protected with password.

2. The VBA code inside the .bas module requires:

   1. Trust Access to VBA Project Object Model
      Enabling it, is, dangerous and users must turn it off as soon as this macro was run.
      I shall not be held responsible for any damages whatsoever happens to users' computer hardware or software or otherwise.
      Please use the code herein provided only if the user agreed to the above line.

   2. MS Visual Basic for Applications Extensibility 5.3 and above.
      So, check it in the References in VBA Editor.

   3. There are ActiveX controls: Listviews and Treeview utilized in this macro.
      Please be aware that MS ActiveX controls can be used to jeopardize a computer system.
      Again, please use this macro only if the user agreed that I shall not be held responsible for any untoward side effects from using the code shared in the attached files.

3. This code runs without any problem on a 64bit Windows 10, 32bit MS EXCEL 2010.
   I don't think there is any limitation which might stop the barebone version from running on a 64bit MS EXCEL.
   But the full version might be having some issues regarding a 64bit MS EXCEL. But I will try to make it compatible.

FUTURE (feature) Road Map

1. There will be 2 new but separate userforms containing 1 LV and 1 TV respectively, in addition to the userform mentioned above in the barebone version, displaying what procedures, called every single sub/function in a module, was almost completed and will be available shortly.
   This shall be made available here on this repo, some time later.
   It is completed but shall not be released right now as further bugs were checked and cleaned.
   That feature would allow me to create shapes and draw a code flow diagram using shapes but that would take more time which will be one of the two/three future relases.

2. I have always wanted a VBA code obfuscator.
   There are both free and paid versions out there but they are too complicated or expensive.
   Using this code that I shared, one can simple create a translation table to change every occurance of a procedure name to some random alphanumeric ones, as the last step after development before releasing it, so that the code cannot be easily tracked/traced.
   Imagine doing the same thing to Variable Names.

3. Create a separate Dictionary for Dim declarations to rename the variables for obfuscation.

4. Create a separate Dictionary for UserForm Controls so that the default event handlers and label caption updates for these controls can be linked to procedures.

Remarks

1. Even though I prefer incorporating the .frm and .bas into an existing project inside a .xlsm, these could be easily modified to load a closed .xlsm file and check it's codes but that's up to the users' preferences and requirements.
   The code inside .bas module could be copy/pasted into the userform module (.frm) file if the users wish so, according to their requirements and preferences.

2. The full version will have more informative GUI and bells and whistles for quality of life improvements for easier and clearer view of information.

Possible Short-Comings and Declaration of Exemption/Immunity/Obligation

1. The code herein shared inside of the .bas and .frm/.frx files were designed to syntactically look though an existing VBA project to:
   1. create a list of existing procedures, subroutines (subs), functions (funcs) with limited emphasis on property get/let/set procedures.
   2. map out procedure calls made within the same or between different modules in a given VBA project (using procedures released in the same or different .frm/.frx and .bas files).
   3. obfuscate the code in a project where the necessary .bas and .frm/.frx were imported/incorporated (using procedures released in the same or different .frm/.frx and .bas files).
2. Since the process was based on SYNTAX and not through actually calling of the procedures to list existing procedures, the output of this tool were not guaranteed to list every available/existing procedures or their calls or 100% obfuscation were guaranteed.
3. Therefore, the use of the code and tools proviced in the released file(s) were subjected to loss of data and/or function of the code in users' project and, if and when such mishaps do occur, it is thus understood and binding that the user has agreed to such liability and henceforth, the owner of the release code shall not be held responsible by any mean whatsoever.
4. The ownder of the released code and files may or may not acknowledge/respond to the requests/comments/questions, in any form, at his own discretion/convenience in any manner he deems fit.