VBA Rubberducking

12:01 AM

RELOAD!

[retailcoder/Rubberduck] 14 commits. 159 additions. 1275 deletions.

[rubberduck-vba/Rubberduck] 20 commits. 2 opened issues. 4 closed issues. 8 issue comments. 506 additions. 1993 deletions.

Mat's Mug

1:30 AM

@ThunderFrame that would be massive.. but by June isn't impossible

Dain II Ironfoot

2:53 AM

@Vogel612 RD doesn't expect a remote, or at least it didn't when I stopped working on the SC feature.

Comintern

3:25 AM

2017-01-24 21:24:32.0257;TRACE-2.0.12.38402;Rubberduck.Root.TimedCallLoggerInterceptor;Inte‌rcepted invocation of 'ProcedureCanBeWrittenAsFunctionInspection.GetInspectionResults' ran for 2278ms;

^^^Down from 2 minutes.

Duga

3:37 AM

[rubberduck-vba/Rubberduck] comintern created pull request #2579: Reduced ProcedureCanBeWrittenAsFunctionInspection complexity from O(you've got to be shitting me) to O(k). to merge comintern/Rubberduck/next into...

rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] comintern synchronized pull request #2579: Reduced ProcedureCanBeWrittenAsFunctionInspection complexity from O(you've got to be shitting me) to O(k).

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

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

[rubberduck-vba/Rubberduck] retailcoder commented on pull request #2579: Reduced ProcedureCanBeWrittenAsFunctionInspection complexity from O(you've got to be shitting me) to O(k).

> O(wow)!

[rubberduck-vba/Rubberduck] retailcoder merged pull request #2579: Reduced ProcedureCanBeWrittenAsFunctionInspection complexity from O(you've got to be shitting me) to O(k). from comintern/Rubberduck/next into...

rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] comintern pushed commit de88556f to next: Reduced inspection complexity from O(you've got to be shitting me) to O(k).

[rubberduck-vba/Rubberduck] comintern pushed commit 1db9b082 to next: Merge branch 'next' of github.com/comintern/Rubberduck.git

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

[retailcoder/Rubberduck] web-flow pushed commit ae1a7802 to next

Merge pull request #2576 from retailcoder/next

inspection fixes

Duga

4:28 AM

[rubberduck-vba/Rubberduck] comintern commented on issue #2482: Build 2.0.10 code inspections take a long time

> Progress report - timings for this project with the current build:
```
Parsing: 4.6426s
Resolve Declarations: 21.8388s
Resolve References: 7.4707s
Inspections: 12.4649s

Total Runtime: 46.6875s
```

Times for individual inspections (in ms, descending):
```
Inspection Time (ms)
---------- ---------
ParameterCanBeByValInspection 4098
ParameterNotUsedInspection 2859
ProcedureNotUsedInspection 2705
ImplicitByRefParameterInspection 2656
ProcedureCanBeWrittenAsF

Duga

5:10 AM

[rubberduck-vba/Rubberduck] retailcoder commented on issue #2482: Build 2.0.10 code inspections take a long time

> Unsurprisingly, in `ParaemterCanBeByVal` the hotspot is right here:

var formEventHandlerScopes = State.FindFormEventHandlers().Select(handler => handler.Scope);

In that call tree, `state.AllDeclarations` is iterated twice. Once to get `events`, and once to get `handlers`. But `UserDeclarations.ToList` already iterated them all once already, and the next line goes:

var eventHandlerScopes = State.AllDeclarations.FindBuiltInEventHandlers().Concat(declarations.FindUserEventHandle

Mat's Mug

yay performance profiler

and TTGTB

Duga

[retailcoder/Rubberduck] retailcoder pushed commit 8547d54e to next: added performance profiler report (note to self: not for merging)

Mat's Mug

ParameterNotUsed would reuse the finder cache here:

        var builtInHandlers = State.AllDeclarations.FindBuiltInEventHandlers();

and ProcedureNotUsed has this hotspot:

        handlers.AddRange(Declarations.FindBuiltInEventHandlers());

Comintern

@Mat'sMug Wow, you read my mind.

Mat's Mug

ImplicitByRefParameter as well

        var builtinEventHandlers = Declarations.FindBuiltInEventHandlers();

wow, basically the top-5 slowest inspections all share a common bottleneck

Comintern

5:18 AM

PR already on the way...

Mat's Mug

oh nice!

Duga

[rubberduck-vba/Rubberduck] comintern created pull request #2580: Consolidate FindBuiltInEventHandlers into DeclarationFinder. to merge comintern/Rubberduck/next into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] comintern synchronized pull request #2580: Consolidate FindBuiltInEventHandlers into DeclarationFinder.

Comintern

Shaved off another ~3 seconds. Inspections loaded in 9942ms;

Duga

[rubberduck-vba/Rubberduck] retailcoder commented on RetailCoder.VBE/Common/DeclarationExtensions.cs of pull request #2580: Consolidate FindBuiltInEventHandlers into DeclarationFinder.

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

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

Mat's Mug

I wonder who the first caller is

Duga

5:23 AM

[rubberduck-vba/Rubberduck] retailcoder merged pull request #2580: Consolidate FindBuiltInEventHandlers into DeclarationFinder. from comintern/Rubberduck/next into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] comintern pushed commit 230bd52e to next: Remove 8 expensive calls to FindBuiltInEventHandlers

[rubberduck-vba/Rubberduck] comintern pushed commit 10a773a8 to next: Fix threading for FindBuiltinEventHandlers

Comintern

@Mat'sMug No clue. It deadlocks if it isn't concurrent though.

...thus the 2 commits.

Mat's Mug

:)

first caller is an inspection, that's why

most finder stuff is already cached from resolver passes

Comintern

Damn. That insane performance project is inspecting faster than it's resolving now.

Duga

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

Mat's Mug

pretty sure declarationList.Where(..) could leverage the finder cache to locate built-in event declarations even faster ;-)

the var handlers = declarationsList.Where(..) iterations are less obvious though

Duga

5:27 AM

[retailcoder/Rubberduck] comintern pushed commit 230bd52e to next: Remove 8 expensive calls to FindBuiltInEventHandlers

[retailcoder/Rubberduck] comintern pushed commit 10a773a8 to next: Fix threading for FindBuiltinEventHandlers

Comintern

I'm almost thinking it's time to start looking for resolver bottlenecks.

Mat's Mug

hehe

Comintern

2.0.12 is on track to be epic.

6

Mat's Mug

if we implement all inspections currently on a GH issue, I think we'll have over 100 of them, or close to that. they have to be fast :)

@Comintern IKR!

if only that darn commandbar would keep quiet :)

Comintern

I'll have to take another look at that tomorrow - too bad work picked up again or I'd do it during the day...

I'm pretty sure that at this point that's the main stumbling block for a consistent clean exit.

Mat's Mug

5:32 AM

aye

'night!

Comintern

Night.

Mat's Mug

awesome work there, btw

Comintern

Thanks.

Duga

5:49 AM

[rubberduck-vba/Rubberduck] PeterMTaylor commented on issue #2482: Build 2.0.10 code inspections take a long time

> Wow @comintern I love this report as it is useful for benchmarking any vba code. Is this an ad hoc one or is available for anybody?

[rubberduck-vba/Rubberduck] PeterMTaylor commented on pull request #2579: Reduced ProcedureCanBeWrittenAsFunctionInspection complexity from O(you've got to be shitting me) to O(k).

> O(boy)! Dats good 😊

Comintern

1:59 PM

@Duga @PeterMTaylor - I just pulled that out of the RD log file with a regex.

Quack Exchange

2:37 PM

1

Q: Efficient method to sort read-in text file in Excel

I've read in a text file to excel from a database and I've done it in such a way that it filters out unnecessary columns. My approach to filter rows was to use two subroutines and call the 2nd from within the first. It takes ~8 seconds for the sheet to be filtered and there is only 400 or so rows...

0

Q: VBA Message Box a bit clunky, can my code be optimized?

Currently writing code so that when a red colored cell appears in a column a message box will appear letting me know that column A for example has an error. Would it be possible to optimize the following code so that it goes to column ED and there isn't 134 + lines of code to write? All help is g...

vba

M.Doerner

3:25 PM

@Comintern Could you share the regex?

I am currently putting some work into refactoring the ParseCoordinator and seeing what this does to the performance would be very helpful.

Comintern

3:46 PM

@M.Doerner I just hacked it together in NP++. It was along the lines of .+Intercepted invocation of '(.+).GetInspectionResults' ran for (.*)ms;

M.Doerner

4:55 PM

Thanks

Mat's Mug

9:38 PM

interesting

1

Q: Difference between Excel.ThisWorkbook and just ThisWorkbook?

I have some VBA code that ran fine in Excel at my desk at work but at home it's crashtastic. Turns out all I had to do was change Set shtObj = ThisWorkbook.Sheets(1) to Set shtObj = Excel.ThisWorkbook.Sheets(1) Anyone know why? Our organization has just moved to a SharePoint platform, which...

excel vba object qualifiers

Comintern

shtObj? That's like double-Hungarian.

Comintern

9:55 PM

You're trying to unload the default instance of the form from inside the default instance of the form. Change Unload UserForm1 to Unload Me. — Comintern 33 secs ago

Why are UserForms so hard for everyone?

Mat's Mug

default instances, I guess

@ticker another one that could use an indenter

ThunderFrame

10:49 PM

0

A: Difference between Excel.ThisWorkbook and just ThisWorkbook?

Does it work if you change it back to ThisWorkbook? I suspect it will, and the reason would be because the VBA recompiled itself (and didn't compile properly the first time - hence the propensity to crash). Recompilation occurs when the version details embedded in the file differ from the versi...

Mat's Mug

@ThunderFrame ha, good point there

Transcript for