VSTO - Testing for empty cell not working.

[jmazorra]

Hello folks:

I am using a windows form in VSTO. On the form my code is to check if the cell J34 is empty, if it is give me a message Else perform some code. Right now, if the cell contains a value the Else part of my code works just fine. But if it does not contain a value (which is what I am checking for) it errors out on this line:

decAnnualMid = Convert.ToDecimal(WS.Application.Range("J34").Value)

I get an error: Format exception was unhandled by user code

I could always use an exception handling as a work around but it does not seem as the right thing to do. Here is the rest of 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)


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


        Dim decAnnualMid As Decimal


        Dim WS As Excel.Worksheet = CType(CType(thisWB.ActiveSheet, Excel.Worksheet), Excel.Worksheet)


        With WS


            If chk10thPercentile.Checked = True And WS.Application.Range("J34").Value Is Nothing 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 
[COLOR=#b22222]                decAnnualMid = Convert.ToDecimal(WS.Application.Range("J34").Value)  'This is my line with the trouble.[/COLOR]


                'convert annual salary to hourly
                decHourlyMid = decAnnualMid / 52 / 40


                'display in the Mid label box
                lblAnnualMid.Text = decAnnualMid.ToString("C")
                lblHourlyMid.Text = decHourlyMid.ToString("C")


                'Uncheck all other boxes


                chk25thPercentile.Checked = False
                chk50thPercentile.Checked = False
                chk75thPercentile.Checked = False
                chk90thPercentile.Checked = False
                chkOtherAmount.Checked = False


            End If


        End With


    End Sub

Any help will be appreciated. Or if anyone knows how to do it differently or better.

Thank you.