VBA Rubberducking

Comintern

12:00 AM

Here's a thought. Pick a non-activex project and compare the symbol loads.

this

Might not anyway... Asm intructions arent really 1:1 to code

@Comintern uh...

RELOAD!

woosh!

[rubberduck-vba/Rubberduck] 2 opened issues. 31 issue comments.

mansellan

12:01 AM

(way above my head)

Comintern

OK, the output window will show symbols loading for every DLL that VB6 pulls in:

mansellan

ok

Comintern

I.e.:

'VB6.EXE' (Win32): Loaded 'C:\Windows\SysWOW64\wldp.dll'. Symbols loaded.
'VB6.EXE' (Win32): Loaded 'C:\Windows\SysWOW64\crypt32.dll'. Symbols loaded.
'VB6.EXE' (Win32): Loaded 'C:\Windows\SysWOW64\msasn1.dll'. Symbols loaded.
'VB6.EXE' (Win32): Loaded 'C:\Windows\SysWOW64\wintrust.dll'. Symbols loaded.

mansellan

heh, mine all say cannot find or open pdb

guess I need to go grab them from MS?

this

Ah you didnt configure symbol server

Comintern

12:02 AM

You need to set up a symbol server.

this

Vs should have one builtin from ms

Just make sure to load it, not skip it

Comintern

Is that an an install option?

this

Uh.... Good q. It was always there for me.

Comintern

^^ I know the local symbol cache has to be configured if you don't want to continually download them.

mansellan

there's an option to use MS symbol server

this

12:05 AM

Yes

Use that its the simplest option

Comintern

Also the slowest.

mansellan

ok, rebuilding

will set up a local server another time

Comintern

If you set up a symbol cache it will only retrieve symbols for ones that you haven't already seen.

this

Just dont be thrilled with the startup

mansellan

wow that's slow!

Comintern

12:07 AM

Thus the cache.

It's super easy to set up:

msdn.microsoft.com/en-us/library/ms241613.aspx

mansellan

Oh I just give a cache dir

win!

awww... they didn't publish symbols for VB6.EXE

Comintern

Turning on the dll exports might be useful, although I haven't checked with the vb6 stuff.

this

Wonder if that would help? stackoverflow.com/questions/16418056/…

Comintern

Wait a second. Is that control unicode aware?

this

Likely not

Comintern

12:17 AM

This is the disassembly that it fails on:

004CBC00  call        0040CAFB
004CBC05  mov         eax,dword ptr [eax+18h]
004CBC08  mov         eax,dword ptr [eax+68h]    <---

this

Unicode waa a thing with 2000 and onwards

Comintern

The first mov succeeds.

I wonder if it isn't getting the correct string length.

this

Hmmm. Did they still ise BSTR?

Comintern

Yeah, I think BSTR came with COM.

mansellan

ok so got all the symbols loaded. doesn't help though as all the frames are from vb6.exe and mso97rt.dll, both built without debug info

Comintern

12:20 AM

The exports don't load anything either.

mansellan

also: wow that's a lot of modules loaded...

this

@mansellan why do you think I keep telling Iven it's all a lie?

mansellan

lol

this

abstractions upon abstractions upon abstractions. It's one giant ponzo scheme

Comintern

Somebody else had the same issue here: office-outlook.com/outlook-forum/index.php/m/224813

mansellan

12:24 AM

so... I wonder if its all commandbar captions, or just some

Comintern

That one alludes to it being the active one.

this

do you know hte path to Office8 dll VB6 uses?

Comintern

C:\Program Files (x86)\Common Files\microsoft shared\VBA

Not sure if this is relevant or not:

windows\dwm\dwmapi\attribute.cpp(92)\dwmapi.dll!72283CF4: (caller: 731690ED) ReturnHr(38) tid(1774) 80070006 The handle is invalid.
Rubberduck.UI._DockableWindowHost : 2018-08-15 19:14:08.1471;TRACE;Rubberduck.UI._DockableWindowHost;IOleObject::DoVerb() called with iVerb OLEIVERB_SHOW.;

windows\dwm\dwmapi\attribute.cpp(92)\dwmapi.dll!72283CF4: (caller: 731690ED) ReturnHr(39) tid(1774) 80070006 The handle is invalid.
Exception thrown at 0x761FDDC2 (KernelBase.dll) in VB6.EXE: 0x000006BA: The RPC server is unavailable.

