VBA Rubberducking - 2019-09-17 (page 2 of 3)

« first day (1929 days earlier) ← previous day next day → last day (1251 days later) »

6:11 PM

having an underscore or even extra dots is pretty common, though.

So long as nobody has a quoted space, we should be good - that's all I'm removing from the email string.

Underscores and dots are common. .. probably isn't that common.

somebody seems to store f.r.eeman@so.com for the email address, but sends it to freeman@so.com - I was getting email for the former at the latter for a while. It was a scientific org, so I don't really think they were trying to spam me on purpose. I finally just unsubscribed a couple of times and they stopped

and yes, it seems it does take 2-3 requests to unsubscribe. I have a friend who works for Big Manufacturing Corp. in the email marketing realm. He says they have to get 3 unsubscribe requests before they actually turn it off!

I just glared at him when he said that. Not his fault, but... :/

@FreeMan Which business?

Mathieu Guindon

@FreeMan lol, screw GDPR

@MathieuGuindon That's been illegal for years here too.

protects the ~~innocent~~ guilty

6:19 PM

If you report them to the BBB, the government will get after them.

And I do too, if it takes more than 1 try.

Also, just screw me...

I accidentally used the wrong HTML template...

So now I get to redo a couple hours worth of work.

d'oh

#NamingIsHard

Mathieu Guindon

deletes half the code in every class -- now that's refuctoring

I have a proc that gets data from the API, then calls the sproc to load it to the database.

Basically, we were loading it from a CSHTML file.

What do I call the procedure that calls GetDataFromAPI then calls StuffItInTheDB...

6:22 PM

Now we're turning it into tokens usable by email-generators too.

We had a couple variants of it--one for emails and one for the web.

But now we need to break it up to make it more flexible.

@FreeMan free\ man@domain.com will also be affected.

yeah... I don't care. :)

@FreeMan FWIW, I usually use noun-verb convention

the issue is ' freeman@domain.com'. Every email address has a bunch o'leading spaces

Then call SQL's trim on it.

6:25 PM

@this ApiDataLoadGet?

therefore, dbo.ImportFilesProcess calls dbo.ImportFilesGetAPIData then dbo.ImportFilesUpsertData

something like that.

@Hosch250 not working. :/

Why not?

that gives you the advantage of grouping all the logically related procedures, too.

dunno, ask the server. No errors, warnings or messages that I'm aware of...

Mathieu Guindon

6:26 PM

@FreeMan weird. nchar(255) would be trailing spaces. it's almost as if the spaces were deliberately put there

I supose you could have done that with a schema but that technically is an abuse of what it's mean to be.

@MathieuGuindon yeah. pretty much...

Maybe they make it available, but don't really want people to use it so they make it difficult...

Mathieu Guindon

was it imported from an AS/400 or something?

@MathieuGuindon no clue. Hitting a REST API - I don't know what their backend is.

maybe they're right-justifying it?

which would be totally moronic but...

6:31 PM

@this could be...

haven't bothered counting leading spaces to see.

I'm using this as the start of my factory method:

if you query it, you should see all strings right-aligned

Public Function Create(ByVal inURL As String, ByVal inFunction As String, ByVal inUserID As String, ByVal inPassword As String) As IRestDriver

  With New RESTDriver

and this is what I'm seeing in the Locals window

user image

is that to be expected?

I've moved a couple of my parameters to their appropriate resting places, and I'm not seeing them anywhere in the local window - I've expanded everything...

if you're still inside the Class_Initialize or it's wihtin your call stack, then maybe.

things get weird when you do things inside Class_Initialize.

I'm looking at it within .Create - the Class_Initizalize has already executed & exited on the With New line.

just as a sanity check, are you inside the With block?

6:35 PM

yes

interesting, when I step into Set Create = .Self, I see all the properties in Me, but Self remains as above

@FreeMan Have you tried LTRIM as well?

@M.Doerner I have. same result

user image

Was just wondering because TRIM is very new to Sql Server.

^that's within this code

