Excel VBA to Update ThisWorkbook Code Module

At a previous job I designed an Excel template that was used to create program estimate to complete/estimate at completion. The template would be configured based on the program structure. VBAs were used to help configure the template, retrieve the latest financial data from a SharePoint document library, and check the workbook for errors. Because the VBAs were being updated over time I created a VBA in the ThisWorkbook module that updated the VBA source code, which was also stored on SharePoint.

This has worked well for many years up to the point where the SharePoint site was updated and moved off site. This isn’t a big issue. The file access now requires a web address and the module VBAs can be updated to point to the new addresses. But the VBA in ThisWorkbook module also needs updating since the VBA source files have moved with the financial data. Updating the template is simple, but there are many, many already configured program Excel files need updating. This requires updating the VBA code in the ThisWorkbook module.

To solve this problem I created a Excel VBA tool that uses a file dialog to select one or more files to update the macros in the ThisWorkbook module. This update is different than deleting modules and importing new VBA source code modules. This actually required deleting all the source lines in the ThisWorkbook module and adding the new file.

The code is straight forward. For each workbook assigned to the wb object, delete all the lines inthe code module and add from a file the new source code. I found several examples in my Internet search. I had issues with some of the sample code for DeleteLines that used StartLine and Count parameters as well as using parenthesizes on the method. It caused a compile syntax error requiring an “=” assignment. In the end I just used DeleteLines 1, CountOIfLines, which worked. A sample code snippet is shown below.

With wb.VBProject.VBComponents("ThisWorkbook").CodeModule
    .DeleteLines 1, .CountOfLines
    .AddFromFile "filename"
End With

Overall very simple approach to update the VBA code in the “ThisWorkbook” module using an external Excel VBA.

Bin Packing Problem

I saw an interesting project on Guru.com to look at pallet packing efficiency. The client input had job numbers/quantities and they wanted an optimized pallet loading. The loading was based on a minimum/maximum pallet quantities with a criteria that a job quantity can’t be split across pallets (a job must ship together). The client also had a “desired” quantity, which really didn’t provide additional information. The goal was to develop an Excel VBA to provide the optimized pallet loading for what I assumed to be their daily output.

Although I haven’t worked on bin packing algorithms, this sounded a lot like memory allocation schemes, which I have implemented; first, best, and worst fit algorithms. I performed some basic research and found that bin packing solutions are similar to memory management allocation schemes. The bin packing algorithms include next-fit, first-fit, and best-fit algorithms. There are also variations of these algorithms that pre-sorts the data in decreasing quantities sizes before apply the algorithm.

The next-fit algorithm checks to see if the the current bin can hold the quantity. If so, then place that quantity in that bin, else place the quantity in a new bin. With the next-fit algorithm you never go back to an earlier bin.

The first-fit algorithm scans open bins in order and places the quantity in the first bin that will hold it. If the quantity doesn’t fit in any bin, then start a new bin. The best-fit scans all bins for the best fit, if it doesn’t fit in an existing bin, then start a new one. The decreasing algorithm versions have the quantities sorted by size, largest to smallest, and then the algorithm is run.

Although not seen in the literature, I also included a worst fit algorithm which is the opposite of the best-fit algorithm where bins are scanned for the worst fit (most space left after adding the quantity to the bin). If the quantity doesn’t fit in an existing bin, start a new one.

I created an Excel VBA to run some trials where I could vary different parameters and create constrained random values. Random parameters includes the number of jobs and quantities per job. Fixed parameters are the min/max quantity on a pallet (in a bin) and the number of trials. After each trial the most efficient solution was selected (least number of pallets/bins) that met the minimum pallet quantity (i.e. a solution with the minimum number of pallets wasn’t selected if any pallet quantity was below the minimum threshold).

After varying a number of parameters and using uniform random numbers, generally the best fit or best fit decreasing algorithm was the most favorable. This experiment did not account for processor speed. One would expect that best and worst fit algorithms to require the most processing power since every open bin needs to be examined prior to placing a job in a bin.

Excel Selenium Element Test

During web scraping sometimes an expected element is not present. This recently happened during a web scraping project when the client input data was not correct causing the page to load with an error and didn’t have the expected data to scrape. The web scraping was being done in Excel using Selenium and Chrome browser. The error handling can be done a couple of different ways. I used the approach of assigning a webelement object to the expected element and then test if the object is Nothing.

Element Test Code

' class that has distance in miles
Set miClass = driver.FindElementByClass("mi", timeout:=0, Raise:=False)
If Not miClass Is Nothing Then
    {Element Present}
    { Element not Present, Error Handling}

This project required loading almost 20,000 web pages. Sometimes the web page url failed to load. Although not directly tied to testing for the present of an element this error case still needed to be handled. For this case Excel VBA error handing is used. Multiple attempts are made to load the url. If the web page loads then the “attempt loop” is exited early. If the maximum number of attempts is reached, then error handling is used.

In an Excel VBA, On Error is used to control the error handing. Before making the web page loading attempt On Error GoTo and Exit For are used to manage the multiple attempts.

Web Page Loading Error handling

' attempt to get website
On Error GoTo tryAgain    ' if error GoTo tryAgain
For try = 1 To Attempts   ' number of attempts to try (const)
    ' get website
    driver.Get url        ' attempt to load web page, if error GoTo tryAgain
    Exit For              ' web page loaded exit For
Next try                  ' next attempt
On Error GoTo 0           ' reset Error handling to stop on error
If (try <> (Attempts + 1)) Then   ' Test if maximum attempts were reached
    {Page Loaded, Do Stuff}
    {Page Didn't Load, Error Handling Stuff}

Excel Selenium Web Scraping Error 33

I’ve done Chrome web scraping in both Python and Excel using Selenium on a Windows 10 platform. I have also web scraped in Python using BeatifulSoup4. A new client had a requirement to find the distance between two zip codes. The client had created an Excel worksheet with a matrix of zip codes (vertical) and cities (horizontal). I found a web site that calculates the distance between two zip codes in a web page. The zip codes are part of the web page URL I converted the cities to city airport zip codes so I just needed to read rows and columns to create the URL and the scrape the results. Easy right?

Error 33

I implemented the VBA code and got a runtime error 33 when opening the Chrome browser. I checked other Excel scrapers that I had written and they all failed. So something was wrong with my Excel environment. An Internet search indicated that the version of Chrome and the chromedriver were not the same. I remembered that I had created a directory for Selenium webdrivers and added it to the system path. I updated the driver to match the Chrome version and I still got the same runtime error. I then tried one of my Python scrapers and they worked! So my problem was with Excel. I continued my search and couldn’t find a resolution until….

Excel Selenium Environment

To setup the Excel environment you need to add SeleniumBasic library, which I already had and the version hadn’t changed since 2016. To add the library to your VBA you need to select the Selenium Type Libary (editor Tools > References…). As I re-read the installation instructions I found that the driver needs to be placed in the same directory as the SeleniumBasic library, which for me was not the webdriver directory I had already updated. That was the difference. Excel did not use the system path for the webdriver.

Once I updated the chromdriver in the SeleniumBasic directory very thing started working again. Watch out for Chrome updates. For my environment I need to update the driver in two locations – the system path directory (Python) and the SeleniumBasic directory (Excel). A good description on setting up Excel to work with Selenium can be found at myOnlineTrainingHub.