VBA Rubberducking

Duga

12:01 AM

RELOAD!

[rubberduck-vba/Rubberduck] 13 issue comments.

[Zomis/minesweeper-server] 3 commits. 413 additions. 415 deletions.

[Zomis/xploria-perms] 1 commit. 33 additions.

[Minesweeper] Games Played: 98, Bombs Used: 45, Moves Performed: 12303, New Users: 27

Mathieu Guindon

1:44 AM

It would be nice if IDE's supported coloring member variables differently from other variables. — Scott Langham Sep 22 '08 at 9:58

hmm...

this

2:02 AM

@MathieuGuindon feels kinda wrong to rely on colors to distinguish one variable from another. I'm thinking that if your code requires an IDE to be comprehensible, isn't that a smell?

Mathieu Guindon

lol true

still better than HN though

this

makes me wonder whether there's big style difference between those who code in an text editor vs. those in IDE.

Mathieu Guindon

I like the one that says seeing m_ tells them where to go look for the declaration.

like goto definition or ctrl+click wasn't a thing.

and yet I cling to my _underscore

this

yeah, I have had that feeling that we're still doing it HN-esque; only not so blatantly obvious but it's still there.

(in my case, it's usually those that gets suffixed... stuff like fooTextBox, to distinguish from fooField or something silly like that.)

Mathieu Guindon

...I suffix control names too

feels like "ha! no prefix!"

this

2:09 AM

IKR? One thing that suffix does have over the HN prefix is that it's more clear and isn't subject to variants

(e.g. txt vs tbx vs tx or whatever)

Mathieu Guindon

a semantic meaning like Field isn't harmful though

this

But in the end, it's still encoding the type....

Mathieu Guindon

FooField?

this

you'd have to if you had more than one field to work with

Mathieu Guindon

BarField

GarField

Mathieu Guindon

2:27 AM

Curso de VBA em Excel - Aula Extra - RubberDuck - Traga o VBE para este século

►

ugh

runs inspections, doesn't talk about the fix button, and the undocked toolwindow has the linkbuttons at the bottom entirely out of sight. guy goes and types "option explicit" manually, renames his modules manually, types "public" in front of the test procedure...

I really need to start making RD vids

3

like, really

Mathieu Guindon

3:04 AM

It is worth pointing out that 0 / 0 is a special case. The normal argument against calculations of the form x / 0 where x <> 0 is that there is no number y such that y * 0 = x, and that x / 0 is therefore undefined. This is not the case for 0, as 0 * 0 is indeed 0. So if VBA generates a different error for 0 / 0 as opposed to 1 / 0, I take my hat off to it! — Jonathan Willcock Aug 3 '17 at 14:18

#nuggets

IvenBach

3:29 AM

@MathieuGuindon Do it. It's not like you're trying to write a book. Or herding ~~cats~~ ducklings for an OSS project. Or even working full time. You have plenty of time for all these ideas.

Mathieu Guindon

lol

IvenBach

You're one of the reason why I started my Excel blog trying to teach my co-workers.

All the links I get made me wish I had a canned explanation for questions I get.

Duga

[rubberduck-vba/Rubberduck] hitmanpc starred us

4

Quack Exchange

5:09 AM

0

Q: Excel VBA: Looping through table rows and replacing keyterms in word template

So below is my code for a sub which does the following: read header of a table into an array reads first row of said table into a 2nd array loops through the first array and replaces the marked keywords <keyword> in a word template file (.dotx) with the data row cell (includes header and ...

IvenBach

5:27 AM

ooof... Nerd-sniped by my own question. That's rough.

night pond</iven>

M.Doerner

7:08 AM

0/0 is as undefined as 15/0. From a mathematical standpoint the division a/b is just a shorthand for the multiplication a*b^-1 where b^-1 is the inverse of b in the group (C\{0},*). </math mode>

Duga

10:41 AM

[rubberduck-vba/Rubberduck] Dob4115 created comment on issue #4922: Code explorer does not automatically track "active" codepane

> Agreed. The most important thing is to indicate the module and that will allow a first pass at the mechanics required to get the feature right, such as opening the correct path in the tree GUI, scrolling the tree to bring the active module into view, etc.

FreeMan

10:59 AM

@MathieuGuindon can we change fonts in the IDE???

Mathieu Guindon

Yes but then it must be Comic Sans

FreeMan

I'd rock that!

@MathieuGuindon OutStandingInMyField

Maybe if the user turns off the HN inspection, silently run it in the background, then set ForegroundColor = BackgroundColor for any detected instances. :) <evilLaugh>

FreeMan

11:14 AM

for my git workflow on VBA projects, this is what I'm thinking:

1) local HD directory for exporting all VBA code modules
2) Personal network share directory for my repo
3) Shared network directory as the "master" repo for everyone to push to

