Main Content

Implement User Interface Using Visual Basic Form Controls

Overview

This example illustrates the creation of a comprehensive Excel® application to perform spectral analysis. It requires knowledge of Visual Basic® forms and controls, and Excel workbook events. See the VBA documentation for a complete discussion of these topics.

You create an Excel add-in using functions that perform a fast Fourier transform (FFT) on an input data set and plot the power spectral density. The function returns the FFT results, an array of frequency points, and the power spectral density of the input data. You then create a custom VBA macro with form controls to designate input and output ranges in the current worksheet. Invoke the spectral analysis application from the Excel Tools menu.

To create the add-in:

  1. Build an Excel add-in from MATLAB® code.

  2. Implement the necessary VBA code to collect input and dispatch the calls to your component. This code also creates a menu item for the application in Excel.

  3. Create the UI using form controls.

  4. Save the finished Excel add-in and package all necessary components for application deployment.

Before creating Excel add-ins, verify that you have met all of the MATLAB Compiler™ Excel target requirements. For details, see Excel Target Requirements and Limitations for MATLAB Compiler.

Build Excel Add-In

  1. Save the path to the xlspectral folder that ships with MATLAB.

    copyfile(fullfile(matlabroot,'toolbox','matlabxl','examples','xlspectral'))
  2. Examine the MATLAB functions computefft.m and plotfft.m.

     computefft.m

     plotfft.m

  3. Build the Excel add--in with the Library Compiler app or compiler.build.excelAddIn.

    Use the following information for your project:

    Project NameFourier
    Class NameFourier
    File to compileplotfft.m

    Note

    In this example, the application that uses the fourier class does not call computefft directly. The computefft method is required only by the plotfft method. Thus, you do not need to manually add the computefft function to the package, as the compiler automatically includes it during dependency analysis.

    For example, if you are using compiler.build.excelAddIn, type:

    buildResults = compiler.build.excelAddIn('plotfft.m', ...
    'AddInName','Fourier', ...
    'ClassName','Fourier', ...
    'GenerateVisualBasicFile','on', ...
    'Verbose','on');

    Your component has one class with two methods:

    • computefft — Computes the FFT and power spectral density of the input data, and computes a vector of frequency points based on the length of the data entered and the sampling interval.

    • plotfft — Performs the same operations as computefft, and also plots the input data and the power spectral density in a MATLAB figure window.

Implement VBA code

Having built your component, implement the necessary VBA code to integrate it into Excel.

Note

The shipped Excel add-in Fourier.xla in the xlspectral folder already contains the following VBA code. To use the shipped add-in, open it in Excel, add references to Fourier 1.0 Type Library and MWComUtil X.X Type Library in the Excel Visual Basic Editor, and save the add-in. You can then skip to Test the Add-In.

  1. Start Excel.

  2. Open your generated Excel add-in.

  3. From the Developer tab, click Visual Basic, or press ALT+F11 to open the Visual Basic Editor. In older versions of Excel, it may be located under Tools > Macro > Visual Basic Editor.

  4. In the Visual Basic Editor, select Tools > References to open the Project References dialog box.

  5. Select the Fourier 1.0 Type Library entry that corresponds to your generated Excel add-in and MWComUtil X.X Type Library that corresponds to your version of MATLAB or MATLAB Runtime. Click OK to add the references.

Create Main VB Code Module

The add-in requires initialization code and global variables to hold the application state between function invocations. Implement a Visual Basic code module to manage these tasks.

  1. If there is not already a module present under Modules, right-click the VBAProject item in the project window and select Insert > Module.

    A new module appears under Modules in the VBA Project.

  2. In the module property page, change the Name property from Module1 to FourierMain. You may need to show the property window by pressing F4.

  3. Enter the following code in the FourierMain module and close the code window.

    '
    ' FourierMain - Main module stores global state of controls
    ' and provides initialization code
    '
    Public theFourier As Fourier.Fourier 'Global instance of Fourier object
    Public theFFTData As MWComplex   'Global instance of MWComplex to accept FFT
    Public InputData As Range        'Input data range
    Public Interval As Double        'Sampling interval
    Public Frequency As Range        'Output frequency data range
    Public PowerSpect As Range       'Output power spectral density range
    Public bPlot As Boolean          'Holds the state of plot flag
    Public theUtil As MWUtil         'Global instance of MWUtil object
    Public bInitialized As Boolean   'Module-is-initialized flag
    
    Private Sub LoadFourier()
    'Initializes globals and Loads the Spectral Analysis form
        Dim MainForm As frmFourier
        On Error GoTo Handle_Error
        Call InitApp
        Set MainForm = New frmFourier
        Call MainForm.Show
        Exit Sub
    Handle_Error:
        MsgBox (Err.Description)
    End Sub
    
    Private Sub InitApp()
    'Initializes classes and libraries. Executes once
    'for a given session of Excel
        If bInitialized Then Exit Sub
        On Error GoTo Handle_Error
        If theUtil Is Nothing Then
            Set theUtil = New MWUtil
            Call theUtil.MWInitApplication(Application)
        End If
        If theFourier Is Nothing Then
            Set theFourier = New Fourier.Fourier
        End If
        If theFFTData Is Nothing Then
            Set theFFTData = New MWComplex
        End If
        bInitialized = True
        Exit Sub
    Handle_Error:
        MsgBox (Err.Description)
    End Sub

