News

VBA Mac File Dialog Selecting Multiple Files

Previously I have written about selecting a file from a Microsoft VBA using a file dialog window on a Mac. This approach used an Applescript and selected a single file. This approach was limited to selecting a single file located on the local hard drive. Recently I had the need to updated this approach to allow selecting multiple files that are located on any drive/cloud.

The ability to select multiple files in the dialog window required a small change to the Applescript. The text “multiple selections allowed false” changed “false” to “true.” This allows selecting multiple files that the Applescript returns.

mypath = MacScript("return (path to documents folder) as String")
    
sMacScript = "set applescript's text item delimiters to "","" " & vbNewLine & _
"try " & vbNewLine & _
"set theFiles to (choose file " & _
"with prompt ""Please select a file or files"" default location alias """ & _
mypath & """ multiple selections allowed true) as string" & vbNewLine & _
"set applescript's text item delimiters to """" " & vbNewLine & _
"on error errStr number errorNumber" & vbNewLine & _
"return errorNumber " & vbNewLine & _
"end try " & vbNewLine & _
"return theFiles"

inFiles = MacScript(sMacScript)     ' get filenames

When the MacScript is run it returns a string of full path filenames separated by “,” unless no files are selected then “-128” is returned. The string of filenames are easily split into a array file filenames for VBA use. For the VBA to access each file two changes are required to each filename. First the Mac directory separator “:” is replaced with “/” symbol. The second change is to add a prefix “/Volumes/” to a full path filename. This allows the use of different drives/cloud files. In my previous post I removed “Macintosh HD” for the path name.

filenameSplit = Split(inFiles, ",")
For N = LBound(filenameSplit) To UBound(filenameSplit)
    selectedFile = "/Volumes/" & Replace(filenameSplit(N), ":", "/")

    <... DO WORK ...>

Next

This updated Applescript and code was used in a Word VBA but should work well with Excel on the Mac.

ESP32 deep Sleep (RTC) GPIO Output

In the course of developing an ESP32 battery based application I was stumped along with others, on how to maintain a GPIO output in a known state while in deep sleep. My plan was to hold an external peripheral in a reset state to minimize it’s power. The reset signal was an active low signal and the external peripheral had a 10K pullup resistor on the signal. I program the ESP32 in the Arduino environment so I had to find a solution that supported this IDE. I performed a bunch of searches and finally found a working example by Xabi Alaez.

The ESP32 has several “sleep” modes that are listed below. A great article about ESP32 sleep modes is found on the Last Minute Engineers web page.

  • Active keeps everything running including WiFi, Bluetooth, and processing core at all times. This mode consumes more than 240mA with some power spikes as high as 790mA.
  • Modem Sleep is Active minus the WiFi, Bluetooth, and radio. Dependent on the clock speed, which is programmable, consumes between 3mA (low clock speed) to 20mA (high clock speed).
  • Light Sleep the CPU(s) clock is gated while the RTC and ULP co-processor are active. This sleep mode has full RAM retention. Once entering Light Sleep a wake-up source is needed to resume processing. This power consumption is less than 1mA.
  • Deep Sleep the CPU(s), most RAM, and all digital peripherals are powered off while the ULP co-processor and RTC components remain operational. The power consumption is about 0.15mA with the ULP co-processor running.
  • Hibernation only has one RTC timer running and a few RTC GPIOs needed to wake up the chip from hibernation mode. Power consumption is about 2.5uA.

I was most interested in Deep Sleep and Hibernation modes. It turns out you control the power domains before entering Deep Sleep to create Hibernation mode (no API call for hibernation sleep). This also means you can be selective in which power domains are enabled to create a “custom” sleep mode that has less current consumption than Deep Sleep, but not as good as Hibernation.

