VBA Excel Strings

Question

While using the VBA for Excel, I am facing runtime error with the string datatype.

I have a variable declared as string and trying to read a value from the cell in the spreadsheet and add as an item in a listbox. The data that is present in the particular cell is greater than 2000 character. My vba is able to read the value from the cell and print in the debug window. When I try to add the item in the listbox, it is not able to add. It is throwing an runtime error.

The runtime error code is "-2147352571 (80020005): Type Mismatch".

Is there any work around for this sort of problem.

Public Sub update_form()
Dim a1, b1, c1, d1 As Single
Dim a2, b2, c2, d2 As String
Dim a3, b3, c3, d3 As String
Dim a4, c4, d4 As String
Dim i As Single
Dim b4$

    a2 = req_no.Value
    Sheets("Design Trace - Current").Select
        Range("A1").Activate
        Columns("A:A").Select
        Selection.Find(What:=a2, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        a1 = ActiveCell.Row
    b2 = Sheets("Design Trace - Current").Cells(a1, 2).Value

    c2 = "A" & a1
    d2 = "BBB" & a1
    b1 = Application.WorksheetFunction.CountA(Range(c2, d2))
    For i = 2 To b1 Step 3
        a4 = Cells(a1, i).Value
        b4 = Cells(a1, i + 1).Value
        d1 = Len(b4)
        Debug.Print " Length : " & d1
        c4 = Cells(a1, i + 2).Value

        design_ele.Text = a4
        reverse_req.Text = b4
        code_file_name.Text = c4
        Debug.Print "a4 : " & a4
        Debug.Print "b4 : " & b4
        Debug.Print "c4 : " & c4

        If (Len(a4) > 500) Then
            ListBox1.AddItem "Refer Value"
        Else
            ListBox1.AddItem a4
        End If

        ListBox2.AddItem b4

        If (Len(c4) > 500) Then
            ListBox3.AddItem "Refer Value"
        Else
            ListBox3.AddItem c4
        End If


    Next
End Sub

It is an userform and the version of excel is 2007.

Thanks

Is it required to show 2000 characters for an item in the listbox? — shahkalpesh
– shahkalpesh, Commented Sep 24, 2012 at 7:04
You haven't read the answer properly, you have to declare like Dim a4 as string, c4 as string , d4 As String — Larry
– Larry, Commented Sep 24, 2012 at 8:23
oh... I'm sorry, I have mistaken.. I tried updating and running the script, but still it throws the same error. — Matt
– Matt, Commented Sep 24, 2012 at 8:42

Alex K. · Accepted Answer · 2012-09-24 11:48:15Z

1

There is a ~2000 character limit to an individual listbox item.

If you want to avoid the error, limit the length when adding:

listbox3.additem left$(a4, 2000)

If you want to store the full text do so in an array.

answered Sep 24, 2012 at 11:48

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel Strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related