Create Visual Basic Form

Develop a user interface for your add-in using the Visual Basic Editor.

  1. Right-click VBAProject in the VBA project window and select Insert and then UserForm.

    A new form appears under Forms in the VBA project window.

  2. In the form property page, set the Name property to frmFourier and the Caption property to Spectral Analysis.

  3. Add the following controls to the blank form:

    Controls for Spectral Analysis

    Control TypeControl NamePropertiesPurpose

    CheckBox

    chkPlot

    Caption = Plot time domain signal and power spectral density

    Plot input data and power spectral density.

    CommandButton

    btnOK

    Caption = OK

    Default = True

    Execute the function and dismiss the dialog box.

    CommandButton

    btnCancel

    Caption = Cancel

    Cancel = True

    Dismiss the dialog box without executing the function.

    Frame

    Frame1

    Caption = Input Data

    Group all input controls.

    Frame

    Frame2

    Caption = Output Data

    Group all output controls.

    Label

    Label1

    Caption = Input Data:

    Label the RefEdit for input data.

    RefEdit

    refedtInput

     

    Select range for input data.

    Label

    Label2

    Caption = Sampling Interval:

    Label the TextBox for sampling interval.

    TextBoxedtSample Select sampling interval.

    Label

    Label3

    Caption = Frequency:

    Label the RefEdit for frequency output.

    RefEdit

    refedtFreq

     

    Select output range for frequency points.

    Label

    Label4

    Caption = FFT - Real Part:

    Label the RefEdit for real part of FFT.

    RefEdit

    refedtReal

     

    Select output range for real part of FFT of input data.

    Label

    Label5

    Caption = FFT - Imaginary Part:

    Label the RefEdit for imaginary part of FFT.

    RefEdit

    refedtImag

     

    Select output range for imaginary part of FFT of input data.

    Label

    Label6

    Caption = Power Spectral Density

    Label the RefEdit for power spectral density.

    RefEdit

    refedtPowSpect

     

    Select output range for power spectral density of input data.

    Your form should look similar to the following image. The text in the boxes is for your reference only.

    Spectral Analysis form

  4. When the form and controls are complete, right-click the form and select View code.

    Enter the following code in the form code window. If you used different names for any of the controls or any global variable, change this code to reflect those differences.

'
'frmFourier Event handlers
'
Private Sub UserForm_Activate()
'UserForm Activate event handler. This function gets called before
'showing the form, and initializes all controls with values stored
'in global variables.
    On Error GoTo Handle_Error
    If theFourier Is Nothing Or theFFTData Is Nothing Then Exit Sub
    'Initialize controls with current state
    If Not InputData Is Nothing Then
        refedtInput.Text = InputData.Address
    End If
    edtSample.Text = Format(Interval)
    If Not Frequency Is Nothing Then
        refedtFreq.Text = Frequency.Address
    End If
    If Not IsEmpty (theFFTData.Real) Then
    If IsObject(theFFTData.Real) And TypeOf theFFTData.Real Is Range Then
            refedtReal.Text = theFFTData.Real.Address
        End If
    End If
    If Not IsEmpty (theFFTData.Imag) Then
    If IsObject(theFFTData.Imag) And TypeOf theFFTData.Imag Is Range Then
            refedtImag.Text = theFFTData.Imag.Address
        End If
    End If
    If Not PowerSpect Is Nothing Then
        refedtPowSpect.Text = PowerSpect.Address
    End If
    chkPlot.Value = bPlot
    Exit Sub
Handle_Error:
    MsgBox (Err.Description)
End Sub

Private Sub btnCancel_Click()
'Cancel button click event handler. Exits form without computing fft
'or updating variables.
    Unload Me
