VBA Rubberducking

but in practice it's impossible to get that much allocated

nevermind that is 255 chars

@KySoto At what point? Like I said, I couldn't replicate that at all.

Clean and rebuild.

6:27 PM

lol

hah

im doin nothing to truncate that i know of

but it is

the string i pulled out

is directly from the parameter

the debug window shows that the Len(argSql) is 403?

yeah

im confused by that too

humor me. don't delete & re-create.

Just change the querydef's SQL.

mid debug, or pass a different query

6:31 PM

e.g. CurrentDb.QueryDefs(argQueryName).SQL = argSql

in lieu of the whole body in the AttachQuery

i ran that in the immediate window

it worked.

what?

i think you're running into a bug w/ DAO lib

-_-

hmm.

can't replicate it either.

tried this MCVE

Public Sub t()
    Dim s As String
    s = "SELECT '" & String(5000, "a") & "';"
    x1 s
    x2 s
End Sub

Public Sub x1(ByVal s As String)
    Dim q As DAO.QueryDef
    Set q = CurrentDb.CreateQueryDef
    q.Name = "foo"
    q.SQL = s
    CurrentDb.QueryDefs.Append q
End Sub

Public Sub x2(ByVal s As String)
    Dim q As DAO.QueryDef
    Set q = CurrentDb.CreateQueryDef("foo2", s)
    CurrentDb.QueryDefs.Append q
End Sub

note that I'm more likely to do something similar like x1 rather than x2 because there is a bug with DAO.TableDef where doing it inline causes error.

but that's not the case here, so I have no idea what is making it truncate.

i think i'll just go with the workaround

6:39 PM

yes, all that deleting & recreating is busy-work

♫ Busywork keeps the boss happy!™

the whole point of it, is that the querys that pop up in htere need to be in pretty much every access app we make, so it makes more sense to just have it auto insert them in (it only happens in the dev folders though, not production)

sure, but it'd be simpler to just blindly create the QueryDef, ignore error, then set the SQL property.

or if you rather, write a QueryExists fucntion and conditionally create querydef.

TBH I never saw the pointing of looping a collection just to avoid an error.

Suppose you have 10000 items in the collection and the one you want is the very last one?

#Slow

if you have 10k items in a query def collection... you might be doing it wrong

6:48 PM

Most likely, but even if it's only 500 (and that is a reasonable in a big enough project), still.

if you blindly were adding it

what if it already existed?

would it error on add? or would it replace it

no-op

you'd just get an error which you ignore (that's what i mean by doing it blindly usually)

Duga

[rubberduck-vba/Rubberduck] comintern synchronized pull request #4517: Sort out dlls for different versions.

Duga

7:16 PM

[rubberduck-vba/Rubberduck] Vogel612 deleted branch retailcoder-patch-1

[rubberduck-vba/Rubberduck] Vogel612 deleted branch aboutbox

[rubberduck-vba/Rubberduck] Vogel612 deleted branch revert-4414-bugfixes

[rubberduck-vba/Rubberduck] Vogel612 deleted branch av-org

@MathieuGuindon - just noticed a little SCP niggle. Starting with var1 & | var2 (with spaces on either side of the caret). Hitting " yields var1 &"|" var2.

You have an existing issue to add that to, or you wanna new one?

there's currently no known or opened SCP issues

prettifying var1 & | var2 apparently yields var1 &| var2

ugh. ... git highway anyone?

2

@Vogel612 look at Linux's git and come back and tell us if ours is a highway.

Life is a highway...

7:21 PM

it's much less of a highway around .3150

@MathieuGuindon is that the RD prettifier or the VBE's?

@FreeMan there's not really such thing as a RD prettifier

more like just VBE's and RD's fake-sham-for-testing-only

So, you know how all the Chicago-ans call themselves "chitowners"?

I take it all back.

7:23 PM

I realized the other day that it's pronounced "shit-towners".

holy carp, the graph is the whole with of my console at 2.1.2.3021

I just wish our highways had that many lanes :)

Duga

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

Actually, on second thoughts, I don't.

well imagine a world where instead of putting up with lame surly drivers who can't drive their way out of a wet paper bag, you just switch -b myPersonalLane

7:27 PM

@Vogel612 how does that happen?

@this I've seen massive rolls of dry paper fly out of garbage trucks and blip other cars on the freeway.

git ar-hero

2

I've also seen people throw popcans out of their window into my car.

not perfectly sure, but most likely long-running branches

On the freeway, at 60mph.

7:28 PM

preferrably those that are based off weird things

obtw: I'll be nuking prereleases from 2.1.1 and 2.1.2 for a bit

