VBA Rubberducking

Vogel612

7:00 PM

yaaay, I got a red test!

maybe yay, that is

FreeMan

@TweetingDuck Dave Thomas. As in Wendy's Dave Thomas? Isn't he dead?

In case anyone has a bit o' free time:

0

Q: Calculate NPS in a stored procedure

We collect customer surveys and one of them is the dreaded "How likely are you to recommend our service?" question from which we calculate a Net Promoter Score. (I hate this calculation, but I'll leave that discussion for a different time.) We store the surveys in our database, and, since the qu...

Vogel612

there's a null somewhere ..

I'm tempted to declare that test methods don't know how to run themselves

actually... that seems rather reasonable

Mathieu Guindon

7:16 PM

^

FreeMan

hmmm... I've noticed several times that Calculator.exe is in a suspended state on my machine. Not an issue per se, but it's mildly annoying. A quick Google search turned up this Superuser question that also mentions that it interferes with short-cut keys.

There is a windows support thread referenced. They are talking about Windows short-cut keys to launch an app, not application shortcut keys, but I wonder if there is anything in there that would impact RDs recognition of keyboard shortcuts?

Vogel612

aand that dropped the TestMethod to a thin wrapper around Declaration without any reference to RDParserState or IVBETypeLibsAPI

2

nice

this

Good job!

is it also now not a viewmodel? That struck me a bit odd.

Comintern

@FreeMan It's possible. RD's are system wide. That behavior is certainly obnoxious - Windows 10 is slowly turning itself into malware...

Vogel612

@this that part was separated out by me before already. one of the first things I did

FreeMan

7:21 PM

@Comintern indeed!

Vogel612

otherwise I couldn't have moved it into it's own project

this

oh, i missed that, Even better then!

FreeMan

I've not read the full support thread yet, but it did start back in 2016. No mention yet of MS fixing the issue.

Vogel612

hmm ... ~60 todo/fixme/hack markers in the whole solution ... seems good enough

I think the next step after pushing this is to find out how I can fake different test results ...

this

down from what?

Vogel612

7:23 PM

probably slightly up

I've just littered FIXMEs everywhere and finally got to adressing some of them

@this but I got myself an issue to whittle them down a bit :)

somewhat relatedly to that issue: I wonder whether my boss will be annoyed if I drop a few hours onto fixing warnings across two codebases

this

@Vogel612 if you can write unit tests for it, it becomes a bit more defensible

"see, this could have blown up but now we are handling it so we know it won't blow up"

Vogel612

hmm ... a few k of those warnings are "just" rawtypes warnings

IvenBach

YASE! Got 2 diverging workbooks brought back together into 1. Now I'm going to wait for the #IForgotThat moment when it's least helpful.

FreeMan

I read through the whole tread - ended after about 45 days. Back in 2016. No resolution. There was a link to another Superuser thread though that covers basically the same info.

Duga

