VBA通过COM端口控制Adafruit塔灯的命令发送异常问题

问题描述 #

我尝试用最新版MS Access的VBA控制从Adafruit购买的USB塔灯（型号#5125），设备已连接至Windows电脑的COM5端口。我使用两款通信模块发送命令，虽能成功连接端口并发送数据，但塔灯响应不正确——可实现闪烁和变色，但效果不符合预期，疑似命令出现混乱或拼接错误。我已尝试使用VBA的Hex函数、常量及直接带引号的命令形式，但问题仍存在。以下是官方提供的Python示例代码及我编写的VBA代码，恳请帮忙排查问题或指明方向。

官方Python示例代码 #

import serial
import time

serialPort = 'COM57'  # 改为塔灯的串口/COM端口
# serialPort = '/dev/USBserial0'  # Mac/Linux系统下为/dev路径
baudRate = 9600

RED_ON = 0x11
RED_OFF = 0x21
RED_BLINK = 0x41

YELLOW_ON= 0x12
YELLOW_OFF = 0x22
YELLOW_BLINK = 0x42

GREEN_ON = 0x14
GREEN_OFF = 0x24
GREEN_BLINK = 0x44

BUZZER_ON = 0x18
BUZZER_OFF = 0x28
BUZZER_BLINK = 0x48

def sendCommand(serialport, cmd):
    serialport.write(bytes([cmd]))

if __name__ == '__main__':
    mSerial = serial.Serial(serialPort, baudRate)

    # 清除旧状态
    sendCommand(mSerial, BUZZER_OFF)
    sendCommand(mSerial, RED_OFF)
    sendCommand(mSerial, YELLOW_OFF)
    sendCommand(mSerial, GREEN_OFF)

    # 依次点亮各LED
    sendCommand(mSerial, RED_ON)
    time.sleep(0.5)
    sendCommand(mSerial, RED_OFF)

    sendCommand(mSerial, YELLOW_ON)
    time.sleep(0.5)
    sendCommand(mSerial, YELLOW_OFF)

    sendCommand(mSerial, GREEN_ON)
    time.sleep(0.5)
    sendCommand(mSerial, GREEN_OFF)

    # 蜂鸣器响
    sendCommand(mSerial, BUZZER_ON)
    time.sleep(0.1)
    sendCommand(mSerial, BUZZER_OFF)

    # 使用内置闪烁模式
    sendCommand(mSerial, RED_BLINK)
    time.sleep(3)
    sendCommand(mSerial, RED_OFF)

    sendCommand(mSerial, YELLOW_BLINK)
    time.sleep(3)
    sendCommand(mSerial, YELLOW_OFF)

    sendCommand(mSerial, GREEN_BLINK)
    time.sleep(3)
    sendCommand(mSerial, GREEN_OFF)

    # 重置设备状态
    sendCommand(mSerial, BUZZER_OFF)
    sendCommand(mSerial, RED_OFF)
    sendCommand(mSerial, YELLOW_OFF)
    sendCommand(mSerial, GREEN_OFF)
    mSerial.close()

我的VBA代码 #

Private Sub Command0_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strError  As String
Dim strData   As String

Dim RED_ON As String
RED_ON = Hex(11)  '"0x11"
Dim RED_OFF As String
RED_OFF = Hex(21) '"0x21"
Const RED_BLINK = "0x41"

Const YELLOW_ON = "0x12"
Dim YELLOW_OFF As String
YELLOW_OFF = Hex(22) '0x22
'YELLOW_BLINK = 0x42

'GREEN_ON = 0x14
Dim GREEN_OFF As String
GREEN_OFF = Hex(24) '0x24
Dim GREEN_BLINK As String
GREEN_BLINK = Hex(44)

'BUZZER_ON = 0x18
'BUZZER_OFF = 0x28
'BUZZER_BLINK = 0x48
' Initialize Communications
lngStatus = CommOpen(5, "COM" & CStr(5), "baud=9600 parity=N data=8 stop=1")
If lngStatus <> 0 Then
' Handle error.
    lngStatus = CommGetError(strError)
    MsgBox "COM Error: " & strError
End If

' Set modem control lines.
lngStatus = CommSetLine(5, LINE_RTS, True)
lngStatus = CommSetLine(5, LINE_DTR, True)

' Write data to serial port.
'MsgBox CommWrite(5, Hex(12))  'Blinking Orange
'MsgBox CommWrite(5, Hex(42))  'blinking red
'MsgBox CommWrite(5, Hex(22))  'does nothing
'MsgBox CommWrite(5, Hex(44))  'blinking yellow
'MsgBox CommWrite(5, Hex(10))  'blinking red
'MsgBox CommWrite(5, Hex(18))   'nothing
'MsgBox CommWrite(5, Hex(21))   'blinking green
MsgBox CommWrite(5, Hex(24))    'does nothing

CommClose (5)
End Sub

问题排查与解决方向 #

核心问题：命令数据类型不匹配 #

Python代码中，命令是十六进制整数（如0x11），通过bytes([cmd])转换为单字节原始数据发送给设备；但你的VBA代码错误地将命令转换为十六进制字符串（如Hex(11)返回字符串"11"），串口实际发送的是字符串的ASCII码（"1"对应0x31，重复发送两次），而非设备需要的单字节指令，导致设备接收到错误指令，出现响应混乱。

修正步骤 #

1. 定义正确的命令常量
   在VBA中用十六进制整数定义指令，而非字符串：

   ' 定义字节型常量，与Python中的十六进制值一一对应
   Const RED_ON As Byte = &H11
   Const RED_OFF As Byte = &H21
   Const RED_BLINK As Byte = &H41
   Const YELLOW_ON As Byte = &H12
   Const YELLOW_OFF As Byte = &H22
   Const YELLOW_BLINK As Byte = &H42
   Const GREEN_ON As Byte = &H14
   Const GREEN_OFF As Byte = &H24
   Const GREEN_BLINK As Byte = &H44
   Const BUZZER_ON As Byte = &H18
   Const BUZZER_OFF As Byte = &H28
   Const BUZZER_BLINK As Byte = &H48
   

2. 发送原始字节数据
   确保CommWrite发送的是原始字节而非字符串。如果你的通信模块的CommWrite函数接收字符串参数，用Chr$()将字节转换为对应ASCII字符发送：

   ' 示例：发送RED_ON指令
   lngStatus = CommWrite(5, Chr$(RED_ON))
   

3. 初始化设备状态
   参考Python代码，先发送所有关闭指令，清除设备残留状态：

   CommWrite(5, Chr$(BUZZER_OFF))
   CommWrite(5, Chr$(RED_OFF))
   CommWrite(5, Chr$(YELLOW_OFF))
   CommWrite(5, Chr$(GREEN_OFF))
   

4. 添加指令延迟
   在切换指令之间添加短暂延迟，确保设备有足够时间处理。需要先声明Sleep函数：

   ' 放在模块顶部声明
   Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
   
   ' 使用示例
   CommWrite(5, Chr$(RED_ON))
   Sleep 500 ' 延迟500毫秒
   CommWrite(5, Chr$(RED_OFF))
   

5. 验证通信模块行为
   确认CommWrite函数的参数要求：如果它需要字节数组而非字符串，需调整为字节数组形式传递指令。

内容的提问来源于stack exchange，提问作者Ira