The ESP power domains are controlled by calling esp_deep_sleep_pd_config() with the power domain and options that include ESP_PD_OPTION_OFF, ESP_PD_OPTION_ON, and ESP_PD_OPTION_AUTO. The auto option powers down the power domain unless it is needed by one of the wakeup options. The different ESP power domains are:

  • ESP_PD_DOMAIN_RTC_PERIPH for RTC IO, sensors and ULP co-processor.
  • ESP_PD_DOMAIN_RTC_SLOW_MEM for RTC slow memory.
  • ESP_PD_DOMAIN_RTC_FAST_MEM for RTC fast memory.
  • ESP_PD_DOMAIN_XTAL for XTAL oscillator.
  • ESP_PD_DOMAIN_RTC8M for internal 8M oscillator.
  • ESP_PD_DOMAIN_VDDSDIO for VDD_SDIO.

During Deep Sleep the only GPIO pins that are available are the RTC IO (RTC_GPIO), which are part of the ESP_PD_DOMAIN_RTC_PERIPH power domain. The ULP co-processor is also active in this power domain. The table below shows the mapping of GPIO pins to analog and RTC functions available through the RTC Mux. The table information is from the Espressif Systems ESP32 Technical Reference Manual Version 4.2, section 5.11, table 21.

GPIO NumRTC GPIO NumAnalog
1
Analog
2
Analog
3
RTC
1
RTC
2
360ADC_HADC1_CH0RTC_GPIO0
371ADC_HADC1_CH1RTC_GPIO1
382ADC_HADC1_CH2RTC_GPIO2
393ADC_HADC1_CH3RTC_GPIO3
344ADC1_CH6RTC_GPIO4I2C_SCL
355ADC1_CH7RTC_GPIO5I2C_SDA
256DAC_1ADC2_CH8RTC_GPIO6I2C_SCL
267DAC_2ADC2_CH9RTC_GPIO7I2C_SDA
338XTAL_32K_NADC1_CH5TOUCH8RTC_GPIO8
329XTAL_32K_PADC1_CH4TOUCH9RTC_GPIO9
410ADC2_CH0TOUCH0RTC_GPIO10
011ADC2_CH1TOUCH1RTC_GPIO11
212ADC2_CH2TOUCH2RTC_GPIO12
1513ADC2_CH3TOUCH3RTC_GPIO13
1314ADC2_CH4TOUCH4RTC_GPIO14
1215ADC2_CH5TOUCH5RTC_GPIO15
1416ADC2_CH6TOUCH6RTC_GPIO16
2717ADC2_CH7TOUCH7RTC_GPIO17
GPIO Pin Function via RTC Mux Selection

To setup the RTC Mux and control the RTC GPIOs in the Arduino environment, calls are made to rtc_gpio_* functions. These functions allow you to initialize the pin as RTC GPIO and set it’s direction and level. My test code adapted from Xabi Alaez’s is shown below.

#include "driver/rtc_io.h"
gpio_num_t RST_PIN = GPIO_NUM_14;

void setup() {
  rtc_gpio_init(RST_PIN);
  rtc_gpio_set_direction(RST_PIN,RTC_GPIO_MODE_OUTPUT_ONLY);
  // toggle reset pin
  rtc_gpio_set_level(RST_PIN,1); //GPIO high
  rtc_gpio_set_level(RST_PIN,0); //GPIO LOW
  rtc_gpio_set_level(RST_PIN,1); //GPIO high
  delay(5000);  // wait
  rtc_gpio_set_level(RST_PIN,0); //GPIO hold low
  esp_sleep_pd_config(ESP_PD_DOMAIN_RTC_PERIPH, ESP_PD_OPTION_ON);
  esp_sleep_enable_timer_wakeup(60*1000*1000);  // 60 seconds
  esp_deep_sleep_start();
}

void loop() {
  // none
}

The first step calls rtc_gpio_init(gpio_num_t gpio_num) with the GPIO pin number to the initialize the pad for an analog/RTC function. Next, rtc_gpio_set_direction(gpio_num_t gpio_num, rtc_gpio_mode_t mode) is used to set the pin as output. Finally rtc_gpio_set_level(gpio_num_t gpio_num, uint32_t level level) is used to set the pin output as either HIGH (1) or LOW (0).

After the pin setup and toggled, a delay of 5 seconds is introduced before setting the pin to the value needed during Deep Sleep. Note that the ESP_PD_DOMAIN_RTC_PERIPH is enabled prior to setting the wake up timer and entering Deep Sleep. After waking from Deep Sleep, setup() is repeated again.

