VBA: Access crash with no error when calling function

Question

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.

What exactly does *"crash with no error message"* mean? It locks up? Or it closes (then does the Access process still run?) ? — Andre, Jun 19 '17 at 12:01
As a first step I recommend a full [Decompile](http://stackoverflow.com/a/3268188/3820271). — Andre, Jun 19 '17 at 13:06
If that doesn't help, please put a breakpoint inside the function (on `Set el = Nothing`), and step from there to find the exact line where it crashes. — Andre, Jun 19 '17 at 13:08
I have decomplied. it crashes right after this point `IEClick Str(Item)`. If i write out `IEClick "ctl00_cmdCustom"` it works just fine with no crash. It has to be something to do with `Item`. Is it an issue with maybe the `For` loop thinking of `Item` as an object but im using it as a string? — Quint, Jun 19 '17 at 13:13

score 1 · Accepted Answer · answered Jun 19 '17 at 13:28

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.

Thank you for your help Andre. I will give this a go and let you know. — Quint, Jun 19 '17 at 13:40

score 0 · Answer 2 · answered Sep 30 '20 at 08:12

0

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

answered Sep 30 '20 at 08:12

Summer-Time

1,824
15
19

VBA: Access crash with no error when calling function

2 Answers2