News

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.

Nextion Touchscreen with ESP32

Nextion has a nice series of touch displays that make a very good human machine interface (HMI) solution for embedded products. The interface combines an onboard processor and memory with a touch display. Nextion has developed a software editor to support HMI GUI development.

The Nextion HMI editor has drag and drop components to create your user interface. The display is connected via TTL serial (3V3) RX/TX and requires a 5V/GND power connection with enough current to run the display.

The device I used was part of the Basic Series, NX3224T024, which is a 2.4″, 320×240, resistive touch panel. The document is not clear on which wires are the TX and RX. I found the Nextion TX is the yellow wire and the RX the blue wire. Also not well documented are signal levels, which are 3V3.

For my application I needed a method for a user to configure the embedded system. Instead of using a serial port I selected the Nextion touchscreen. The application was hosted on an ESP32 and code was developed using the Arduino IDE. I found the itead library, which implemented all of the objects I needed in version v0.9.0. I had to modify some of the files in the library to work properly with the ESP32. In NexUpload.cpp I moved the include software serial #include to be within the define block USE_SOFTWARE_SERIAL (makes sense). I also had to modify the NexHardware.cpp NexInit function to assigned the hardware serial pins I used (had to move Serial1 pins due to conflict with flash control).

// NexUpload.cpp
//#define USE_SOFTWARE_SERIAL
#ifdef USE_SOFTWARE_SERIAL
#include <SoftwareSerial.h>
SoftwareSerial dbSerial(3, 2); /* RX:D3, TX:D2 */
#define DEBUG_SERIAL_ENABLE
#endif
// NexHardware.cpp
bool nexInit(void)
{
    bool ret1 = false;
    bool ret2 = false;
    
    dbSerialBegin(9600);
    nexSerial.begin(9600,SERIAL_8N1, 32, 33);	// modified for serial1 I/O
    sendCommand("");
    sendCommand("bkcmd=1");
    ret1 = recvRetCommandFinished();
    sendCommand("page 0");
    ret2 = recvRetCommandFinished();
    return ret1 && ret2;
}

To build the ESP32 Nextion interface there are basically 5 steps after you create the HMI using the Nextion editor.

  • Define objects
  • Create an object listen list
  • Create object callbacks (what to do on touch screen events)
  • Attach callbacks to objects
  • Use nexLoop to monitor the Nextion device

Defining the object requires information from the Nextion HMI editor. To define an object you need the object name, page, and id number. Watch out when editing Nextion pages, ids can change. The listen list is a NexTouch type array of pointers to the objects you created that have the events you are interested in. The callbacks are actions to take based on Nextion events (button push, release, etc.). Attaching callbacks associates the callback routine with an object and event. The object method attachPop() is used when attaching a callback for a button release.

So putting it all together. For a MIDI project I had multiple parameters that could be modified, e.g. MIDI channel number. The parameters were all numerical values so I create a change page that allowed a user to increase or decrease the value by pushing a plus or minus button. Once finished, the value was updated.

Nextion Parameter Change Page

The parameter page had five objects, a title (text), value (numeric), and buttons for plus, minus, and done. The title was static and loaded with the page. The parameter value was also loaded showing the current parameter value. All objects except the title and value were setup for “Touch Release Event” to “Send Component ID”, which means when the button is pushed and then released, a serial string is transmitted with the Nextion object page, id, and name. The protocol is handled by the itead Nextion library.

Defining the objects for the parameter change page used the itead library definitions. These objects were on my Nextion page 6. The library parameters are <Object>(<page>,<id>,<name>).

// Define Nextion Objects
// page 6
NexButton minusChange         = NexButton(6,4, "b1");
NexButton plusChange          = NexButton(6,5, "b2");
NexNumber parameterValue      = NexNumber(6,2, "n0");
NexButton setParameter        = NexButton(6,3, "b0");
NexText parameterName         = NexText(6,1, "t0");

Next these objects were added to the listen list. The reduced version showing the page 6 objects is shown below. Remember to put a NULL at the end of the list. Note that I didn’t setup events for the parameter name or value. Only the plus and minus button that changed the parameter and a done button to set the new value were setup for events.

// Listen List
// object list for touch screen
NexTouch *nex_listen_list[] = {
  // page 6
  &minusChange,
  &plusChange,
  &setParameter,
  NULL
};

