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

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

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.


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
    'Store in fullpath variable if file selected
    If .SelectedItems.Count <> 0 Then
        fullpath = .SelectedItems.Item(1)
        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
        'Store in fullpath variable if file selected
        If .SelectedItems.Count <> 0 Then
            BrowseWin = .SelectedItems.Item(1)
            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)
    ' 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 >

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.

Python Sudoku Puzzle Solver using Backtracking Algorithm

When learning a new programming language, it is always beneficial to tackle a problem that keeps your interest. I always found writing a game usually does it for me. Writing computer games typically involves lots of programming aspects such as user interfaces, data structures, and algorithms.

I really enjoy sudoku puzzles. So while I was learning Python I decided to create a sudoku puzzle solver. There is a lot of Internet literature written on sudoku puzzle solving using the backtracking algorithm.

Sudoku Puzzle

Sudoku is a number placement puzzle that has become popular in the last 10 years. In a “classic” sudoku puzzle there is a 9×9 grid with 9, 3×3 sub-grids. The object is to place a number 1 through 9 in each square. Each number in a row and column in the 9×9 grid must be unique. Also each number in a 3×3 sub-grid must also be unique.

To begin, a partially solved puzzle is provided. Typically the fewer already placed numbers, the harder the puzzle. Each sudoku puzzle is designed to have only a single solution.

Backtracking Algorithm

The backtracking algorithm is a method for solving problems recursively by testing incremental solutions. If the solutions fails, then you “backtrack” the previous solution and attempt another solution.

The backtracking algorithm has been used to solve many problems including the Knight’s Tour Problem, Rat in a Maze, N Queen Problem, Subset Sum, Hamiltonian Cycle, and many others. A cool website, GeeksforGeeks, discusses the backtracking algorithm and application.

To solve a sudoku puzzle using the backtracking algorithm you place a value in the first empty square and test if that solution is valid. If the value is not valid, then the next value is tried. If the test solution is valid, then the next empty square is filled in with a value and then tested until a valid solution is found. This continues until all empty squares have a value.

Python BAcktracking Algorithm

The interface uses Tkinter buttons and creates a 9×9 grid. Code is dedicated for puzzle setup by clicking on a button to enter a known value. Some support functions were also created to check if the placed value is valid for row, column, and sub-grid (square).

The sudoku puzzle is represented as a 2-D array of integer values with 0 being empty. My Python backtracking works a row at a time. A number (1-9) is place in an empty cell and is tested for a valid entry. If the entry is not valid, then the next number is tested. When a number can be placed, the foreground color is changed to blue and the number added as text to the button (cell).

def solvePuzzle(sudoku):
    emptyFound = False
    for row in range(0,9):
        for column in range(0,9):
            if(sudoku[row][column] == 0):
                emptyFound = True
                for num in range(1,10):
                    sudoku[row][column] = num
                    if okRow(sudoku, row):
                        if okColumn(sudoku, column):
                            if okSquare(sudoku, row, column):
                                buttons[(row * 9) + column].configure(fg = 'blue')
                                buttons[(row * 9) + column].configure(text = num)
                                if solvePuzzle(sudoku):
                                    return True
                                    sudoku[row][column] = 0
                                sudoku[row][column] = 0
                            sudoku[row][column] = 0
                        sudoku[row][column] = 0

                # no solution, tried all numbers but failed
                return False
    if not emptyFound:
        return True

The code segment below shows testing a row for valid entries. A simple array is used to accumulate the number of times a number is entered in the row. If any entry is greater than 1, then that number was duplicated and the row is not valid.

# check if row is good, number already in row
def okRow(sudoku, row):
    # check for duplicate 1-9 in this row
    check = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]  # count number is row
    for i in range(0, 9):
        check[sudoku[row][i]] += 1
    for i in range(1, 10):
        if(check[i] > 1):   # multiples of the same numbers have sum > 1
            return False
    return True

For me, most medium difficult sudoku puzzles are solved in about 10 minutes. Many of the puzzles I have solved using Python take less than 1 second using the backtracking algorithm. The longest solution for an extremely hard puzzle was about 5 minutes.

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 blue wire and the RX the yellow wire. Since the original post I have found documentation showing the interface is 3V3 compatible and will accept 5V on the RX.

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
#include <SoftwareSerial.h>
SoftwareSerial dbSerial(3, 2); /* RX:D3, TX:D2 */
// NexHardware.cpp
bool nexInit(void)
    bool ret1 = false;
    bool ret2 = false;
    nexSerial.begin(9600,SERIAL_8N1, 32, 33);	// modified for serial1 I/O
    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

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;
  if (number > minParameterValue)
    parameterValue.setValue(number - 1);
void plusPopCallback(void *ptr) {
  uint32_t number;
  if (number < maxParameterValue)
    parameterValue.setValue(number + 1);
void setParameterPopCallback(void *prt) {
  uint32_t getValue;
  // update variable that is changing
  *modifyParameter = (int)getValue;
  // go back to last page
  // 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(;;) {
    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(Outfall_uA * 1.25 - 5000)
 // Overflow sensor
 if(Overflow_uA < 4000)
    OF01_Overflow.AddValue(Overflow_uA * 1.25 - 5000)

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.