that make sense?

especially since both 2 & 3 will be backed up by Corp IT every night while ! won't be.

or does 1 need to really be a repo, too?

M.Doerner

I do not think 1 is useful.

Usually, you just work on the source currently loaded into the workspace of your local repo.

Since the VBE does not directly work off files, it is the next best thing to export to and import from the workspace of your local repo.

So, I think either 1 or 2 is not really needed.

Unless you want a github style workflow with forks.

However, that is probably overkill.

this

@MathieuGuindon ask and ye shall receive:

Enjoy!

FreeMan

11:31 AM

@M.Doerner Thanks, Max! I'm currently set up with 1) & 3), but was thinking that adding in 2) might be worthwhile.

I might shift 1) to 2) simply for the nightly backups, though. If my HD were to fail, I'd lose all the work I'd done but not pushed to production.

Of course, if we keep a master/production branch on 3) and make a next branch that we push to regularly, with a variety of WIP branches, then I can push to 3 on a daily basis and only merge to master when it's thoroughly tested.

right?

Mathieu Guindon

@this =D

FreeMan

@this that white on blue is so old skool WordPerfect v2.0!!!

I must be doing something wrong (he says, not for the first time)...

I installed the git-scm.com version of the git client. When I do something like git log -p -2 I get automatic pagination, which is perfect. I hit space to go from page to page. When I get to the end of the output, I hit... I dunno, space, enter, esc, ctrl-c, something eventually returns me to my $ prompt, but then I get no visual display of what I've typed.

For example, I can type git log<enter> again at the $ prompt. I cannot see "git log", but I get the output.

the only way to fix it is to close the git bash session & restart.

What am I screwing up?

Duga

12:10 PM

[rubberduck-vba/Rubberduck] retailcoder added label up-for-grabs to issue #4923: Inspection to warn about interfaces exposing too many members

[rubberduck-vba/Rubberduck] retailcoder opened issue #4923: Inspection to warn about interfaces exposing too many members

> **What**
An inspection that flags interfaces with more than `n` members (10? 20? 50? Configurable?) and reminds users about the Interface Segregation Principle.

This should include classes' default interface, since any class can implement any other class' public members.

**Why**
Interfaces should not be designed to continually grow new members; we should be keeping them small, specific, and specialized.

---

**QuickFixes**

1. **Move Members**

A refactoring that pops a di

[rubberduck-vba/Rubberduck] retailcoder added label enhancement to issue #4923: Inspection to warn about interfaces exposing too many members

[rubberduck-vba/Rubberduck] retailcoder added label difficulty-02-ducky to issue #4923: Inspection to warn about interfaces exposing too many members

[rubberduck-vba/Rubberduck] retailcoder added label feature-inspections to issue #4923: Inspection to warn about interfaces exposing too many members

[rubberduck-vba/Rubberduck] retailcoder edited issue #4923: Inspection to warn about interfaces exposing too many members

Mathieu Guindon

@sputier @andrew_hoefling @Frackham1 Great idea, it's up for grabs! https://github.com/rubberduck-vba/Rubberduck/issues/4923

