Here is the complete report
List of Direct URLs
List of Search String Counts
Category: xlGenie
These posts will focus on my insights into the proper use of Excel VBA for development. It will also showcase my collection of Excel VBA tools.
Topic Search Excel VBA Automation with Word
Here is the complete report for my topic search for Excel VBA Automation with Word
Here is my listing of Direct URLs:
Here is my list of Search String Counts:
Topic Search Regular Expressions
Here are a collection of Resources for Regular Expressions
Here is the complete report for Regular Expressions
Here is a listing of the Direct URLs for Regular Expressions
Here is a listing of the Search String Counts for Regular Expressions
Topic Search VBA File System Object FSO
Here is a collection of resources for the File System Object (FSO)
Full Report
Here is a listing of the Direct URLs
Here is a report on the Search String Counts
Topic Search VBA and VBScript
Collection of resources for VBA and VBScript
Here is the full report workbook
Here is the report for the Direct URLs
Here is the report for the Search Strings
TSRP: Explore List Objects Excel and VBA
TRSP Input Workbook
TRSP Output Workbook
TRSP Search String Counts PDF
TRSP Direct URLs List PDF
Identify Special Characters that can be used to generate valid Excel Range Names
There are times when evaluating a client’s workbook that it becomes convenient to add an organized set of unique range names.
These new range names provide a detailed level of control and simply the effort to re-engineer the targeted workbook.
Using special characters to create these new names makes it very easy to manage, control and ultimately remove them from the project without disturbing the original range names.
Here is a link to a workbook that identifies these special characters either as the beginning of a range name or in the middle of a range name.
There is also a link to a PDF report as well.
Suggestions for personalizing the Excel VBA Editor
A sample of my view of VBA Source Code.
Always enable Require Variable Declaration.
Do not enable the automatic syntax checker. It will only interrupt your train of thought while you are working with and manipulating your VBA source code.
To make your code more visible select the Lucida Console (Western) FONT.
The Lucida Console Western makes it much easier to distinguish:(1;i;I;l;L).
The digit One; the lowercase “eye”; the uppercase “eye”; the lowercase “ell” and the uppercase ‘ell”.
To make the VBA keywords standout display them in RED. RED is far more visible than the default BLUE.
To keep track of syntax errors display them in MAGENTA.
Important Note about the VBA Compare Utility from Formula Software
From time to time, when developing VBA codes it is necessary to compare the VBA code from different projects. A Google Search for “vba diff” will yield links to a number of products that will identify and document VBA differences.
Of all the products listed VBA Code Compare from Formula Software, Inc. is the only freeware product. It can be downloaded from this link:
http://www.formulasoft.com/vba-code-compare.html
Here is a short description of the features of the current version of VBA Code Compare.
VBA Code Compare 0.4 beta (April 2006)
- Supports Word VBA projects (Word 97/2000/2003)
- Supports command line options
- Added ability to open “.MDW” workgroup files
- New option: Ignore columns. This option allows you to specify ignored and compared characters ranges for each line
- Added ability to close open modules.
However, I discovered that VBA Code Compare can also work with current versions of Excel XLSM Workbooks!
I discovered this functionality based on the technique that is used to open XLSM projects to present the underlying XML structure. The technique is carried out by postfixing “.zip” to the XLSM workbook.
With this in mind, I took two XLSM workbooks and postfixed “.XLS” to each one. With this simple modification, I was able to use VBA Code Compare to open both workbooks.
This workaround will not work if Excel 2003 or any earlier Excel releases are on your computer!!!!
Several of the VBA Differencing Products listed on the Google search:
VbaDiff http://vbadiff.com/
xlCompare http://www.xlcompare.com/compare-vba-projects.asp
DiffEngineX https://www.florencesoft.com/compare-diff-excel-vba
Sorting in VBA using VBA-ADO
Several months ago I re-published my Code for an Enhanced Insertion Sort Algorithm.
At that point, my colleague SNB wrote to tell me about other methods that could be applied to achieve the same results.
SNB is the owner of a very valuable Web Site: VBA for Smarties
In particular, check out his great tutorial on VBA Arrays
http://www.snb-vba.eu/VBA_Arrays_en.html
He evaluated my code and provided a much simpler solution using
VBA-ADO Library which has a sorting method.
I am republishing the improved VBA project to provide a hands-on demonstration that presents the hard way and the easy way to sort using VBA.
SNB’s code is very efficient and direct, one routine for sorting numerical values and another one for sorting string. Each routine has 30 lines of code!
My code, on the other hand, has two routines for sorting numerical values and two routines for sorting strings along with a number of supporting routines. All in all, my codes require nearly 900 lines of code.
I have included a tab in the Workbook to demonstrate that our results match the stable sorting that is provided by the Worksheet sorting.
I want to point out that our sorting procedures are stable. Meaning that as the sorting is progressing through the original data, that the position of each new data item that is added to the sorted collection maintains its relative position to where it was in the original data.
I also want to underscore the importance that I give generating a permutation array to act as a sorting operator.
Based on what I have learned from SNB, I will be using his code as I continue the development of my Tool Kit of Numerical Techniques.
Link to the Workbook:
https://drive.google.com/open?id=0BxNZW5nboqyIaHlJdW9jQTF5d28
Other important references:
Stable Sorting:
https://en.wikipedia.org/wiki/Sorting_algorithm#Stability
Insertion Sorting:
https://en.wikipedia.org/wiki/Insertion_sort
http://www.geeksforgeeks.org/insertion-sort/
https://www.tutorialspoint.com/data_structures_algorithms/insertion_sort_algorithm.htm
Permutations for Sorting:
https://rosettacode.org/wiki/Sorting_algorithms/Permutation_sort
Other Links to VBA for Smarties:
alfred-excel-vachris 