@Hosch250 oof. Can't say I saw that before. That'd make me a teensy weensy bit annoyed.

We should be able to pause Duga for a bit.

And then un-pause her when we are done.

@this oh...

@this It was annoying.

Fortunately, it didn't damage my car,

It was scary, and amusing, to see the paper come out and blanket the car behind the garbage truck.

that must have sucked to be that poor driver

7:31 PM

umm ...

I was afraid there would be an accident, but it just blew away.

I'm just now realizing that we're not differentiating master tags from prerelease tags

I saw it before them because I was two lanes over, so I could see the side view of the truck.

soo ... this is going to be put on ice for now

for the record: 112 releases are 2.1.[12].*

of those, two are probably full releases

-.-

since this is taking a bit longer, I won't have the time right now. So I'll be doing it later tonight or possibly tomorrow

feel free to poke me with something that's not a sharp stick so I do it :)

1.7K commits will do that..

can we script tag deletion?

7:35 PM

> Currently the code is taking a long time to execute (a day per percent).

Wow. That's sloooow.

OP misspelled "forever".

2

@Comintern Just 1/4-1/3 of a year. Somewhere in there.

2/7?

@MathieuGuindon Should be able to script it remotely with git push origin :tagname. stackoverflow.com/a/5480292/4088852

@Hosch250 I always thought it was "shy". I can see where "Shi" would be more appropriate and not in the least bit surprising.

i my co worker sent me a funny

news.ycombinator.com/item?id=18442941

@FreeMan I thought it was "chy"

8:02 PM

Duck check: Logging for RD is part of telemetry data?

@IvenBach Telemetry data would be sent back to us automatically.

RD just uses normal logs.

also, telemetry usually doesn't just cover normal logs operation

^

it'd usually include other data like how many times you clicked this or that button

telemetry is "phoning home" which a lot of people consider spy-ware

8:04 PM

^

hence the need for explicit TOS agreement

makes note to incorporate telemetry in RD...

@this That depends on the level the users sets, usually.

:)

hmm are there any major OSS projects that does use telemtry?

telemetry could collect/aggregate data such as anonymized usage stats for RD features, but also usage stats of VBA and Excel type libraries, something I'm pretty sure Microsoft would pay big bucks for :)

@this and opt-in, not opt-out

8:06 PM

unless they already collect that data?

they don't. and can't.

why "can't"?

I understand. We could in effect see what feature is being used.

Yes. That's the whole idea behind the telemetry - it would help you prioritize the features/bugfixes.

:+1:

8:08 PM

@MathieuGuindon I'm sure they already have usage stats of VBA and that stuff from the Full level setting.

Always learning.

Windows collects it, not VBA.

It's deeper than the application layer--its way down at the device driver layer.

@Hosch250 that's tinfoil-hat worthy

Of course, the Windows team may not be willing, or even able, to share it with the Office team.

idk if that level is even useful...

8:09 PM

@this They can tell which button you click at that level.

And which key you press.

ok.

the Office Extensibility team working on Office-JS would love to see that data

Everything goes through the keyboard/graphical interface/whatever drivers.

They can capture any input they want.

I figured that; i wasn't sure if the drivers even knows there's a button

I thought they deal primarily with coordinates

or something like that.

@this From what I'm looking at, it's kind of between the drivers and applications, so it would be able to tell which button from its interactions with Windows.

8:11 PM

hmm sounds more closer to the message pump then.

Yeah.

makes sense.

Using Application.Statusbar = "Foo" for a simple update of where my code is. Since I've gone from Excel2010 where this was quite nice to Excel2016, this is usually what I get:

Any idea why it's dark grey on a green background? other than to be unreadable

Remember, the parent system can always get everything from the child system.

@FreeMan to help you exercise your eyes.

8:12 PM

The tricky part is preventing the child system from being able to get anything from the parent system.

we could collect metrics and publish a yearly report showing how using rubberduck demonstrably reduces implicit member call usages, for example. and reduces cyclomatic complexity, nesting, coupling, etc. whatever.

@this yeah, that must be it... I guess that's better than the normal "Not Responding" totally white window I often get...

I'm guessing that the Windows team just can't share the data with the Office team for legal reasons.

Or maybe they just plain don't know the Office team wants it.

-_-

my bug happened again, but reverse

a more realistic guess is that the Windows team simply isn't collecting that data

8:14 PM

@KySoto you sent 255 characters and got 403?

lol

it started on byref, then worked with byval

totally different application

bleh

lunch time

@MathieuGuindon I bet they are. I doubt they limit it to specific applications.

I bet they can, but don't.

That's a pretty big hint that it isn't the ByRef and ByVal...

^

i'm sure it's just a red herring.

