VBA Rubberducking

6:04 PM

Is there any way to loop with HTTP request until page loaded in vba please? I have read that .READYSTATE = 4 : DoEvents : Loop does not apply to HTTP requests. Or what better way is recommended for waiting til page loads?

Vogel612

how are you firing off the request?

also: VBA has language-level support for events. use it

QHarr

With http
.Open "GET", url, True

this

if you don't want to loop, you shouldn't use MSXML

use WinHttp

WinHttp is the one that provides events, which as Vogel said, is waht you want if you don't want to loop.

QHarr

In full I was doing this

ublic Function GetTestHTML(Optional ByVal url As String = "https://www.w3schools.com/cssref/trysel.asp") As HTMLDocument
Dim http As New XMLHTTP60
Dim html As New HTMLDocument

Do
With http 'Set http = CreateObject("MSXML2.XMLHttp60")
.Open "GET", url, True
.send

Do Until .readyState = 4
DoEvents
Loop
html.body.innerHTML = .responseText
End With
Loop While html.body.innerHTML = vbNullString

Set GetTestHTML = html
End Function

this

yeah XMLHTTP60 is MSXML.

QHarr

6:07 PM

Then I was told that didn't achieve anything. Is that true?

this

actually MSXML2

as it is, it should work (the looping part); you're just stuck waiting for it to complete because you can't use events w/ MSXML2

and that means this code must block until then.

what actually happens?

QHarr

1) Is that loop redundant in my code despite True on open? 2) In reference to the language level events support - that means using WinHTTP does it?

Code works perfectly it was someone else saying it wouldn't do anything.

this

if true is for the async parameter, it's pointless, yeah.

just leave it false.

QHarr

Ah...

this

and yes, you must WinHttp if you want events.

they might be referring to the true part- which is basically wasted on this code

because you are not allowing for asynchrony whatsoever in that code.

if you truly want this to run asynchronously, different approach is needed.

QHarr

6:11 PM

No, the quote I saw was "Do Until xHttp.READYSTATE = 4 : DoEvents : Loop doesn't make any sense when you send any request using xmlhttp"

Mathieu Guindon

@QHarr there's no browser involved, just a synchronous (?) HTTP request

this

that would be only true as long the async was false

Mathieu Guindon

takes foot out of mouth

this

but you set it to true, so you're now adding a loop to make asynchronous code synchronous

IOW, you're adding lot of noise

so, take out the true or make it false, then you can remove the loop.

QHarr

Gak.....ok.... so I have change to False. Is the construct now ok or can I simply strip out all the looping?

Beat me to it.

this

6:13 PM

but you still have to check the http status. Suppose you got a 4XX or 5XX? Then what?

QHarr

Ok so .Status = 200 ...

Mathieu Guindon

success!

QHarr

:-)

this

yeah, no point in consuming the document if it's not a 200.

Mathieu Guindon

FWIW if you want asynchronous, you need a WithEvents object and handle onreadystatechange and ontimeout

this

6:15 PM

hmm. that works?

Mathieu Guindon

IDK, looking at HTMLXMLHttpRequest class in OB

this

AFAIK, MSXML events are.... not really events

Mathieu Guindon

OB seems to think so

this

hmm. going to look.

we're talking about MSXML2.XMLHTTP60, right?

Mathieu Guindon

ok, wrong lib

QHarr

6:18 PM

@MathieuGuindon Thanks. I will need to do some reading to better understand I think. My problem is with slow loading pages I stop using XMLHTTP60 and switch to looping with IE. I hate IE so there must be a way I can use something else that allows me to monitor for page being loaded??

this

you don't want IE

QHarr

I know

this

use WinHttp if you want events

Mathieu Guindon

@this ha, you're right, onreadystatechange isn't an event in that lib

this

WinHttp ≠ ShDocVw (IE) ≠ MSXML2

QHarr

6:19 PM

I was told to use WinINet over WinHTTP by someone once

this

but WinHttp and MSXML2 are mostly similar so lot of code is easily ported but WinHttp is more friendly.

reason being?

QHarr

msdn.microsoft.com/en-us/library/windows/desktop/…

Mathieu Guindon

what's mshtml.tlb for then?

QHarr

They sent me to that link

this

@MathieuGuindon the actual HTML

QHarr

6:20 PM

I just want something that works that is not IE

this

it has nothing to do with making HTTP requests and that kind of stuff

Mathieu Guindon

but it defines an HTMLXMLHttpRequest object

this

oh? going to look.

Mathieu Guindon

that does have a proper onreadystatechange event

this

oh my

Mathieu Guindon

6:21 PM

that said, I've never used either

this

why wasn't I told about that?

Mathieu Guindon

lol

sorry

this

the fact that I used WinHttp and also MSHTML annoyed the heck out of me

now I feel dumb for never have seen that object in MSTHML. Could have avoided one extra reference....

thanks for edumcating me, @MathieuGuindon !