Tweeted by rubberduckvba on April 18, 2019 at 12:10 PM in reply to sputier

Vogel612

@FreeMan q

git log uses less internally, which is a better version of more.

you quit it with q

though Ctrl+C and Ctrl+D should also work

M.Doerner

12:31 PM

And after editing a commit message it is Esc - > :wq - > Enter

As a windows user, the vim editor really caught me by surprise.

Vogel612

use git config --global core.editor to change it

one of the many things notepad is useful for

note that the commit only comes through if the program returns with a nonzero exit code on closing the file given, so using np++ is not a good idea

Duga

[rubberduck-vba/Rubberduck] retailcoder created comment on issue #3618: "Move Members" refactoring

> @Bzgr I think the linked issue might be throwing a wrench into the class-to-class scenario, or warrant an interface-to-interface scenario (very much not trivial if already implemented). Thoughts?

FreeMan

@Vogel612 thanks for the tips. I set git config --global core.editor to use np++ on install. I intend to type the commit message on the command line -m 'message', so I don't think that'll be an issue, but I've been wrong before...

q. I'd have never thunk that...

@M.Doerner I've used vi before, but it's been many moons...

and yes, it's a nightmare!

this

12:49 PM

> and yes it's a nightmare for mouse users!

T, FTFY. ;-)

FreeMan

comfortable with a keyboard, but many years using 98+% Windoze has unlearned me some *nix CLI ninja skillz

Tweeting Duck

When performing a code review: 1. Mention what you like, don't focus only on the bad things 👍 2. If you are nitpicking, be clear about it. (nit: I don't like this name). 3. When you are not sure about something, ask for clarification. 4. Always be nice. Don't forget to thank.

Tweeted by SitnikAdam on April 17, 2019 at 12:05 PM

Due to me doing a lot of support for VBA code nowadays. I am going to have to check out @rubberduckvba

Tweeted by donbowman_info on April 18, 2019 at 3:48 AM

In #VBA with Rubberduck you can decorate a member with (no space between @ and Obsolete): ´@ Obsolete("Little bandage for the huge technical debt") Marks the procedure as obsolete; code inspections will then give you a warning for every usage. https://twitter.com/sputier/status/1118433966334386176

Tweeted by rubberduckvba on April 18, 2019 at 11:48 AM

FreeMan

Well, this sucks: Application.VBE.ActiveVBProject.VBComponents(workingName).Remove is returning a 438 Object doesn't support this property or method. Can I not specify which VBComponent I want by passing it the name as an index?

@TweetingDuck #TIL!

this

1:05 PM

I don't think the syntax is right.

Remove is a member of VBComponents collection, not a member of VBComponent object.

you wanna more like ...VBComponents.Remove ...VBComponents(workingName)

FreeMan

so I need to call .Remove with an argument that is a VBComponent and that is what I identify with the subscripted name of the component.

That makes sense!

BTW- attempting to use VBComponents.Remove to remove the module that is currently executing fails! <-- #ThingsThatAreGoodToKnow

Vogel612

@FreeMan I wouldn't have expected anything else...

FreeMan

#SpokeTooSoon it does not update the PE to show the removal until code execution has stopped. Once code execution has stopped, the existing module is, in fact, gone!

#VBAExpectTheUnexpected

this

yeah, you're basically pulling the carpet out under your feet.

FreeMan

Wheeee!!!!

It does continue to execute, though, which is, I guess, kinda nice!

this

1:15 PM

@FreeMan Excuse me, need to call my lawyer. Something about copyright infringement.

@FreeMan FWIW, even if it did work, I still wouldn't want to do it to a module that's currently executing.

FreeMan

I'm working with some old code from Chris (pre-git integration days old!). He does explicitly skip the module name that he gave the code. For kicks, I decided to not skip it, just to see what happens.

I'll probably put the "skip me" code back in... ;)