The call backs are the actions to perform when the event occurs for that object. For the plus and minus the parameter value is read from the Nextion object, checked against a max/min value, and updated accordingly. The Done button updates the parameter value to the current page value and returns to the calling page. Global variables were used for the min, max, and return information.

// Callbacks
// page 6
void minusPopCallback(void *ptr) {
  uint32_t number;

  parameterValue.getValue(&number);
  if (number > minParameterValue)
    parameterValue.setValue(number - 1);
}

void plusPopCallback(void *ptr) {
  uint32_t number;
  
  parameterValue.getValue(&number);
  if (number < maxParameterValue)
    parameterValue.setValue(number + 1);
}

void setParameterPopCallback(void *prt) {
  uint32_t getValue;

  // update variable that is changing
  parameterValue.getValue(&getValue);
  *modifyParameter = (int)getValue;
  // go back to last page
  returnPage->show();

  // more stuff based on returning page
}

Attaching the callbacks for the release event uses the attachPop() object method. The parameters are the callback function and object. If I had setup the touch event for a push, then the method is attachPush().

// Attach Callbacks
void setupNextion (void) {
  // page 6
  minusChange.attachPop(minusPopCallback, &minusChange);
  plusChange.attachPop(plusPopCallback, &plusChange);
  setParameter.attachPop(setParameterPopCallback, &setParameter);

}

Finally, once everything is setup and initialized, use nexLoop with the listen list to handle the interface to the Nextion touch screen. The nexLoop polls the serial interface and is non-blocking, so my listener was setup as a task.

// Monitor Nextion Interface
// task
void getControl(void * parameter) {
  for(;;) {
    nexLoop(nex_listen_list);
    vTaskDelay(50 / portTICK_PERIOD_MS);
  }
}

Overall the interface was very successful and I was happy with the results. The project had 7 different pages that required almost 900 lines of code. I’m sure there are more efficient ways of coding this interface, which I will discover as I continue to work with Nextion touchscreens.

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.

DAQFactory Introduction

Recently I had an assignment to interface and display sensor data on an old Windows XP laptop. The client was reusing some existing infrastructure, sensors and serial communication radios, to display real-time sensor information at a remote office location. The original effort was to interface Excel serially to existing sensors. The client had already selected a 4-20mA DAT3015-I sensor interface that used Modbus protocol.

I hadn’t planned on the Modbus protocol. Although I have worked with data communications with sensors for more than 35 years, I had never worked with Modbus. Modbus is an application layer messaging protocol that provides client/server communication over different types of busses and networks. Modbus has been a serial de facto standard since 1979 and has a request/replay PDU structure.

Although there are Excel VBA code to support Modbus, I assumed that if the standard has been around since 1979 with companies are still building Modbus protocol hardware, there must be commercially available software for Windows XP that would work for this application.

I performed an industry survey and found about 10 software packages that met our requirements. I selected DAQFactory Express since it has custom screens that contain multiple real-time indicators and graphs, it appeared to be a robust environment with flexible compatibilities including custom coding, and the Express version is free. The free version limits the number of pages (2), I/O (8 channels), and screen components (11).

The display used a single screen and had indictors for

  • 2D trending graph showing real-time sensor data
  • Instantaneous sensor values in mA and GPM (converted data)
  • Signal status using colors and blink text when an error was detected
  • Real-time operating controls (stop/continue), reset, test mode and mode indicators
  • Error feedback
  • Display control (graph data and error thresholds)
DAQFactory Finished Product

DAQFactory has multiple setup screens for device setup (physical interface and protocol), channel setup, conversion formulas, and others. DAQFactory provides a lot of flexibility in setting up your device interface and how data are handled within the environment. DAQFactory is not limited to Modbus protocol and supports even custom interfaces.

I chose to use DAQFactory sequences, which are code segments similar to C in syntax and is object oriented. For this application the primary sequence is GetData that is used to get sensor current readings, the device name, and reset coil status (a Modbus thing). All other sequences work with runtime operation such as stop/continue and entering/exiting test mode.

Sequences have a nice try/catch structure to handle events like data timeouts. With the Modbus protocol and server requests data from the client. Sometimes the message is missed and no data are received from the client. The try/catch is perfect to handle this type of error. To read data from the DAT3015-I, a simple method was used that specified the data type, address, and number of values.

inputData = Device.DAT3015.ReadHoldingS16(1,40015,2)  // read registers (15 & 16)

This call created the properly formatted Modbus string that was sent to the client and received the response and placed the 16-bit signed data into an 2-D array, inputData. To get the instantaneous values from the most recent data a simple array access was used.