this

ah, MSO97RT.DLL

mansellan

the addresses in the last exception line look the same?

Comintern

12:33 AM

Do we need a PIA for that?

this

hmm. I wonder if that can be distributed.

Mansellan already made one, IINM.

Comintern

VB6's installer downloaded it on mine.

this

but earlier he observed that a RD install w/o any Office install doesn't work on VB6.

install Office, RD magically works.

mansellan

@this i did, as part of the original vb6 PR

this

thinking that if MSO97RT.DLL is redistributable, we could distribute this to support non-Office installs

#ResearchNeeded

mansellan

12:35 AM

so - it's just the caption of the Rubberduck main menu. Adding if (value != "Ru&bberduck") to the setter makes it happy

@this ah, of course, that's why... The RT part sounds hopeful... #CrossesFingers

this

anyway.

	[id(0x60040003), propput, helpcontext(0x0000138b)]
	HRESULT Caption([in] BSTR pbstrCaption);

so it's unlikely it's an Unicode/ANSI thing, I think

Comintern

good there

this

even if the control itself was ANSI, the re-encoding would have to be done within the DLL, not on our side.

Comintern

Do we get an hWnd for it?

Could just post a WM_SETTEXT to it.

this

hmm. bypass the interface altogehter would make it much easier.

mansellan

12:39 AM

do office buttons have hWnds? iirc they're... funky.

this

Office8's stuff are..... lovely.

we'll have to spy++ to find out but they should

Comintern

^^

mansellan

i'm gonna have to call it a day there... work ~~tomorrow~~ later today

ttgtb

Comintern

night

mansellan

thanks for all the help @Comintern, @this - would never have got any of the above on my own

Comintern

12:42 AM

NP

this

yw

hmm. plenty of sketchy websites and suspicious DLL download sites offering the file.

ah, VB6 has a text file REDIST.TXT listing all DLLs and stuff that may be redistributed.

unfortunately, MSO97RT.DLL isn't listed. :\

Comintern

1:05 AM

What did other addins used to do?

this

IDK. I never done any VB6

I was too busy picking my nose in its heyday, you see...

Comintern

I'm looking at the legacy SmartIndenter code. The interface doesn't look the same.

this

wasn't it written in vb6, though?

Comintern

Yep.

Shouldn't matter though, it's using the same controls.

this

Huh. We're using similar APIs for hotkeys.

Duga

1:26 AM

[rubberduck-vba/Rubberduck] comintern assigned comintern to issue #4013: IgnoreModule annotation doesn't stop all Inspection Results

Duga

1:40 AM

[rubberduck-vba/Rubberduck] comintern created comment on issue #4026: False name clash warning on Refactor|Rename

> This is fixed, probably by (as of now un-merged) changes in #4274.

[rubberduck-vba/Rubberduck] comintern closed issue #4026: False name clash warning on Refactor|Rename

IvenBach

2:00 AM

:barf: Past home time, way past...

this

3:10 AM

I'm proud of myself - I managed to achieve this (note the output):

Comintern

3:21 AM

