Programming Excel with VBA and .NET

Separate Work Code from UI Code

Whenever you work with forms, your code winds up in two places:

• The form’s class contains the event procedures that initialize the form and respond to user actions.

• The form’s work module displays the form and contains the procedures that perform tasks in Excel.

Can’t you just put all the code in the form class? Not really: first, you can’t show the form from there, and second, it’s harder to debug procedures in a class since you must first instantiate the class before it can run. That’s kind of a chicken-and-the-egg problem, and the best solution is to separate the two types of code in two different places.

For example, the following form class responds to the events on the Stock History form:

 ' frmStockHistory class Option Explicit   Private Sub spnDays_Change( ) txtDays.Value = spnDays.Value End Sub   Private Sub txtDays_Change( ) ' Ignore error if txtDays isn't between spnDays Min and Max. On Error Resume Next spnDays.Value = txtDays.Value End Sub   Private Sub cmdGetHistory_Click( ) Dim fname As String ' Show the source worksheet. Worksheets("VBForm").Activate ' Make sure the user entered a symbol. If txtSymbol.Text <> "" Then ResetWorksheet CreateQuery txtSymbol.Text, spnDays.Value HideUnneededCells fname = CreateChart(imgChart.height, imgChart.width) ' Update the image control. Set imgChart.Picture = LoadPicture(fname) End If End Sub   ' Create a copy of the worksheet and a full-sized chart. Private Sub cmdViewChart_Click( ) AddChartSheet (txtSymbol.Text) ...