[rubberduck-vba/Rubberduck] Vogel612 synchronized pull request #4146: (WIP) Splice unit testing away from Rubberduck.Core

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) edited comment on pull request #4146: (WIP) Splice unit testing away from Rubberduck.Core

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4146?src=pr&el=h1) Report
> Merging [#4146](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4146?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/055d9110421e53908807d8aac9348209f4af3b55?src=pr&el=desc) will **decrease** coverage by `0.33%`.
> The diff coverage is `43.08%`.

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

FreeMan

7:34 PM

This is the modern app host. If you kill it, you can't run modern apps. Most people don't care about this anyway. It causes a problem because it listens to all possible shortcut registrations for some reason, it didn't do that before Win10. Windows has always seemed to deal with multiple competing shortcut registrations badly... it leads to a delay. Every answer in this thread is related to such competing registrations in one way or another, but Windows 10 introduced ApplicationFrameHost.exe as a new culprit. — VoidStar Aug 24 '15 at 4:26

It causes a problem because it listens to all possible shortcut registrations

Duga

[rubberduck-vba/Rubberduck] Vogel612 added label feature-unit-testing to pull request #4146: (WIP) Splice unit testing away from Rubberduck.Core

[rubberduck-vba/Rubberduck] Vogel612 added label PR-Status: WIP to pull request #4146: (WIP) Splice unit testing away from Rubberduck.Core

[rubberduck-vba/Rubberduck] Vogel612 removed label PR-Status: Conflicting from pull request #4146: (WIP) Splice unit testing away from Rubberduck.Core

[rubberduck-vba/Rubberduck] Vogel612 added label technical-debt to pull request #4146: (WIP) Splice unit testing away from Rubberduck.Core

M.Doerner

@this No chaining, just LINQ.

this

@M.Doerner good. In theory, that is easier to analyze; we can check whether a SCW is being used in a LINQ and prevent the use of LINQ.

Duga

[rubberduck-vba/Rubberduck] comintern created comment on issue #3996: Hotkey For Refactor Sometimes Not Working

> Linking chat, might be relevant.

M.Doerner

The ComReferenceSynchronizerBase is not disposing a single wrapper it uses.

Duga

7:46 PM

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

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) edited comment on pull request #4146: (WIP) Splice unit testing away from Rubberduck.Core

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4146?src=pr&el=h1) Report
> Merging [#4146](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4146?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/055d9110421e53908807d8aac9348209f4af3b55?src=pr&el=desc) will **increase** coverage by `0.16%`.
> The diff coverage is `51.23%`.

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

[rubberduck-vba/Rubberduck] build for commit 7328ba35 on unknown branch: 52.47% (target 0%)

KySoto

is it bad that i dont really know what unit testing is?

Vogel612

depends.

You should find someone to make you one of today's lucky 10k though

KySoto

i see the option in rubberduck but, i have no idea what to use it for

this

@KySoto have you written a complex procedure that had several possible outcomes and wished you could check if it's working right, without having to do all the setup and cleanup?

KySoto

yeah, i usually step through it and ensure the critical points have the expected value

this

7:51 PM

Right, now further imagine your boss come to you and say, we need to handle something new.

KySoto

i also sanitize my inputs as much as i can

this

and you find yourself changing several place in your code to handle that new request

have you worried that you were breaking something when you were doing that?

KySoto

usually its broken data inserted by operators that causes issues

this

sure, but I'm talking about modifying a already working process

KySoto

they thought up some thing to break it all

yep

FreeMan

7:52 PM

unit testing takes that from "usually" to "always" because you know you haven't broken anything by modifying code

this

so in either situations --- that's exactly what unit testing is for.

What @FreeMan said.

KySoto

to be honest, i usually make my procedures as wide open as i can

so if i THINK one day i might have to deal with situation y

this

Let's take the operator breaking something because it's simpler.

KySoto

i set it to be able to handle it

yeah

this

so let's suppose they broke the system becuase they fat-fingered a ' in the data

so you need to handle that ', right?

KySoto

7:53 PM

usually its a supervisor that goes and fudges data

and they dont know what they are doing

this

so you first write a unit test that assert that procedure will not blow up when handed a '

KySoto

but they know about the shift button

this

This will fail since your procedure right now doesn't handle it right.

But that's OK! You then modify your procedure to handle the ' (or whatever)

then you run all unit tests, including the new unit test you just added

and that way, you validate that your changes did not introduce any new bugs

KySoto

that sounds difficult to integrate into an existing codebase

this

Unfortunately, that is true. That's where the refactoring part comes in.

There's a jerk here who is supposed to fix up one of the refactoring but he keeps put it off because... I don't know. He's a drawler, that one.

KySoto

7:56 PM

so we have several hundred access applications

front ends

some arent used very often

FreeMan

let me take a guess @this jerk's name...

KySoto

but access is the data collection tool of choice at the moment

this

But you should talk to IvenBach, he's in same situation as you are --- he is slowly adding unit tests to his big hairy Excel file(s)

KySoto

though

FreeMan

@KySoto you can disable the functionality of the Shift key in your Access code. I don't recall how, but I did do that at a previous job.

KySoto

7:58 PM

ah yeah, id rather not do that though because some of hte supervisors DO know what they are doing in the tables

FreeMan

You have to run some VBA code to lock & unlock it. I think that the code (especially the unlock code) needs to exist in a different .accdb.

KySoto

and then theres the engineers

they sometimes need to edit the data in hte table out "in the field"

FreeMan

give those who need to a way to do what they need programaticaly, don't just let 'em rummage willy-nilly!

KySoto

heh, all of our thin clients run the access runtime

most operators use those

but yeah, i know what you mean by give them a proper way to edit

its just... HUNDREDS of applications

i mean hell

im still migrating a few access backends to sql server

FreeMan

@KySoto :shudder:

KySoto

8:00 PM

im still migrating a few access backends to sql server

i created an application to keep track

FreeMan

@this @FreeMan knows why to unit test. Don't ask if @FreeMan has implemented unit testing...

KySoto

though unit testing would probably be the most usefull in my function that parses out data from access applications

its this... 300+ line monster of a function

it builds the dependancy web

because before i started

we had access applications that were linked to old archived versions of applications... it was a tangled mess

this

do consider that some refactoring may be needed. Unit tests are meant to test only one thing --- the logical behavior of a function, no more, no less.

KySoto

ive made it a long term goal to just straight nuke that activity from orbit

mansellan

@kysoto do you at least have them under source control? That's what we did at the last job I worked that had a big Access estate.

this

8:03 PM

It sholdn't be messing with files or doing DB access, etc.

KySoto

for the most part yes

this

(though I do think RD is in a dire need of a mock database framework similar to tSQLt)

KySoto

we have a seperate drive with all of hte source copies of our access applications

this

because there are some scenarios that I just don't see happening without that support.

KySoto

then i have common code modules that sit in a master "template storage" that i pull from

and it has its own source thing going on too

when i get into an application that hasnt been worked on for a while, it invokes that update modules function i was discussion with @this

mansellan

8:05 PM

Oh I meant actual SC... they're used to be a plugin for access to vss from microsoft but they dropped it. There are a couple of commercial providers now.

KySoto

well i manually run it anyway

its a pretty well integrated system

all the new stuff is standardized

mansellan

I'm hoping to start an open source project for vb(a) source control but realistically won't be anything usable for 6 months at least.

KySoto

and when i get tasked to do some update on an old app, i standardize it to the new versions

**standardize it to what i put into everything else

i even have some access control for sensitive applications using AD groups

so if they try to do anything form based they get booted

im still working on locking down table permissions but my boss isnt a fan

Vogel612

'nuff coding for today.

this

if security is an issue why not use SQL Server?

mansellan

8:15 PM

^^

FreeMan

^^^

KySoto

for most things we are

im still working on getting everything over

this

ah gotcha

KySoto

then theres the matter of table permissions, like i said, im still trying to get my boss sold on setting them up on the doodle of least access needed

i cant remember hte exact name for that concept

this

oh i might have misread - thinking you meant permissions on Access table

KySoto

8:27 PM

oh no. thats another reason im moving away from access as a backend

this

I think it goes by few different name. Principle of least privilege is one I usually use.

KySoto

we have tables that must be controlled/logged

thats the one @this

so we have triggers logging any activity on a couple special tables

its not perfect, but its better then the forms based auditing that was in use

this

definitely. form based auditing don't really make sense.

KySoto

they dont.

its too easy to just shift open and scramble

the other thing that really sucks about access backends is the whole, you have to have read/write permissions to a database

otherwise you lock everyone who has write perms out

this

and there are some who try to hide that by denying the tranverse permission,.

They do it mainly because the IT is all "no, you can't have SQL Server", so they have to resort to all those crazy gymnastics just to get an illusion of security. Ironic, eh?

KySoto

8:32 PM

well im lucky that i have sql server

i mean... its 2008 r2

so its about to die in a year

but its better than access as a back end

mansellan

@this btdt. At one point I also went to the trouble of setting up a "dropbox" share - logs could be created by all users, but once closed they became read-only.

That was, uh, fun.

KySoto

you just made my face curl up in disgust

and a frown

mansellan

Yep. Should have been a sql server, but that was #NotAllowed

Vogel612

@KySoto at least you're off 2k

KySoto

yeah we have 2k for our ERP only

FreeMan

8:35 PM

@mansellan run your own rogue MySQL server or similar. that'll make 'em happy once they find out!

3

mansellan

The hoops that "proper IT" make "shadow IT" jump through...

KySoto

for everything else, there is sql server 2008 r2

im trying to hint towards upgrading to 2016 to my boss

mentioning things like being out of support next year

and we can directly deploy reports to sharepoint using 2016

and if we directly deploy to sharepoint that cuts down on the need for those access applications that exist to for reports only

this

@mansellan it only get worse when the "proper" IT try to ratchet down everything. It's like grabbing sand and trying to hold it by tightening the grip. People gonna do what they must do to do their job.

KySoto

im lucky, i dont have to build applications for anyone but our company

and my position exists within IT

mansellan

@this Couldn't agree more.

this

8:38 PM

I'm hopeful (mwahahaha) that with power platforms, it will incentivize the IT dept to give more power to the workers.

KySoto

yeah, gotta maintain that balance of security vs usability

this

@FreeMan you'd be surprised how far they will go!

jcrizk

what is the benefit of sharepoint? i've read about it but i don't fully understand it

FreeMan

any love?

0

Q: Calculate NPS in a stored procedure

We collect customer surveys and one of the questions is the dreaded "How likely are you to recommend our service?" question from which we calculate a Net Promoter Score. (I hate this calculation, but I'll leave that discussion for a different time.) We store the surveys in our database, and, sin...

sql sql-server t-sql stored-procedure

jcrizk

also, I think i've done about 50 internship applications so far lol

mansellan

8:46 PM

@jcrizk If you find out, could you let everyone else know ;-)

FreeMan

@jcrizk sometimes, that's what it's like applying for jobs!

jcrizk

@FreeMan you don't say

Vogel612

@FreeMan I'm calling that someone tells you off for the horizontal scrolling

IvenBach

@jcrizk Keep at it. It's tough when you don't get responses back.

FreeMan

@Vogel612 what horizontal scrolling?

Vogel612

8:47 PM

first Select

FreeMan

:)