Duck check. Am I missing anything here?

        /// <summary>
        /// Tests to see if a Declaration implements another Declaration as an interface member.
        /// </summary>
        /// <param name="member">The member to test for implementation.</param>
        /// <param name="interfaceMember">The interface member to test for implementation of.</param>
        /// <returns></returns>
        public static bool ImplementsInterfaceMember(this Declaration member, Declaration interfaceMember)
        {
            if (interfaceMember.Accessibility != Accessibility.Public

I think the order can be optimized a bit to short circuit on cheaper tests.

Duga

[rubberduck-vba/Rubberduck] comintern assigned comintern to issue #4178: Implement Interface refactoring doesn't implement get/let/set for public variables

this

TBH I'm more concerned about the readability. This has a mix of positive and negative assertions.

and it looks like we're dealing with both member and the class level?

Comintern

Only members (have to be public ProcedureTypes or Variable).

this

ok, the name PropertyTypes made me thought it was looking up all procedures

but that's jsut me looking at the code w/ zero context

Comintern

I think the inverted version might be less readable:

    if (interfaceMember.Accessibility == Accessibility.Public &&
        ProcedureTypes.Contains(member.DeclarationType) &&
        (interfaceMember.DeclarationType == DeclarationType.Variable ||
         ProcedureTypes.Contains(interfaceMember.DeclarationType)) &&
        (member.ParentDeclaration is ClassModuleDeclaration declaration &&
         declaration.Supertypes.Contains(interfaceMember.ParentDeclaration)) &&
        member.IdentifierName.Equals($"{interfaceMember.ComponentName}_{interfaceMember.IdentifierName}"))

this

3:31 AM

it also doesn't help that we're mixing ands and ors

Comintern

@this That's a valid review - I should change the xmldoc.

this

it's probably my OCD from SQL but I try to minimize the # of "nestings" among the ORs and ANDs. That results in fewer branches which usually help with readability. I don't know if it's possible but I would see if I could group them all into one group.

Comintern

/// <summary>
/// Tests to see if a member Declaration is a concrete implemention of an interface member.
/// </summary>

this

now that's more clearer.

Comintern

@this Yeah, I might give that a shot. The tricky part is that I was trying to order them by short-circuit performance.

this

3:34 AM

if you want to support short-circuit, maybe consider breaking them up into methods?

so that it's more readable via the method names?

Comintern

I think that kills the performance with the stack pushes and pops. This is going be called a lot.

this

hmm, yeah.

i know! 1000 pages of comments!

that always help, right?

Comintern

lol

this

Sorry, that's all I have. Good luck!

Comintern

Mainly after any cases that I might have missed.

Inarion

6:29 AM

@Comintern If all you have for tools is Excel - everything looks like a worksheet to you.

IvenBach

7:57 AM

Night pond

Duga

10:59 AM

[rubberduck-vba/Rubberduck] mansellan milestoned issue #4280: Some VB6 projects cause an RD Access Violation

[rubberduck-vba/Rubberduck] mansellan created comment on issue #4280: Some VB6 projects cause an RD Access Violation

> Only one commandbar control is involved - the main Rubberduck menu (caption Ru&bberduck). The COM target appears to have lost its pointer at the point where the caption setter is called.

Duga

12:28 PM

[rubberduck-vba/Rubberduck] CodeJericho starred us

5

FreeMan

12:50 PM

Interesting, I just got an RD state of Unexpected Error when parsing an Excel '16 project.

I'd left it sitting open on my machine overnight and the file's stored on the network. Maybe a network glitch?

some relevant log bits:

2018-08-16 08:35:06.8970;DEBUG-2.2.0.3632;Rubberduck.UI.Command.MenuItems.CommandBars.AppCommandBarBase;(23997610) Executing click handler for commandbar item 'Unexpected Error', hash code 52413307;
2018-08-16 08:35:06.9124;DEBUG-2.2.0.3632;Rubberduck.Parsing.VBA.ParseCoordinator;Parsing run started. (thread 64).;
2018-08-16 08:35:06.9280;INFO-2.2.0.3632;Rubberduck.Parsing.VBA.RubberduckParserState;RubberduckParserState (26) is invoking StateChanged (Started);
2018-08-16 08:35:07.4596;INFO-2.2.0.3632;Rubberduck.Parsing.VBA.RubberduckParserState;RubberduckParserState (27) is invoking StateCh…

2018-08-16 08:35:09.6238;ERROR-2.2.0.3632;Rubberduck.Parsing.VBA.ParseCoordinator;Unexpected exception thrown in parsing run. (thread 64).;System.InvalidOperationException: Sequence contains no elements
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at Rubberduck.CodeAnalysis.CodeMetrics.CyclomaticComplexityListener.ExitFunctionStmt(FunctionStmtContext context) in C:\projects\rubberduck\Rubberduck.CodeAnalysis\CodeMetrics\CyclomaticComplexityMetric.cs:line 62
   at Rubberduck.Parsing.Grammar.VBAParser.FunctionStmtContext.ExitRule(IParseTreeListener listener) in C:\proj…

2018-08-16 08:35:09.7228;INFO-2.2.0.3632;Rubberduck.Parsing.VBA.RubberduckParserState;RubberduckParserState (30) is invoking StateChanged (UnexpectedError);

Musta been a network glitch. It parsed clean yesterday, and after closing Excel & reopening the file, it parsed cleanly again this morning.

I can write up an issue if anyone wants to see the full log, but I'm considering it the network's fault for now.

ThunderFrame

1:05 PM

@FreeMan I seem to recall a similar issue when leaving Excel overnight, but no network issues when doing so (file was local)

FreeMan

1:33 PM

Want me to write it up?

this

2:04 PM

@Duga @mansellan does that imply that you already verified that you can set other captions without trouble?

@FreeMan My guts say need a MCVE; if same thing happens even w/o RD loaded, then it's not RD's doing but if it only happens when RD is loaded and Excel left idle, then that's definitely worth reporting.

FreeMan

@this I'll leave it open overnight again, just to see if I can get a repeat. If I do, then I'll do some additional testing to see what happens w/o RD loaded. this is gonna be a loooong test cycle...

this

sorry, man!

The country and the duck salutes you, @FreeMan

FreeMan

not as sorry as the person who picks it up to try to fix it! ;)

hrm... I have to report YTD NPS scores on a monthly report card. I've been calculating them in my VBA code. Now I have to do a graph showing the YTD NPS score over a rolling 12-month period. I think it's time to calculate that NPS in a stored procedure instead of pulling all the bloody data over the wire.

I've already got some SQL code that I load up for testing/debugging purposes, time to make a SP outta it.

this

have you seen tSQLt?

M.Doerner

That error is strange. The declaration finder does not have the function declaration for the context of the function declaration the listener is exiting.

FreeMan

2:11 PM

yup.

wait... tSQL t?

what's the extra t for?

this

Yes. It's a unit testing framework for T-SQL

mansellan

@this captions for all other buttons are fine. I even tried "Test" on the problematic button to rule out (for example) a clash with the shortcut key - no dice.

FreeMan

tsqlt.org cool... nope, hadn't seen that before

^bookmarked

this

so.... it's specifically the caption Ru&bberduck that makes it blow up?

even if you put it on some different button?

mansellan

no - it's specifically the button to which we apply that caption - i.e. the "header" of the main RD menu.

this

2:13 PM

@FreeMan it's perfect for this kind of situation where you need to verify that the output will look exactly as whatever you had it originally.

@mansellan Ok so giving it "Test" on that same button = OK

FreeMan

velly, velly intelesting...

this

giving any other buttons the caption Ru&bberduck = OK

mansellan

@this no. any attempt to set caption text on that button fails.

this

ah OK

mansellan

at least, in the place that we set it atm

yet to try from a different timing point

this

2:17 PM

At least it's only one control.

mansellan

yep, hoping it will turn out to be something straightforward

this

(of course, having said that, I've gone and jinxed you; everything now will blow up to smithereens. I apologize profusely in advance)

mansellan

rofl

KySoto

morning

FreeMan

in tSQL, Decimal [5,2] will hold numbers from -100.00 through 100.00, correct? Since I'll be doing math to get there, is there any value in increasing either the precision or scale to allow for rounding?

monking!, @KySoto

KySoto

2:23 PM

i would think it would be more like -999.99 to 999.99

i guess i could test

actually you might want numeric

i dunno.

nevermind from what i googled, numeric and decimal are hte same

FreeMan

@KySoto Yeah, very valid... blinders on thinking about my specific application...

that's what the MS docs say.

Also, use (), not []...

KySoto

yeah true.

though im lazy, i use ssms to build tables rather than writing out the t-sql

this

eye twitch

FreeMan

@KySoto what ^ he said

Comintern

^ :involuntary shudder:

FreeMan

2:29 PM

lol

Comintern

The weird thing is that I write the t-sql because I'm lazy.

this

^ Once you get to that point, it becomes faster to just write it out than mousing'n'clicking

and more importantly you say exactly what you want. The designers are maybe OK but they have this tendency to add lot of crap

FreeMan

I write the SQL because there wasn't dragon-dropping to do it for you when I learned.

Comintern

And MS is a lot less likely to change t-sql than the UI.

this

@Comintern funny, they haven't changed the UI.... it still shows timestamp.... WHY?!?

FreeMan

2:32 PM

@this especially if your designer is Access. involuntary eye twitch/shudder/mild convulsion

this

@FreeMan at least you can turn off some of them via the client settings.

Comintern

@FreeMan Not to mention the major convulsions that come from reading the SQL it generates.

FreeMan

@this you can????? Dang, maybe I need to read the instructions

this

Yes, for example, I assume you refer to automatic creation of index - that can be disabled.

FreeMan

@Comintern parenthesis for breakfast, lunch and dinner, with an afternoon snack thrown in for good measure

@this mostly the abundance of ()...

@this am I correct in my assumptions?

12 mins ago, by FreeMan

in tSQL, Decimal [5,2] will hold numbers from -100.00 through 100.00, correct? Since I'll be doing math to get there, is there any value in increasing either the precision or scale to allow for rounding?

this

2:34 PM

@FreeMan that's a flaw of query designer, not table designer.

FreeMan

@this mmm. yeah. good point. I was thinking "designer" in general...

this

@Comintern yeah, that's just sad. If I had to write much more complicated Access queries, I would be using a plugin like this: fieldeffect.info/wp/access-sql-editor-documentation

It's only $10

@FreeMan What KySoto said - I think that'll give you -999.99 to 999.99. If you want to further constraint to only -100.00 to 100.00, you would need a CHECK constraint, or otherwise enforce it via your stored procedure.

Comintern

I write them in notepad++. It has decent SQL syntax highlighting.

ThunderFrame

Jun 9 '16 at 19:54, by Duga

> This is a bit off-topic but you're the guy who made the Access SQL Editor @transistor1? I use that addin every day, it makes it possible to use Access without going insane, thanks for making it :)

this

@Comintern can't say I'm a big fan of copying'n'pasting'n'shuttling

FreeMan

2:37 PM

@this OK, as I said, blinders. I'm not worried about a 9 in the hundreds place...

thx!

Comintern

@this I can't say I'm a big fan of Access.

this

lol

it pays the bill.

FreeMan

@this imma buy that for the office if they won't stump up for it! My co-worker/very occasional programmer writes his in Access and that's some UGLY stuff.

Comintern

@this Also can't say I'm a big fan of bills either. I'm sure it beats working with DBMS's that have been obsolete for 20 years on a regular basis, but that pays the bills here.

this

do be aware that if they edit it outside the plugin, you get the ugly stuff again and may lose any comments/customizations.

ThunderFrame

2:41 PM

@this What do you use when you need to execute several/multiple queries/macros (eg. for ETL purposes), in Access? I've always written my own BatchRunner, that executes queries/commands defined in a table. That way I can easily enable/disable certain steps, and optionally resume from a certain location. I'm curious how you do the same.

this

@ThunderFrame I don't do that typically.

We will use SSIS, for example.

I see Access as mainly a front-end program that has a good support for SQL binding & temporary local storage.

As an example, we typically might build a form to collect files they pick, and either 1) upload to SQL Server via XML for fast bulk inserts or 2) move the files to proper location, and then kicking off a stored procedure to process the import. If the ETL is fully automated, then Access won't be involved at all.