Outfall_uA = inputData[0][0]     // outfall uA value reg 40015
Overflow_uA = inputData[0][1]    // overflow uA value reg 40016 

The most recent data are then converted and added to the real-time graphical display using the AddValue method.

 // convert data algorithm, data in uA
 // if < 4mA GPM = 0
 // GPM = uA * 1.25 - 5000
       
 // Outfall sensor
 if(Outfall_uA < 4000)
    OF01_Outfall.AddValue(0)
 else
    OF01_Outfall.AddValue(Outfall_uA * 1.25 - 5000)
 endif
  
 // Overflow sensor
 if(Overflow_uA < 4000)
    OF01_Overflow.AddValue(0)
 else
    OF01_Overflow.AddValue(Overflow_uA * 1.25 - 5000)
 endif 

A 2D trending graph was used for each sensor and displays GPM verses time. The graphs auto scales in the y-axis (GPM) utilizing the Min/Max expressions on the channel data (e.g. Min(OF01_Outfall)). The time scale is user adjustable using the runtime display control at the button of the display. This setting uses a registry variable and is retained between DAQFactory restarts.

Instantaneous values for mA and GPM are displayed for each sensor using Variable Value Components. The value is set using an expression to the most recent reading and conversion (e.g for mA Overflow_uA/1000, for GPM OF01_Overflow[0]). The [0] index is the most recent value. No color changes or actions are associated with these objects.

Each sensor has a prominent display using a Descriptive Text Component (OutfallStatus, OverflowStatus). An inline if expression on the sensor uA variable is used to generate either a 1 (good) or 0 (problem).

iif(abs(Outfall_uA) < Registry.signalThreshold,0,1)

Overall the project was a success. The develop time was short and help by the client sending the DAT3015-I for integration test prior to installation. The client was able to install and add the DAQFactory project to the Windows XP laptop. Once the client found a problem with an interface cable, the system ran without an issues. I highly recommend DAQFactory.

Arduino SD Card Reader

I recently had a project that required having multiple files that were used to control an animatronics display. Each file was a scene for the display and the code simply would play 1 scene, pause, and then play the next scene.

I used the Adafruit MicroSD card breakout board. This assembly is inexpensive, works with in either 3V or 5V systems, and has an SPI interface. A standard Arduino SD library was used for access. I used the MicroSD card with both a Nano and ESP32.

The SD library supports FAT16/FAT32 file structures. So preparing the microSD card with the file used a Windows 10 computer with a SD reader. The card was formatted and the files were copied.

Accessing the files were simplified using the Arduino SD library. Using root (declared as File) that was pointed to the root directory (“/”) the openNextFile() method was used to get the next filename to use. When we were at the end of the directory, a rewindDirectory() was performed. The filename was then checked for the correct extension.

showFile = root.openNextFile();   // get next file
if (!showFile) {                  // end of directory start over
  root.rewindDirectory();         // beginning
  showFile = root.openNextFile(); // file
}
filename = showFile.name();       // check filename ends in ".BIN"
filename.trim();                  // remove whitespace
filename.toUpperCase();           // all upper case
if(filename.endsWith(".BIN")) {   // check for bin file
  break;    // found good file  
}
else {
  showFile.close();               // close unneeded file
}

Bytes were read from the file using the read() method. It should be noted that the documentation states that EOF is returned at the end of the file. Actually the number of bytes read is returned. When the value is zero (0), then the end of file was encountered.

I used the microSD card with both a Nano (5V) and ESP32 (3V3) and worked without any issues on both systems. For my application I had to read a small buffer (3 bytes) at a 30 frames per second rate (33.3 milli-seconds).

Finding Unknown Resistor Value using Voltage

In the world of sensing, there are many sensors that change their resistance value based on the environment. Knowing the sensor resistance provides a measurement of the environment being measured. These variable resistor sensors include:

  • Thermistor – a variable resistor that changes value with the surrounding temperature changes. There are two types: negative temperature coefficient (NTC) and positive temperature coefficient (PTC). The NTC thermistor decreases in value when the temperature increases the the PTC thermistor increases in value when the temperature increases.
  • Magneto Resistor – a variable resistor that changes value when a magnetic field is applied. When the magnetic fields increases, the resistance increased. When the magnetic field decreases, the resistance decreases.
  • Photoresistor – a variable resistor that changes value based on light energy. The photoresistor resistance decreases when light energy is increased and increases when light energy is decreased.
  • Humistor – a variable resistor that changes value based on humidity.
  • Force Sensitive Resistor – a variable resistor that changes values based on the force that is applied.