Although this approach worked well, I was trying to save 5uA on an external peripheral. By enabling the RTC peripheral that includes the ULP co-processor, I added about 100uA+ to the ESP power. So in the end I didn’t use this approach.

The key to setting an ESP32 output to a known state during Deep Sleep is to select an RTC_GPIO, initialize and operate the pin using RTC_GPIO_* calls, and enabling the RTC peripheral power domain prior to entering Deep Sleep.

MIFARE CLASSIC® 1K SECTOR TRAILER ACCESS BITS

Radio Frequency Identification (RFID) has become an effective, low cost, contactless method of transferring data between a tag and reader. A reader is a device that has one or more antennas that emits radio waves. These radio waves energize a close proximity passive tag allowing for the exchange of data between the tag and reader. Some common RFID applications include inventory management, asset tracking, loyalty cards, fare cards, employee cards, and access control. The radio wave frequency affects the distance between the tag and reader.

  • Low-Frequency 30kHz to 500kHz (typical 125kHz) range few inches to less than 6 feet.
  • High-Frequency 3MHz to 30MHz (typical 13.56Mhz) range few inches to several feet.
  • Ultra High-Frequency 300MHz to 960MHz (typical 433MHz) up to 25+ feet.
  • Microwave 2.45GHz up to 30+ feet.

The MIFARE Classic® 1K is a common low cost RFID tag that operates at 13.56MHz. These tags come in key card and keychain styles and costs less that $1 each in low quantities. As the 1K indicates, this tag stores 1K bytes of data. For the reader you can use a standard product like the ACR122U that has a USB connection to your computer. If you are making your own embedded system then the RC522 module is a simple, low cost reader module that interfaces to a processor using SPI.

The MIFARE Classic® 1K tag is organized into 16 sectors (0 – 15) with each sector containing 3 data blocks (blocks 0-2) and 1 sector trailer (block 3). Each data block/sector trailer has 16 bytes (16 sectors * 4 blocks/sector * 16 bytes/block = 1024 bytes). Not all bytes are available for user data. Sector 0, data block 0 is reserved for manufacturing data and each sector trailer can contain up to 7 user data bytes if key B isn’t being used. If only data blocks are used then 752 bytes are available for user data.

MIFARE Classic® 1K Data Organization

Manufacturer block (sector 0, block 0, address 0, 16 bytes) is programmed and write protected during product test. Data blocks are configurable as either read/write or value blocks as defined by the access bits in the sector trailer. Value blocks have special commands such as increment and decrement for direct control of stored values, which are helpful in electronic purse applications.

The sector trailer is the last block of the sector. Each sector trailer contains:

  • secret keys A (bytes 0-5) and B (bytes 10-15), which when protected from reading returns ‘0’
  • access conditions for all four sector blocks (bytes 6-8)

All keys are set to 0xFFFFFFFFFFFF at chip delivery and key A is never readable.

Sector Trailer Byte Definitions

Access bits control the access conditions for every data block and sector trailer in the given sector. Access control is defined by 3 bits for each block, which are stored in the sector trailer bytes 6-8 (3 bits/block x 4 blocks/sector = 12 bits/sector * 2 for true and inverted values = 24bits/sector = 3 bytes/sector). Access control limits the ability to read or write sector areas including the sector trailer as well as defining data block’s functions (read/write vs. value block). Formatting of bytes 6-8 is somewhat complex. These three bytes are formed by combining the access bits values of the four sector blocks as shown in the next figure (e.g., bit C22 is the C2 bit value for block 2 within the sector).

Access Bits Byte Definition

Conditions for sector trailer access are defined in the table below. Access control includes the ability to write key A (reading key A is never allowed), read and write key B, and the ability to read and write access bits. For example if you never want to write either key A or key B, but you still want to change the access bits in the future, then C1C2C3 = 101 (5) is a good selection. With this access setting you can never read or write either key, but you can still read the access bits using either key A or key B and write the access bits using key B. The factory default value is C1C2C3 = 001 (1), where key A is used for read and write access to all sector trailer values.

Sector Trailer Access Conditions

