1. Introduction to VBA Visual Basic Applications (VBA) is a long-term goal of Microsoft, aiming to perfectly implement programmable application software. As a universal macro language, it can be shared by all Microsoft programmable applications. Before VBA, applications such as Excel, Word, Access, and Project used their own macro languages for user development. However, each macro language was independent, requiring users to learn it separately, and they were incompatible, preventing interoperability between applications. Having a development tool that can span multiple applications, providing efficient, flexible, and consistent development across products, is crucial. VBA, as a new generation of standard macro language, possesses the aforementioned ability to span multiple applications and control application objects. This allows programmers to learn only a unified standard macro language and then switch to a specific application. Programmers see the same user interface when programming and debugging code, and VBA is compatible with the original application's macro language, protecting users' investment in code and work. With VBA, multiple applications can share a single macro language, saving programmers' learning time and improving the interoperability and calling capabilities between different applications.
2. Serial Communication Program A simple serial communication program typically involves the host computer sending a read (write) or write (read) command to the slave computer, and then waiting for the slave computer's response. Upon receiving the command, the slave computer first verifies the data command. For commands that meet the verification constraints, the slave computer replies with the corresponding data to the host computer. For commands that do not meet the verification constraints, the slave computer either discards the command or replies with an error message. After receiving the response from the slave computer, the host computer first verifies the received reply information. Data that does not meet the verification constraints is handled as an exception, while information that meets the verification constraints is decoded. The decoded information is the valid information obtained by the host computer from the slave computer.
After the host computer sends a read command to the serial port, it needs to wait for the slave computer's response and read the reply information. There are three common ways to wait:
1) After the polling host computer sends a read command to the serial port, it waits for the lower-level computer to respond, and the communication program is in a waiting loop.
Advantages: High speed and low error rate (almost no errors).
Disadvantage: Consumes CPU. (Solution: Use the Sleep() API function).
2) Message-based communication uses the method of responding to WINDOWS messages to read the serial port.
Advantages: Saves CPU and has a low error rate.
Disadvantage: The sending and receiving mechanism is too cumbersome.
3) Timed receiving type: Timed receiving type communication reads data according to a preset timer interval.
Advantages: The sending and receiving mechanism is relatively simple and saves CPU resources.
Disadvantages: The error rate is too high; different hardware requires different communication time parameters; and strict verification calculations are required.
3 EXCEL-VBA serial communication example software: EXCEL.
Hardware: Emerson EC10 PLC.
Function: Control the PLC output relay Y0 via buttons in Excel.
Interface: RUN (run communication), STOP (stop communication), Y0 (Y0 ON/OFF control).
Main program:
Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public com1 As New MSCommLib.MSComm
Public y0Stt As Boolean
Public y0_on As Boolean
Public tmrFlag As Boolean
public tmr As Long
Sub runn()
On Error GoTo ed
com1.Settings = "9600,e,8,1"
If com1.PortOpen = False Then
com1.PortOpen = True
End If
tmr = SetTimer(0, 0, 500, AddressOf ontimer)
Exit Sub
ed:
MsgBox "Serial port opening error!"
End Sub
Sub stopp()
If com1.PortOpen = True Then
com1.PortOpen = False
KillTimer 0, tmr
End If
End Sub
Public Function ontimer()
Dim a(7) As Byte
Dim add As Long
On Error GoTo ed
If tmrFlag = False Then
tmrFlag = True
If y0_on = True Then
y0_on = False
If y0Stt = True Then
a(0) = &H1
a(1) = &H5
a(2) = &H0
a(3) = &H0
a(4) = &HFF
a(5) = &H0
a(6) = &H8C
a(7) = &H3A
com1.Output = a
add = 0
Do
DoEvents
Sleep 10
add = add + 1
If add >= 100 Then
Exit Do
End If
Loop Until com1.InBufferCount >= 8
Else
a(0) = &H1
a(1) = &H5
a(2) = &H0
a(3) = &H0
a(4) = &H0
a(5) = &H0
a(6) = &HCD
a(7) = &HCA
com1.Output = a
add = 0
Do
DoEvents
Sleep 10
add = add + 1
If add >= 100 Then
Exit Do
End If
Loop Until com1.InBufferCount >= 8
End If
End If
End If
tmrFlag = False
Exit Function
ed: MsgBox "Serial port error!"
tmrFlag = False
End Function
Interface program:
Private Sub cmd1_Click()
y0_on = True
y0Stt = Not y0Stt
End Sub
Private Sub cmdRun_Click()
runn
CheckBox1.Value = 1
End Sub
Private Sub cmdStop_Click()
stopp
CheckBox1.Value = 0
End Sub
4. Conclusion As can be seen from the above, VBA has been widely used in Microsoft application software. Therefore, in order to better improve the secondary development function, many configuration software have made efforts to support VBA. The development language of Century Star configuration software is very similar to VBA. It is believed that in future versions, Century Star will fully support VBA, and its secondary development function will reach a new level.