Thermistors are variable resistors that are more sensitive to temperature changes then a standard circuit resistor. The simple first order thermistor relationship between resistance and temperature is:

ΔR = kΔT, where
ΔR is change in resistance (in ohms)
Δ is change in temperature (in kelvin)
k is first-order temperature coefficient (in ohms/kelvin)

In general the first order approximation is only accurate over a limited temperature range. The Steinhart-Hart equation is a widely used third order approximation that improves accuracy to less than 0.02 oC over a much wider temperature range.

where
T is absolute temperature (in kelvins)
R is the resistance (in ohms)
a, b, and c are coefficients

NTC thermistors can also be characterized with the Β (beta) parameter equation, which is just a specialized case of the Steinhart-Hart equation.

where
T is absolute temperature in kelvins
T0 is 298.15 K (25 oC)
R0 is resistance at T0
R is the resistance

Having the B parameter and measuring the thermistor resistance, the temperature can be determined. But most embedded systems don’t measure the resistance directly. So the question is how do we measure the thermistor resistance. The answer is to use a voltage divider. Measuring the voltage divider voltage, which is common using analog to digital converters, gives us a way to get the thermistor resistance.

Remember that a voltage divider is two series resistors in this case connecting power and ground. The voltage between the two resistors is given by:

NTC Thermistor Voltage Divider

VOUT = VIN x (R1 / (R1 + R2))
R2 = R1 x (VIN/VOUT – 1), where
R2 = unknown thermistor resistance (in ohms)
R1 = known resistance (in ohms)
VIN = known input voltage (in V)
VOUT = measured voltage between resistors (in V)

Generally NTC thermistors have a nominal resistance at 25oC. Most common is either 10K or 100K ohms. When picking the known resistor R1, the value should match the nominal thermistor resistance, e.g. for a 100K thermistor, R1 should be 100K.

Using an embedded controller like an Arduino UNO or Nano, the code is very simple to convert the sensed input voltage to the thermistor resistance, to a temperature as shown in following code segment.

  // in setup, one time calculation
  Rinf = NTCRESISTOR * exp(-1*BETA/298.15);


  // in Arduino loop
  tempIn = analogRead(TEMPPIN);  // 0 to 1023 values

  // find thermistor R
  // SERIESRESISTOR = R1, 1023.0 = VIN, tempIn = VOUT
  Rth = SERIESRESISTOR * ((1023.0/tempIn) - 1);
  // calculate temp in K and convert to C
  tempC = BETA/(log(Rth/Rinf)) - 273.15;

One final item to consider with a voltage divider is the input impedance of the measuring device. To limit the impact of input impedance on circuit performance, generally you want the input impedance to be at least 10 times the value of R1 in the circuit above. The input impedance is in parallel with R1 so if the input impedance is only 100K, then the effective value of R1 in our circuit is only 50K, which greatly affects the measurement and calculations.

Op Amp Voltage Follower

One way to solve this problem is to use a voltage follower op amp circuit. This circuit provides unity gain (voltage divider Vout equal op amp Vout), has a low output impedance, and very large input impedance. It is important to select an op amp that has stable unity gain.

MOL-AU5016 MP3/WAV Player

I recently had a project where a client wanted to interface an Arduino Nano and ESP32S with MDFLY Electronics AU5016 Embedded SD/SDHC Card MP3/WAV Player Module. The player is controllable using either a digital or parallel interface. It’s features from the datasheet are:

AU5016 Assembly without Headers
  • High performance 32Bit CPU
  • High quality on-chip stereo DAC
  • Decodes MP3/WAV/APE audio format
  • Supports bitrate from 32Kbps to 320Kbps
  • Supports MicroSD/HC memory card up to 32GB
  • Low-power operation
  • Ultra-low background noise
  • TTL serial interface
  • Input voltage: 5VDC
  • Compact design

Some of the limitations of the AU5016 include a maximum of 200 tracks (files) stored/used on the self contained SD card, some of the digital interface commands are required to be repeated until the proper response is received, 5VDC only, and the datasheet is missing some key electrical properties (e.g. Busy IOH). The good stuff, standalone audio playback, works with multiple file types, high performance, and very compact.

I used the digital interface with the Arduino Nano and ESP32S. The digital interface uses start/stop protocol at a default 9600 baud 8N1. The signal levels are TTL (5V only). There is a discrete Busy signal when audio is playing. All of the commands are a single byte.