A similar table is used to configure the sector data blocks access bits. Access conditions include read, write, increment, decrement/transfer/restore. If you want to only read a data block using only key B, then C1C2C3 = 101 (5) could be used. Note that if key B is readable (set by the sector trailer access bits), it can’t be used for access control (see note [1]).

Data Block Access Conditions

Unless you are using a tag reader with support software, the calculation of the access control bytes is a little tedious. I have created an Excel spreadsheet where the user selects each blocks access bits integer decimal values (unshaded table area) using the appropriate tables (C1C2C3) and the access bits byte values are automatically calculated (B6 B7 B8).

Excel Worksheet to Simplify Access Bits Byte Calculation

This has been a quick discussion on setting the MIFARE Classic® 1K tag access bits. If you want to learn more about this tag, Sanga Chidam YouTube channel has excellent video tutorials on the MIFARE Classic® 1K tags. All figures and tables in this post are from the NXP MF1S503X datasheet.

Air Quality Index Fitbit App Released

Anyware, LLC Fitbit app for the display of relevant air quality data for ozone and 10 & 2.5 micron particles has been approved and published on the Fitbit Gallery.

This app helps you to know the air quality in your area before exercising outdoors. The app displays the current air quality index (AQI) using the official U.S. AQI, a color-coded index designed to communicate whether air quality is healthy or unhealthy for you. Data are provided by AirNow, which is a one-stop source for all air quality data. Using your location provided by your phone, the app finds the nearest reporting station and displays the AQI for ozone and particles 10 & 2.5 microns or less. Touch on a reported value to see the value description as provided by the Environmental Protection Agency.

To use this app, you must register and create an account at AirNow. AirNow will provide you a unique API key that is entered under the app settings on your phone. Enter the API key as provided by AirNow including all dashes. This app uses this API key to get the most relevant reported AQIs using your phone’s GPS and network connection. Instructions for obtaining an API key and configuring the app are found here.

The app only works in the U.S. and some locations in Canada and Mexico. The user needs to be within 250 miles of a reporting station used by AirNow.

Fitbit SunRise SunSet App Issue [Resolved August 20, 2022]

This published Fitbit app utilizes an API provided by sunrise-sunset.org to get the sunrise and sunset times for a given date and location. As of August 9. 2022, the sunrise-sunset API https call has an invalid certificate creating an issue. Fortunately a user notified me of this problem. Since the API certificate is invalid, returned data is throwing an error that wasn’t being properly handled in the code [Note: sunrise-sunset.org updated their certificate allowing the API to respond properly].

I am currently working on an update to the companion code to handle the unexpected error, which is discussed below. Even with this error handling, if the sunrise-sunset.org API certificate is not resolved, this will required a change in how the sunrise and sunset times are obtained. Currently my plan is to publish an app update by October 1, 2022 [Note: An app update to handle malformed JSON responses was published August 24, 2022, Version 1.1.0].

This issue with the unexpected error is found in the companion app (the code that runs on the Fitbit app on your phone). In the Fitbit architecture, messages are passed between your watch and the companion app (see this Fitbit guide for more information). The companion app provides the gateway to the Internet. The API call uses fetch() with the api calling string. The response should be a JSON string, which is parsed and the data sent back to the watch for display.

The solution to handle the unexpected error is simple. I added a “catch” statement to handle the error and pass an error indicator back to the watch. The companion code segment is show below. This code waits for a message from the watch to get sunrise and sunset data. The companion app builds the api string and then makes the api call using fetch(). The JSON string components are extracted and sent back to the watch for display. The catch sends back-100 lat and lon to indicate an error has occurred.

// Listen for messages from the device
// Message has the date we need, companion has everything else
messaging.peerSocket.onmessage = function(evt) {
  if (evt.data) {
    console.log("evt.data " + JSON.stringify(evt.data));
    if (messaging.peerSocket.readyState === messaging.peerSocket.OPEN) {
      // build api string
      var apiString = "https://api.sunrise-sunset.org/json?lat=" + lat + "&lng=" + lng + "&date=" + evt.data.getDate;
      console.log(apiString);
      
      // go to the web to get sun rise/set data that is returned in a json string
      fetch(apiString)
        .then(function(response) {return response.json();})
        .then(function(json) {
        console.log("Got JSON response from server:" + JSON.stringify(json));
        
        messaging.peerSocket.send({
          getDate: evt.data.getDate, lat: lat, lng: lng, rise: json.results.sunrise, set: json.results.sunset});
      })
      .catch(function(err) {
        console.error('Error during fetch ' + err.code);
        messaging.peerSocket.send({
          getDate: evt.data.getDate, lat: -100, lng: -100, rise: 0, set: 0}); // network data error occurred
      });
    }
  }
}