I am being very careful to export my code to disk before running the import code that attempts to delete modules.

@this that worked, btw. Thanks again!

makes much more sense than the way I did it. I had the example right in front of me, but just didn't see it.

this

1:58 PM

le sigh. Why do people persist in doing wrong things even though they've been told how to do it right....

FreeMan

you talking programming or society in general?

this

programming

I've told them never to do >= startDate and <= endDate or variants of but they still do it anyway

Mathieu Guindon

2:16 PM

#guilty

FreeMan

#metoo

liking my little private git repo! I have the import/export code working the way I like it (huge shout-out to @rubberduck203 for the initial code).

Just need to work on the git-related thought process. I started working on one thing, but forgot to create a branch to switch topics, so I've got 1 change and 1 partial change comingled into one commit.

I feel like such a grown up now!

Duga

3:13 PM

[rubberduck-vba/Rubberduck] retailcoder edited wiki page VB_Attribute Annotations

Mathieu Guindon

Is there any way to access the Class Attributes without creating the class in notepad and importing? — Michael James 7 mins ago

KySoto

@this idk if i could not use variants, specially in cases where i don't know if something is going to be a single value or an array (when I'm dealing with parameter lists for example)

this

no, not Variant data types - variants of the example

KySoto

OH

Mathieu Guindon

lol

this

3:19 PM

e.g. BETWEEN startDate AND endDate is a variant of >= startDate & <= endDate

KySoto

eh kinda

if you dont provide a time component it defaults to midnight am

this

which is bad.

KySoto

yesh

this

and usually isn't what people mean anyway.

Mathieu Guindon

aye, bit me more than once

KySoto

3:20 PM

thats why i dont use between

Mathieu Guindon

I do, but explicitly cast to Date (i.e. never with DateTime)

this

it get really worse when you need to deal with different languages/systems which may use different data types with different range/precisions.

KySoto

though i guess between might work properly if i force the time component to midnight am on the start, and then a second before midnight pm

Mathieu Guindon

@this like char(10)?

this

@KySoto no. That makes an assumption about the precision.

KySoto

3:22 PM

well in our system i do know the precision

this

@MathieuGuindon no, VBA's Date data type vs. C#'s Date vs. various SQL dialects' definition of DATE/DATETIME

Mathieu Guindon

oh, I totally meant T-SQL date/datetime

this

@KySoto not good enough in my book. It can change. That has happened before.

KySoto

how would it change?

Mathieu Guindon

@KySoto IME it does, but between as a keyword is ambiguous wrt whether it includes or excludes the supplied dates, which isn't ideal.

FreeMan

3:24 PM

@KySoto Y2K bug?

KySoto

also, how would you deal with i want stuff sometime after this date

this

@MathieuGuindon I know. In a stored procedure, you can control it via parameters, but when you're programming from the client's side in the other language and passing parameters to some ad hoc statements (whether it's made by EF, Dapper, by hand, whatever, how doesn't matter here), then it can get messy.

KySoto

actually, i think im losing track of which way you are saying it should be done.

this

the expression someColumn >= startDate AND someColumn < DATEADD(DAY, 1, CAST(endDate AS date)) is the way I'd do it and this is guaranteed to work regardless of the data types, and is still sargable.

KySoto

oh

thats pretty much what i do

Mathieu Guindon

3:27 PM

@this and it's unambiguous

this

Never saw a sargable DATEDIFF() predicate.

KySoto

though 99% of the date comparison stuff i work with ends up being on report filters

this

@MathieuGuindon Yep, there's that, too.

@KySoto which I bet are input by users.

KySoto

i dont remember the last time i needed to use dates to filter a query

yes, but they are forced to be dates by the controls

this

so they'll say thing like "I wanna a report from 4/10 to 4/14" and expect 5 days

not 4 days

KySoto

3:28 PM

they get 5 days

i have a function i built 3 years ago that i plug in the dates for reports

Duga