Those are not the string truncation you're looking for.

8:33 PM

That feeling when you and the guy in the next cube are chatting through Teams instead of just talking to each other...

WTF?

I make this comment:

Maybe you should edit the title then - "vba excel finding line of crash" sure makes is seem like this is "about finding the offending line". — Comintern 10 mins ago

And the edit is:

> Getting VBA to crash on the line you need it to, to see what is causing the problem

That's easy. Tempted to answer with a bunch of ways to crash Excel...

Make sure the VBE settings are configured to "break on all errors". Then run the code, and hit the "Debug" button when the runtime error pops up. The offending line will be highlighted. — Mathieu Guindon 6 secs ago

See, that's why you're an MVP. I think you might be the only person on SO who figured out what the OP was asking...

lol

he mentioned clicking OK which makes me suspicious that he's not even seeing the debug dialog at all

Break on All Errors is certainly one way but it can get annoying/noisy if you're going through a half-ass of code generating 100 non-fatal errors before you get to that fatal error you want to handle.

8:49 PM

Although that can also be a nice incentive to not rely on the error handler for flow control.

^

OTOH, I have seen others who seriously suggested that they write error-free code just to do that.

e.g. like earlier, KySoto had code that loops the entire collection to ascertain whether an item exists.

What, like "I write error free code because I hate debugging"?

The guy (not KySoto - someone else long ago) argued that it makes it easier to debug when you write code that does that instead of just trying the operation, failing and returning False as the result.

C# dictionaries with their TryGets.

8:51 PM

yeah, sorta like that.

Sometimes it's just simpler to just swallow the errors.

Sometimes it simpler to, say, use a Dictionary if you need an Exists function though.

Great! If it's a built-in collection or one that you don't own?

copy it and sync it just to have a Exists function?

shrug yeah

Maybe. For example, if I ever have a Workbook where I have too many sheets to enumerate them, I have much, much bigger problems.

That's likely true for the Workbooks or Worksheets collections

but that isn't generally true.

8:56 PM

Recordset.Fields?

@this says the Access guy? what happened to use SQL? ;-)

^

In a complex Access project, it's entirely possible to have 500+ items in a single collection.

T minus 3 minutes

@MathieuGuindon Not all information you need are available via SQL, though.

8:57 PM

I fully expect the summit registration site to bomb in 4 minutes

You might be surprised.

Me, too

their server's time is not in sync

@dwirony specifically because it's hard to follow, I'd strongly argue against moving all declarations to the top. A wall of 16 declarations at the top isn't going to make anything clearer. — Mathieu Guindon 8 secs ago

"you are unlikely to drop a refrigerator in the sink". Thanks, I needed that laugh! — FreeMan 27 secs ago

@FreeMan On the other hand, a leaking condenser drip pan makes it not unlikely that you'll have a fridge with a killer ground path that isn't through the case...

9:05 PM

well, that was smooth

It didn't go down?

surprisingly, it didn't.

heck, even the hotel registration was fast

that's the part that usually locks up and crash

Does it usually go down?

it's almost a yearly occurence, yeah

You'd think MS would know enough to not DDOS themselves, even if it was just by enabling it to blocks of people at a time...

9:10 PM

well it's not MS

Oh.

Who is it?

CVent?

they subcontracted out to a company that specializes in event planning

EventCore? something.

Hmmm.

@ViniciusACP welcome to the pond.

@this Recommend my company to them, will you, and give them my name?

That's literally a huge part of our business--and we are good at it.

And if they use us, I'll get a bonus :)

9:11 PM

bwahaha Hyatt already booked. went with Hilton

hmm send me some materials before March and I can pass along. I usually don't interact with them a lot except for the initial in-person registration

@MathieuGuindon not fast enough. :D

Vinicius ACP

@IvenBach Thanks! :)

@this motivaction.com

And be careful not to let the event planner people hear--they are our competitors :)

oh you want to share the business w/ MS?

Yes.

And give my name as a contact.

9:13 PM

I was a bit confused because i thought your company is about providing incentives to employees

It's both.

ah OK

We are a travel and incentives.

Cheap travel is one of our incentives.

@KySoto I have encountered your problem myself. The problem was not the length of the string, but the length of the line. Something in the Access OM did not like long lines.

We often get into the company with travel, then get a small incentives contract, and grow from there.

9:16 PM

Adding explicit line breaks in the string fixed it for me.

^^ The infection starts small and spreads from there?

Something like that...

@M.Doerner interesting. TBH I never had to do that.

Normally the truncation is usually to something like using parameters or running a query that aggregates the result. Those are common candidates for the string truncation.

but I have assigned long SQL string without any linebreaks before.

