Oracle Database Time Model Viewer in Excel 3

March 3, 2011 (Updated March 5, 2011)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the previous articles of this series we started building a solution in Microsoft Excel that duplicates some of the functionality in one of my programs – the Oracle Database Time Model viewer.  So far, the project only works at just the system-wide level.  Hopefully, not too many people are having difficulty following along with this article series – it has been more than a decade since I professionally taught programming and Microsoft Excel, so I might be overlooking a couple of “obviously” difficult points. 

If you have not done so yet, you may need to change Excel’s default behavior when an error occurs in the code, since some errors are expected and should be handled appropriately within our programming code.  To check the error trapping setting in the Excel code editor window, select from the menu Tools – Options.  On the General tab, find and select the setting Break on Unhandled Errors, then click the OK button.  If you plan to do much programming in Excel, I also suggest setting the Require Variable Declaration option on the Editor tab – setting that option helps to avoid some forms of typing errors (all used variables must be officially declared before use).

If you find that the macro seems to pause unexpectedly when first started, there is a simple solution for that issue, and we will apply the simple solution later in this article.

We will start by adding a couple of more controls to the UserForm to add a little more functionality to the programming code.  In my sample project I will be adding the extra controls at the top of the UserForm.  We need to add three CheckBox controls to the UserForm with the (Name) property set to: chkPauseRefresh, chkDisplaySessionDetail, and chkExcludeIdleWaits (one name for each CheckBox).  Change the Caption property of those checkboxes to describe the function of the CheckBoxes (I used: Pause Refresh, Show Session Detail, and No Idle Waits).  Add two ComboBox controls to the UserForm with the (Name) property set to: cboUpdateFrequency and cboSessionMinimumPercent.  Set the Text property of the cboUpdateFrequency ComboBox to 60.  Set the Text property of the cboSessionMinimumPercent ComboBox to 10.  Add a Label control near each of the ComboBox controls, and change the Caption property of the Label controls to describe the purpose of the ComboBox controls (I used: Update Freq (S) and Min Utilization to Inc. Session %).  Your UserForm should look something like this when finished with the above instructions:

The extra controls at this point do nothing, other than occupy space on the UserForm, so we need to add functionality to the extra controls.  Double-click the chkPauseRefresh CheckBox to show the default code event for the CheckBox – the “Click” event.  The TimerEvent subroutine that we modified in the previous article is set to abort re-executing the TimerEvent subroutine any time the intKillFlag variable is set to something other than False (a value of 0).  So, the code for the chkPauseRefresh CheckBox’s Click event will simply toggle this intKillFlag variable between the values of True and False – if the value becomes True we need to restart the re-execution of the TimerEvent procedure.  The easiest way to accomplish this task is with the following code in the chkPauseRefresh CheckBox’s Click event (note that in Visual Basic versions 4.0 through 6.0 this same code will toggle the intKillFlag variable between the values of 0 and 1, but the code as written will behave the same way.  Value is the default property of a CheckBox control, so technically we could have omitted the .Value portion of the code):

Private Sub chkPauseRefresh_Click()
    intKillFlag = chkPauseRefresh.Value

    If intKillFlag <> False Then
        TimerEvent
    End If
End Sub 

The chkDisplaySessionDetail CheckBox technically does not require any special programming code in its Click event, so we will come back to this program functionality later.

The Click event for the chkExcludeIdleWaits CheckBox is similar to that of the same event for the chkPauseRefresh CheckBox.  On the UserForm, double-click the chkExcludeIdleWaits CheckBox (or simply select that name from the left ComboBox (drop-down list) in the code editor).  Change the Click event for that CheckBox to the following:

Private Sub chkExcludeIdleWaits_Click()
    intExcludeIdleWaits = chkExcludeIdleWaits.Value
End Sub

We need to add a little code to the two ComboBox controls in order to prevent the user of this tool from entering silly values, such as Sixty for the update frequency or 1,000,000 for the minimum utilization percent – we will check the entered values only when the user “tabs” out of the controls (or clicks something else).  Double-click the cboUpdateFrequency ComboBox, then select the Exit event from the ComboBox (drop-down list) that is at the top-right of the code window.  Add the following code to that event:

