VBA Rubberducking

those are somewhat huge, I highly recommend doing something less large first. That issue should get you around plenty as well ... and it can serve as an entry point into the test explorer, which in turn gets you into unit-tests....

Hosch250

It's been one I've wanted to do for a while, but never had enough time after my internship when I was whacking more urgent bugs.

Vogel612

either way: 2AM, I've been up all day, I'll head to bed.

Hosch250

Sleep tight, @Vogel612.

Vogel612

12:12 AM

g'night :)

IvenBach

I know the parametric one is beyond where I'm at. Still want it.

night @Vogel612

Properly thinking about it Unit Test inconclusive with long path #3843 and not having Inconclusive results would speed up my productivity.

Why'd you have to go and get hired Hosch. You need more free time for RD.

Hosch250

Hey, I told Mat he had first dibs :)

He wouldn't hire me :)

IvenBach

I'm off to home. I'll sneak some time for RD and hope the missus wont notice.

ThunderFrame

@Hosch250 No gas, or car, required if you work for RD... Maybe you could lower your rate to, say, zero?

;-p

Hosch250

@ThunderFrame Not my only expense :P

Just a significant one.

And I don't use my car only for work. Just mainly.

IvenBach

12:57 AM

You can get by without owning a car, I did for 31+ years.

Hosch250

Correct.

I could use duct tape and binder twine to fasten a basket to my bike. I'd be good for about a 10-mile radius then.

this

@IvenBach cos you are/were a city slicker?

here, living w/o a car is practically impossible. Biking is dicey.

IvenBach

@this Please please don't use that terrible term with me. Unfortunately I grew up and live in SoCal, but belong in the backwoods wit the rest of my kin.

I couldn't ever afford to buy a car was the main reason.

Hosch250

@IvenBach LOL!

Yeah, my commiseration. They are expensive. Either you pay the dealership or the mechanic--or both.

IvenBach

I biked till I got married and even then I still biked cause y'know $.$ seems to never like me no matter how hard I work and save.

this

1:06 AM

yeah, I really think we all should do less caring and more biking.

(and it's unforunate that you can't add a -ing to a car)

IvenBach

Less caring is my general rule.

this

but here, they totally screwed the pooch

everything is far away

and everything has a big highway

so it's basically big trucks. If you ain't a truck, yer a roadkill or roadkill-to-be

IvenBach

Sounds like SoCal.

this

Really. Haven't been to SoCal. I always envisioned them being perpetually stuck in traffic jam

thus impossible to make roadkills

IvenBach

Rush hour ~~can be pretty~~ is terrible.

Thankfully I don't live in LA.

ThunderFrame

1:18 AM

@IvenBach it's OK, I can confirm that LA's highways are perpetually stuck in traffic too.

Hosch250

I'm strongly tempted to enable Bitlocker on my work computer.

It'll kill the spy software we use :)

@ThunderFrame Would it be appropriate to ask about future interviews toward the end of this interview?

IvenBach

A project with an Output Type of Class Library cannot be started directly.

Hosch250

For example, when I could be to contacted about the next phase and if I'd need to have an on-site interview.

@IvenBach Correct...

A class library is like a collection of stuff you can use. It doesn't have a defined start/end point like a console app.

IvenBach

I wasn't getting this notice till I updated and did the removal of Retailcoder.VBE

Hosch250

Oh, you changed the startup project.

ThunderFrame

1:26 AM

@Hosch250 I'd ask about what the next steps in the process are, and what sort of timeframes are involved. How quickly will you hear back, are there going to be 5 rounds, if you need me to come to Redmond, how much notice will you provide?

Hosch250

You need to A) set the startup project correctly, and B) set the startup process to EXCEL.EXE.

@ThunderFrame 5 rounds?

IvenBach

@Hosch250 A) How/where do I set this B) complete .

Hosch250

When you set the startup project, open the properties (ALT-ENTER on the project in the solution explorer).

The Debug tab will have a place for you to enter it.

I think you check the radio button "start external program" or something, then provide the EXE.

ThunderFrame

@Hosch250 5 interviews... Some places do that.

Hosch250

I mean, why 5 interviews?

I've only ever had 2--phone and onsite.

ThunderFrame

1:29 AM

Meet the PM, meet the technical lead, do a behavioral with a manager, meet with the Dept head...

Hosch250

OK.

IvenBach

@Hosch250 Project>Properties>Startup Project> RD.Core?

