none
Best practices in building a function/procedure RRS feed

  • Question

  • Hello:

    I am building a application using VS2012/VB. Below is a series of procedures that perform the same function over and over again. The only difference is that a different checkbox is checked and a different excel cell is read. I believe I can write a function and then call on that function to perform the actions necessary.

    The problem is that I am new to VB.net and to programming so I am looking for assistance on how to write one and what is the best practice.

    Here is my code:

    Option Explicit On
    Option Strict On
    Imports XL = Microsoft.Office.Tools.Excel
    
    
    Public Class frmCustomRanges
    
        Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)
        Dim WS As Excel.Worksheet = CType(CType(thisWB.ActiveSheet, Excel.Worksheet), Excel.Worksheet)
    
        'Variables for the MidPoint TextBoxes
        Dim decAnnualMid As Decimal
        Dim decHourlyMid As Decimal
    
    
        Private Sub chk10thPercentile_CheckedChanged(sender As Object, e As EventArgs) Handles chk10thPercentile.CheckedChanged
    
            'This event runs when the 10th checkbox is checked
            'The event clears the cells where the value will be pasted
            'enters the value from cell J34 into calculationShet H45 and clears
            'the checkboxes for 25th, 50th, 75th, 90th and Other Amount
            'checkboxes.
    
            With WS
    
                If chk10thPercentile.Checked = True Then
    
                    If (Not Decimal.TryParse(CStr(WS.Application.Range("J34").Value), decAnnualMid)) Then
    
                        MsgBox("The Market Data section of the Position Dashboard does not have a value for the 10th Percentile.", MsgBoxStyle.Critical, "Input Error")
    
                        Me.Close()
    
                    Else
    
                        'convert the value of J34 to decimal and
                        decAnnualMid = Convert.ToDecimal(WS.Application.Range("J34").Value)
    
                        'display in the Mid label box
                        lblAnnualMid.Text = decAnnualMid.ToString("C")
    
    
                        'Uncheck all other boxes
                        chk25thPercentile.Checked = False
                        chk50thPercentile.Checked = False
                        chk75thPercentile.Checked = False
                        chk90thPercentile.Checked = False
                        chkOtherAmount.Checked = False
    
                    End If
                End If
    
            End With
    
        End Sub
    
        Private Sub chk25thPercentile_CheckedChanged(sender As Object, e As EventArgs) Handles chk25thPercentile.CheckedChanged
    
            'This event runs when the 25th checkbox is checked
            'The event clears the cells where the value will be pasted
            'enters the value from cell K34 into calculationShet H45 and clears
            'the checkboxes for 10th, 50th, 75th, 90th and Other Amount
            'checkboxes. 
    
            If chk25thPercentile.Checked = True Then
    
                If (Not Decimal.TryParse(CStr(WS.Application.Range("K34").Value), decAnnualMid)) Then
    
                    MsgBox("The Market Data section of the Position Dashboard does not have a value for the 25th Percentile.", MsgBoxStyle.Critical, "Input Error")
    
                    Me.Close()
    
                Else
    
                    'convert the value of K34 to decimal and
                    decAnnualMid = Convert.ToDecimal(Globals.dsbPositionBoard.Range("K34").Value)
    
                    'display in the Mid label box
                    lblAnnualMid.Text = decAnnualMid.ToString("C")
    
                    'Uncheck all other boxes
    
                    chk10thPercentile.Checked = False
                    chk50thPercentile.Checked = False
                    chk75thPercentile.Checked = False
                    chk90thPercentile.Checked = False
                    chkOtherAmount.Checked = False
    
    
                End If
    
            End If
    
    
        End Sub
    
        Private Sub chk50thPercentile_CheckedChanged(sender As Object, e As EventArgs) Handles chk50thPercentile.CheckedChanged
    
            'This event runs when the 50th checkbox is checked
            'The event clears the cells where the value will be pasted
            'enters the value from cell K34 into calculationShet H45 and clears
            'the checkboxes for 10th, 25th, 75th, 90th and Other Amount
            'checkboxes. 
    
            If chk50thPercentile.Checked = True Then
    
                If (Not Decimal.TryParse(CStr(WS.Application.Range("L34").Value), decAnnualMid)) Then
    
                    MsgBox("The Market Data section of the Position Dashboard does not have a value for the 50th Percentile.", MsgBoxStyle.Critical, "Input Error")
    
                    Me.Close()
    
                Else
    
                    'convert the value of L34 to decimal and
                    decAnnualMid = Convert.ToDecimal(Globals.dsbPositionBoard.Range("L34").Value)
    
                    'display in the Mid label box
                    lblAnnualMid.Text = decAnnualMid.ToString("C")
    
                    'Uncheck all other boxes
    
                    chk10thPercentile.Checked = False
                    chk25thPercentile.Checked = False
                    chk75thPercentile.Checked = False
                    chk90thPercentile.Checked = False
                    chkOtherAmount.Checked = False
    
    
                End If
    
            End If
    
        End Sub
    
        Private Sub chk75thPercentile_CheckedChanged(sender As Object, e As EventArgs) Handles chk75thPercentile.CheckedChanged
    
            'This event runs when the 75th checkbox is checked
            'The event clears the cells where the value will be pasted
            'enters the value from cell K34 into calculationShet H45 and clears
            'the checkboxes for 10th, 25th, 50th, 90th and Other Amount
            'checkboxes. 
    
            If chk75thPercentile.Checked = True Then
    
                If (Not Decimal.TryParse(CStr(WS.Application.Range("M34").Value), decAnnualMid)) Then
    
                    MsgBox("The Market Data section of the Position Dashboard does not have a value for the 75th Percentile.", MsgBoxStyle.Critical, "Input Error")
    
                    Me.Close()
    
                Else
    
                    'convert the value of M34 to decimal and
                    decAnnualMid = Convert.ToDecimal(Globals.dsbPositionBoard.Range("M34").Value)
    
                    'display in the Mid label box
                    lblAnnualMid.Text = decAnnualMid.ToString("C")
    
                    'Uncheck all other boxes
                    chk10thPercentile.Checked = False
                    chk25thPercentile.Checked = False
                    chk50thPercentile.Checked = False
                    chk90thPercentile.Checked = False
                    chkOtherAmount.Checked = False
    
    
                End If
    
            End If
    
        End Sub
    
        Private Sub chk90thPercentile_CheckedChanged(sender As Object, e As EventArgs) Handles chk90thPercentile.CheckedChanged
    
            'This event runs when the 90th checkbox is checked
            'The event clears the cells where the value will be pasted
            'enters the value from cell K34 into calculationShet H45 and clears
            'the checkboxes for 10th, 25th, 50th, 90th and Other Amount
            'checkboxes. 
    
            If chk90thPercentile.Checked = True Then
    
                If (Not Decimal.TryParse(CStr(WS.Application.Range("N34").Value), decAnnualMid)) Then
    
                    MsgBox("The Market Data section of the Position Dashboard does not have a value for the 10th Percentile.", MsgBoxStyle.Critical, "Input Error")
    
                    Me.Close()
    
                Else
    
                    'convert the value of M34 to decimal and
                    decAnnualMid = Convert.ToDecimal(Globals.dsbPositionBoard.Range("N34").Value)
    
                    'display in the Mid label box
                    lblAnnualMid.Text = Convert.ToDecimal(Globals.dsbPositionBoard.Range("N34").Value).ToString("C")
    
                    'Uncheck all other boxes
                    chk10thPercentile.Checked = False
                    chk25thPercentile.Checked = False
                    chk50thPercentile.Checked = False
                    chk75thPercentile.Checked = False
                    chkOtherAmount.Checked = False
    
    
                End If
    
            End If
    
        End Sub

    As you can see, it is the same variables and actions over and over again. I want to save lines of codes and I think a function would do that. But if not, I am looking for any advice on how to shorten or make it more efficient.

    Thank you

    Sunday, June 30, 2013 3:31 PM

Answers

  • For starters, copy one set of the lines of code like this (incl. Else and End If):
      If (Not Decimal.TryParse(CStr(WS.Application.Range("N34").Value), decAnnualMid)) Then

    and put it in a new procedure. The procedure would need a string parameter for the cell range you want to test, as well as Checkbox parameter for the Windows Form control that has been checked. A possible method signature would be:
      Private Sub ConvertCellValueToDecimal(cellRange as String, chk as Checkbox)

    Substitute the parameter for the literal cell range strings, such as N34, M34, etc.

    For changing the checked state of the checkboxes, I believe you can test like this for each one:
      If Not chk Is chk10thPercentile Then chk10thPercentile.Checked = False

    You can then call ConvertCellValueToDecimal in the CheckedChange of each checkbox control that should perform these actions.

    It would be possible to have all the checkboxes call the same procedure for the CheckedChange event; you'd need to test the sender parameter to know which checkbox triggered the event in order to get the cell range.

    Note: it would probably be better to assign Range Names to the cells with special meaning and use those, rather than N34, M34, etc. That would make both the workbook and the code more "readable".


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, June 30, 2013 5:41 PM
    Moderator