Build and Integrate Spectral Analysis Functions

Overview

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

The example creates an Excel add-in that performs a fast Fourier transform (FFT) on an input data set located in a designated worksheet range. The function returns the FFT results, an array of frequency points, and the power spectral density of the input data. It places these results into ranges you indicate in the current worksheet. You can also optionally plot the power spectral density.

You develop the function so that you can invoke it from the Excel Tools menu and can select input and output ranges through a GUI.

Creating the add-in requires four basic steps:

Build a standalone COM component from the MATLAB^® code.
Implement the necessary VBA code to collect input and dispatch the calls to your component.
Create the GUI.
Create an Excel add-in and package all necessary components for application deployment.

Building the Component

Your component will have 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, but also plots the input data and the power spectral density in a MATLAB Figure window

Note

The MATLAB code for these two functions resides in two MATLAB files, computefft.m and plotfft.m.

The code for computefft.m:

function [fftdata, freq, powerspect] = 
                               computefft(data, interval)
    if (isempty(data))
        fftdata = [];
        freq = [];
        powerspect = [];
        return;
    end
    if (interval <= 0)
        error('Sampling interval must be greater than zero');
        return;
    end
    fftdata = fft(data);
    freq = (0:length(fftdata)-1)/(length(fftdata)*interval);
    powerspect = abs(fftdata)/(sqrt(length(fftdata)));

The code for plotfft.m.m:

function [fftdata, freq, powerspect] = plotfft(data, interval)
    [fftdata, freq, powerspect] = computefft(data, interval);
    len = length(fftdata);
    if (len <= 0)
        return;
    end
    t = 0:interval:(len-1)*interval;
    subplot(2,1,1), plot(t, data)
    xlabel('Time'), grid on
    title('Time domain signal')
    subplot(2,1,2), plot(freq(1:len/2), powerspect(1:len/2))
    xlabel('Frequency (Hz)'), grid on
    title('Power spectral density')

Build the COM component using the Library Compiler app and the following settings.To proceed with the actual building of the component:

Setting	Value
Component name	`Fourier`
Class name	`Fourier`
Project folder	The name of your work folder followed by the component name
Show verbose output	Selected

See the instructions in Package Excel Add-In with Library Compiler App for more information.

Where Is the Example Code?

See Example File Copying for information about accessing the example code from within the product.

Integrate the Component Using VBA

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

Note

To use Fourier.xla directly in the folder xlspectral (see Example File Copying) add references to Fourier 1.0 Type Library and MWComUtil 7.X Type Library.

Select the Libraries

To open Excel and select the libraries you need to develop the add-in:

Start Excel on your system.
From the Excel main menu, select Tools > Macro > Visual Basic Editor.
When the Visual Basic Editor starts, select Tools > References to open the Project References dialog box.
Select Fourier 1.0 Type Library and MWComUtil 7.x Type Library from the list.

Create the Main VB Code Module for the Application. The add-in requires some initialization code and some global variables to hold the application's state between function invocations. To achieve this, implement a Visual Basic code module to manage these tasks:

Right-click the VBAProject item in the project window and select Insert > Module.
A new module appears under Modules in the VBA Project.
In the module's property page, set the Name property to FourierMain.

Enter the following code in the FourierMain module:

'
' 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.Fourierclass
    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 the Visual Basic Form

The next step in the integration process develops a user interface for your add-in using the Visual Basic Editor. To create a new user form and populate it with the necessary controls:

Right-click VBAProject in the VBA project window and select Insert > UserForm.
A new form appears under Forms in the VBA project window.
In the form's property page, set the Name property to frmFourier and the Caption property to Spectral Analysis.

Add a series of controls to the blank form to complete the dialog box, as summarized in the following table:

Controls Needed for Spectral Analysis Example

Control Type	Control Name	Properties	Purpose
`CheckBox`	`chkPlot`	Caption = `Plot time domain signal and power spectral density`	Plots input data and power spectral density.
`CommandButton`	`btnOK`	Caption = `OK` Default = True	Executes the function and dismisses the dialog box.
`CommandButton`	`btnCancel`	Caption = `Cancel` Cancel = True	Dismisses the dialog box without executing the function.
`Frame`	`Frame1`	Caption = `Input Data`	Groups all input controls.
`Frame`	`Frame2`	Caption = `Output Data`	Groups all output controls.
`Label`	`Label1`	Caption = `Input Data:`	Labels the `RefEdit` for input data.
`TextBox`	`edtSample`	Not applicable	Not applicable
`Label`	`Label2`	Caption = `Sampling Interval`	Labels the `TextBox` for sampling interval.
`Label`	`Label3`	Caption = `Frequency:`	Labels the `RefEdit` for frequency output.
`Label`	`Label4`	Caption = `FFT - Real Part:`	Labels the `RefEdit` for real part of FFT.
`Label`	`Label5`	Caption = `FFT - Imaginary Part:`	Labels the `RefEdit` for imaginary part of FFT.
`Label`	`Label6`	Caption = `Power Spectral Density`	Labels the `RefEdit` for power spectral density.
`RefEdit`	`refedtInput`	Not applicable	Selects range for input data.
`RefEdit`	`refedtFreq`	Not applicable	Selects output range for frequency points.
`RefEdit`	`refedtReal`	Not applicable	Selects output range for real part of FFT of input data.
`RefEdit`	`refedtImag`	Not applicable	Selects output range for imaginary part of FFT of input data.
`RefEdit`	`refedtPowSpect`	Not applicable	Selects output range for power spectral density of input data.