Hosch250

What would the "behavioral" be? See if I can be polite?

IvenBach

For some reason it was RD.Parsing.

Hosch250

@IvenBach I don't know. Vogel knows, and @this might know.

ThunderFrame

1:30 AM

I'm not saying there will be 5, but asking what the rest of the process looks like will prepare you for what could be a 3 week process, or a 6 month process.

Hosch250

OK.

I think they are pretty hot-to-trot to get someone. I've never had an interview scheduled this fast.

But that may be because it does take a long time and they don't want to waste any time putting it off.

IvenBach

Hosch250

Did you set the startup application?

Also, remember this process. It won't be the last time you need to know it.

ThunderFrame

@Hosch250 usually it just checks that you aren't a 2-headed monster. Questions like: how do you respond to conflict? How do you deal with difficult stakeholders? If you were asked to lead a technical workshop on your first day, how would you react? Do you know any good dad jokes?

Hosch250

Umm, OK.

That would be, ummm, interesting?

IvenBach

1:33 AM

@Hosch250 Figured it out. It is indeed RD.Core.

Hosch250

Respond to conflict? I try to avoid it. Keep the atmosphere calm, etc.

IvenBach

A novel concept.

ThunderFrame

Water-cooler skills....

Hosch250

Good dad jokes? Not really :)

IvenBach

I have tons of those.

Hosch250

1:34 AM

Lead a technical workshop, well, I could, so long as I don't have to get up in front of a crowd and present and am somewhat familiar with the topic.

IvenBach

More like inappropriate uncle jokes though.

ThunderFrame

@Hosch250 IKR, it's a trick question... There are no good dad jokes.

Hosch250

If it's more interactive, I'm good at those.

@ThunderFrame Oh yes there are!

I love dad jokes.

@IvenBach We'll have to hijack the channel sometime and swap jokes.

My dad doesn't have any. Maybe that's why I like them :)

IvenBach

@Hosch250 My mentor when I interned got tired of them. Even he said they were bad.

Duga