but gosh. that's ridiculous

Mathieu Guindon

yeah, well, thank me when it's proven to actually work

IvenBach

@this So much cancer in that single link.

this

6:23 PM

we now have... ShDocVw, WinHttp, MSHTML, MSXML/MSXML2

all with lot of overlaps and not exactly like one other. :yay:

QHarr

I'm playing catch-up. So what should I be looking at please?

Mathieu Guindon

Microsoft HTML Object Library

this

yeah it has what you want

QHarr

Ok. And therein I will find a way to monitor for page load?

this

(aka MSHTML)

yes, as Mat said, HTTPXMLsometgjljdljfflkjdlghlkgjflj

RE: that link ----

> Developer audience

WinINet is designed for use by C/C++ programmers. It requires a basic understanding of the FTP and HTTP protocols.

IOW, not VB-friendly

QHarr

6:25 PM

Ok. So I need to look into HTMLXMLHttpRequest right?

this

yeah

there's one big problem....

@IvenBach see what I mean RE: context? Reading the article, one'd think they should use WinINet which is a total non-solution

QHarr

So.... to conclude my homework is to research onreadystatechange event within MSHTML.HTMLXMLHttpRequest ? yah?

Mathieu Guindon

I'd do that :)

QHarr

Thank you all

this

that name is so not growing on me.

it's quite a mouthful.

QHarr

6:35 PM

HXHR ? :-)

this

I think that's worse..

QHarr

lol

this

simply MSHTML.HttpRequest would have suffice

but no, Micorosft has to be Microsoft and solemnly remind you that it's good for HTML, XML and it does HTTP and it makes request. Oh and it can slice'n'dice. Even jubliee!

QHarr

Is VBA considered to be particularly verbose? Anything hierarchical I guess would be.

this

By definition it's meant to be

which isn't a bad thing in itself

I'd rather read obfuscated VBA over obfuscated C++ code. any time

I mean, you can get over resultOfSubtraction = something + otherthing

QHarr

6:38 PM

but....

this

in C++, however, you literally have no guarantee that + is in fact an addition operator.

Mathieu Guindon

you can override operators in C# too

IvenBach

@this I was referring to your flashing seizure link.

Mathieu Guindon

lol

this

@MathieuGuindon point

Still my point is that it's very hard to write WORN code in VBA.

It won't make WTF/sec go down but you do have hopes of eventually deciphering what the heck they were doing. With C++, well, might as well just write new one from scratch cos you ain't getting anywhere.

@IvenBach for whatever reasons, it was a thing with C64. The more flashing colors they coded, the "better" the program was.

they'd have lot of bouncing rainbows words and whatnots

Mathieu Guindon

6:46 PM

reminds me of that floppy disk I'd put in and run and it would turn the TV into that gif and play "pump up the jam" ...decades before MP3 was even a thought.

okay not too many decades there, but still

Mathieu Guindon

7:06 PM

to this day I've no idea how in the bloody hell they managed to encode the song, fit a 512kb floppy, and be recognizeable through the C64's sound chip.

all I could do with that chip was mosquito sounds

FreeMan

@QHarr it's less verbose than COBOL... ADD TAX TO PRICE GIVING TOTAL. It don't get much more verbose than that.

shudders thinking that he still remembers COBOL syntax...

QHarr

@MathieuGuindon love that song

@FreeMan Never did COBOL.

FreeMan

You didn't miss ~~much~~ anything.

this

@FreeMan you know they're paying up to $200K for anyone who knows COBOL?

Mathieu Guindon

7:28 PM

feels sudden urge to learn COBOL

> ADD TAX TO PRICE GIVING TOTAL

urge gone

reads like F'n LOLCODE

this

meh. LOLCODE is really distracting compared to COBOL

at least COBOL says what it's doing.

LOLCODE well, u gonna CAN I HAZ CHEESEBURGER

Mathieu Guindon

okay: LOLCODE reads like COBOL with renamed tokens

this

lol

Mathieu Guindon

      OMGWTF
        ANY OF BOTH SAEM MOOV AN "ZPOCK" AN BOTH SAEM MOOV AN "zpock" MKAY
        O RLY?
          YA RLY, MOOV R SEEKRITMOOV
          NO WAI
            MOOV R SMOOSH "... WUTS " AN MOOV AN " ANYWAI" MKAY
        OIC

this

also, the other reason they pay so much is because the main customer are those in finance sector and they will flip out if even as much one penny goes missing, soooo. #NoPressure.

Mathieu Guindon

7:32 PM

cobol.net stackoverflow.com/questions/tagged/cobol.net

this

hmm. now come to think, LOLCODE seems better compared to ARNOLDC

Vogel612

> TALK TO THE HAND

this

sure they're fun to read in one line. A whole paragraph of them? Sheesh

Mathieu Guindon

NO WAI SHEESH

this