jcrizk

@IvenBach I'm not discouraged in the slightest; just the nature of the beast.

mansellan

I have zero love for sharepoint

jcrizk

I've been doing it since my freshmen year of college and I quickly learned the nature of the beast lol

FreeMan

ok... TTGH. TTFN!

Comintern

8:48 PM

@FreeMan Put a copy on Pastebin or something - that way it can be viewed as intended.

IvenBach

@jcrizk Glad to hear. I applied to NASA on a lark thinking I'd never have been picked. I was the only candidate my mentor wanted for the internship. #WhodaThought

You can't get an internship you don't apply to. Usually

jcrizk

@IvenBach That's pretty sweet lol and totally true

Mathieu Guindon

@FreeMan I would totally do that

actually for a while I had a rogue Access database setup

M.Doerner

Does a project reference to another Excel file or database show a FullPath?

KySoto

@jcrizk The benefit of sharepoint.... is that it was here when i got here and thats what people are used to.

jcrizk

8:53 PM

:+1:

KySoto

the other part is the person who previously built the sites was not super up to date on tech, but that doenst matter anymore since she passed

Vogel612

BTW if any of you find the time to shoot the EngineTests I pushed a glance, I'd be really grateful for simplifications to the setup there...

KySoto

So since we have it, im determined to see if i can make it useful beyond shared docs which our file server does better) and calendar sharing-y stuff

