VBA, mid replies with Run-time error 5 (Invalid procedure call or argument)

[MoBo]

I'm strugling with VBA code. I'm quite new at VBA programming. The code below returns an error at line with mid function Scraping from web page works OK (red bold text). I would like to get only part of the text.
Sample of data from web page looks like this:
firstXXXXsecond
Desired result of this VBA should return XXXX to cell C2 on sheet "All".
If I use only variable data without mid line it works Ok, but result is not the one i want.

I would appreciate any hint.

Sub GetData()

Dim IE As Object
Dim part_1 As Integer, len As integer
Dim data As String, data_trimed As String

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://www.xy.com/a.php?ID_xy_200=1"
IE.Visible = True

While IE.busy
DoEvents
Wend

IE.document.All("input_name").Value = ThisWorkbook.Sheets("All").Range("B2")
IE.document.All("input_name").Select
IE.document.All("trigger").Select

data = IE.document.getElementById("output").innerHTML
part_1 = inStr(data, "first") + 5
len = inStr(data, "second") - inStr(data, "first") - 5
data_trimed = Mid(data, part_1, len)
ThisWorkbook.Sheets("All").Range("C2") = data_trimed

IE.Quit

End Sub

 

[Joe4]

len is a reserved word; it is a VBA function.
You never want to use reserved words for variables names. It can cause ambiguity and errors, as you are seeing in this case.

Change your variable name to something like "myLen" and it should work.

 

[Norie]

What's the value of len when you get the error?

PS I agree with Joe4 about the name of the variable but I'm not sure that's what's causing the problem.

 

[Joe4]

What's the value of len when you get the error?

PS I agree with Joe4 about the name of the variable but I'm not sure that's what's causing the problem.

I tried it out on my system, and was getting errors. When I change the variable name, those errors went away.

 

[Norie]

What error(s) where you getting?

 

[JoeMo]

I think Joe4 is correct. I doubt you can even get past the Dim len as integer line w/o VBA raising a flag.

 

[Joe4]

Compile error, syntax error.
And it highlight all three rows with "len" in it in red.

You can comment all the IE lines and just temporarily set:

data = "[COLOR=#333333]firstXXXXsecond"[/COLOR]

to isolate that part of the code.

Try it for yourself and see!

 

[MoBo]

len is a reserved word; it is a VBA function.
You never want to use reserved words for variables names. It can cause ambiguity and errors, as you are seeing in this case.

Change your variable name to something like "myLen" and it should work.

I changed it as you advised but still got an error at mid line
Sub GetData()

Dim IE As Object
Dim part_1 As Integer, mylen As integer
Dim data As String, data_trimed As String

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://www.xy.com/a.php?ID_xy_200=1"
IE.Visible = True

While IE.busy
DoEvents
Wend

IE.document.All("input_name").Value = ThisWorkbook.Sheets("All").Range("B2")
IE.document.All("input_name").Select
IE.document.All("trigger").Select

data = IE.document.getElementById("output").innerHTML
part_1 = inStr(data, "first") + 5
mylen = inStr(data, "second") - inStr(data, "first") - 5
data_trimed = Mid(data, part_1, mylen)
ThisWorkbook.Sheets("All").Range("C2") = data_trimed

IE.Quit

End Sub

 

[Joe4]

Actually, this is all you need to do to see the error:

Sub Test()
    Dim len as Integer
End Sub

Then try compiling the code.

 

[Norie]

Oops, I assumed the OP was able to get the code to run because of the error they reported.