Duga

[rubberduck-vba/Rubberduck] retailcoder merged pull request #4274: Misc bug fixes from comintern/Rubberduck/next into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] retailcoder closed issue #4197: ProcedureCanBeWrittenAsFunctionInspection should ignore members that do not assign to the ByRef parameter

[rubberduck-vba/Rubberduck] retailcoder closed issue #4202: 'Rename Parameter' QuickFix fixes too much

[rubberduck-vba/Rubberduck] retailcoder closed issue #3800: Indenter cycles declaration indents on and off

[rubberduck-vba/Rubberduck] retailcoder closed issue #4221: Adding a test module results in unable to create test module error

ThunderFrame

@this I'd agree, but I've found myself in situations where I need a temporary local database of data that is stored in multiple locations. I need to reconnect linked tables, import the data for each location. Setting up Access Macros could do the job, but they don't offer the ability to disable certain steps, or to resume, so my BatchRunner has always been my goto approach.

Duga

[rubberduck-vba/Rubberduck] retailcoder closed issue #4241: Find All Implementations is broken

[rubberduck-vba/Rubberduck] retailcoder closed issue #4179: Implement Interface refactoring doesn't implement any members if any member already exists

[rubberduck-vba/Rubberduck] retailcoder closed issue #3949: Wrap VB6 CommandBarEvents

