CODEWITHSUNDEEP
ms-accessvba
Quint
Quint

Reputation: 590

VBA: Access crash with no error when calling function

I have an Issues when calling this 

Public IE As InternetExplorer
Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
Dim el As Object

Public Function IEClick(Idstr As String)
Do
    Set el = Nothing
    On Error Resume Next
    Set el = IE.Document.getElementById(Idstr)
    On Error GoTo 0
    DoEvents
    Sleep (100)
Loop While el Is Nothing
Sleep (1000)
IE.Document.getElementById(Idstr).Click
End Function

Its to loop true untill it finds the correct elementid. I crash with no error message on this line IEClick Str(Item) when the Function is being called like so.

For Each Item In coll
    IEClick Str(Item)
    p = p + 1
Next

This is the full body of code.

Public Sub AutoExtract()
Dim ProgressBar_Extract As Object
Dim coll As Collection
Set coll = New Collection

'/////////////////controls in order///////////////
With coll
    .Add "ctl00_cmdCustom"
    .Add "chkAll"
    .Add "ctl00_cmdContinue"
End With
Me.ProgressBar_Extract.Max = coll.Count
'////////////////////start ie//////////////////
Set IE = New InternetExplorerMedium
With IE
    '.AddressBar = False
    '.MenuBar = False
    .Navigate ("*****")
    .Visible = True
End With
'//////////////////////Start Automation///////////////////
For Each Item In coll
    IEClick Str(Item)
    p = p + 1
Next
MsgBox "Extract Complete"
End Sub

I narrowed it down to IEClick Str(Item).When debugging if I put a stop on it and step to next line it crashes.

Upvotes: 2

Views: 787

Answers (2)

Summer-Time
Summer-Time

Reputation: 1874

Str() is very buggy and crash my access sometimes better use cstr()

Upvotes: 0

Andre
Andre

Reputation: 27634

1) Don't use Item as variable name, it is a reserved word in VBA (Collection.Item)

2) Put Option Explicit at the top of each module.
It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time. To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor.

3) I couldn't reproduce a crash, but I get a runtime error when calling Str() with a string parameter.

Try this:

Option Explicit

Public Sub TestCrash()

    Dim coll As Collection
    Dim vItem As Variant

    Set coll = New Collection

    With coll
        .Add "ctl00_cmdCustom"
        .Add "chkAll"
        .Add "ctl00_cmdContinue"
    End With

    For Each vItem In coll
        ' Gives Runtime error 13 "Type mismatch"
        ' Debug.Print Str(vItem)

        ' This works
        Debug.Print vItem
    Next

End Sub

and if it works, adapt your code.

Upvotes: 1

Related Questions