VBA Rubberducking

12:00 AM

RELOAD!

[Minesweeper] Games Played: 109, Bombs Used: 64, Moves Performed: 13249, New Users: 15

2 hours later…

2:23 AM

@this thank you for having continually hammered the idea of abstractions so much.

Understanding that helps me break each step down. Successively explaining it to myself as I reread the material.

12 hours later…

2:13 PM

[rubberduck-vba/Rubberduck] noah-severyn forked us into noah-severyn/Rubberduck

2:36 PM

@IvenBach iirc workbook-scoped names are in Workbook.Names, and sheet-scoped names in Worksheet.Names. Presumably using Range("name") with an out-of-scope sheet-scoped name would blow up the same?

I believe that's right.

@BigBen if the sheet isn't qualified and it works, and qualifying it doesn't, then I think the problem isn't the name scope ;-)

(implicit ActiveSheet member calls being evil as usual)

Range("WBScoped") works. So does Worksheets("Parent").Range("WBScoped").

Or Sheet1.Range("WBScoped").

I guess I'm missing the reason for the "shouldn't qualify the worksheet" claim.

I know it's not necessary, given the workbook is active.

But I don't see the downside of qualifying it anyway.

3:35 PM

@MathieuGuindon using Names does blow up. Range doesn’t. As long as it exists within the workbook it was fine.

_Global implicitly being called IIRC was the reason when it is unqualified.

weird, because I'd expect using an out-of-scope name in a worksheet formula would evaluate to a #NAME! error

IDK I can't think of a reason to not pull Name objects from the appropriate Names collection if I'm the one defining the names and writing the code.

But is there any reason to not use Range("NamedRange") then? That's what I'm missing.

Or Sheet1.Range("NamedRange") since I'm the one defining the name and I know the range's parent?

@BigBen it puts the name-retrieval onto the Excel calc engine; it's basically an Application.Evaluate call

But what downside is there to that?

I'd be curious to compare that to whatever Names("NamedRange").RefersToRange is doing under the hood.

I steer clear of using Names because a Name doesn't necessarily refer to a Range. May be personal preference at this point.

And not necessarily worth spinning the wheels.... Or maybe it is, Idk.

unnecessary indirection; if I want to pull a name, and pull it out of a Names collection, I'm calling a cat a cat. If Excel takes the parameter and needs to evaluate and infer that I'm looking for a Name, IDK feels too indirect to my taste

3:45 PM

But Range should be optimized to do just that... seeing as it's possible to do Range("WBScopedName").

and Sheet1.Range("SheetScopedName").

@BigBen that's a good point. I do use that kind of dynamic names with INDEX and MATCH

Eh at this point I may let sleeping dogs lie lol.

For some reason I typed sleeping cats first. Weird.

@BigBen I think what I dislike is that the API has too many ways to retrieve that reference. A good API should make it clear how to do things... this isn't it ;-)

I can wholeheartedly agree with that

I guess either is fine - arguably the Names collection would be useful when you need to work with the actual names, rather than the Range they refer to.

so a "best practice" would be to stick to Range to retrieve Range references, and use Names when you need the Name object itself, for anything other than the range it's referring to.

#consistency

3:54 PM

On a completely unrelated note... been using more Python, where chained methods are du jour. Which is great, but then I have to remind myself not to do that in VBA.

4 hours later…

8:19 PM

WP blog stats: 2020 is now 148 views short of beating 2019

(with more than a whole month left, so 2020 is going to end up just slightly over 2019 - previous years' stats were more like 100-250% growth, with previous year beaten around June or July)

2 hours later…

10:19 PM

@BigBen You have any suggestions for getting started with python?

this

@IvenBach If you insist, at least make sure you have a long enough handler made with quality & durable metal - they are very slippery and can constrict hard enough to render you limbless. A cattle prod may be prudent.

limbless? Shouldn't that be unconscious?

They squeeze until limbs disappear? #TIL

[rubberduck-vba/Rubberduck] majkaz opened issue #5635: False Positive "Parameter can be passed by value"

> **Rubberduck version information**
Version 2.5.1.5557
OS: Microsoft Windows NT 10.0.14393.0, x64
Host Product: Microsoft Outlook x86
Host Version: 16.0.4266.1001
Host Executable: OUTLOOK.EXE

**Description**
As part of my old code, currently in refactoring, a MailItem is passed ByRef to a function and the function manipulates the subject if a condition is met.
Inspection shows "Parameter can be passed by value".

**The complete function for review:**

```
Private Function Defered

[rubberduck-vba/Rubberduck] majkaz added label bug to issue #5635: False Positive "Parameter can be passed by value"

this

@IvenBach AIUI, they can basically kill it off that the doctors has no choice but to amputate it

hence, limbless. Whether you remain conscious is contingent on your and the python's part.

10:49 PM

Python-Survivor: I'm not ded.
Doctor: Yeah you ain't. But your arm sure is.

Feeds

[scrolling through a giant spreadsheet of transcribed data] 'Wow, a surprising number of users grew up at 420 69th St.' 'Yeah, must be a high-rise or something.'

Much wanting this day to be over...

11:15 PM

@IvenBach what do you want to use it for?

11:34 PM

[rubberduck-vba/Rubberduck] retailcoder created comment on issue #5635: False Positive "Parameter can be passed by value"

> Hi! Thanks for the feedback!

The *reference itself* is not being `Set`, so the result is legitimate, and the parameter can (arguably *should*) indeed be passed by value. `oMail.Subject = ...` is not altering the object reference `ByRef`/`ByVal` is referring to, only a property of that object; passing the reference `ByVal` would guarantee that the function cannot alter that reference as a side-effect.

Try adding `Set oMail = Nothing` just before `End Function`: with `ByRef` the caller pro

[rubberduck-vba/Rubberduck] retailcoder removed label bug from issue #5635: False Positive "Parameter can be passed by value"

[rubberduck-vba/Rubberduck] retailcoder added label support to issue #5635: False Positive "Parameter can be passed by value"

[rubberduck-vba/Rubberduck] retailcoder added label feature-inspections to issue #5635: False Positive "Parameter can be passed by value"

[rubberduck-vba/Rubberduck] retailcoder added label status-bydesign to issue #5635: False Positive "Parameter can be passed by value"

11:46 PM

Primarily to get exposure to another language and step outside my comfort zone.

Outside of Excel and my work on RD I don't know what's out there or how to get started...

Ah. I guess I'm doing it more for data analytics purposes.

There are a ton of Python resources tho.

almost too many I'd say