2

I'm trying to run the below user defined function, but I receive the following error:

object variable or with block variable not set

Private Function Find_Select_Option(selectElement As HTMLSelectElement, optionText As String) As Integer

    Dim i As Integer

    Find_Select_Option = -1
    i = 0
    While i < selectElement.Options.length And Find_Select_Option = -1 ' ### error occurs on this line
        DoEvents
        If LCase(Trim(selectElement.Item(i).Text)) = LCase(Trim(optionText)) Then Find_Select_Option = i
        i = i + 1
    Wend

End Function

I have attached the VBA code below (source). Please go through it and let me know, what's wrong in this code.

Public Sub IE1()

    Dim URL As String
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument

    URL = "http://douglasne.mapping-online.com/DouglasCoNe/static/valuation.jsp"

    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend
        Set HTMLdoc = .document
    End With

    '<select name="StreetDir">
    Dim optionIndex As Integer
    Dim dirSelect As HTMLSelectElement
    Set dirSelect = HTMLdoc.getElementsByName("StreetDir")(0)
    'dirSelect.selectedIndex = 2            'set option index directly
    optionIndex = Find_Select_Option(dirSelect, "E")
    If optionIndex >= 0 Then
        dirSelect.selectedIndex = optionIndex
    End If

    '<select name="StreetSfx">
    Dim suffixSelect As HTMLSelectElement
    Set suffixSelect = HTMLdoc.getElementsByName("StreetSfx")(0)
    optionIndex = Find_Select_Option(suffixSelect, "PLAZA")
    If optionIndex >= 0 Then
        suffixSelect.selectedIndex = optionIndex
    End If

End Sub

How can I fix this?

Community
  • 1
  • 1
prashant
  • 53
  • 6
  • 1
    What is a `HTMLSelectElement` in Excel? – Dominique Jan 02 '18 at 15:01
  • 1
    Your selectElement variable `Is Nothing` (check it in the immediate window). The problem is in the code that's calling this function. Specifically, the code that's instantiating the `HTMLSelectElement`. – Dick Kusleika Jan 02 '18 at 15:06
  • 1
    Welcome to the site! Check out the [tour](https://stackoverflow.com/tour) and the [how-to-ask page](https://stackoverflow.com/help/how-to-ask) for more about asking questions that will attract quality answers. You can [edit your question](https://stackoverflow.com/posts/48062924/edit) to include more information. I agree with @DickKusleika. Would you please show the code that is *calling* `Find_Select_Option`? I can't see any obvious issues with the code you have posted so far. – cxw Jan 02 '18 at 15:47
  • @ Dick Kusleika & CXW, I have added the code as well. Please check it and let me know, what's wrong in this code. Thanks in advance !! – prashant Jan 03 '18 at 08:08
  • Thanks for the code update! By the way, it's important to cite your sources (and [legally required](https://stackoverflow.blog/2009/06/25/attribution-required/) to use code you get from Stack Overflow). I have added the OzGrid source to your question to give you an example. – cxw Jan 03 '18 at 13:22

1 Answers1

0

When I was poking around I also saw the OzGrid post you're pulling from. The problem is that the test URL, http://douglasne.mapping-online.com/DouglasCoNe/static/valuation.jsp, no longer has the elements you are looking for! For example, it does not have <select name="StreetDir">. So dirSelect is Nothing at the time you call Find_Select_Option.

I recommend testing with a local file. For example, create c:\users\prashant\foo.htm (or wherever you want to put it) with the following contents (modified from w3schools):

<!DOCTYPE html>
<html>
<body>

<select name="Car">
  <option value="volvo">Volvo</option>
  <option value="saab">Saab</option>
  <option value="opel">Opel</option>
  <option value="audi">Audi</option>
</select>

</body>
</html>

Then the following code should work (it does for me):

Public Sub IE1()

    Dim URL As String
    Dim IE As SHDocVw.InternetExplorer
    Dim HTMLdoc As MSHTML.HTMLDocument

    URL = "c:\users\prashant\foo.htm"    ' *** Read from a local file

    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .ReadyState <> READYSTATE_COMPLETE
            DoEvents
        Wend
        Set HTMLdoc = .document
    End With

    '<select name="Car">
    Dim optionIndex As Integer
    Dim dirSelect As HTMLSelectElement
    Dim message As String

    Set dirSelect = Nothing   ' *** Set up for the error checking below
    On Error Resume Next

    'Set dirSelect = HTMLdoc.getElementsByTagName("select").Item(0) ' This is OK, too
    Set dirSelect = HTMLdoc.getElementsByName("Car").Item(0)  ' *** It exists!

    ' *** Here's some error-checking code you can use
    If Err.Number <> 0 Then         ' Report errors
        message = "Error " & CStr(Err.Number) & vbCrLf & Err.Description
    ElseIf dirSelect Is Nothing Then
        message = "No element found"
    Else
        message = "OK" & vbCrLf & dirSelect.textContent
    End If
    On Error GoTo 0   ' *** Back to normal

    MsgBox message
End Sub

When the parameter to getElementsByName is "Car", I get an OK response. When I change that parameter to "Nonexistent", I get No element found. This confirms that dirSelect is Nothing in your code at the point you call Find_Select_Option.

cxw
  • 16,685
  • 2
  • 45
  • 81
  • @ CXW Thanks for your help. If you could help me to solve another code of mine, Which I have already posted on this side previously with title as "How to select an specific item on a drop down list on ASPX site", It would be great. Thanks again !! – prashant Jan 04 '18 at 10:30
  • @prashant I took a look at [that question](https://stackoverflow.com/q/47910269/2877364), but I'm not sure what you're asking. You might have better luck if you edited to clarify. By the way - as far as I know, there's no space after the "@" sign when you are responding to someone. I think the system only notifies the person if there's no space. For example, if you type @ and then start typing the name (with no space), it will pop up a suggested name. However, it doesn't do that with a space (at least in my test just now). Happy hacking! – cxw Jan 04 '18 at 13:46
  • I want to select one item from that drop down list.I have added screenshot of that drop down list. Let me know if it's clear enough or you want me to put the entire source code. Thanks!! – prashant Jan 04 '18 at 15:12