Categories
Web Scavenging xlGenie

TSRP: Explore List Objects Excel and VBA

TRSP Input Workbook

TRSP Output Workbook

TRSP Search String Counts PDF

TRSP Direct URLs List PDF

Categories
Uncategorized

TSRP -Topic Search Reporting Processor

Motivation

When executing a Google search, one is presented with a statistic that reports the number of results that were found and the time it took to find them. For example:

This statistic is frustrating because it shows that there is a whole universe of results out there but only the smallest fraction of results is presented for examination.

TSRP Topic Search Report Processor

This frustration has motivated me to develop an Excel-based process that attempts to expand the collection of search results.

The process is built on two main ideas:

Formulate a “Composite Topic Search”

Make use of the Google Search Engine for both “All” and “Image” search choices. This new process supersedes the original GISP reported earlier.

A “Composite Topic Search” is a search that starts with a collection of search phrases that explore various aspect of the topic of interest.  The individual search results are then collected and organized into a composite report.

Making the individual search using both the Google All Search and Google Image Search Engines gives an expanded collection of results.

I have joined these ideas together into what I call the Topic Search Report Processor (TSRP)

How to start TSRP – the Topic Search Workbook

The TSRP begins by preparing an Excel Workbook that contains a collection of search phrases or search strings that capture the essence of the desired Topic. A preliminary set of search strings can be extracted from Google.

For example, consider the following topic: how to exchange worksheet data with VBA arrays

After the search string is entered into the Google search box, Google will sometimes display a collection of searches that complement the original search.

Here is a screenshot of the suggested searches:

Using a screen capture tool to copy this list as an image, one can then take advantage of the OCR functionality that is built into Microsoft One Note 2016