Public Property Get Self() As IRestDriver
  Set Self = Me
End Property

was just curious if that's expected behaviour

@M.Doerner I'm on SS2017

That is actually the first version supporting TRIM.

6:41 PM

56 mins ago, by Mathieu Guindon

@FreeMan  

was suggested. I haven't tried deleting that, because my sproc (posted up there somewhere) does get rid of them using a regular, normal space.

Mathieu Guindon

@FreeMan the Self node refers to the object itself, ...no use expanding it

it's the same object, all the way down

to infinity

and beyond

i.e. that's expected behavior.

that's not flying, that's falling, with style.

OK, just curious. Don't recall having dug into that last time I set up something like that and was curious

Mathieu Guindon

depends where the current instruction is, really

but the main problem is that Self should return the class' default interface

I think.

something's off anyway, locals should show the values regardless

I think it's because of the interface

(locals gets flakey with interfaces)

@FreeMan Loop through an email and print the character codes.

wasn't there a open issue about locals replacement?

6:50 PM

@MathieuGuindon Hi. I'm finally at home. So I've grabbed random commit with a date of May 2019 and the "size reset" is not there. The size of the windows stays the same.

github.com/SonGokussj4/Rubberduck/commit/…

d6f87b1a975804400d678aebb99c8e807c805825

@Hosch250 bigger fish to fry ATM. I'm satisfied with my sproc that strips out all spaces.

If you are clever enough to know you could be "free man"@domain.com, then you're clever enough to know that 1/2 (give or take 50%) of your email won't actually get to you, and you can deal with it.

Wait until I contact your CEO and tell him what his IT team is doing :P

Just because half the people do things wrong means you can too.

Half the sites on the web are insecure. Doesn't mean I can ignore security on my site.

Mathieu Guindon

@SonGokussj4 I understand the annoyance and will welcome a pull request that addresses this, but it's an edge case against an extremely flakey part of the VBE, behind about 100 higher-priority issues

on the bright side, I can now get appointments via an API call instead of web scraping, and I can load the resulting XML via an sproc that consumes the incoming nvarchar(max). Now, if I can only get the 2nd API call I need to return me some results instead of a 500 Internal Error every bloody time, I'll be golden!

and, it's really stinking fast compared to the web scrape!!!

gonna have to get hold of their tech support on that 500 error, I think. I'm not getting it when requesting via Postman, so I'm sure I'm missing some important detail, I'm just not seeing it... :/

Have you compared the headers yet?

7:00 PM

@MathieuGuindon Okay. Honestly, it's an annoyance. Not a reason I would stop using this fantastic tool. :-) It just surprised me a little bit. I would be eager to do PR if I knew how to do that. My time with C# (I think it's C#...) will be better few months from now.

Mathieu Guindon

tbh it's more of a VBE/Win32/COM/Registry thing than C#

Oh..kay :-) If it's annoying in the future, I will try to do something with that. It's a shame you're not doing anything with Python/Bash. I would be soooo helpful. :-)

@Hosch250 I thought I'd duplicated from Postman to code, but I may have missed something. I moved on to a different API call out of frustration, so now I'll get back to it.

Mathieu Guindon

@SonGokussj4 I have a policy of sticking to strongly-typed languages where whitespace doesn't make or break my code :)

@Hosch250 good luck with that. He'll give you a blank look, I'd imagine...

Of course if IT knew how many shadow-IT depts there were running who knows what throughout the company, they'd ... be shocked...

7:06 PM

Do you interpret the email address at some place or do you just store it and provide it an email client?

@MathieuGuindon I think if that's your only problem, there should be Python library for that... :-D

@MathieuGuindon literally 20 seconds of googling. Here it is.

pypi.org/project/brackets

You can code with brackets instead of white-space/tabs. Another reason to not code in Python? :-D

You are forgetting that he said strongly-typed.

