Display array in msgbox not working... RRS feed

  • Question

  • Hello,

    Below is the code I am using to check a range in an excel spreadsheet. If there is a null value in the last column, then a msgbox would display with a list of the null values using the information from the first column (name of data point). Here is what the spreadsheet looks like (there is a blank column between A and C):

    ColA       ColB      ColC
    Alpha                    1
    Beta                     2
    Grape                   4
    Sam                      6
    Tiger                     8

    Here's the code:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim tFile As String = Nothing
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                tFile = OpenFileDialog1.FileName
                TextBox1.Text = tFile
            End If
            Dim newXL As Excel.Application = Nothing
            Dim WBS As Excel.Workbooks = Nothing
            Dim WB As Excel.Workbook = Nothing
            Dim WS As Excel.Worksheet = Nothing
            Dim RNG As Excel.Range = Nothing
            Dim row As Integer = Nothing
            Dim nullArray As Object = Nothing
            Dim message As String = Nothing
            newXL = New Excel.Application
            WBS = newXL.Workbooks
            WB = WBS.Open(tFile)
            WS = WB.Worksheets(1)
            RNG = WS.UsedRange
            row = 1
                For r = 0 To RNG.Rows.Count - 1
                    If IsNothing(RNG.Cells(row, 3).Value) = True Then
                        nullArray = RNG.Cells(row, 1).value
                        message = String.Join(",", nullArray)
                    End If
                    row = row + 1
            Catch ex As Exception
            End Try

    Running the above, code will return only the final value which in this case would be "LION". I am trying to get the msgbox to display all null values (ie. Orange, Tom, Kick).

    I haven't used arrays much, so I thought I would try to display the null values as an array in the msgbox. Looking online, I found the above code (using "message" string) to concatentate and display these values. However if I change the nullArray to nullArray() [I think this is for an array], the I get an error message stating "Unable to cast object type system.string to object type system.object".

    Any help I could get with this would be most appreciated. Thanks!!

    Join the darskide. We have cookies!

    Tuesday, January 14, 2014 3:19 PM


All replies

  • Hello Darth,

    It looks like you need to declare an array in the following way specifying the type:

    Dim myArray() as String
    Please take a look at the Functions For VBA Arrays page for more information about arrays. Also you may be interested in the Arrays in Visual Basic article in MSDN.
    Tuesday, January 14, 2014 4:43 PM
  • Thanks for the reply Eugene...
    (I did review the links above and found them helpful - will study them more later)

    I tried your suggestion in the code above and received an error similar to the first one:
    "Unable to cast object of type 'System.String' to type 'System.String[]'"

    I fumbled a bit with the new code and with some more research, found a solution:

    1. Dim nullArray as New ArrayList
    It was mentioned that arrayLists were better than arrays as the arraylist will grow as items are added to it. In general there were more people for the arraylist over the array (do you have an opinion in this?)

    2. nullArray.Add(RNG.Cells(row, 1).value)
        message = String.Join(vbCr, nullArray.ToArray)
    This part almost reminds me of adding items to a datatable. I guess the part that confuses me here, is the nullArray.ToArray part. Is this converting the nullArray to strings?

    Thanks for the reply and assistance in this...

    Join the darskide. We have cookies!

    Wednesday, January 15, 2014 6:24 PM
  • Hi Darth,

    1. It is up to you which one is to choose.

    2. Please take a look at the ArrayList.ToArray method.

    Thursday, January 16, 2014 2:12 PM
  • Thanks for the link...I feel a bit silly now for even asking the question in the first place. It was pretty simple to understand after reading...

    Join the darskide. We have cookies!

    Thursday, January 16, 2014 9:34 PM