When the watch receives a message with the error indicators, the watch displays that an error has occurred but continues to allow the user to change dates and try again.

Excel Macro Non-Letter Shortcut KeY/Key Sequence Assignment

Recently I had an Excel VBA project where the client wanted to use CTRL-ALT-9 as the shortcut key to run a macro. For most of us when we create macros we assign a shortcut key sequence by going into Developer > Macros > Options… and assigning the shortcut key. The problem with this method is that it only allows lower and upper case letters to be used with the CTRL key.

Fortunately we have an Application method to associated any key/key sequence to procedure. We use the Application.OnKey method to assign the key/key sequence to press to run a specified procedure. It just requires running code when the workbook opens and closes.

To assign the shortcut key, VBA code is added to ThisWorkbook in the Microsoft Execl Objects. When the workbook is opened we assign the shortcut key/key sequence. When the workbook is closed we return the key/key sequence to its normal meaning. Sample code is provided below.


' run when workbook is opened
Private Sub Workbook_Open()

    ' sets the hot key to CTRL-ATL-9 to run ConvertTableMacro
    Application.OnKey "^%9", "ConvertTableMacro"

End Sub

' run when workbook is closing
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ' resets the CTRL-ATL-9 hot key to normal meaning 
    Application.OnKey "^%9"

End Sub

The Microsoft link above provides all the information related to special characters for key sequences. For example “^” is the CTRL key, “%” is the ALT key, and “+” is the Shift key. This is a very simple solution to assigning other characters for macro shortcut keys.

Excel VBA Sudoku Puzzle Solver using Backtracking Algorithm

A while back I created a Sudoku puzzle solver using the backtracking algorithm in Python. I decided to re-create that same solver as an Excel VBA. The translation was straight forward. Instead of using Tkinter user interface I used an Excel worksheet for the individual puzzle cells. Simple command buttons are used to clear, start, and stop the puzzle.

The results was a nicer looking puzzle with bold outer and 3×3 groups. The biggest issue is the use of copy and paste on the user interface, which will modify border styles. A simple solution was implemented to re-draw the borders when clearing or starting a new puzzle.