That means? (I'm not programmer by the school. Just self-learning one)

Compile time type validation.

Actually, that is statically typed.

Okay. Python is interpreted language so no compiling. But you CAN tell it which variables are what type and then there are tools that check if you're not assigning something different.

(well you -can- compile it. But it's not the compile like the C, C##...)

Mathieu Guindon

7:11 PM

Python is a scripting language, even more so than VBA

Yes I agree.

Actually, according to wikipadia, python is strongly-typed.

Mathieu Guindon

huh

#TIL

O.O what is Stronly-typed :-D

It does type checks at runtime.

Mathieu Guindon

7:13 PM

lol yeah, so, basically like VBA blows up at runtime for stuff any compiler worth its salt would just say "nope"

However, these are really weak type checks as in Does the member exist?.

Mathieu Guindon

@SonGokussj4 when foo is a string, you can't treat it like an int

Okay. But in Python you define what will the type be... It just work even if you do:
my_number = 5
my_number = list(5, 5)

Mathieu Guindon

same as VBScript, where everything is a Variant

yes.

7:15 PM

@M.Doerner Me? no, I'm just passing them along to yet another 3rd party who will send email on our behalf (opt-in, don't worry). I don't know what they do with 'em.

But try to access an element in the first example.

For some reason this simple code line is taking about 6 seconds to execute

Set aCell = .Range("D2:D" & i - 1).Find(What:=projNum, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

It will tell you that the corrsponding member does not exist.

Guess it could leave the leading spaces and let them deal with it... :)

i is only 900

any idea what may be the reason it's so slow?

Mathieu Guindon

7:16 PM

is that inside a loop?

Yes

That checks.
But as I mentioned, you can have

def add(x: int, y: int): --> int
return x + y

And if you check it with another program, it can tell you there is a problem if you pass it a string.

@FreeMan Good, then it is not a security concern for you.

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim i As Long, lRow As Long, projNum As String
Dim aCell As Range
Dim wINP As Worksheet

Set wINP = ThisWorkbook.Worksheets("Trevose Tracker")

With wINP
lRow = .Range("D" & .Rows.Count).End(xlUp).Row

For i = lRow To 3 Step -1

projNum = .Range("D" & i).Value

Set aCell = .Range("D2:D" & i - 1).Find(What:=projNum, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _

this is the full code

Still, the leading spaces are not valid in an email address.

7:17 PM

@M.Doerner whew! :)

Once you start to interpret the address, you should know ehat you are doing.

yeah. I may get 'round to asking them about that, too, but I need to get the other API call functioning before I worry about that too much. I have a work-around, so it's not the #1 priority

Even some common email clients get the resolution wrong for some edge cases.

There are exploits based on this.

just Range.find line is taking more than 6 seconds need help

Mathieu Guindon

put it up on Code Review, there are a lot more inefficiencies in that code :)

7:19 PM

@M.Doerner guesses Outlook/Exchange is one of those.

5 hours ago, by this

Remember, this, it's Microsoft we're talking of

Outlook is actually quite good at it.

#ColorMeShocked

Thing is even when I use the Find function manually using the utility it's taking a lot of time

Why?

So there is no issue in the code but I think something do with excel settings itself

When I use the same code in different workbook it runs fine

Mathieu Guindon

I pretty much never use Range.Find

shove the range into a 2D array, iterate that

you'll be done iterating the whole thing 10 times over by the time the Find returns

also consider not deleting your rows as you pass them (looping bottom-to-top), instead do normal iteration (top-to-bottom) and Union the ranges for the rows you want to delete - see this:

5

A: Looping through an Array backwards (Bottom to the top) VBA

For Each...Next loops vastly outperform For...Next loops for iterating object collections (source), and For...Next loops outperform For Each...Next loops for iterating an array. So, use a For loop if you're iterating an array, and a For Each loop it you're actually iterating the invidivual cells ...

Also, if this is a ExcelDB, use a real database. ;-)

7:26 PM

[rubberduck-vba/Rubberduck] MDoerner created pull request #5150: Fix as type names for array types resolved from COM to merge MDoerner/Rubberduck/MoreFixesAroundFailedCoercionReferences into...

rubberduck-vba/Rubberduck/next