[rubberduck-vba/Rubberduck] IvenBach synchronized pull request #3835: Freeze column header for vertical scrolling.

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) commented on pull request #3835: Freeze column header for vertical scrolling.

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/3835?src=pr&el=h1) Report
> Merging [#3835](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/3835?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/7a599fe61eefd866fa702a71662d86fba6f946e6?src=pr&el=desc) will **decrease** coverage by `0.23%`.
> The diff coverage is `n/a`.

```diff
@@ Coverage Diff @@
## next #3835 +/- ##
==========================

[rubberduck-vba/Rubberduck] IvenBach commented on pull request #3835: Freeze column header for vertical scrolling.

> I think this should take care of the Retailcoder.VBE rename and get my PR current.

Hosch250

1:37 AM

@IvenBach Let's use this channel and go through them: chat.stackexchange.com/rooms/25394/the-nth-monitor

ThunderFrame

@Hosch250 I forget where I read it, but MS loves to ask non-technical problem solving type questions, and then hear you're reasoning and logic, even if you don't get the right answer.

Hosch250

@ThunderFrame I heard that too. I also heard they don't do that much anymore.

ThunderFrame

Eg. How many petrol stations are there in Seattle?

Hosch250

Well, how big is Seattle?

And what's the population?

ThunderFrame

Or, how does an 80 story apartment building ensure all apartments get hot water?

Hosch250

1:39 AM

@ThunderFrame Well, one solution would be to install a hot water heater in every apartment.

@ThunderFrame The economics answer would be as many as fulfill the demand :P

I suppose that smart-aleck answer wouldn't be considered good :P

ThunderFrame

@Hosch250 you can guess the population, and maybe the number of cars per 1000 people, but you need to think about journey lengths, stations per 1000 people etc.

Hosch250

@ThunderFrame Yeah, I know. I just need some random figures to start with. I never studied geography.

ThunderFrame

@Hosch250 but that's not how they usually do it.

Hosch250

@ThunderFrame No. They install a big water pump and a big hot water heater.

And the apartments at the top don't always get hot water simply because of throughput--the water loses its heat through the pipes. At least, some of them, like my grandma's.

ThunderFrame

Yep, usually up top, but water cools as it travels, so it's not the complete solution.... But again, there's not so much a correct answer (although a construction guy could tell you), as there is a logic to working with an unfamiliar problem.

Hosch250

1:43 AM

Yeah, these are the problems I'm least bothered by.

It's just like solving an issue on a codebase you are unfamiliar with.

I'm mostly worried about a computer-science-level interview.

Like traversing trees, and stuff.

Duga

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

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) commented on pull request #3835: Freeze column header for vertical scrolling.

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/3835?src=pr&el=h1) Report
> Merging [#3835](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/3835?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/7a599fe61eefd866fa702a71662d86fba6f946e6?src=pr&el=desc) will **decrease** coverage by `0.23%`.
> The diff coverage is `n/a`.

```diff
@@ Coverage Diff @@
## next #3835 +/- ##
==========================

[rubberduck-vba/Rubberduck] build for commit eb9f700d on unknown branch: 55.99% (target 0%)

IvenBach

@Hosch250 Heat loss problems were fun in Heat&Mass transfer.

ThunderFrame

@Hosch250 how's your statistics? What's a Bollinger Band? Can you write a SQL query that calculates standard deviation without using a standard deviation function?

Hosch250

@ThunderFrame I didn't do statistics very much, but I can with a bit of brushing up.

I never heard of a Bollinger Band before.

IvenBach

@ThunderFrame Do you work for MS?

@Hosch250 Neither have I.

ThunderFrame

1:52 AM

@IvenBach same building.

Hosch250

I could write a SQL query that calculates standard deviation if I needed to and I re-learned the equation.

ThunderFrame

Then again, its the same building as Apple.

Hosch250

But then, I'd think long and hard about reinventing the wheel.

Well, I hope I could.

ThunderFrame

@Hosch250 it's a common question... It tests your knowledge of self joins and subqueries or CTEs, and it tests your knowledge of maths.

Hosch250

It would take a long time, though, and probably involve if's and stuff.

OK, thanks. I'll take a look at it.

IvenBach

1:54 AM

Off to fail at volleyball.

Hosch250

I know self joins and I've done CTE's before a few years ago.

Wonder if the VS IDE team works with SQL too often.

ThunderFrame

Good point, but I'm sure there would be some DB work... Not many technical jobs can avoid using a DB at some point...

Hosch250

Oh, sure there would.

I learned a lot of SQL on the job at my current position.

I just forget most of it again because I'm not using it intensively.

Like cross apply. I have no idea how to do that anymore, but I used it once.

But if they need a simple select/from/where optionally group/by, I can do that.

I build a couple big paginated ranking/aggregation queries to reduce the data transferred between the DB and the server that I'm proud of. Reduced the time to process + load the data from well over 2 minutes (timing out on production) to about 1 minute for our largest data sets and just milliseconds for most of them.

But SQL is still one of my weaker points.

ThunderFrame

I wonder if MS still uses stack ranking for performance reviews.. IMO, it's a really bad idea, but IIRC, Ballmer was a big fan.

Hosch250

I wouldn't get into performance reviews in my first interview, but I'll let you if I find out and it's not censored.

Got to get to bed. See you tomorrow.

ThunderFrame

2:05 AM

Night

ThunderFrame

2:17 AM

@IvenBach umm, good luck?

this

2:59 AM

@ThunderFrame:

IvenBach

I claim it as my farcical exersize.

ThunderFrame

3:23 AM

@this nice. Will get testing tonight.

this

still testing the uninstalling of previous versions

Duga

4:16 AM

[rubberduck-vba/Rubberduck] bclothier commented on pull request #3836: Gather metadata required for per-user install for Rubberduck

> After doing more tests about mixed modes, I think it would give the best user experience to generate 2 separate `AppId`s for each mode. For example, we can use this pascal code:

```
function GetAppId(Unused: string): string;
begin
if ShouldInstallAllUsers then
result := '{979AFF96-DD9E-4FC2-802D-9E0C36A60D09}'
else
result := '{DF0E0E6F-2CED-482E-831C-7E9721EB66AA}';
end;
```

Note the first GUID is the same `AppId` already used by the existing installers and correspond

M.Doerner

7:16 AM

@IvenBach Make Rubberduck.Main the startup project and set the application to start in its project->debug settings.

Moreover, please test whether the tests with long names are still on next. As stated in the issue, that problem probably has been fixed already with the switch to the new method execution engine.

Duga

10:22 AM

[rubberduck-vba/Rubberduck] Vogel612 merged pull request #3835: Freeze column header for vertical scrolling. from IvenBach/Rubberduck/Issue3829 into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] Vogel612 closed issue #3829: Keep Test Explorer Header visible as you scroll the list

[rubberduck-vba/Rubberduck] IvenBach pushed commit 4bd803fe to next: Freeze column header for vertical scrolling.

[rubberduck-vba/Rubberduck] IvenBach pushed commit eb91056a to next: Merge branch 'next' of github.com/rubberduck-vba/Rubberduck into Issue3829

[rubberduck-vba/Rubberduck] IvenBach pushed commit eb9f700d to next: Merge branch 'next' of github.com/rubberduck-vba/Rubberduck into Issue3829

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

Merge pull request #3835 from IvenBach/Issue3829

Freeze column header for vertical scrolling, fixes #3829

Duga

10:35 AM

[rubberduck-vba/Rubberduck] retailcoder published prerelease Rubberduck-v2.1.2.2981

[rubberduck-vba/Rubberduck] retailcoder created tag Rubberduck-v2.1.2.2981

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

Simon Forsberg

12:59 PM

@IvenBach @Hosch250 It's just Phrancis trying out what happens if we write everything in VanillaJS, without using any framework. Cardshifter will never die ;)

"What is dead may never die" ;)