Private Sub cboUpdateFrequency_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim intResult As Integer

    If IsNumeric(cboUpdateFrequency.Text) Then
        If (Val(cboUpdateFrequency.Text) >= 1) And (Val(cboUpdateFrequency.Text) <= 18000) Then
            'OK
            lngTimerTriggerSeconds = Val(cboUpdateFrequency.Text)
        Else
            intResult = MsgBox(cboUpdateFrequency.Text & " is an invalid value." & vbCrLf & _
                "Must enter a number between 1 and 18000", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer") 

            cboUpdateFrequency.Text = "60"
            lngTimerTriggerSeconds = 60
        End If
    Else
        intResult = MsgBox(cboUpdateFrequency.Text & " is an invalid value." & vbCrLf & _
            "Must enter a number between 1 and 18000", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer")

        cboUpdateFrequency.Text = "60"
        lngTimerTriggerSeconds = 60
    End If
End Sub 

We need similar code in the Exit event of the cboUpdateFrequency ComboBox.  Double-click the cboUpdateFrequency ComboBox, and switch to the Exit event.  Add the following code:

Private Sub cboSessionMinimumPercent_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim intResult As Integer

    If IsNumeric(cboSessionMinimumPercent.Text) Then
        If (Val(cboSessionMinimumPercent.Text) >= 0.001) And (Val(cboSessionMinimumPercent.Text) <= 100) Then
            'OK
        Else
            intResult = MsgBox(cboSessionMinimumPercent.Text & " is an invalid value." & vbCrLf & _
                "Must enter a number between 0.001 and 100.0", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer")

            cboSessionMinimumPercent.Text = "10"
        End If
    Else
        intResult = MsgBox(cboSessionMinimumPercent.Text & " is an invalid value." & vbCrLf & _
            "Must enter a number between 0.001 and 100.0", vbCritical, "Charles Hooper's Oracle Database Time Model Viewer")

        cboSessionMinimumPercent.Text = "10"
    End If
End Sub 

We still need to add the functionality for the chkDisplaySessionDetail CheckBox and the cboSessionMinimumPercent ComboBox, and we have not done anything with the wait events yet (that feature will be added in the next article in this series).  Find the following code section in the UpdateDisplay subroutine:

'    If chkDisplaySessionDetail = 0 Then
'        intDisplaySessionDetail = False
'    Else
'        intDisplaySessionDetail = True
'    End If
'    sglSessionMinimumPercent = Val(cboSessionMinimumPercent.Text) / 100 

Remove the single quote characters (‘) in front of each of those lines so that the code appears like this:

    If chkDisplaySessionDetail = 0 Then
        intDisplaySessionDetail = False
    Else
        intDisplaySessionDetail = True
    End If
    sglSessionMinimumPercent = Val(cboSessionMinimumPercent.Text) / 100 

The above simple fix adds the functionality to the chkDisplaySessionDetail CheckBox and the cboSessionMinimumPercent ComboBox.  We still need to provide a list of items in the ComboBoxes that the users are able to select from, and fix the problem where the macro seems to pause unexpectedly when first started.  Switch to the Initialize event in the UserForm (double-click the UserForm’s background area), and then locate the following code in that subroutine:

    'More code will be copied here
    '
    '
    ' 

Just below that section of the code (and above the TimerEvent line), add the following code:

    cboUpdateFrequency.AddItem "5"
    cboUpdateFrequency.AddItem "10"
    cboUpdateFrequency.AddItem "30"
    cboUpdateFrequency.AddItem "60"
    cboUpdateFrequency.AddItem "120"
    cboUpdateFrequency.AddItem "600"
    cboUpdateFrequency.AddItem "3600"
    cboUpdateFrequency.AddItem "7200"
    cboUpdateFrequency.Text = "60"
    lngTimerTriggerSeconds = 60

    cboSessionMinimumPercent.AddItem "1"
    cboSessionMinimumPercent.AddItem "5"
    cboSessionMinimumPercent.AddItem "10"
    cboSessionMinimumPercent.AddItem "15"
    cboSessionMinimumPercent.AddItem "20"
    cboSessionMinimumPercent.AddItem "25"
    cboSessionMinimumPercent.AddItem "50"
    cboSessionMinimumPercent.AddItem "75"
    cboSessionMinimumPercent.Text = "10"

    DoEvents 

In the above, the lines containing .AddItem add entries to the list that is suggested to the user of the tool that we are building.  The lines containing .Text set the default text that appears in each of the ComboBoxes, and the lngTimerTriggerSeconds value must be identical to the numeric value that is assigned to the cboUpdateFrequency.Text property.  Save the project and press the F5 key on the keyboard to display the UserForm and start updating the statistics (after a 60 second delay).  Place a check in the chkDisplaySessionDetail CheckBox (identified as Show Session Detail in the sample project).  You should see something like this (up to 60 seconds after placing a check in that CheckBox):

By looking at the above screen capture it is probably obvious that those sessions which had consumed a small percentage of a Time Model Statistic are displayed with a yellow background, those sessions that had consumed 50% of a Time Model Statistic are displayed with a deep orange background, and sessions that had consumed 100% of a Time Model Statistic are displayed with a solid red background.

We still have a bit more to add to this tool, so keep an eye open for the next article in this series.

—-

Added March 5, 2011:

The Excel project code to this point, save with a .XLS extension (currently has a .DOC extension, please change):
timemodelviewerexcelarticle3xls