XLRS232 Add-in RS-232 Communications with EXCEL© for Microsoft Windows 3.1 Version 1.0 Beta LITTLE MAN© Software Copyright © 1994 by Albert J. Fahey ajf@arms.gps.caltech.edu Compuserve: 75570,2147 75570.2147@compuserve.com July 31, 1994 All Rights Reserved Windows 3.1 and Excel are trademarks of Microsoft Corporation. INTRODUCTION XLRS232 is a set of 13 functions that add serial line communication capabilities to Microsoft EXCEL Version 4 (and Version 5 as well, according to Microsoft). The primary purpose for developing this software was to automate data acquisition from serial- line devices (such as a voltmeter or digital thermometer) and to place the data directly into a spreadsheet where it can be manipulated and displayed. As of this writing, the functions have been tested via a laptop connected to the serial-line of my PC at home with Excel Version 4. Therefore, there may be bugs of that may surface through use with other RS- 232 devices that I could not find. Bugs should be reported to: 75570.2147@compuserve.com for this beta release (before Oct 1, 1994). XLRS232 is provided as a "stand-alone DLL" or XLL for EXCEL. It can be temporarily loaded into EXCEL through the FILE/OPEN... menu item or can be made to load at startup via the OPTIONS/ADD-IN... menu item. The functions provided become "native" EXCEL functions and need not be REGISTERed or CALLed but simply can be used as any other EXCEL function. The functions are probably best used from macro sheets but can be used in ordinary worksheets. When used in worksheets care should be taken to note the order in which EXCEL re-evaluates cell expressions. The product is a shareware item. You are permitted to evaluate it for a period of 30 days. If you are satisfied with its usefulness a registration fee is requested to maintain the development and support of this software. A registration form is provided that you can fill out and send to LITTLE MAN Software. REQUIREMENTS This code is compiled for a 386 machine or higher. It will not run on 286 machines! You must have Excel 4.0 or higher, and Microsoft Windows 3.1. DISCLAIMER & COPYRIGHT These programs are Copyright © 1994 by Albert J. Fahey All Rights Reserved. They are provided as shareware with the following limitations: These programs are shareware and are not to be resold or distributed for sale with other programs which are for sale. There is no warranty or claim of fitness or reliability. The programs are distributed AS IS, and as such neither the author, nor LITTLE MAN Software shall be held liable for any loss of data, down time, loss of revenue or any other direct or indirect damage or claims caused by these programs. FUTURE DEVELOPMENTS If these functions prove to be popular and useful I will probably extend them to provide more sophisticated input and output parsing. I can probably write functions to do things like paste multi-column data read from the serial port to multiple columns on the worksheet. If there are many requests for a Macintosh version of this code I should be able to come up with it. The port is fairly straightforward. If things work really well I'll write some functions for specific (the most popular) instruments that are used with this software. If there are any requests for GPIB functions in Excel I may have something available soon (at least for the National Instruments Boards). FUNCTION DESCRIPTIONS OPENMODE Returns the device ID of the opened communications port. Call this function to set up the communications port and get the device ID needed to pass to other communications functions. This function must be called before any other communications functions that need a device ID. It should only be called once. Syntax OPENMODE(Device_control_string) Device_control_string is a string that has the same form as the parameters used in the MS-DOS mode command. (e.g. COM1:9600,n,8,1 sets COM1 for 9600 baud, 8 bits, no parity, one stop bit). Remarks n Call this function before calling any other communications function that needs a device ID. n This function must be called only once. It will complain that the device is already open otherwise. n Flow control is set up as XON/XOFF. Hardware handshaking is not implemented as yet. n Input buffer size is 2048 bytes and the output buffer sizes is 256 bytes. Example OPENMODE("COM1:9600,n,8,1") equals 0 (on my machine). Related Function Function Description CLOSECOM Closes an open communications device. CLOSECOM Returns the zero if successful or less than zero otherwise. Call this function to close the communications port. This function should be called after all other communications functions that need a device ID have been called. Syntax CLOSECOM(Device_id) Device_id is a the value returned by the OPENMODE function. Remarks n Call this function after calling all other communications function that needs a device ID. Example CLOSECOM(A1) equals 0 where A1 is the cell in which OPENMODE() was called. Related Function Function Description OPENMODE Set up and open serial communications. SENDCOM Returns the total number of bytes sent. Call this function to send data to the communications port. Syntax SENDCOM(Device_id,Data) Device_id is a the value returned by the OPENMODE function. Data is the information to be sent. It can be a number, a string, or a cell-reference. If it is a number, the value is converted to its ascii representation before being sent. If it is a string, or a reference to a cell that contains a string, certain escape-sequences may be used to include control characters in a way that is similar to the C-function printf. Data Interpretation \a bell \b backspace \f formfeed \n linefeed \r carriage return \t horizontal tab \v vertical tab \xNN hexadecimal number NN (N can be one of: 0123456789abcdefABCDEF). Remarks n Call this function to send data on the serial line. n The largest string that can be sent in a single call to this function is 256 characters. n Beware of sending Control-S (the XOFF Character) or Control-Q (the XON Character). n No characters other than those that you specify are added to the data stream. If you want to terminate your transmission with a CR-LF you must explicitly do so! n The function will time-out after the time period specified by the COMTIMEOUT function if it cannot write to the output buffer in that period of time. (i.e. there is pending output that has not been read by the receiving instrument or device). Example SENDCOM(A1,"Where is the little man?\n\r") equals 26 -or- SENDCOM(A1,B3) where A1 is the cell in which OPENMODE() was called. examples of the contents of B3 are: 3.14159 A number SIN(H3) A function This is a string\r\n A string Related Function Function Description COMTIMEOUT Set the time-out interval for reading and writing. SENDFROMFILE Send data directly from a file. FLUSHOUTQUE Flush (clear) the output buffer. CBOUTQUE Returns the number of bytes in the output buffer. READCOM Returns a string of characters read from the serial line. Call this function to read data from the communications port. Syntax READCOM(Device_id,Number_of_bytes) Device_id is the value returned by the OPENMODE function. Number_of_bytes is the number of bytes to be read. If you pass a value larger than 256 it will be truncated to 256. If fewer bytes than Number_of_bytes is read then the function times-out after the period specified by the COMTIMEOUT function and returns whatever data it has read. Remarks n Call this function to read data from the serial line. n The largest string that can be read in a single call to this function is 256 characters. n No characters are removed from the data stream (such as CR, LF, or NULL), except maybe Control-S (XOFF) and Control-Q (XON). n The function will time-out after the time period specified by the COMTIMEOUT function if it has not read any characters in that period of time. n Non-printing characters are translated to their hexadecimal equivalent representations (e.g. CR become \x0d). n This function tries to read whatever is in the input buffer (up to the number of bytes requested) in one call to the C-function ReadComm(). Example READCOM(A1,128) might equal 'Is that the little man?\x0d\x0a where A1 is the cell in which OPENMODE() was called. Related Function Function Description COMTIMEOUT Set the time-out interval for reading and writing. READTOFILE Read data directly into a file. READCOMLINE Returns a line (up to the EOL) of data from the serial line. FLUSHINQUE Flush (clear) the input buffer. CBINQUE Returns the number of bytes in the input buffer. READCOMLINE Returns a string of characters read from the serial line. Call this function to read data from the communications port up to the first EOL character. Syntax READCOMLINE(Device_id,Number_of_bytes) Device_id is a the value returned by the OPENMODE function. Number_of_bytes is the maximum number of bytes to be read. If you pass a value larger than 256 it will be truncated to 256. The function reads the characters up to the first EOL character provided it is fewer than Number_of_bytes characters. Remarks n Call this function to read data from the serial port one line at a time. n End-Of-Line characters are: CR (0x0D), LF (0x0A), and a third character that can be set by the user via the SETEOLCHAR function (see below). n The largest string that can be read in a single call to this function is 256 characters. n The function will time-out after the time period specified by the COMTIMEOUT function if it has not read any characters or found the End-Of-Line in that period of time. n Non-printing characters are translated to their hexadecimal equivalent representations (except for EOL characters). n This function reads the input buffer one byte at a time. n Additional EOL characters beyond the first one found are not removed from the input buffer. To remove additional EOL characters from the input buffer use the READCOM function before calling READCOMLINE again. For example: this situation may occur if a device sends a CR and a LF as an EOL sequence. Example READCOMLINE(A1,128) might equal 'What is the little man doing? where A1 is the cell in which OPENMODE() was called. Related Function Function Description COMTIMEOUT Set the time-out interval for reading and writing. READTOFILE Read data directly into a file. READCOM Returns a string of data from the serial line. SETEOLCHAR Set an additional End-Of-Line character. FLUSHINQUE Flush (clear) the input buffer. CBINQUE Returns the number of bytes in the input buffer. SENDFROMFILE Returns the number of bytes sent. Call this function to send data to the serial port directly from a file. Syntax SENDFROMFILE(Device_id,File_Name) Device_id is a the value returned by the OPENMODE function. File_Name is the name of the disk file to send. Remarks n Call this function to read data from the serial port from a file. n The function will time-out after the time period specified by the COMTIMEOUT function if it cannot write to the output buffer in that time. n Data is sent from the file untranslated. No characters are added nor removed from the input stream except maybe Control-S (XOFF) and Control-Q (XON). Example SENDFROMFILE(A1,"AUTOEXEC.BAT") might equal 1004 where A1 is the cell in which OPENMODE() was called. Related Function Function Description COMTIMEOUT Set the time-out interval for reading and writing. READTOFILE Read data directly into a file. FLUSHOUTQUE Flush (clear) the output buffer. CBOUTQUE Returns the number of bytes in the output buffer. READTOFILE Returns the number of bytes read. Call this function to read data from the serial port directly into a file. Syntax READTOFILE(Device_id,File_Name,Number_of_bytes) Device_id is a the value returned by the OPENMODE function. File_Name is the name of the disk file to write to. Number_of_bytes is the maximum number of bytes that will be read. Remarks n Call this function to write data from the serial port into a file. n The function will time-out after the time period specified by the COMTIMEOUT function if it has not read any data in that time. n Data is sent to the file untranslated. No characters are added nor removed from the input stream except maybe Control-S (XOFF) and Control-Q (XON). Example READTOFILE(A1,"TEST1.TXT",2048) might equal 1004 where A1 is the cell in which OPENMODE() was called. Related Function Function Description COMTIMEOUT Set the time-out interval for reading and writing. DENDFROMFILE Send data directly from a file. FLUSHINQUE Flush (clear) the input buffer. CBINQUE Returns the number of bytes in the input buffer. SETEOLCHAR Returns the integer value of the ascii character. Call this function to set a third End-Of-Line character for the READCOMLINE function to search for. Syntax SETEOLCHAR(String) String a string containing the EOL character to be used along with CR, and LF. Some interpretation of the string is performed as follows. String Interpretation \a bell \b backspace \f formfeed \n linefeed \r carriage return \t horizontal tab \v vertical tab \xNN hexadecimal number NN (N can be one of: 0123456789abcdefABCDEF). Remarks It is useful, when reading multi-column data, to set the additional EOL character as the column separator character (such as SPACE, or HORIZONTAL TAB). n This function only effects the operation of the READCOMLINE function. n This character is used in addition to the ascii CR, and LF characters as an EOL marker. n DO NOT set Control-S (the XOFF Character) or Control-Q (the XON Character) as an EOL character. n The third EOL character can effectively be "disabled" by setting it equal to the ascii CR or LF. Example SETEOLCHAR("\x00") equals 0 -or- SETEOLCHAR("\f") equals 12 Cell references may also be used as arguments, as with all of the XLRS232 functions. Related Function Function Description READCOMLINE Returns a line (up to the EOL) of data from the serial line. COMTIMEOUT Returns the previous time-out value. Syntax COMTIMEOUT(Time) Time the number of milliseconds to wait before timing-out on an operation. Remarks n This function sets the time-out period for all read and write operations. n The default period is 2000 milliseconds (2 seconds). n Approximately 49.7 days after Windows 3.1 has started the tick-counter rolls- over (it is a 32-bit unsigned integer, counting in milliseconds). When this happens an error may occur in any one of the functions that waits for a time-out. The solution is to re-start Windows in shorter than 49-day increments. (i.e. please don't use this software to control missile-launchers or anything of the sort!). n If the value of Time is less than or equal to zero the current time-out period is returned. Example COMTIMEOUT(1000) equals 2000 (the 1st time called) -followed by- COMTIMEOUT(0) equals 1000 -followed by- COMTIMEOUT(500) equals 1000 Cell references may also be used as arguments, as with all of the XLRS232 functions. Related Function Function Description READCOMLINE Returns a line (up to the EOL) of data from the serial line. READTOFILE Read data directly into a file. READCOM Returns a string of data from the serial line. SENDFROMFILE Send data directly from a file. SENDCOM Send data to the serial port. FLUSHINQUE Returns zero if successful, otherwise an error occurred. Syntax FLUSHINQUE(Device_id) Device_id is a the value returned by the OPENMODE function. Remarks n This function clears all characters from the input buffer. Example FLUSHINQUE(A1) should equal 0 where A1 is the cell in which OPENMODE() was called. Related Function Function Description FLUSHOUTQUE Flush (clear) the output buffer. FLUSHOUTQUE Returns zero if successful, otherwise an error occurred. Syntax FLUSHOUTQUE(Device_id) Device_id is a the value returned by the OPENMODE function. Remarks n This function clears all characters from the output buffer. Example FLUSHOUTQUE(A1) should equal 0 where A1 is the cell in which OPENMODE() was called. Related Function Function Description FLUSHINQUE Flush (clear) the input buffer. CBINQUE Returns the number of bytes in the input buffer. Syntax CBINQUE(Device_id) Device_id is a the value returned by the OPENMODE function. Remarks n This function returns the number of bytes in the input buffer. The maximum value is currently 256. Example CBINQUE(A1) might equal 14 where A1 is the cell in which OPENMODE() was called. Related Function Function Description CBOUTQUE Returns the number of bytes in the output buffer. CBOUTQUE Returns the number of bytes in the output buffer. Syntax CBOUTQUE(Device_id) Device_id is a the value returned by the OPENMODE function. Remarks n This function returns the number of bytes in the output buffer. The maximum value is currently 256. Example CBOUTQUE(A1) might equal 25 where A1 is the cell in which OPENMODE() was called. Related Function Function Description CBINQUE Returns the number of bytes in the input buffer. REGISTRATION The basic registration fee for a single user version of the XLRS232 is US$15. If you register your copy of XLRS232 you will receive a disk with the first release version of XLRS232 incorporating all bug reports (and some enhancements) received before Oct. 1, 1994. Checks or Mail Orders should be made out to Albert J. Fahey and sent to: LITTLE MAN Software 260 South Lake Avenue Box 225 Pasadena, CA 91101 You should fill out the following order form and send it along with your check or Mail Order to the above address. O R D E R F O R M for XLRS232 version 1.0 Software Ship to: Bill to: $ License to use XLRS232 for users. TOTAL: (US) MEDIA SIZE: (Check one) * 5.25" disk with the latest version of XLRS232 * 3.5" disk with the latest version of XLRS232 Your e-mail address (optional - print clearly): Date sent [______________] Expected delivery Date [_______________]