this

@ThunderFrame you don't need to use macros. You could kick off a VBA procedure in the Access, for example.

Duga

2:47 PM

[rubberduck-vba/Rubberduck] retailcoder merged pull request #4268: Fix the leaky (event) sink! from mansellan/Rubberduck/3949 into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] mansellan pushed commit a0cfa89e to next: Closes #3949

[rubberduck-vba/Rubberduck] mansellan pushed commit 5c763f47 to next: Changes following PR comments

[rubberduck-vba/Rubberduck] mansellan pushed commit d5dab45d to next: Strengthened SafeRedirectedEventedComWrapper

[rubberduck-vba/Rubberduck] mansellan pushed commit 59b5c9d3 to next: Fixed leak

[rubberduck-vba/Rubberduck] mansellan pushed commit 79a9f017 to next: Fixed bug with AttachEvents

[rubberduck-vba/Rubberduck] mansellan pushed commit abf9e6e6 to next: Fixed rewraping issue

[rubberduck-vba/Rubberduck] mansellan pushed commit fc5ce70e to next: Disallow reattaching COM events

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

Merge pull request #4268 from mansellan/3949

Fix the leaky event sink for VB6 commandbars

this

But the problem is that if you are automating it fully, Access is wrong choice because it's not meant to be used headless. None of Office programs are. If you did it all using only DAO and ADO, without touching any of Office OM, then you might be OK, but it takes careful design.