Oh. My. God.

Wow. That has to be about the most bizarre way to "Clear values" that I've ever seen. You realize that .Cells((k * 9) + (3 + a), j - 18 + c * 52 + g * 26 + b * 104 + w * 208) will give you a deterministic set of addresses in that the loop iterations are hard coded, right? This is convoluted enough that I can't visualize the sheet you're working with, but wouldn't a named range be more appropriate? — Comintern 15 secs ago

An empty cell value will not be Null, it will be Variant/Empty ...pretty sure swapping Null for Empty slashes execution time by quite a lot, since unless I'm mistaken, <> Null will be True for every single cell. — Mathieu Guindon 5 secs ago

9:37 PM

@this I do not remember the exact circumstances in which this happened. (I think with DoCmd.RunSql.) However, I tend to format my sql strings in a way that looks like I would do it in SSMS now anyway.

So, I am not likely to ever encounter that problem again.

with explicit line breaks?

I always do explicit line breaks.

@Comintern something tells me if we get to the bottom of that code, runtime will drop from >1year to sub-second.

It's a lot easier in c#.

I do format SQL string but I don't bother to add line breaks to the string even though I may use more than one lines & concatenation.

9:39 PM

@MathieuGuindon IKR? It looks like it's just calculating a IRR table.

I'm kind of tempted to add an inspection for DoCmd usage, though.

I have this vision of 2 million cells with formulas in them, chugging away as the OP writes them one by one for the next 3 months...

I always use what Rd's smart concat would yield.

I.e. when you past the string into a path through query, it looks like it would in SSMS.

I think the last time I used DoCmd was about 3.5 years ago.

as far as i know thats what you have to use to open reports and forms in access

it's not the only way, though.

9:44 PM

there is another way to open them eh?

yes.

sigh

and its not absolutely stupid?

assuming a code-behind (the form must have HasModule property set to true)...

ah yeah,

With New Form_Thing, I guess

9:45 PM

i never use "macros" or any of that nonsense

Dim frm As Form_MyForm
Set frm = New Form_MyForm
'Keep it alive....

hm.

for an Access form with no code behind, no way to do this without DoCmd, I think.

(this works for report, too)

so what do you do when they start clickin the button over and over? a bunch of... is this active stuff?

don't follow.

9:47 PM

so you have a button that opens myform

the click it, the form opens, the end up back on the original form, and just click the button to open myform again

you don't declare it in the procedure

instead of clicking the tab

if you do, it dies as soon as you leave the button's procedure

hence the comment about keeping it alive

could be a module-level variable

sorry, i think im a bit brain dead today

i should have got that

could be a custom replacement class that mimics the Forms collection

9:49 PM

but what i meant was

the whole, they cause a new instance to be created

@this or a presenter that knows whether a view is shown or not :)

Sure. That's just the implementation detail. Don't want it created, don't.

by clicking the button that "opens" the form

yeah

What Mat said.

@KySoto first thing the button does, is to disable itself

9:50 PM

^

#ProblemSolved

heh

wish that was a standard thing but it's not.

or i can be lazy and just use the docmd.open acform

and enjoy stateful default instances and global state

9:51 PM

^

yeah, it doesnt make any difference with what im doing

and let's not forget the stringified references!

lol

the big question would be, if i started changing it up to work the way you guys are talking about, when i left, if something broke

they would be in a pretty crappy spot

@KySoto The problem is that with stateful default instances and global state, you can't be sure it doesn't make a difference with, well, anything.

because the talent pool in this area... well they were lucky they found me

9:52 PM

if they hired a guy who know a little VBA, sure, they might be.

thats what would happen

but if they hired a professional programmer, the programmer will be "Ok, I can do this!"

as opposed to "ugh, my god why do I have to work with this pos"

again, very difficult to do in this area

we are ag centric

lot of Excel users use Worksheets("Sheet1"), not even knowing there's just Sheet1

it also doesnt help that the available programming jobs here are pretty much... well ruby on rails

9:54 PM

Solid design makes it easier to maintain, not harder.

do we want to enable that mentality? Seems like we've been enabling the same old for last 20 years.

^^

To be clear, that should be "SOLID" (the acronym), not the word "Solid" (which is also true)

heh

Take a look at the VBA tag on SO sometime. The majority of the "WTF does that do" code is the stuff that is poorly designed.

@this TBH, it was meant as "Solid" the adjective, but SOLID the acronym also applies. :-)

lol. Double entendre.

so the "database" class at my local community college (which is all i took database wise) was basically designing an access application with local tables

mostly with macros

9:57 PM

oh boy.

yeah

shrug nothing wrong with that.

so thats your talent pool for the area