this

1:13 PM

@M.Doerner In my PR, I now have the new Deployment project as the startup project. Should I change it back when I'm ready to merge the PR?

Duga

[rubberduck-vba/Rubberduck] bclothier edited issue #3850: Redundant whitespace left by the rewriter

this

@Duga edit a typo in the title

Vogel612

@IvenBach RD.Main

Duga

1:40 PM

[rubberduck-vba/Rubberduck] retailcoder commented on issue #3850: Redundant whitespace left by the rewriter

> Would it be acceptable to run indent procedure to fix that?

[rubberduck-vba/Rubberduck] Vogel612 commented on issue #3850: Redundant whitespace left by the rewriter

> Honestly: IMO it wouldn't. We don't want to force SmartIndenter use. The solution should be to update the grammar to parse whitespace in a way that makes the rewriter behaviour correct.

[rubberduck-vba/Rubberduck] retailcoder commented on issue #3850: Redundant whitespace left by the rewriter

> @Vogel612 agreed. However updating the grammar to deal with WS would be excruciatingly hard, and would break many places that deal with this parse tree quirk.

IMO the rewriter itself could smarten up, and verify whether there are extraneous WS tokens on the same line, that should be removed too.

Duga

2:55 PM

[rubberduck-vba/Rubberduck] bclothier synchronized pull request #3836: Gather metadata required for per-user install for Rubberduck

this

@Duga everyone, I would like to apologize profusely in advance for having converted a simple installer script into an unholy big ball of mud held by bailing wire and spit.

5

Duga

[rubberduck-vba/Rubberduck] bclothier removed label PR-Status: WIP from pull request #3836: Gather metadata required for per-user install for Rubberduck

Mathieu Guindon

@this lol, and duck tape!

Duga

[rubberduck-vba/Rubberduck] bclothier commented on Rubberduck.Deployment/InnoSetup/Installer Build Script.iss of pull request #3836: Gather metadata required for per-user install for Rubberduck

this

aw, dang! that was a great pun.... Yeah, duck tape, too!

Mathieu Guindon

3:03 PM

I'm assuming that means your code is now under WorkbookOpen, and still working against ActiveWorkbook - change your code to work against the Wb parameter instead (that's the workbook that was opened); the event might be firing before the workbook is even activated. If it's something else, I'm afraid my telepathic debugging skills are outmatched ;-) — Mathieu Guindon 1 min ago

need to add telepathic debugger to my CV

2

Hosch250

@MathieuGuindon Psychopathic :P

Mathieu Guindon

FWIW that really sound like the kind of assumption that's safe & true, until it isn't anymore. — Mathieu Guindon 5 secs ago

Duga

[rubberduck-vba/Rubberduck] build for commit ed4baf74 on unknown branch: 55.99% (target 0%)

[rubberduck-vba/Rubberduck] build for commit ed4baf74 on unknown branch: AppVeyor build failed

BUILD FAILURE!

[rubberduck-vba/Rubberduck] bclothier synchronized pull request #3836: Gather metadata required for per-user install for Rubberduck

this

@Duga funny Git didn't pick up the new files for some reason. That should fix it.

Duga

[rubberduck-vba/Rubberduck] bclothier edited pull request #3836: Gather metadata required for per-user install for Rubberduck