ThunderFrame

@this sure, but disabling a step is a code change, if the steps are defined in a procedure. Using my approach, it's a data change.

this

Cool. Haven't used that particular program. May look at it. Why wouldn't you use something like SSIS?

I get using Access for a ETL with some human intervention (e.g. selecting files or some manual data massaging); but for an automated batch processing?

ThunderFrame

@this The DBA's don't trust me ;-|

this

Maybe you need to stop writing thundercode?

Duga

2:51 PM

[rubberduck-vba/Rubberduck] retailcoder commented on Rubberduck.Core/UI/CodeExplorer/Commands/AddComponentCommand.cs of pull request #4225: Adds Add Predeclared Class Module Command

FreeMan

@this never!!! that's what's keeping RD in edge-case bugs!

@Duga @mansellan apply a liberal bead of plummer's putty, did you? :)

Duga

[rubberduck-vba/Rubberduck] retailcoder commented on Rubberduck.Core/UI/CodeExplorer/Commands/AddPredeclaredClassModuleCommand.cs of pull request #4225: Adds Add Predeclared Class Module Command

this

My condolences, though. We've had a number of clients who were in that situation, as well. What they don't understand is that they've basically created a shadow IT that's unaccounted for. But hey, no skin off my nose....

FreeMan

@this hey, watch who yer talkin' about!

Mathieu Guindon

@ThunderFrame that "Add Predeclared Class Module" command is BLOODY AWESOME and it's going to be one of the most-used RD features - at least as far as I'm concerned =)

I wonder if the command text should say "PredeclaredId", or if that would be confusing

ThunderFrame

2:55 PM

@this In one instance, there are 5 geographically dispersed Sybase servers, each with a subset of the data I need. i.e. the data doesn't exist in one place. I need about 20% of the total data from certain tables. I can't execute queries/joins on the remote tables without Access causing locks, so I execute remote SQL into temporary tables, then link to them, and download the data I need.

Mathieu Guindon

like, "Add class module with PredeclaredId"

this

@MathieuGuindon absolutely agree - there's so much potential. Need that template going... ;-)

Duga

[rubberduck-vba/Rubberduck] ThunderFrame commented on Rubberduck.Core/UI/CodeExplorer/Commands/AddPredeclaredClassModuleCommand.cs of pull request #4225: Adds Add Predeclared Class Module Command

this

@ThunderFrame right. Access is perfect for that kind of job, gluing together data from disparate sources; it's just that we typically have a human involved since there's some kind of review process required for that so it cannot be done full-on automatic (nor would they want to). Truly headless process starts to make more sense when you have pretty much a fixed format.

Transcript for