OCR capabilities are also available within the GreenShot freeware utility. ( http://getgreenshot.org/)

TSRP Reports

Two Important Definitions

Before describing the details of the GISP Reports, there are two terms that need to be defined.

A Direct URL is a URL captured by GISP, pointing directly to the web page that was discovered by GISP.

A Primary URL is the leftmost part of a Direct URL with the html prefix HTTPS:// or HTTP:// removed.

The VBA code to extract a Primary URL from a Direct URL is as follows:

Reporting Search String Counts

The Search String Counts report shows the effectiveness of each search string.

Note – the information shown here is from an earlier effort that does not reflect the integration of searching Google “All” and Google “Image”

This Topic Search returned 172 Primary URLs.

The first page of the Search String Counts report is shown here.

All counts greater than or equal to 5 are highlighted.

The three main features of this report are:

  1. It identifies the more effective search strings and it gives a starting point to expand the Topic Search.
  2. By ranking the Primary URLs from the most frequently found to the ones that are not frequently visited or not widely known, it identifies the outstanding resources to study a Topic.
  3. Reviewing the list of Primary URLs and looking for those that are not frequently visited or not widely known, lead to the discovery of new resources.

For example:    

www.bluepecantraining.com

www.guru99.com

www.aakaksatop.club

Since the report is presented as a PDF, each URL reported is a hyperlink.

Clicking on the URL will then open your browser to the web page.

Note that not all the Primary URLs will link back to a web page, it all depends on the setup of the web site.

Reporting Direct URLs

The Direct URLs report show the full URL with the different search strings that returned the URL.

The list of Direct URLs is sorted to follow the Rank of the Primary URLs.

This Topic search returned 399 URLs.

The first few rows of the Direct URLs report are shown here:

The combined titles are most important when reviewing the YOUTUBE URLs.

Both reports were exported from the GISP Composite Topic Search Report Workbook. This workbook can be used to generate other customized reports.

Categories
Uncategorized

GISP Google Image Search Processor

Motivation

When executing a Google search, one is presented with a statistic that reports the number of results that were found and the time it took to find them. For example:

This statistic is frustrating because it shows that there is a whole universe of results out there but only the smallest fraction of results is presented for examination.

GISP Google Image Search Processor

This frustration has motivated me to develop an Excel/VBA process that attempts to expand the collection of search results.

The process is built on two main ideas:

Formulate a “Composite Topic Search”

Make use of the Google Image Search Engine.

A “Composite Topic Search” is a search that starts with a collection of search phrases exploring various aspect of the topic of interest.  The individual search results are collected and organized into a composite report.

Making the individual searches using the Google Image Search Engine returns an interesting collection of results.

I have joined these two ideas together into the Google Image Search Processor (GISP).

How to start GISP – the Topic Search Workbook

GISP begins by preparing an Excel Workbook containing a collection of search phrases that capture the essence of the desired Topic. A preliminary set of search strings can be extracted from Google.

For example, consider the following topic:

how to exchange worksheet data with VBA arrays

After the search string is entered into the Google search box, Google can then display a collection of suggested searches complementing the original search. For example:

Using a screen capture tool to copy this list as an image, one can then take advantage of the OCR functionality that is built into Microsoft One Note 2016 to extract the list as text.

OCR capabilities are also available within the GreenShot freeware utility. (http://getgreenshot.org/)

The GISP Input Workbook for this topic search looks like this:

Note the report title must not duplicate the search string.

Note also the search strings shown here could have used advanced Google search constructions.

Link to the Input Workbook

GISP Reports

Two Important Definitions

Before describing the details of the GISP Reports, there are two terms that need to be defined.

Direct URL is a URL collected by GISP pointing directly to the web page that was discovered by GISP.

Primary URL is the leftmost part of a Direct URL with the HTML prefix HTTPS:// or HTTP:// removed.

The VBA code to extract a Primary URL from a Direct URL is as follows:

Reporting Search String Counts

The Search String Counts report shows the effectiveness of each search string.

Link to Search String Counts Report

This Topic Search returned 172 Primary URLs.

The first page of the Search String Counts report is shown here.

All counts greater than or equal to 5 are highlighted.

The three main features of this report are:

  1. It identifies the more effective search strings and it gives a starting point to expand the Topic Search by selecting more search strings.
  2. By sorting the Primary URLs from the most frequently found to the ones that are not frequently visited or not widely known, it identifies the outstanding resources to study a Topic.
  3. Reviewing the list of Primary URLs and looking for those that are not frequently visited or not widely known, lead to the discovery of new resources.

For example some new resources:

www.bluepecantraining.com

www.guru99.com

www.aakaksatop.club

Since the report is presented as a PDF, each URL reported is a hyperlink.

Clicking on the URL will then open your browser to the web page.

Note that not all the Primary URLs will link back to a web page, it depends on the setup of the web site.

Reporting Direct URLs

The Direct URLs report show the full URL with the different search strings that returned the URL.

Link to the Direct URLs Report

The list of Direct URLs is sorted to follow the list of the Primary URLs.

This Topic search returned 399 URLs.

The first few rows of the Direct URLs report are shown here:

Each Direct is reported along with the Search Strings associated with the URL as well as the title of the web page.

Note the web pages titles are most important when reviewing the YOUTUBE URLs.

Both reports were exported from the GISP Composite Topic Search Report Workbook. This workbook can be used to generate other customized reports.

Link to the Composite Topic Search Report Workbook

Categories
Uncategorized

GISP Report Best Practices Excel and VBA

Look for my POST the introduces GISP – Google Image Search Processor

Categories
xlGenie

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.
Workbook Identify special symbols that can be used to create Valid Excel Range Names
There is also a link to a PDF report as well.
PDF Identify special symbols that can be used to create Valid Excel Range Names
Categories
xlGenie

Suggestions for personalizing the Excel VBA Editor

A sample of my view of VBA Source Code.

Code Example

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.

primary excel vba options

To make your code more visible select the Lucida Console (Western) FONT.

Select Lucida Console 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.

Keywords in RED

To keep track of syntax errors display them in MAGENTA.

Syntax Errors in Magenta
Categories
SmartWare xlGenie

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

Categories
xlGenie

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

http://www.snb-vba.eu/

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://stackoverflow.com/questions/1517793/what-is-stability-in-sorting-algorithms-and-why-is-it-important

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:

http://www.snb-vba.eu/VBA_Dictionary_en.html

http://www.snb-vba.eu/VBA_Collection_en.html

Categories
Dot to Dot Calculus

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

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)

Categories
Dot to Dot Calculus

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

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.