Main Content

## 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, and Excel workbook events. See the VBA documentation for a complete discussion of these topics.

You create 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 UI.

To create the add-in:

1. Build a standalone COM component from the MATLAB® code.

2. Implement the necessary VBA code to collect input and dispatch the calls to your component.

3. Create the UI.

4. Create an Excel add-in, and package all necessary components for application deployment.

### Building the Component

Your component has one class with two methods:

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

• `plotfft` — Perform the same operations as `computefft`, but also plot the input data and the power spectral density in a MATLAB Figure window.

Note

The MATLAB code for these two functions is 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 these settings:

SettingValue
Component name`Fourier`
Class name`Fourier`
Project folderName of your work folder, followed by the component name
Show verbose outputSelected

For more information, see the instructions in Create Excel Add-In from MATLAB.

#### Where Is the Example Code?

For more information about accessing the example code from within the product ,see Example File Copying.

### 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 should develop the add-in:

1. Start Excel on your system.

2. From the Excel main menu, select Tools > Macro > Visual Basic Editor.

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

4. Select Fourier 1.0 Type Library and MWComUtil 7.x Type Library.

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

1. 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, set the `Name` property to `FourierMain`.

3. 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

Develop 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:

1. Right-click VBAProject in the VBA project window, and select Insert > 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 a series of 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

Dismisse 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.

`TextBox``edtSample`

Not applicable

Not applicable

`Label`

`Label2`

Caption = ```Sampling Interval```

Label the `TextBox` for sampling interval.

`Label`

`Label3`

Caption = `Frequency:`

Label the `RefEdit` for frequency output.

`Label`

`Label4`

Caption = ```FFT - Real Part:```

Label the `RefEdit` for real part of FFT.

`Label`

`Label5`

Caption = ```FFT - Imaginary Part:```

Label the `RefEdit` for imaginary part of FFT.

`Label`

`Label6`

Caption =``` Power Spectral Density```

Label the `RefEdit` for power spectral density.

`RefEdit`

`refedtInput`

Not applicable

Select range for input data.

`RefEdit`

`refedtFreq`

Not applicable

Select output range for frequency points.

`RefEdit`

`refedtReal`

Not applicable

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

`RefEdit`

`refedtImag`

Not applicable

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

`RefEdit`

`refedtPowSpect`

Not applicable

Select output range for power spectral density of input data.

4. 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 for Spectral Analysis. 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

Add 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 events `AddinInstall` and `AddinUninstall` that install and uninstall menu items. The menu item calls the `LoadFourier` function in the `FourierMain` module.

To implement the menu item:

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```
3. Save the add-in into the `<project-folder>\for_testing`.

`<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. In the Workbook Properties dialog box, 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. In the Save As dialog box, 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.

### 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 the Test Problem

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

2. From the main menu, select Tools > Add-Ins.

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

4. 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.

5. Click OK to load the add-in.

This add-in installs a menu item under the Excel Tools menu. To display the Spectral Analysis UI, select Tools > Spectral Analysis. Before invoking the add-in, create some data, in this case 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.

#### Create the Data

1. Enter `0` for cell A1 in the current worksheet.

2. Click cell A2, and type the formula ```"= A1 + 0.01"```.

3. 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–10 incremented by 0.01.

4. 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:

1. Select Tools > Spectral Analysis from the main menu.

2. Click the Input Data box.

3. Select the `B1:B1001` range from the worksheet, or type this address 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 and imaginary parts, and spectral density.

7. Click OK to run the analysis.

The next figure shows the output. The power spectral density reveals the two signals at 15 Hz and 40 Hz.

### Package and Distribute the Add-In

Package the add-in, the COM component, and all supporting libraries into a self-extracting executable. This package can be installed on other computers that use the `Spectral Analysis` add-in.

1. On the Main File section of the toolstrip, choose one of these two options.

OptionWhat Does This Option Do?When Should I Use This Option?
Runtime downloaded from webThe MATLAB Runtime installer downloads the MATLAB Runtime from the MathWorks website.
• You have many end users who deploy applications frequently

• Your users have Internet access

• Resources such as disk space, performance, and processing time are significant concerns for your organization

Runtime included in packageThe MATLAB Runtime is included in the generated installer, which uses the included MATLAB Runtime.
• You have a limited number of end users who deploy a few applications at sporadic intervals

• Your users have no Internet 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.

2. Add others files that would 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.

3. Click Package.

### Install the Add-In

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

## Support Get trial now