[rubberduck-vba/Rubberduck] daFreeMan created comment on issue #4913: False Positive 'Assignment is not used' inspection

> Just for funs, here's another example I just discovered in my code:

```VBA
If markerLocation > 0 Then
Dim workingName As String
workingName = Right$(componentName, Len(componentName) - markerLocation - 1)
Else
workingName = componentName
End If
markerLocation = InStr(1, workingName, ".")
```

The first assignment is the one that's highlighted.

KySoto

Public Function GetReportDateFilter(ByVal FName As String, ByVal argStart As Variant, ByVal argEnd As Variant) As String
    'Takes in the field name [FName] start date and end date, from that it builds a filter string to pass into
    'a report. if the start string is empty it assumes  that it is open ended in that direction. if the end is empty
    'it assumes it is open ended in that direction too. if they are both empty it assume you want all data
    Dim filter As String
    Dim FieldName As String

its designed to take control values

this

you're still doing >= and <=

that's wrong.

and will fail on SQL Server's datetime which has that weird precision of 1/3 milliseconds

that's why you don't want to do the second thing

KySoto

yes, but all of the date times are generated in access

this

because you have to assume that the database will never store the time at different precision and that it won't be altered in process of saving.

because it's a float value, it is subject to rounding, so if you do some math on the dates, you might end up iwth 23:59:59.333 which then gums up things.

BT,DT.

KySoto

3:33 PM

yeah but im pretty sure that access truncates that .333

this

Nope, not necessarily.

KySoto

i have literally never seen it happen in 3 years

this

I have.

KySoto

what is being used to generate the date time?

this

The point is that just because you haven't meant it won't happen - you're basically assuming that ALL of the systems used in the whole picture will all mesh together seamlessly and not collide in their different assumptions about a value.

Why does that matter? You don't want to care where/how. Just that the comparison is always reliable and accurate regardless of the input given.