M.Doerner

Our sharepoint at work is awfully slow.

and finding anything you did not get a direct link to is a nightmare.

KySoto

define slow?

hmm bad site design?

M.Doerner

8:57 PM

I guess.

KySoto

ours isnt too difficult to find at least the area you are looking for

each department has a site

Mathieu Guindon

@M.Doerner it does

M.Doerner

Sometimes I get lucky with the search, but ther are a gazillon documents on that server.

KySoto

ahh yeah

each department has a shared doc library

so the stuff in each area at least pertains to the department

so all of the HR stuff is pretty easy to find

this

@M.Doerner sounds like they didn't configure the search engine fully

M.Doerner

9:00 PM

I am looking at the documents from a 5 year 50+ consultants software implementation project.

Would not surprise me knowing our IT.

this

normally one has to do some setup to enable IFilters and set up a crawl schedule. Then after that, the search should be pretty good

M.Doerner

Hm, finding out whether a reference points to a user project is annoying.

I basically have to look at the file path of the project.

Unfortunately, I can only get that from the project itself.

this

Didn't Wayne abstract the getting of path from a reference?

M.Doerner

Oh, where did we put it?

No, wrong way around.

I have the path on the reference.

The problem is the path on the project.

I have to determine the projectId in case it is pointing to an open project.

Actually, I want its QMN.

this

but if you have the path, can't you enumerate the projects to match the path? It should be also unique?

M.Doerner

9:07 PM

I can do that, but that is exactly what I wanted to avoid to do for each reference on each project.

I might have to simply do that up front.

this

maybe set up a new method on the provider to match based on the path?

that way, the provider can cache the paths

(just for reference: this is the reference i was referring to -- github.com/rubberduck-vba/Rubberduck/blob/… )

mansellan

9:43 PM

well, this is very odd.... its just the caption property of that button. Setting the HelpFile property is, apparantly, just fine and dandy...

so not a null pointer

Comintern

Huh. Is it losing the string somewhere?

this

Just to make sure - did you already verify if setting the caption works at other point?

it might be just that VB6 does not like you touching its bits while it's in middle of setup

mansellan

@this not yet - this was the easier test

will try now.

IvenBach

9:57 PM

@this have you ever had any issues with enum members, when not qualified, causing you grief?

Comintern

@IvenBach Are you referring to VBA or C#?

IvenBach

VBA-Landia

Comintern

I'm more likely to have issues colliding with the referenced ones.

this

can't recall but I always use two part naming, period.

Comintern

You can do that with constants too. This one is fun to slip into a module somewhere...

Global Const vbNullString As String = "vbNullString"

this

10:02 PM

I'm not gonna play Guess Who with any given declaration.

See? I gonna protect myself from ThunderCode.

