User Interfaces in Excel and MATAB Kipp Martin University of Chicago Booth School of Business

March 6, 2012

1

Files Excel Files: I

vanillaConeGUI.xlsm

I

hotFudgeGUI.xlsm

MATLAB Files: I

vanillaConeGUI.m

I

vanillaConeGUI.fig

I

simulationDemoGUI.m

I

simulationDemoGUI.fig 2

Outline

GUI Basics Motivation

VBA GUIs GUI Tools Generating the Code Other Issues

MATLAB GUIs

3

GUI Basics Building a GUI in MATLAB is very similar to building one VBA (or any other IDE for that matter). Key Ideas: I

There is a user interface (or user form) that the user interacts with in order to generate the model data

I

There are “tools” that are part of a tool box used to construct the user interface

I

These tools generate stubs or callback functions.

I

When you “click” on things this generate events corresponding to the callback functions. The code in these functions is executed.

Motivation Why a GUI?

I

Make it easy for the user! The user may not know about optimization, simulation, or even much about Excel or MATLAB.

I

Accuracy. By controlling the input into the model we have more accurate data entry. Example – entering the State in a Web form.

I

Speed. It is much faster for the user to cycle through the GUI inputs than figure things out from scratch.

VBA GUI Basics A blank VBA user form. To get a UserForm go into the VBA editor and select Insert → UserForm.

6

VBA GUI Basics

The VBA ToolBox. See the “hammer and wrench” icon to bring up the tool box. It is also available under View in the VBA Toolbar.

7

VBA GUI Basics A VBA user form with tools. (Drag them over from the Toolbox)

8

VBA GUI Basics Here is what the user will see.

9

VBA GUI Basics Here is the VBA property explorer.

10

GUI Tools I

Command Button (VBA) Push Button (MATLAB): clicking on this will cause a procedure or function to execute.

I

TextBox (VBA) Edit Text (MATLAB): used to enter parameter values or show values to the user – used for both input and output.

I

Label (VBA) Static Text (MATLAB): Used to “explain” the other tools to the user. These cannot be used to provide any information to the code.

I

ScrollBar (VBA) Slider (MATLAB): Can select values in a continum between a min and max value.

I

Image (VBA) Axes (MATLAB): Display a graphic. 11

GUI Tools I

ListBox (VBA) Pop-Up Menu (MATLAB): select from a finite set of options.

I

CheckBox (VBA) Check Box (MATLAB): select an option if desired.

I

OptionButton (VBA) Radio Button (MATLAB): select among several options

I

Frame (VBA) Panel (MATLAB) organize a related set of tools or options

GUI Tools Each of the GUI tools has a Properties Window (Inspector). Here is one for VBA. Click on the icon in the User Form to bring it up.

13

GUI Tools Here is one for MATLAB. Click on the icon in the User Form to bring it up.

14

Generating the Code KEY IDEA ONE: For each tool there is an empty stub or callback Sub or function. VBA Private Sub CommandButton1_Click() End Sub MATLAB % --- Executes on button press in pushbutton1. function pushbutton1_Callback(hObject, eventdata, handles) % hObject handle to pushbutton1 % eventdata reserved - to be defined in a %future version of MATLAB % handles structure with handles and %user data (see GUIDATA) 15

Generating the Code KEY IDEA TWO: You fill in the stub or callback with your own code! VBA code for clicking on the Generate Histogram button. numTrials = num_trials_scrollBar.Value numBins = ListBox1.Value ReDim values(numTrials) Dim i As Long For i = 1 To numTrials values(i) = RandNorm(0, 1) Next i Dim chartName As String, fname As String Call CreateHistogram("Histogram", numBins, values) fname = ThisWorkbook.Path & "\temp.gif" Worksheets("Histogram").ChartObjects(1).Chart.Export _ Filename:=fname, FilterName:="GIF" Image1.Picture = LoadPicture(fname)

Generating the Code

KEY IDEA TWO: You fill in the stub or callback with your own code! MATLAB code for clicking on the Generate Histogram button.

function gen_Histogram_Callback(hObject, ... eventdata, handles) hist (randn(1, handles.num_trials), ... handles.number_bins);

Generating the Code

KEY IDEA THREE: The code is executed when you “click” or “change” a tool. This generates an event or callback.

