Skip to content

Latest commit

 

History

History
119 lines (87 loc) · 3.81 KB

How-To.md

File metadata and controls

119 lines (87 loc) · 3.81 KB

Installing and Using functions as Excel Formulas

Worksheet Cell Formulas

Before starting

  • Check that the Com Port settings match on both the PC and attached device.
  • Connect a terminal emulator or similar device to the PC's COM Port.
  • See screenshot in folder for Com Port settings check on host PC.

Installing VBA

  1. Download SERIAL_PORT_EXTRA_SIMPLE_VBAn.bas
  2. Open a new Excel document
  3. Enter the VBA Environment (Alt-F11)
  4. From VBA Environment, view the Project Explorer (Control-R)
  5. From Project Explorer, right-hand click and select Import File.
  6. Import the file SERIAL_PORT_EXTRA_SIMPLE_VBAn.bas
  7. Check that a new module SERIAL_PORT_VBA_SIMPLE is created and visible in the Modules folder.
  8. Check/Edit COM_PORT_NUMBER value at start of module SERIAL_PORT_VBA_SIMPLE
  9. In function READ_COM_PORT, remove the comment mark before Application.Volatile
  10. Close and return to Excel (Alt-Q)
  11. IMPORTANT - save document as type Macro-Enabled with a file name of your choice.

Excel Formula Testing

Start Com Port

  • In Cell A3, type the formula =start_com_port() and hit return
  • Check that TRUE is now shown in cell A3
  • TRUE confirms that the port has started.
  • FALSE means port not started, i.e. not found, already in use etc.

Send to Com Port

  • Enter some short text in Cell B5 - e.g. TEST123

  • In Cell A5, type the formula =send_com_port(B5) and hit return

  • Check that TRUE is now shown in cell A5

  • Check that TEST123 appears on your device

  • Change the text in Cell B5 - e.g. QWERTY and hit return

  • Check that TRUE is still shown in cell A5

  • Check that QWERTY appears on your device

  • This confirms that send_com_port is working.

Read from Com Port

  • In Cell B7, type the formula =read_com_port() and hit return
  • Enter some text on your device - e.g. HELLO
  • Change any other cell or press F9 key on your PC
  • Check that HELLO appears in Cell B7
  • Change any other cell or press F9 key on your PC for a second time
  • Check that Cell B7 returns to blank (no new data to read)
  • Enter some new text on your device - e.g. AGAIN
  • Change any other cell or press F9 key on your PC for a third time
  • Check that AGAIN appears in Cell B7
  • This confirms that read_com_port and Application.Volatile are working.

Stop Com Port

  • In Cell A9, type the formula =stop_com_port() and hit return
  • Check that TRUE is now shown in cell A9
  • TRUE confirms that the port has stopped.
  • Change any other cell or press F9 key on your PC
  • Check that FALSE appears in Cell A5 (send_com_port has failed as expected)
  • This confirms that stop_com_port is working.

Serial Devices

Passive Devices

These devices do not need a command to be sent before replying.
Reads should function from Excel with no further action required.
Send functions are usually not required, or required once only.

Active Devices

These devices do need a command to be sent before replying.

A read delay will normally be required to allow sufficient time for the :-

a) device to process the read command
b) serial data to be transmitted back

Remove the comment mark from Kernel_Sleep_Milliseconds in function read_com_port

Note that Functions may still be used in VBA routines as required.