> Closes #5149

@Hosch250 - weird, I'm seeing 2 different things in the headers from Postman (I think)

It's showing me this as the headers:

POST /Locations/GetLocations?Authorization:Basic=<snip>:<snip>&amp; response_type=xml HTTP/1.1
Host: ws.appointment-plus.com
Authorization: Basic (Base64 encoded <snip>:<snip>)
User-Agent: PostmanRuntime/7.16.3
Accept: */*
Cache-Control: no-cache
Postman-Token: <snip>
Host: ws.appointment-plus.com
Accept-Encoding: gzip, deflate
Cookie: PHPSESSID=<snip>
Content-Length:
Connection: keep-alive
cache-control: no-cache
Content-Type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW

but also shows me this:

user image

which appears to be somewhat different

but I don't set any of those when I make my request:

@FreeMan That one looks like the state after the request.

Note that the Connection changed from keep-alive to close.

user image

that's all that I'm setting up to make the request

Basically, Postman auto-sets some headers that the VBA library might not set.

I'd guess the VBA library doesn't set any by default that need to be set.

^

7:33 PM

For example, maybe the connection one.

when using web anything in VBA, you're basically in "manual override" mode

Maybe it's closing the connection without waiting for the response?

there's also this:

I don't think so, not at least with the one he's using

I'm assuming he's still using WinHttp

user image

@this yes

7:34 PM

but headers & cookies he do have to manually re-forward

I'm getting a 500 Internal Server Error from my code

@this That is one of the headers.

I'm explicitly setting Content-Type = application/x-www-form-urlencoded, and setting the reqeust parameters

which is working just fine for the other query I'm posting

this is from the one that does work from my VBA code:

user image

I'm not seeing any difference in the Temporary Headers section between the one that works in VBA and the one that doesn't...

[rubberduck-vba/Rubberduck] build for commit 756763be on unknown branch: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] bclothier closed issue #5149: Code Inspection: Highlighted variable is not the variable discussed in the inspection

[rubberduck-vba/Rubberduck] bclothier merged pull request #5150: Fix as type names for array types resolved from COM from MDoerner/Rubberduck/MoreFixesAroundFailedCoercionReferences into...

rubberduck-vba/Rubberduck/next

@FreeMan (off-topic) what are you using as screenshotter program? Seems like FastStone Capture.

7:40 PM

[rubberduck-vba/Rubberduck] MDoerner pushed commit 756763be to next: Fix as type names for array types resolved from COM

[rubberduck-vba/Rubberduck] web-flow pushed commit f5e88d1e to next

Merge pull request #5150 from MDoerner/MoreFixesAroundFailedCoercionReferences

Fix as type names for array types resolved from COM

@SonGokussj4 Greenshot. I think it was @Hosch250 who mentioned it here a year or so ago. Love it!

10 minutes into a support chat and the last message I have is 3-5 minutes old:

> Thank you for letting me know that and I am glad you contacted us today regarding your query but please be advised that my expertise is in line with your requirements and benefits and anything that has to do with your account and as your trusted advisor for today, I will be more than happy to provide you the best option to your concern.

@FreeMan Phrancis, maybe?

I've never used it.

@FreeMan I'm using it for 10 years. Didn't find anything better. Very glad you like it :-)

ah. that must be it. now that you mention it, I think that's what you said last time I tried to blame you... :)

7:44 PM

I just use the Win+Shift+S screenshot.

It's built into Windows :)

with a crappy diagramming tool

@Hosch250 Oh my god don't write another word. :-D

Greenshot has much better tool for that.

@this English much?

What do you mean? I speak murican.

7:45 PM

@this Huh? No diagramming at all. Just select-and-go.

You get to use Paint if you want diagrams :D

@this Yeah, but your support bot doesn't

@FreeMan doh! Follow the highlight, Luke

lol

@Hosch250 I was pretty sure they could sketch stuff with it. I get several "sent from snipping tool" with fisher-price scrawls all over it.

Oh, Snipping Tool, yes.

That's an app Windows comes with. Not that great, TBH.

The keyboard shortcut is a different feature.

IIRC, it started as a OneNote feature, then Windows took it over, or something.

7:48 PM

so Windows comes with 2 tools?!?

How typically Microsofty

Yeah.

Yeah, you can freehand draw with ~~Sketching~~ Snipping tool. Is ugly, but you get SO bonus points for "Hand drawn red circle". Don't think there's much of anything you can do with <PrtScr>, <shift><PrtScr> or other variations unless you paste into ~~Draw~~ Paint

Mathieu Guindon

holds back from mentioning SnagIt rocks, fails

nextofwindows.com/…

^what happens from beating one's head upon one's desk for far too long

7:50 PM

It's basically capture part of the snipping tool, and just copies it into your clipboard.

Mathieu Guindon

@FreeMan huh no snipping tool lets you draw red circles all you want

you just can't undo whatever you did, but it lets you draw on top of a capture

@PrisonMike Hi!! How is it in the prison, prison Mike? :-D

Bad very bad!!

@MathieuGuindon "Jim, we kind of forgot the Undo feature." "Meh, nobody'll need it. SHIP IT!"

Don't drop the soap!

Mathieu Guindon

7:51 PM

@this lol pretty much

@this v

6 hours ago, by this

Remember, this, it's Microsoft we're talking of

and that is why friends don't let friends use snipping tools.

Mathieu Guindon

but ...SnagIt

not free, though

Is SnagIt paid?

7:52 PM

unless you're MVP

@PrisonMike My condolences, maybe. Just don't be a Jef Epstein.

jinx

Mathieu Guindon

^^^

It's OK. I think I prefer Greenshot anyway. the UI works better for me

Worst thing about the prison are the dementors

7:53 PM

Suck the living soul out of you, I hear.

OK, I have a philosophy question now.

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot prereleased draft prerelease Prerelease-v2.4.1.4942

Yeah. it hurts

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot created draft prerelease Prerelease-v2.4.1.4942

@MathieuGuindon 0:37 ... SnagIt is the only screenshot tool with built in image editing and screen recording. Bulls*it. Fast Stone capture has this for years...

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot published prerelease Prerelease-v2.4.1.4942

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot created tag Prerelease-v2.4.1.4942

7:54 PM

You guys got it soft and cushy.

@MathieuGuindon But I agree it looks good. :-)

Given that a dementor has sucked the soul out of you, does your soul get to leave the prison through the sewer system? Or does your soul stay in the prison, even after your sentence is served?

right now, the support bot is sucking my soul

It goes into the dementors

@PrisonMike But they gotta use the loo sometime?

7:55 PM

Can I make git universally case-insensitive so it quits picking up Value -> value changes in VBA?

@Hosch250 No they were pads

*wear

Mathieu Guindon

@FreeMan by reviewing diffs before you commit, and dropping unnecessary changes?

makes note to self to quit typing value when he really means Value. It's a PITA

one can see why they'd turn into a dementors if they were used as a pad.

Mathieu Guindon

that's how I did with VB6+SVN back then

7:57 PM

@FreeMan git config --global core.ignorecase true

yeah, but... every git status reports back the changes since they've been changed in the host source file

@SonGokussj4 ding ding! can I do that on a per-project? If I'm going to work on the duck, I don't think I want that...

Mathieu Guindon

@SonGokussj4 without --global it's only for the local repo?

yeah.

to both of you

[rubberduck-vba/Rubberduck] build for commit f5e88d1e on next: AppVeyor build succeeded

answers that question.

Mathieu Guindon

7:58 PM

@Duga thanks, already downloaded

for once, I have an up-to-date RD at work!

ok, project builds

and ...I F'd up

« first day (1929 days earlier) ← previous day next day → last day (1251 days later) »

Transcript for

Sep '1917

VBA Rubberducking

This chat has moved to Discord: discord.gg/MYX9RECenJ

antlr com-interop parsing rubberduck vb6 vba whiskey-tango-foxtrot

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile