Here is the complete report

List of Direct URLs

List of Search String Counts

Skip to content
# Tag: Excel VBA Automation

## Topic Search Excel VBA Automation with Outlook

## Topic Search Excel VBA Automation with Word

## Topic Search VBA and VBScript

## TSRP: Explore List Objects Excel and VBA

## Identify Special Characters that can be used to generate valid Excel Range Names

## Suggestions for personalizing the Excel VBA Editor

## Sorting in VBA using VBA-ADO

## A User Controlled Demonstration of the Aitken-Lagrange Construction for Points on a Cubic.

## A User Controlled Demonstration of the Aitken-Lagrange Construction for Points on a Parabola.

## Using Excel Range Names to parse a Matrix into a List

Here is the complete report

List of Direct URLs

List of Search String Counts

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:

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

TRSP Input Workbook

TRSP Output Workbook

TRSP Search String Counts PDF

TRSP Direct URLs List PDF

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.

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.

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:**

Aitken Lagrange Cubic Construction

A few weeks ago Peter Bartholomew (uk.linkedin.com/in/peterbartholomew ) visited my blog. He made a very valuable suggestion – put up a demonstration for the construction of points on a cubic using the Aitken- Lagrange construction.

He sent me a sample workbook that demonstrated the point wise construction of a Bezier Cubic.

I have now re-engineered his workbook and have generated a user controlled demonstration for the Aitken-Lagrange construction of points on a cubic.

This workbook also demonstrates the technique of incorporating User Define Functions in Named Ranges. I believe that Bob Umlas (www.linkedin.com/pub/bob-umlas/46/278/479 ) was the first to discover this technique.

The User Defined Functions employed by this demonstration are:

Name Definition

X_Values =OFFSET(Aitken_Lagrange!$D1,0,0)

y_12_FLINE =(Y_P1*(X_P2-X_Values)+Y_P2*(X_Values-X_P1))/(X_P2-X_P1)

Y_23_FLINE =(Y_P2*(X_P3-X_Values)+Y_P3*(X_Values-X_P2))/(X_P3-X_P2)

Y_34_FLINE =(Y_P3*(X_P4-X_Values)+Y_P4*(X_Values-X_P3))/(X_P4-X_P3)

Y_123_FLINE =(y_12_FLINE*(X_P3-X_Values)+Y_23_FLINE*(X_Values-X_P1))/(X_P3-X_P1)

Y_234_FLINE =(Y_23_FLINE*(X_P4-X_Values)+Y_34_FLINE*(X_Values-X_P2))/(X_P4-X_P2)

Y_1234_FLINE =(Y_123_FLINE*(X_P4-X_Values)+Y_234_FLINE*(X_Values-X_P1))/(X_P4-X_P1)

Note well: The definition for the X_Values coming from Column D on Aitken_Lagrange Sheet enables the calculation of both the XStar lookup at the top of the sheet as well as the table of values at the bottom of the sheet.

The construction first evaluates Y_12_FLINE, Y_23_FLINE and Y_34_FLINE at XStar.

These results are the used to evaluate Y_123_FLINE and Y_234_FLINE at XStar.

And finally Y_1234_FLINE at XStar.

These steps can also be presented using the Functional Form for FLINE:

Function FLINE(ByRef XL As Double, _

ByRef YL As Double, _

ByRef XR As Double, _

ByRef YR As Double, _

ByRef XSTAR As Double) As Double

‘======================================

If Abs(XR – XL) > 0 Then

FLINE = (YL * (XR – XSTAR) + YR * (XSTAR – XL)) / (XR – XL)

Else

FLINE = 0.5 * (YL + YR)

End If

End Function

y_12_FLINE =FLINE(X_P1,Y_P1,X_P2,Y_P2,XStar)

Y_23_FLINE =FLINE(X_P2,Y_P2,X_P3,Y_P3,XStar)

Y_34_FLINE =FLINE(X_P3,Y_P3,X_P4,Y_P4,XStar)

Y_123_FLINE =FLINE(X_P1,Y_12_FLINE,X_P3,Y_23_FLINE,XStar)

Y_234_FLINE =FLINE(X_P2,Y_23_FLINE,X_P4,Y_34_FLINE,XStar)

Y_1234_FLINE =FLINE(X_P1,Y_123_FLINE,X_P4,Y_234_FLINE,XStar)

Aitken Lagrange Parabola Construction

A few weeks ago Peter Bartholomew (uk.linkedin.com/in/peterbartholomew )visited my blog. He made a very valuable suggestion – put up a demonstration for the construction of points on a parabola using the Aitken- Lagrange construction.

He sent me a sample workbook that demonstrated the point wise construction of a Bezier Cubic.

I have now re-engineered his workbook and have generated a user controlled demonstration for the Aitken-Lagrange construction of points on a parabola.

This workbook also demonstrates the technique of incorporating User Define Functions in Named Ranges. I believe that Bob Umlas (www.linkedin.com/pub/bob-umlas/46/278/479 )was the first to discover this technique.

The User Defined Functions employed by this demonstration are:

Name Definition

X_Values =OFFSET(Aitken_Lagrange!$D1,0,0)

y_12_FLINE =(Y_P1*(X_P2-X_Values)+Y_P2*(X_Values-X_P1))/(X_P2-X_P1)

Y_23_FLINE =(Y_P2*(X_P3-X_Values)+Y_P3*(X_Values-X_P2))/(X_P3-X_P2)

Y_123_FLINE =(y_12_FLINE*(X_P3-X_Values)+Y_23_FLINE*(X_Values-X_P1))/(X_P3-X_P1)

Note well: The definition for the X_Values coming from Column D on Aitken_Lagrange Sheet enables the calculation of both the XStar lookup at the top of the sheet as well as the table of values at the bottom of the sheet.

A set of Excel/VBA procedures that can be used to parse a Matrix into a List.Using Excel Range Names to Parse a Matrix