End Sub
Private Sub btnOK_Click()
'OK button click event handler. Updates state of all variables from controls
'and executes the computefft or plotfft method.
    Dim R As Range
    
    If theFourier Is Nothing Or theFFTData Is Nothing Then GoTo Exit_Form
    On Error Resume Next
    'Process inputs
    Set R = Range(refedtInput.Text)
    If Err <> 0 Then
        MsgBox ("Invalid range entered for Input Data")
        Exit Sub
    End If
    Set InputData = R
    Interval = CDbl(edtSample.Text)
    If Err <> 0 Or Interval <= 0 Then
        MsgBox ("Sampling interval must be greater than zero")
        Exit Sub
    End If
    'Process Outputs
    Set R = Range(refedtFreq.Text)
    If Err = 0 Then
        Set Frequency = R
    End If
    Set R = Range(refedtReal.Text)
    If Err = 0 Then
        theFFTData.Real = R
    End If
    Set R = Range(refedtImag.Text)
    If Err = 0 Then
        theFFTData.Imag = R
    End If
    Set R = Range(refedtPowSpect.Text)
    If Err = 0 Then
        Set PowerSpect = R
    End If
    bPlot = chkPlot.Value
    'Compute the fft and optionally plot power spectral density
    If bPlot Then
        Call theFourier.plotfft(3, theFFTData, Frequency, PowerSpect, _ 
     InputData, Interval)
    Else
        Call theFourier.computefft(3, theFFTData, Frequency, PowerSpect, _ 
     InputData, Interval)
    End If
    GoTo Exit_Form
Handle_Error:
    MsgBox (Err.Description)
Exit_Form:
    Unload Me
End Sub

Add Event Handlers to Workbook

  1. Right-click the ThisWorkbook item in the VBA project window and select View code.

  2. Place the following code into ThisWorkbook.

    Private Sub Workbook_AddinInstall()
    'Called when Addin is installed
        Call AddFourierMenuItem
    End Sub
    
    Private Sub Workbook_AddinUninstall()
    'Called when Addin is uninstalled
        Call RemoveFourierMenuItem
    End Sub
    
    Private Sub AddFourierMenuItem()
        Dim ToolsMenu As CommandBarPopup
        Dim NewMenuItem As CommandBarButton
        
        'Remove if already exists
        Call RemoveFourierMenuItem
        'Find Tools menu
        Set ToolsMenu = Application.CommandBars(1).FindControl(ID:=30007)
        If ToolsMenu Is Nothing Then Exit Sub
        'Add Spectral Analysis menu item
        Set NewMenuItem = ToolsMenu.Controls.Add(Type:=msoControlButton)
        NewMenuItem.Caption = "Spectral Analysis..."
        NewMenuItem.OnAction = "LoadFourier"
    End Sub
    
    Private Sub RemoveFourierMenuItem() 
    Dim CmdBar As CommandBar 
    Dim Ctrl As CommandBarControl 
    On Error Resume Next 
    'Find tools menu and remove Spectral Analysis menu item 
    Set CmdBar = Application.CommandBars(1) 
    Set Ctrl = CmdBar.FindControl(ID:=30007) 
    Call Ctrl.Controls("Spectral Analysis...").Delete 
    End Sub

    The code adds event handlers for the workbook events AddinInstall and AddinUninstall that install and uninstall menu items. The menu item calls the LoadFourier function in the FourierMain module.

  3. When you are finished, click the save icon or press CTRL + S to save the add-in.

Test the Add-In

Before distributing the add-in, test it with a sample problem. Spectral analysis is commonly used to find the frequency components of a signal buried in a noisy time domain signal. Create a data representation of a signal containing two distinct components and add to it a random component. This data, along with the output, is stored in columns of an Excel worksheet, and you plot the time-domain signal along with the power spectral density.

Create Test Problem

  1. Start a new session of Excel with a blank workbook.

  2. From the Developer tab, select Excel Add-Ins.

  3. In the Add-Ins dialog box, click Browse.

  4. Browse to Fourier.xla and click OK.

    The Spectral Analysis add-in appears in the available Add-Ins list with a checked box.

  5. Click OK to load the add-in.

This add-in installs a menu item under the Excel Add-ins menu.

Create Data

Before invoking the add-in, create test data that contains a signal with components at 15 Hz and 40 Hz. Sample the signal for 10 seconds at a sampling rate of 0.01 s. Put the time points into column A and the signal points into column B.

  1. Enter 0 into cell A1 in the current worksheet.

  2. Press F5 or CTRL+G to launch the Go To dialog. Add the reference A2:A1001 and click OK to select the cells.

  3. Enter the formula = A1 + 0.01, then press CTRL+Enter to apply the formula to the selected cells.

    This procedure fills the range A1:A1001 with the interval 0–10 incremented by 0.01.

  4. Repeat the Go To procedure to enter the following formula into each cell in the range B1:B1001:

    = SIN(2*PI()*15*A1) + SIN(2*PI()*40*A1) + RAND()

Run Test

Using the column of data (column B), test the add-in.

  1. To display the Spectral Analysis UI, select Add-ins > Spectral Analysis.

  2. Click the Input Data box.

  3. Select the B1:B1001 range from the worksheet, or type this range into the Input Data field.

  4. In the Sampling Interval field, type 0.01.

  5. Select Plot time domain signal and power spectral density.

  6. Enter C1:C1001 for frequency output, and likewise enter D1:D1001, E1:E1001, and F1:F1001 for the FFT real part, FFT imaginary part, and spectral density.

  7. Click OK to run the analysis.

The following figure shows an example of the output.

The power spectral density reveals the two signals at 15 Hz and 40 Hz.