this

3:13 PM

@Duga #TIL Closes won't work for multiple issues; has to be one each Closes or it won't pick it up, I think.

Mathieu Guindon

@this yup. Closes #1234, Closes #2345, Closes #3456

this

BTW I also think we could do a tweet blast once we have had 2 or 3 other contributors test the installers to get ~~guinea pigs~~ testers to try out before we can merge the PR. May be a good way to attract new contributors, maybe.

Mathieu Guindon

agreed

was going to go nuts about announcing per-user, non-admin installer anyway :)

this

just wait until we have 2 or 3 other people sign off on my mud ball. ;)

that way I can blame them when reports of Rubberduck setting their laptops on fire come in, mmmkay? :p

2

Mathieu Guindon

haven't had a chance to test it last night, so..

Duga

3:22 PM

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

[rubberduck-vba/Rubberduck] build for commit 983a877f on unknown branch: 55.99% (target 0%)

this

and you saw the bit about i18n?

Mathieu Guindon

no

the installer is en-US only?

this

need french, por favor.

Not anymore.

Mathieu Guindon

cool

this

but there are custom messages

those are english.

so if you run it in french or german, those custom pages will be in english.

but there's relatively few custom messages.

CallumDA

3:36 PM

1

Q: Linear Congruential Generator on VBA

i'm trying to replicate the Linear Congruential Generator in VBA but my procedure returns to me an Error '6': Overflow... Sub test() Dim a As Long, c As Long, period As Long Dim seed As Long, sample As Long, max As Long Dim i As Long seed = 1234 sample = 2 max = 100 ...

What's going on here?

Duga

[rubberduck-vba/Rubberduck] bclothier edited pull request #3836: Create a Deployment project for metadata extraction and upgrade installer to support per-user install

this

@Duga the original title was no longer accurate. #ScopeCreepFTW

Mathieu Guindon

FWIW, #3836 "Adding per-user installer support" would have been just fine too :)

this

true but there's a whole project behind it....

IOW, if we create a new COM class for whatever, the Deployment project will extract all the required information and set up the registration and then feed it into the installer.

Mathieu Guindon

wiki wants to know that :)

BTW that's totally awesome

this

3:46 PM

oh yeah. I will definitely write it up once it's approved

IvenBach

4:04 PM

@Vogel612 Thanks. I'll be getting no this on my work machine a bit later.

Mathieu Guindon

4:17 PM

ugh. cringe

This isn't "simple" in any way - the proper term is "naive". You end up with a single God procedure that's responsible for everything that ever needs to happen. When things start going wrong (need to debug something), or when a 3rd option needs to be added, this solution becomes a horrendous mess of spaghetti code. Future you will definitely regret doing it this way. At the very least implement separate procedures for the CSV & TXT handling. — Mathieu Guindon 1 min ago

IvenBach

Yesterday I found out Win10 has a Paint3D app. I'm just now realizing 3d stuff is going to become commonplace for kids.

> Bla... Bla... Bla... hundreds of lines of code does some stuff to csv files...

@MathieuGuindon ^ That's the cringe part.

this

@MathieuGuindon one thing I'd love to do is to post a new CR post relating to the Deployment project to get reviews - my concerns is mainly around the (abuse?) of the post-event builds to run DLL that was just built. The CR should only focus on that aspect but it would be time-consuming to create a MCVE. I see it's OK to link as long I embed all the code I need. I'm just worried it'll be too isolated to qualify.

Hosch250

In other words, you are saying "Here's a gun. Here's how you shoot yourself in the foot. See you in the hospital." — Hosch250 8 secs ago

I'm in a horrible mood for interacting with random unintelligent people right now.

Maybe I should take myself off the internet for a while.

I'm going to get myself banned on SO one of these days for that kind of comment.

CallumDA

I'm a big fan of that comment

IvenBach

@Hosch250 I'd say less accustomed to software dev. Can't teach nuances and benefits to someone just starting, usually

Mathieu Guindon

4:33 PM

@this rule of thumb, don't post a MCVE on CR

the rubberduck tag will be happy to have another question though

this

oh. I thought CR needs a working sample

Hosch250

The full working code. Not a "sample".

Not an MVCE.

Mathieu Guindon

no, it needs full context, and code that works as intended

this

but it also can't be just "look at this huge project over there on GH, and figure it out and gimme working code, ok?"