For this application the AU5016 contained the audio files on an SD card while the Nano/ESP32S SD card contained control information. The control and audio are time synchronized using a third party application. When the control starts the audio is also started. What clock drift there is between the processor and AU5016 is not noticeable in this application’s 2 minute window.

For the Arduino code interface I created an C++ object. This simplified using the AU5016 as typical in the OOP environment. Differences between the Nano and ESP32S was the object used SoftwareSerial on the Nano while the ESP32S used the HardwareSerial. Thankfully these objects have similar interfaces so the code changes were minimal. The object I provided implemented most of the AU5016 commands.

AU5016 Object Definition

// AU5016 object class
class AU5016 {
  public:
    AU5016(int uartNum, int RXPin, int TXPin, int BusyPin); // AU5016 object
    int StartTrack(int track); // starts playing track, error return
    void Stop(void);           // stops tracking playing
    int SetVolume(int level);  // sets volume level, returns volume level
    int VolumeUp(void);        // increases volume level + 1, returns volume level
    int VolumeDown(void);      // decreases volume level -1, returns volume level
    void Mute(void);           // mutes output
    int PlayPause(void);       // pasues play track, un-pauses track, error return
    int SetEQ(int EQ);         // sets EQ, returns error
    int RepeatMode(int Mode);  // sets repeat mode, return error
    int TrackAfterPower(int Mode);  // sets track to play after power on, return error
    int SetBusyStatus(int Mode);    // sets busy output active high or low, return error

  private:
    HardwareSerial *_AudioSerial;   // UART interface to AU5016
    SemaphoreHandle_t  _sema_v;     // multi task access control
    int _Busy;                      // busy pin
    int _audioSendCommand(int cmd); // send a command, return response
    int _audioGetResponse(void);    // get command response, return response
};

Hardware interfacing to the AU5016 for the Nano was simple since both devices are 5V. For the ESP32S, although in input voltage is 5V, the processor and it’s IO is actually 3.3V. I used level TXS0108E translator between the ESP32 and AU5016. Luckily the SD card used with both processors works at either 5V or 3.3V.

Issues

I only encountered one real issue working with the AU5016. I originally use a 32GB SDHC formatting FAT32 for the audio. I couldn’t get the AU5016 to recognize the card. The response was 0xAB, No Memory card. Working with MDFLY tech support they recommended working with a smaller SD card. So I tried a 2GB SD FAT16 and 8GB SDHC and both appeared to work.

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}
Else
    { 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
tryAgain:
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}
Else
    {Page Didn't Load, Error Handling Stuff}

Calculating Moon Phase

Recently I published a Fitbit app that calculates the Moon phase and then displays the proper phase image and illumination value. The user can select a different date or simply increment the date using buttons. Some feedback I received was that the app was fast and they wanted to understand why?

First, the app performs all calculations on the watch. No phone resources are need No GPS. No Internet. The calculations are based on a dateNow date object (this object is settable by the user though the interface that is not part of this discussion).

The calculation is based on an article I found by SubsySTEMs while performing an Internet search. Their paper broke the Moon phase calculation into a series of steps, which was easy to follow and translated directly to JavaScript. The output of their calculation is the number of days since the last New Moon. From there the Moon phase is determined. There are eight Moon phases (in order from New):

  • New
  • Waning Crescent
  • Third Quarter
  • Waning Gibbous
  • Full
  • Waxing Gibbous
  • First Quarter
  • Waxing Crescent

Using the days since the last New Moon you can find the phase but beware, each phase isn’t just 1/8 of 29.53 days. Shown below is the JavaScript that implements the attached paper.

function calMoonPhase() {
  // calculate moon phase for date
  let month = dateNow.getMonth() + 1;
  let year = dateNow.getFullYear();
  if(month < 3) {
    year = year - 1;
    month = month + 12;
  }

  let a = Math.floor(year/100);
  let b = Math.floor(a/4);
  let c = 2 - a + b;
  let d = dateNow.getDate();
  let e = Math.floor(365.25*(year+4716));
  let f = Math.floor(30.6001 * (month + 1));
  let jd = c + d + e + f - 1524.5;
  let daysSinceNew = jd - 2451549.5;
  let newMoons = daysSinceNew / 29.53;

  <...Determine Moon phase...>
  <...Determine image to show...>
  <...Calculate illumination...>


}

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.