@MathieuGuindon so the answer should be: "Micorosft, WTF? You let COBOL in .NET and it ain't even yours. Maybe let VBA in, too?"

Mathieu Guindon

7:34 PM

they did, it's called VB.NET

:)

this

I think you're confusing it with Visual Fred.

Mathieu Guindon

MEBBE

LOLCAT SPAEK R ANOYNG MKAY

damn

meeting, bbl

FreeMan

@MathieuGuindon Frankly that really does look like COBOL. My college instructor said she had spelling issues from mispelling variables so as to not use reserved words. (Whole other issue there, but yeah...)

(yes, that was intentional)

@this I have no intention of spending the rest of my career among the dead. Wait... $200k? Maybe...

Hosch250

7:56 PM

@this They didn't develop cobol.net, AFAIK.

Anyone can write a .NET language, if they have the resources.

Vogel612

just like anyone can write a JVM language

this

fair point but one difference is that COBOL is basically out in open just like Java. That's not the case for VBA, AFAIK.

and besides, merely re-implementing in VS is pointless if the Office actually doesn't open VS.

Hosch250

If Office can do that, they would use VB.NET, probably.

Is VBA 64-bit?

Or just 32-bit?

Because 32-bit is dying mid 2030's anyway.

FreeMan

@MathieuGuindon Looked at some of that code. Shuddered. Nope, not worth 200k...

this

Hosch, it can be both

it runs inprocess so it must match Office's bitness

Hosch250

8:08 PM

OK.

IvenBach

9:05 PM

@MathieuGuindon Orly?!

Vogel612

IIRC That was the if-else syntax

and OMGWTF was default for switch-case

IvenBach

9:45 PM

I needed that brief jaunt out on my uni.

Mathieu Guindon

> Your stats are booming! Your RD News blog is getting a lot of traffic

Love these push notifications

TTGH

IvenBach

-1

Q: Pull Footer Value As String

Is there anyway to the pull the value of the footer as a string, and not the formula to make that footer? Can provide more information if this is a possibility. Thanks!

with 2 word specific tags, only to later say "I am actually using Excel." ...

Mathieu Guindon

10:29 PM

What a troll

IvenBach

Those are the sort of persons that should be downvoted.

I contrast that with my last couple of answers on CR, which took way longer than I wanted... Bit oh how I so wish someone would have done the same to help me figure stuff out.

Mug have you done any work with ActiveX objects and their properties in Excel?

ActiveSheet.CheckBoxes.Add(579, 69, 150, 97.5) <--- That right there is one reason why the macro recorder is so infuriating. ~~Worksheet.CheckBoxes doesn't exist!~~ FML it's hidden...

this

Ask not for whom the bell tolls for the bell tolls for the trolls

IvenBach

Worksheet.CheckBoxes return an Object ... Why doesn't it return CheckBoxes. And CheckBoxes.Add goes to the hidden CheckBox...

this

It's common for OM to use object and variant to avoid compatibility problems

that way, they could change the types if they made a BetterCheckbox for instance

IvenBach

hrngh...

still...

this

10:43 PM

Yeah, I hate it, too. Most of time, the variant parameters are meant to be something specific

IvenBach

:grumble: I have clue why this irks me so much. I'm going to go file this newly found paperwork before I get more worked up.

this

MsgBox "hi!", "derp" <- this compiles, but blows up runtime

IvenBach

Right as I'm calmed down you go and show me that. TGIF

this

We're VBA and we're here to help!

IvenBach

11:00 PM

Excel.CheckBox vs MSForms.CheckBox

this

I vaguely recall someone railing about two part naming. Do you remember?

IvenBach

Why do I keep thanking you and the pond for this stuff?

Without these tidbits I'd be SOL.

this

cos the learning / teaching is pretty fun

IvenBach

That's why I'm trying to give back in whatever small ways I can.

Duga

11:16 PM

[rubberduck-vba/Rubberduck] MDoerner commented on Rubberduck.Parsing/Symbols/DeclarationFinder.cs of pull request #3893: Improve resolution for ambiguous names WRT scoping

IvenBach

@this Sometimes it's hard to appreciate what someones telling you. That is until you finally understand why.

Duga

11:32 PM

[rubberduck-vba/Rubberduck] BobEboph edited wiki page Installing

IvenBach

In discovering OLEObject I get that an ActiveX control is one. What would be an example of a linked or an embedded OLEObject?

Mathieu Guindon

11:47 PM

@IvenBach an excel chart in a ppt slide

Duga

[rubberduck-vba/Rubberduck] MDoerner commented on RubberduckTests/Symbols/DeclarationFinderTests.cs of pull request #3893: Improve resolution for ambiguous names WRT scoping

[rubberduck-vba/Rubberduck] MDoerner commented on Rubberduck.Parsing/VBA/ReferenceResolveRunnerBase.cs of pull request #3893: Improve resolution for ambiguous names WRT scoping

Transcript for