Tutorial: Creating Macros for Streaming Data

Article
04/23/2021

In this tutorial, you learn how to:

Create modules for organizing macros
Declare reference functions
Create macros for saving trials

Prerequisites

Sensor data previously streamed into Data In page of Excel
Trial data table created and structured
Developer ribbon tab enabled

Create Modules in VBA

Select Developer > Visual Basic to open Visual Basic for Excel
Select Insert > Module to add a module for storing macros
In the Properties tab, rename Module1 as desired. This will store your references.
Repeat steps 2 and 3, also renaming as desired. This will store your active macros.

Declare Reference Functions

In the Project window, select the module desired for your references.
In the code window, start by declaring Option Explicit.
Below this, define the sheet name where your table is stored.

Repeat step 3 for the name of your table.

 Option Explicit
 Public Const sheetname = "Backend"
 Public Const tblname = "Tbl_Backend"
 Public Const sensorcount = 2

Use the Function command to create a shorthand reference for your table.

Function BT_tbl() as ListObject
        Set BT_tbl = Sheets(sheetname).ListObjects(tblname)
End Function

Use the Function command to create a shorthand reference for the streaming columns of each sensor.

Function Live_CH(snum As Integer) As Range
        Set Live_CH = BT_tbl.ListColumns("CH" & snum).DataBodyRange
End Function

Use the Function command to create a shorthand reference for the trial columns each sensor.

Function Trial_CH(snum As Integer, tnum As Integer) As Range
        Set Trial_CH = BT_tbl.ListColumns("CH" & snum & "_T" & tnum).DataBodyRange
End Function

These functions allow the user to easily reference objects and ranges in the workbook, making the active macros substantially easier to write and understand.

Create Macros for Saving Trials

In the Project window, select the module desired for your references.
In the code window, start by declaring the name for your save trial macro, using a sub.vba
```
Sub SaveTrial()

End Sub
```
To allow the macro to understand which trial to reference based on the clicked button, insert the following code into this sub
```
Sub SaveTrial()
        Dim tnum As Integer
        tnum = Right(ActiveSheet.Shapes(Application.Caller).Name,1)
End Sub
```

Finally, for each sensor, add a line of code to make the desired trial column equal to its corresponding streaming column.

Sub SaveTrial()
        Dim Num As Integer
        Num = Right(ActiveSheet.Shapes(Application.Caller ).Name,1)
        References.BT_CH1_Trial(Num).Value = References.BT_CH1.Value
        References.BT_CH2_Trial(Num).Value = References.BT_CH2.Value
End Sub

With this macro written, the user can now assign the macros to objects.

Assign Macros to Objects

Go to the Excel workbook, to the worksheet that contains your table and named objects.
Select all trial buttons by holding SHIFT and selecting each object.
Open the right-click menu on these objects and select Assign Macro...
Select the SaveTrial macro.

When clicked, these buttons will now trigger the SaveTrial macro, copying data from the live data columns to the desired trial columns.

Share via