Especially when you are dealing with more than one system (you're already dealing with at least two -- Access and SQL Server. Arguably, you could have 3rd which is VBA since VBA and Access has their own type systems)

Throw in a C# web application, and it becomes more fun.

KySoto

3:37 PM

so if i used dateadd to move it to the next day midnight, why would it matter if i did <=?

this

You don't.

KySoto

im having a hard enough time getting vb.net

you know what

im an idjit

i really need my migraine to clear up already

i dont want midnight the next day because then its coverage isnt accurate

hence < instead of <=

ugh

sorry i took so much time to realize stuff

this

and that works even if there's a record with 23:59:59.99999999999

which is why you don't want to deal with the precision; that means you have to ensure that all systems involved adhere to the same level of precision and that is hard enough to control.

Mathieu Guindon

hi @ChrisCunningham!

Duga

[rubberduck-vba/Rubberduck] MDoerner created pull request #4924: Remove dymanic calls from resolver to merge MDoerner/Rubberduck/MakeResolverNonDynamic into rubberduck-vba/Rubberduck/next

> This PR replaces the dynamic call in the binding stage of the references resolver.

This has two main advantages: it is easier to navigate the code when debugging (no need to find some code and run it through the debugger to follow the execution flow) and it is slightly faster.

Mathieu Guindon

3:51 PM

@Duga oooh nice!

M.Doerner

That PR was a lot less painful than I had envisioned initially.

Duga

[rubberduck-vba/Rubberduck] Vogel612 created comment on issue #4913: False Positive 'Assignment is not used' inspection

> What happens if the declaration is not inside the "Then-Block"?

this

@M.Doerner Nice! Was that all usages of dynamics in the parser subsystem or are there others beside those that you changed?

M.Doerner

4:10 PM

I think that was the only place we used dynamic.

The dynamic there really wasn't necessary and just a simplification of the code.

At the time it was written, there was no pattern matching.

Hm, I seem to have missed a few.

Duga

[rubberduck-vba/Rubberduck] MDoerner added label PR-Status: WIP to pull request #4924: Remove dymanic calls from resolver

[rubberduck-vba/Rubberduck] MDoerner created comment on pull request #4924: Remove dymanic calls from resolver

> I just did a search for uses of dynamic adn I seem to have missed a few. So, WIP for now.

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

[rubberduck-vba/Rubberduck] codecov[bot] created comment on pull request #4924: Remove dymanic calls from resolver

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

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

[rubberduck-vba/Rubberduck] build for commit 1c02d34e on unknown branch: 64.52% (target 0%)

Duga

4:39 PM

[rubberduck-vba/Rubberduck] retailcoder opened issue #4925: Resolving Late-Bound Member Calls (kinda)

> **Justification**
Right now when the resolver encounters a member it can't find a `Declaration` for, it logs a binding failure and attempts to resolve known identifiers in the rest of the expression. This makes late-bound member calls unresolvable:

Dim foo As Range
Set foo = ActiveWorkbook.Worksheets("Sheet1").Range(cellAddress)

Here `Range` is a late-bound member call made against `Object`, but assuming `cellAddress` is some local variable, that's all Rubberduck will be seeing

[rubberduck-vba/Rubberduck] retailcoder added label resolver to issue #4925: Resolving Late-Bound Member Calls (kinda)

[rubberduck-vba/Rubberduck] retailcoder added label enhancement to issue #4925: Resolving Late-Bound Member Calls (kinda)

[rubberduck-vba/Rubberduck] retailcoder added label discussion to issue #4925: Resolving Late-Bound Member Calls (kinda)

[rubberduck-vba/Rubberduck] daFreeMan created comment on issue #4913: False Positive 'Assignment is not used' inspection

> Interestingly, that resolves the issue for this new instance. However, the original code has the declaration outside the If...End If, so the issue as a whole still stands.

Duga

5:11 PM

[rubberduck-vba/Rubberduck] Vogel612 created comment on issue #4913: False Positive 'Assignment is not used' inspection

> Yes, but it could imply that it's a different underlying issue. I'm not sure whether this needs to be in a separate issue now... Let's leave it in here for now and see whether the same fix applies for both parts.

Vogel612

@Duga gigantic can of worms might be a bit of an understatement here

Mathieu Guindon

lol

FreeMan

~~3-pound coffee can~~ 55 gallon drum o'worms

this

delivered by barrels of monkeys.

FreeMan

but what an interesting and useful drum it would be!

this

5:18 PM

I think the old idea of providing additional resolver metadata floated here some times ago would e less of one.

M.Doerner

5:36 PM

It is such an annoyance that the collactions in the OMs are not stronlgy typed although the documentation states that the values are of a specific type.

Duga

5:50 PM

[rubberduck-vba/Rubberduck] daFreeMan created comment on issue #4318: False positive "Object variable {x} is assigned without the 'Set' keyword"

> This has, once again, reared its ugly head.

Version 2.4.1.4666
OS: Microsoft Windows NT 10.0.15063.0, x64
Host Product: Microsoft Office 2016 x64
Host Version: 16.0.4822.1000
Host Executable: EXCEL.EXE

```VBA
Dim theChart As ChartObject
Set theChart = report.ChartObjects.Add(20, 20, 736, 211)
Dim Ser As Series
With theChart
Set Ser = .Chart.SeriesCollection.NewSeries
With Ser
.Values = "='" & DataRange.Parent.Name & "'!" & DataRange.Rows(mtdRow).Address(e

Mathieu Guindon

@M.Doerner interesting.. Worksheets is documented as returning a Sheets collection. Yet ?TypeName(Worksheets("Sheet1")) prints Worksheet. The documentation is inaccurate/incomplete.

FreeMan

@this They're nice monkeys, though. With the drum, they also bring you a copy of a book

this

Public Sub x()
Dim o As Object
Set o = Sheet1
Debug.Print TypeName(o)
End Sub

Returns Worksheet

Transcript for