VBA ScrollBar: the following code is generated when you move the scroll bar. Private Sub num_trials_scrollBar_Change() slider_value.Text = num_trials_scrollBar.Value End Sub

18

Generating the Code KEY IDEA THREE: The code is executed when you “click” or “change” a tool. This generates an event or callback. MATLAB Slider: The following code is generated when you move the slider. function num_trials_slider_Callback(hObject, ... eventdata, handles) slider_value = get(hObject,’Value’) handles.num_trials = floor( slider_value) slider_value_Callback(hObject, eventdata, handles); guidata(hObject,handles)

Generating the Code KEY IDEA FOUR: You must get “data” from the tools. Here is how to get the value of the VBA ScrollBar (num trials scrollBar Change is the name of the VBA procedure called on the event) num_trials_scrollBar.Value Here is how to get the value of the MATLAB Slider (num trials slider Callback is the name of the MATLAB function called on the event) slider_value = get(hObject,’Value’)

20

Generating the Code KEY IDEA FOUR: A second example. Here is how to get the value of the ListBox (histogram listbox Click is the name of the VBA procedure called on the event) Private Sub histogram_listbox_Click() End Sub Note that we don’t need to set any values in the procedure. Anywhere else in the code we can get the value using the statement numBins = histogram_listbox.Value

21

Generating the Code KEY IDEA FOUR: A second example. Life is more complicated in MATLAB. function histogram_popup_Callback(hObject, eventdata, ... handles) val = get(hObject,’Value’) str = get(hObject, ’String’) switch str{val}; case ’20’ handles.number_bins = 20; case ’50’ handles.number_bins = 50; case ’100’ handles.number_bins = 100; case ’1000’ handles.number_bins = 1000 ; end guidata(hObject, handles);

Generating the Code KEY IDEA FOUR: A second example. Life is more complicated in MATLAB. To better understand the previous slide, here is MATLAB generates for val and str. val = 3 str = ’20’ ’50’ ’100’ ’1000’

23

Generating the Code

KEY IDEA FIVE: Transferring data between the functions or procedures.

Life is much nicer with VBA. With VBA you can call the “value” of a procedure from anywhere in the User Form code. For example, in the Generate Histogram code we have: numTrials = num_trials_scrollBar.Value numBins = histogram_listbox.Value

Generating the Code KEY IDEA FIVE: Transferring data between the functions or procedures. In MATLAB we put the data in a handler called handles. In this example we need the number of bins (numBins) and number of trials (numTrials) to build the histogram. Get the number of bins. handles.number_bins = 1000

;

Get the number of trials. slider_value = get(hObject,’Value’) handles.num_trials = floor( slider_value) Pass the information to the other functions. guidata(hObject,handles)

Generating the Code KEY IDEA FIVE: Transferring data between the functions or procedures. In MATLAB we put the data in a handler called handles. In this example we need the number of bins (numBins) and number of trials (numTrials) to build the histogram.

After putting the data in the handles we can use it anywhere else. For example in the Generate Histogram function gen Histogram Callback we have hist (randn(1, handles.num_trials), handles.number_bins);

26

...

Initialization In VBA there is an initialization routine that called UserForm Click() that gets called before the user sees the form. You can initialize data here. See my code where I initialize the values for the possible number of bins. In VBA it is common to call the UserForm Click() from a main() procedure. This is done as simulationGUI.Show, where simulationGUI is the name of my User Form. In MATLAB there is an initialization routine that called simulationDemoGUI OpeningFcn that gets called before the user sees the form. You can initialize data here. See my code where I parameter values for the histogram.

27

Start-up Worksheet

In VBA you can control the first worksheet the user sees and which worksheets are visible. Here is my code. Private Sub Workbook_Open() Dim ws As Object Worksheets("Begin").Activate For Each ws In ActiveWorkbook.Sheets If ws.Name "Begin" Then ws.Visible = False Next End Sub

MATLAB GUI Basics A blank MATLAB user form. The tool box is on the left. Select File and then New and then Figure to get this. There is also a Toolbar icon.

29

MATLAB GUI Basics A MATLAB user form with tools. (Drag them over)

30

MATLAB GUI Basics Here is what the user will see.

31