Alright.

GTK.

Hosch250

No broken code, and the code must be in the question.

Mathieu Guindon

4:34 PM

154

A: A guide to Code Review for Stack Overflow users

What distinguishes Code Review from Stack Overflow? Different communities have different rules to address different needs. A quick summary: $$ \begin{array}{l|l} \textrm{Code Review} & \textrm{Stack Overflow} \\ \hline \text{Open-ended questions} & \text{Questions with a specific goal} \\ \tex...

Hosch250

You can break sections out of the code, etc. But what is there must be complete.

this

thanks for the link. I had searched the meta but it was 50-50 to my reading.

Gotcha.

Duga

4:49 PM

[rubberduck-vba/Rubberduck] JosephCullen starred us

2

IvenBach

The correct dll to register for RD is ...\RD.Main\bin\debug\Rubberduck.dll? I'll make sure to inclued the /codebase /tlb flags.

Mathieu Guindon

@IvenBach should be

IvenBach

I want to avoid marrying the wrong princess this time. I can't afford to work another 7 years to marry the right one.

2

Hosch250

@IvenBach The wrong one might just be the one that gives you more heirs anyway.

IvenBach

touché

Well that was insightful.

I had the path to the Debug folder where RD.dll is. I ran Clean build and poof all the files disappeared.

It helps to visually see the affect of what pushing all the buttons does.

this

5:00 PM

that's expected of clean

build, however should return it

IvenBach

And it did.

this