When the form and controls are complete, right-click the form and select View Code.
The following code listing shows the code to implement. Notice that this code references the control and variable names listed in Controls Needed for Spectral Analysis Example. 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 the Spectral Analysis Menu Item to Excel

The last step in the integration process adds a menu item to Excel so that you can open the tool from the Excel Tools menu. To do this, add event handlers for the workbook's AddinInstall and AddinUninstall events that install and uninstall menu items. The menu item calls the LoadFourier function in the FourierMain module.

To implement the menu item:

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

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

Save the add-in into the <project-folder>\for_testing folder that Library Compiler created when building the project.
<project-folder> refers to the project folder that Library Compiler used to save the Fourier project.
Name the add-in Spectral Analysis.
1. From the Excel main menu, select File > Properties.
2. When the Workbook Properties dialog box appears, click the Summary tab, and enter Spectral Analysis as the workbook title.
3. Click OK to save the edits.
4. From the Excel main menu, select File > Save As.
5. When the Save As dialog box appears, select Microsoft Excel Add-In (*.xla) as the file type, and browse to <project-folder>\for_testing.
6. Enter Fourier.xla as the file name and click Save 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. In this example you will create a data representation of a signal containing two distinct components and add to it a random component. This data along with the output will be stored in columns of an Excel worksheet, and you will plot the time-domain signal along with the power spectral density.

Create the Test Problem

Follow these steps to create the test problem:

Start a new session of Excel with a blank workbook.
From the main menu, select Tools > Add-Ins.
When the Add-Ins dialog box appears, click Browse.
Browse to the <project-folder>\for_testing folder, select Fourier.xla, and click OK.
The Spectral Analysis add-in appears in the available Add-Ins list and is selected.
Click OK to load the add-in.

This add-in installs a menu item under the Excel Tools menu. You can display the Spectral Analysis GUI by selecting Tools > Spectral Analysis. Before invoking the add-in, create some data, in this case a signal with components at 15 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.

Create the Data

To create the data:

Enter 0 for cell A1 in the current worksheet.
Click cell A2 and type the formula "= A1 + 0.01".
Click and hold the lower-right corner of cell A2 and drag the formula down the column to cell A1001. This procedure fills the range A1:A1001 with the interval 0 to 10 incremented by 0.01.
Click cell B1 and type the following formula
"= SIN(2*PI()*15*A1) + SIN(2*PI()*40*A1) + RAND()"
Repeat the drag procedure to copy this formula to all cells in the range B1:B1001.

Run the Test

Using the column of data (column B), test the add-in as follows:

Select Tools > Spectral Analysis from the main menu.
Click the Input Data box.
Select the B1:B1001 range from the worksheet, or type this address into the Input Data field.
In the Sampling Interval field, type 0.01.
Select Plot time domain signal and power spectral density.
Enter C1:C1001 for frequency output, and likewise enter D1:D1001, E1:E1001, and F1:F1001 for the FFT real and imaginary parts, and spectral density.
Click OK to run the analysis.

The next figure shows the output.

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

Package and Distribute the Add-In

As a final step, package the add-in, the COM component, and all supporting libraries into a self-extracting executable. This package can be installed onto other computers that need to use the Spectral Analysis add-in.

On the Main File section of the tool strip choose one of the two options described in the following table.

Option	What Does This Option Do?	When Should I Use This Option?
Runtime downloaded from web	The MATLAB Runtime installer downloads the MATLAB Runtime from the MathWorks website.	You have a large number of end users who deploy applications frequently Your users have internet/network access Resources such as disk space, performance, and processing time are significant concerns for your organization
Runtime included in package	The MATLAB Runtime is included in the generated installer. The MATLAB Runtime installer uses the included MATLAB Runtime.	You have a limited number of end users who deploy a small number of applications at sporadic intervals Your users have no internet/network access Resources such as disk space, performance, and processing time are not significant concerns Note Distributing the MATLAB Runtime with the application requires more resources.

Add others files you feel may be useful to end users.
To package additional files or folders, add them to the Files installed for your end user field. See Specify Files to Install with Application.
Click Package.

Install the Add-In

To install this add-in onto another computer, copy the Fourier_pkg.exe package to that machine, run it from a command prompt, and follow the instructions in the readme.txt file that is automatically generated with your packaged output.

Was this topic helpful?

Documentation