' copy/paste changes borders
Private Sub setBorders()

    Dim borderRange As Range    ' range to set border
    Dim ws As Worksheet         ' puzzle worksheet
    Dim i, j As Long            ' index counters
    
    Application.ScreenUpdating = False  ' turn off updates while fixing borders
    
    Set ws = ThisWorkbook.Sheets("SudokuPuzzle")
    ' put border around each cells
    For i = 2 To 10
        For j = 2 To 10
            ws.Range(Cells(i, j), Cells(i, j)).BorderAround LineStyle:=xlContinuous, Weight:=xlThin, Color:=vbBlack
        Next j
    Next i
    
    ' thick on outside
    ws.Range("B2:J10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    ' thick in 3x3 groups
    ws.Range("B2:D4").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("E2:G4").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("G2:J4").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    ws.Range("B5:D7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("E5:G7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("H5:J7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    ws.Range("B8:D10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("E8:G10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("H8:J10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    Application.ScreenUpdating = True
    
End Sub

The solving performance speed was about 100 times slower than Python. The solved puzzle shown below was finish in about 1 second using Python where the Excel VBA took more than 80 seconds to solve using the identical solving algorithm.

I wanted to provide the macro enable Excel workbook for download by WordPress doesn’t allow .xlsm files (and for good reason). Sorry.

Arduino Tilt & Laser Distance Finder

A middle school teacher reached out looking for a firmware developer to create a tilt and laser range finder for classroom use to encourage STEM education. This looked like a fun project and appeared straight forward. The teacher had already selected, purchased, and assembled the hardware into a demonstration system for his classroom.

The hardware included:

  • Nano 33 BLE
  • Nextion NX4024T032 basic touch display (400×240, 3.2″ display)
  • Laser range module
  • MB102 power supply
  • 12V AA battery pack (AA cell used for on/off switch)
Laser Distance Finder and Tilt Measuring Device

The Nano 33 BLE has an Inertial Measurement Unit (IMU) to support tilt measurement and the laser range module provided distance information via a serial interface.

The laser module interface documentation was minimal and some test code was created to understand the control and response formats. Second I had planned on using ITEADLIB library to interface to the Nextion display, but the library doesn’t support Nano 33 BLE. So I had to generate code to interface to the Nextion display.

The client provided graphic for the tilt gauge and battery status and conceptual functions and screens. The final system had three screens:

  • Control and measurement used to start/stop measurements, display current measurement data (distance and tilt), and show battery status. It also had buttons to see a previous measurement list and go to the settings page.
  • Measurement list that shows the last 10 measurements in current units.
  • Settings page was used to change tilt and distance calibration offsets, change screen brightness, and toggle units between feet-inches, inches, and millimeters.

On the control and measurement screen there were three primary firmware calculations that were needed, distance measurement, tilt angle, and battery voltage percentage. The only control function was to turn the distance/tilt measurement on and off that used a multi-state Nextion button object.

Distance Calculation

The laser distance module is a self contained system where control and data used an asynchronous start/stop protocol at 115200 baud, 8-N-1. The laser module had both single and multiple (continuous) measurement modes. I used the multiple measurement mode where the module once enabled, continued to send measurements so the system could be pointed and adjusted as needed before stopping the measurement and storing the results.

While in this measurement mode, the distance was updated whenever a new measurement was received through the serial interface. The message was decoded and converted to the display string using the user selected units (ft-inches, inches, or mm). All data was stored in the firmware in inches and converted when needed.

Tilt Calculation

Tilt was updated whenever the Inertial Measurement Unit (IMU) had a new measurement. The interface to the LSM9DS1 IMU used an Arduino library. The IMU x-axis is inline with the laser module. I used all three axis in the calculation and applied the classical method of rectangular to spherical conversion to find the tilt. The key assumption was the only acceleration is due to gravity.

g_tilt = (180.0*acos(x/sqrt(x*x+y*y+z*z))/M_PI) - 90;  // +/- 90 degrees

Battery Percentage Calculation

A low current voltage divider was used to estimate the remaining battery level. Based on the voltage a percentage was calculated. On the Nextion display a progress bar was used to show the amount of remaining battery and the color was changed based on the level between green (50% or greater), yellow, and red (15% or less). The battery level was updated based on a programmable timer value.

Nextion Display

The Nextion Display was developed using the Nextion’s HMI software. Buttons used images to reflect on, off, settings, save, and back. The Nextion display interface is serial and the protocol is defined in the user documentation. Below is a sequence that sets the length of the progress bar, sets the color, and writes the text value of the battery percentage.

  nextionSerial.print("j0.val=");              // progress bar
  nextionSerial.print(String(100-intPerCent));
  nextionSerial.print("\xFF\xFF\xFF");

  if(percentage < 15) {
    nextionSerial.print("j0.bco=63488");       // progress bar red
    nextionSerial.print("\xFF\xFF\xFF");    
  }
  else if (percentage >= 50) {
    nextionSerial.print("j0.bco=1024");       // progress bar green
    nextionSerial.print("\xFF\xFF\xFF");        
  }
  else {
    nextionSerial.print("j0.bco=65504");       // progress bar yellow
    nextionSerial.print("\xFF\xFF\xFF");    
  }
  
  nextionSerial.print("t3.txt=");       // text value
  nextionSerial.print("\"" + String(intPerCent) + "%" + "\"");
  nextionSerial.print("\xFF\xFF\xFF");

Saving Operating Parameters

The Nano 33 BLE does not have EEPROM. The only non-volatile memory is flash. For this project the user wanted to store the units and offsets between power cycles. I looked at several libraries and found one designed to store parameters in flash by Dirk Frehiling on Github. The library works with a data structure defined by the programmer (typedef struct) and uses writePrefs and readPrefs object methods.

// cal flash data structure
typedef struct flashStruct {
  float calDistance;        // inches (always)
  float calTilt;            // degrees
  enum outputType outUnits; // units
} calibrationData;

The project has some other nice features like going into a power down mode after inactivity and displayed fractions of an inch as a fraction for measurements in FT-IN, and INCHES. One future improvement is to increase the accuracy of the tilt measurement using more advance calibration techniques.

NORDIC nRF52840 GPIO High Drive

I recently had a project using the Adafruit Feather nRF52840 Express to control a 2N2222 transistor being used as a high power switch. The nRF52840 SoC is a higher-end Bluetooth 5.2 SoC supporting Bluetooth Low Energy, Bluetooth mesh, NFC, Thread, and Zigbee. It has a 64MHz Arm Cortex-M4 with FPU, 1MB Flash, 256KB RAM and all the standard microcontroller features including UART, SPI, TWI, PDM, I2S, QSPI, PWM, and 12-bit ADCs. Adafruit has created a board support package for the Arduino development environment.

After building the code and making a test circuit with a 620 ohm base resistor, I measured the nRF52840 VOH and the voltage was much lower than expected. Reviewing the nRF52840 datasheet the IOH for standard drive is a minimum of 1mA and a maximum of 4mA. I was exceeding the GPIO pin guaranteed current drive capability.

The nRF52840 is an interesting microcontroller where you can control the GPIO drive between standard, high, and disconnect (open). You can specify both IOH and IOL and they can be different. The RW DRIVE parameter is set in the PIN_CNF[n] register as specified in the nRF52840 Product Specification v1.2. The high drive for a 3.3V VDD is a minimum of 6mA for both IOH and IOL.

First I needed to find the correct GPIO pin [n] value to set the drive parameters. Most of the parameters are set when creating the PWM object, but there is no method to set the RW DRIVE parameter. To find the correct pin number I needed to review variant.h and variant.cpp files in the directory containing the board support package …\packages\adafruit\hardware\nrf52\0.22.0\variants\feather_nrf52840_express.

I was using the Feather A0 pin, which is connected to the nRF52840 P0.04 pin. In variant.h file A0 is mapped to pin 14 (D14). From variant.cpp D14 is mapped to pin 4. This is the [n].

To set the RW DRIVE requires either writing directly to the register (NRF_GPIO->PIN_CNF[4] = 0x203) or use a call to nrf_gpio_cnf with the desired parameters. See the file nrf_gpio.h for enum types and support functions. The following call was made to set VOH to high drive and VOL to standard drive (S0H1). Once this change was made the VOH was well within specification when turning the transistor switch on.

nrf_gpio_cfg(4, 
             NRF_GPIO_PIN_DIR_OUTPUT, 
             NRF_GPIO_PIN_INPUT_DISCONNECT, 
             NRF_GPIO_PIN_NOPULL, 
             NRF_GPIO_PIN_S0H1, 
             NRF_GPIO_PIN_NOSENSE);

Excel VBA File Dialog on a Mac

I’ve been creating Microsoft VBAs for years to solve different problems ranging from Finance to Engineering. These VBAs have been developed and hosted on Windows platforms using different versions of Microsoft Office products. Recently I had a VBA requirement where the client used Excel (365) on a Mac. For tis application the key differences between the two products/OSs were:

  • No userform development UI on Mac
  • Different file dialog
  • Full path filename parsing

To solve the the userform development UI issue, I just developed the userform on Windows. When the VBA executed on the Mac the userform was properly displayed without any issues. Any changes to the userform had to be done on a Windows platform.

The second problem was more difficult and required some research. Prevously I’ve accessed files using the Application.FileDialog function with the msoFileDialogFilePicker parameters. It’s straight forward and I have used this code many times. A code segment is shown below.

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
With Application.FileDialog(msoFileDialogFilePicker)
    'Makes sure the user can select only one file
    .AllowMultiSelect = False
    'Filter to just the following types of files to narrow down selection options
    .Filters.Add "CSV/CRC Files", "*.csv; *.crc", 1
    'Show the dialog box
    .Show
        
    'Store in fullpath variable if file selected
    If .SelectedItems.Count <> 0 Then
        fullpath = .SelectedItems.Item(1)
    Else
        MsgBox ("No file selected. Exiting program...")
        Exit Sub
    End If
End With

The problem is Application.FileDialog is not supported by Excel on the MacOS. I am thankful someone else had already solved this problem using an Applescript. I was able to reuse the posted script without any modifications. I created a function for each operating system to create a file dialog window and return the user selected file. On a Mac, if the user doesn’t select a file, the string “-128” (error code userCanceledErr) is returned. So the Windows function also returns “-128” when the user doesn’t select a file.

Function BrowseMac(mypath As String) As String
  Dim sMacScript As String
  
  sMacScript = "set applescript's text item delimiters to "","" " & vbNewLine & _
    "try " & vbNewLine & _
    "set theFiles to (choose file " & _
    "with prompt ""Please select a file or files"" default location alias """ & _
    mypath & """ multiple selections allowed false) as string" & vbNewLine & _
    "set applescript's text item delimiters to """" " & vbNewLine & _
    "on error errStr number errorNumber" & vbNewLine & _
    "return errorNumber " & vbNewLine & _
    "end try " & vbNewLine & _
    "return theFiles"
  BrowseMac = MacScript(sMacScript)
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function BrowseWin() As String
    'Display a Dialog Box that allows to select a single file.
    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Text Stock Files", "*.txt, 1"
        .Title = "Select Input Stock File"
        'Show the dialog box
        .Show
        
        'Store in fullpath variable if file selected
        If .SelectedItems.Count <> 0 Then
            BrowseWin = .SelectedItems.Item(1)
        Else
            BrowseWin = "-128"  ' what Mac OS returns when file is not selected
        End If
    End With
End Function

Since I was developing on Windows 10 and testing using both operating systems, I used Application.OperatingSystem to determine the host OS to call the appropriate routine. On MacOS Application.OperatingSystem includes “Macintosh” in the returned string. The call to the Mac file browser also uses an Applescript to the provide the documents folder directory path.

' determine OS and get input file via dialog box
theOS = Application.OperatingSystem
If (InStr(1, theOS, "Macintosh", vbTextCompare) = 0) Then   ' not Mac
    ' Windows Version
    fullFilename = BrowseWin
    fileName = getWINFName(fullFilename)
Else
    ' Mac Version
    fullFilename = BrowseMac(MacScript("return (path to documents folder) as String"))
    fileName = getMACFName(fullFilename)
End If
    
' check for a good file name
If fullFilename = "-128" Then
    MsgBox ("File not selected, no action taken.")
    Exit Sub
End If

The returned filename has the full path name. For this application I wanted to capture the filename only. So another difference between the operating sytsem is Windows uses the “\” character and MacOS used the “:” character to separate the path.

Function getWINFName(pf) As String: getWINFName = Mid(pf, InStrRev(pf, "\") + 1): End Function
Function getMACFName(pf) As String: getMACFName = Mid(pf, InStrRev(pf, ":") + 1): End Function

Working with the texted based input file was the same for both Windows 10 and MacOS 10. Below is a small code snippet showing the opening the file, reading text line, and closing the file once finished.

Open fullFilename For Input As #1
Do Until EOF(1)
    ' get input record and separate items
    ' record data time, open, high, low, close, [volume]
    Line Input #1, textLine             ' input record
    inputSplit = Split(textLine, ",")   ' split on '

    < good code stuff >

Loop
Close #1

The Open with the full path name worked on the Mac, but I found if you are using Workbooks.Open with Excel 2016, then the full path filename needs to be modified by changing “:” to “/” and removing the “Macintosh HD” from the full path. I found this nice code on stack overflow, which worked great.

If CInt(Split(Application.Version, ".")(0)) >= 15 Then 'excel 2016 support
    wbName = Replace(wbName, ":", "/")
    wbName = Replace(wbName, "Macintosh HD", "", Count:=1)
End If

So my first experience developing an Excel VBA for MacOS had small, but solvable technical hurdles, which were solved by using a good search engine and leveraging other developer’s code.