(though, only build will delete it but only when it's actually building)

IvenBach

I'm a hands-on learner.

this

that also implies that if you clean, then try to use your Excel....

IvenBach

And rebuild will kill each project as it builds.

this

5:01 PM

it gonna be "where the ducky go"

IvenBach

Like I've ever done that... :gulp:

hrm.... That's odd. The splash screen didn't show up.

this

prolly cos u dont' have the addin registration

that's something VS won't do for you

IvenBach

:+1: RD is loaded, just that tick mark wasn't enabled.

this

oh, good.

IvenBach

I already checked the registry and the values are all there and valid.

this

5:03 PM

prolly got it disabled before.

here's to a happy marriage!

IvenBach

I'll ask a derpy question once so I can learn from my mistakes.

this

until the next version at least. Then it get ugly.

IvenBach

Why's that?

this

That's a joke

IvenBach

even more dll un-register carousel-ing?

#HumorFail

this

5:04 PM

cos you married to the princess, but new version => new princess

Mathieu Guindon

PSA - there's a +500 bounty on a VBA question:

17

Q: A new approach to multithreading in Excel

Intro Multithreading tools exist in Excel - often to run macros in multiple instances of Excel, or to convert macros to vbscripts that can run independently. However I've often come across projects where I'd like to delegate multiple tasks out to async processes, and creating multiple instance...

beginner object-oriented multithreading vba asynchronous

IvenBach

But of course... You always want the newest model.

this

hey, I fail sometime.

oh that?

yeah i saw that before but....

that's not multi-threading.

Mathieu Guindon

+500 should be a motivator

aye, it's async

IvenBach

What's the difference between them? AIUI async is just the results aren't mandated to come back in the order you sent them out in. IE 1,2,3,4,5 results could be r2,r4,r1,r5,r3.

Multithreading is having each process on its own?

Mathieu Guindon

5:08 PM

process != thread

IvenBach

I know the term thread is used, but what is a thread.

Mathieu Guindon

you know that yellow line that highlights the current line of code when you're stepping through?

you can picture that as your thread

IvenBach

Yes.

Mathieu Guindon

there's only ever one such "current line"

IvenBach

process is a synonym for application a-la-Excel?

Mathieu Guindon

5:09 PM

sort of

the process owns a number of threads

so, process > thread

IvenBach

I'm good with sort-of, until my pedantry level rises.

That means a process can be the parent of various threads?

this

yeah

IDK if you ever saw something like that but if you have a MsgBox from VBA open and there's an animation running in background on Excel (or whatever), that was 2 threads running by same process.

The key thing is that you never get to interact with more than one thread in VBA code. It's single-threaded by definition.

Excel (as well any other programs) itself has a number of threads, which is why it can keep doing "calculations" while you run your VBA code... more or less.

But the bad thing is that if the calculation calls a VBA function, then they're blocked until the VBA thread is idle in order for the calculation thread to call into it. which is a big part of why using VBA functions can significantly slow thing down when used as a formula, I guess.

(did chat die for a moment back there?)

IvenBach

@this Yep

Was about to ask the same.

Mini-panic attack over not having access to the pond.

@this So Excel itself is multi-threaded, but VBA is single?

this

yeah

IvenBach

@this Part of the impetus for using any built-in functionality.

this

5:20 PM

yeah x2

IvenBach

Now I understand why so many veteran excel users advocate using built in WorksheetFunction.FooBar() over creating your own VBA version.

Mathieu Guindon

@IvenBach the calc engine would be using multiple threads I think

this

and keep in mind that the thread for last decade is more cores, rather than more cycles

IvenBach

Does VBA has its own calc engine that's separate from Excels?

this

we haven't really increased that much in cycle speed. Rememer the days when CPUs were measured in megahertz?

No, cos it's not a calc engine, per se.

IvenBach

5:22 PM

Yes

this

but for last few years, CPUs has stagnated in terms of CPU speed. YOu aren't seeing any 100ghz CPU anytime soon.

Thus, we have more cores... which means programs that runs on mulitple threads are better off than a single threaded program because each thread can run on each core

which is why you can see better performance on such implementation.

IvenBach

@this That's because of practical limitations? I read about it a while back but lacking better comprehension didn't grok everything.

this

i think so, I don't know for sure.

Mathieu Guindon

@IvenBach VBA is running on the "main thread", or "UI thread" - that's why you get "(not responding)" when VBA code is hijacking it for a long time

this

but it suffices to say that we are at a point where it's easier to add a new core to the CPU than more cycles to the CPU.

Hosch250

5:30 PM

Hey, @Greedo. Have you seen Rubberduck yet?

Greedo

@MathieuGuindon I see the distinction, but I think you're misunderstanding the aim of the project. If I use Excel to create a few new instances of InternetExplorer, what happens? I believe (I may be wrong), that the light lifting; setting up search strings, sending them to internet explorer, initialising the IE objects etc. all happens in a single thread of EXCEL.EXE. The bulk of the work, sending and receiving the request goes on in the IE instances, which run on different threads to EXCEL.EXE.

@Hosch250 Use it in a limited capacity, definitely don't get the most out of it yet!

Hosch250

OK. Here's where we build it.

Mathieu Guindon

@Greedo because it's another process entirely ;-)

Hosch250

Most of us are brilliant VBA'ers, and the rest are brilliant C#'ers. (Puffing is legal in marketing, right?)

Greedo

Anyway, by running the IE objects asynchronously, Excel can get on with other stuff while they are running

@Hosch250 Oh yeah, I saw in the description, it's a pretty cool project

Hosch250

5:33 PM

I'm not a VBA'er, BTW.

this

TBH, I wouldn't bother with a VBA-based solution to conduct this kind of thing. I mean if you have mscore.lib referenced, might as well do it in C# and do it all there.

IvenBach

@Hosch250 I get to be the exception to that rule :wink:

this

In my case, I use vbDotNetLoader to late-bind my custom .NET assemblies, register the events (late bound), and I can just listen for events from the .NET assemblies running asynchronously.

Hosch250

@IvenBach If you are a rarity among rarities... :)

Greedo

@MathieuGuindon Exactly, so that's where the project is useful; not in multithreading excel, but in handling other processes which themselves run asynchronously on other threads. Internet stuff is big for it, because the majority of execution time is waiting for responses, so may as well be done asynchronously. But I've also applied it to ocr and image processing.

@this Of course there's always a better language than VBA, but I'm stuck with it for now because it's all I've learnt, and a lot of places have Excel (so it does come in handy for high level stuff)

Here another much larger project relies on async http requests, so I needed some VBA to handle them all and this is what I came up with.

this

5:38 PM

what I'm saying is that you don't have to be on VBA

The misconception is that one has to "install" .NET assemblies to use it. That's not true and a xcopy deployment of .NET assemblies is possible.

and with .NET framework installed on OS nowadays, it's fairly easy to get one distributed without requiring a whole installer, so you can distribute it with your Excel workbook.

Quack Exchange

5:52 PM

0

Q: Writing data from multiple arrays into cells

I have a code that writes values from multiple different arrays into a worksheet. Currently i am using a For loop which makes this code very slow for 6k+ rows. The write should start on the second row as the first is header. Is there another way to bulk write the values? My original code: For i...

performance array vba excel

Transcript for