@Comintern also, rockin' it old skool with Global, eh?

IvenBach

@this Your belt 'n' suspenders approach appeals more and more to me every day.

this

just be sure there's actually a pant for them.

IvenBach

:meh: They're overrated.

this

well, so you go around with belts and suspenders to hold up what? your leg's skin?

M.Doerner

10:21 PM

Let's see how many tests I have broken with my latest changes.

IvenBach

@this I'd say it's holding up my dignity.

M.Doerner

That is a strange test to fail now.

Ok, no wander that broke.

Adding 20 referenced projects with the same name might not be the best idea.

In the end, it is probably my own oversight.

mansellan

OK, progress... setting the caption in the AddClassModuleCommand (because why not?) == success.

so its a startup timing issue

not sure how to properly fix though, as localisation happens OnStartupComplete - there isn't much after that at startup iiuc?

Comintern

10:41 PM

I was just about to mention that.

You could test to see if it's VB6 and then spin initialize off into a Task that starts with Sleep. That would be pretty hack-tastic though.

mansellan

yeah...

still, com interop in the vbe requires hacks...

oooh

setting it directly at the very end of OnStartupComplete works

wth?

M.Doerner

Remind me, which caption exactly is the problem?

Comintern

@mansellan Rubberduck - fighting bugs with hacks.

mansellan

@M.Doerner it's the caption for Rubberduck on the main menu

M.Doerner

Ah, the one in the main commanbar of the editor, right?

mansellan

10:45 PM

yes

hmm... all this is happening syncronously, so there must be something yet to resolve at the point we set it. sleuthing continues...

M.Doerner

We had to realize several times already that the VBE (and now VS6) has not finished loading when it fires StartUpComplete.

mansellan

OnStartupNotQuiteComplete?

2

M.Doerner

E.g. the VBE is still in the process of loading open user projects at that point.

I guess the VBE has some concurrency issues.

this

11:00 PM

did anyone ever try running the startup asynchronously from the startup?

well, no, not async. More like delayed.

Comintern

Not entirely sure how you'd delay it. The IoC takes a lot of that out of our hands.

On the top of what? Because that isn't VBA code. — Comintern 2 mins ago

How can you not know what language you're using...?

M.Doerner

He is using VBA but is writing VB.NET instead.

mansellan

i don't think it's a timing issue, I think its an ordering issue - we're trying to captionify something that hasn't properly arrived yet.

this

@Comintern my thought was that VBE will not process any further until we've returned from the startup. If that's true, then by spinning off a task to sleep, then dispatching back to teh UI thread, to complete the startup of RD

mansellan

from what i'm seeing here anyway

this

11:06 PM

@mansellan you mean it's our fault that we're setting caption too early when constructing buttons?

mansellan

yes

this

seems simpler

M.Doerner

The problem is that you first need to acquire the UI thread context.

Comintern

@this I'm pretty sure that it's going on concurrently based on what I've seen in the native services.

mansellan

nearing in on a fix I think

M.Doerner

11:08 PM

The UI context is not yet there at the start of StartupComplete.

Comintern

^^ I think that's the last thing that gets loaded IIR.

M.Doerner

We acquire it when we call Startup on the App.

mansellan

@this or more particularly, when setting a main menu top-level item. There's something special about them.

Comintern

I almost wonder if it's trying to register the accelerator for it. Have we tried just "Rubberduck" yet?

mansellan

yes, tried that early on

Duga

11:23 PM

[rubberduck-vba/Rubberduck] MDoerner synchronized pull request #4279: Some refactoring of the com collector

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) edited comment on pull request #4279: Some refactoring of the com collector

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4279?src=pr&el=h1) Report
> Merging [#4279](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4279?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/055d9110421e53908807d8aac9348209f4af3b55?src=pr&el=desc) will **decrease** coverage by `0.11%`.
> The diff coverage is `17.14%`.

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

Duga

11:38 PM

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

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) edited comment on pull request #4279: Some refactoring of the com collector

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4279?src=pr&el=h1) Report
> Merging [#4279](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4279?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/055d9110421e53908807d8aac9348209f4af3b55?src=pr&el=desc) will **decrease** coverage by `0.1%`.
> The diff coverage is `27.51%`.

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

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

mansellan

11:49 PM

eureka!

found it, and it explains all the oddness

a quick patch should be easy, a proper fix may take longer

so - first off, i made a mistake

it's not the main menu entry, its the context menu for the forms designer.

and the projects that throw are those that don't start out adding a form to the project

so it's trying to attach a menu to a "context" that hasn't been loaded yet.

Comintern

Ahhh.... That makes perfect sense.