VBA Rubberducking

Duga

12:00 AM

RELOAD!

Quack Exchange

4:57 AM

0

Q: Error-Handling Class and Logging for VBA

I've been reusing an error class in Excel VBA projects for a few years now and I'd like to see if there are ways to improve it. Any suggestions for style, code, etc. are all welcome. The log file is usually written to a file server so multiple users can access it. I have changed the log path to ...

Simon Forsberg

8:53 AM

@Duga Are the ducks dead?

Vogel612

9:13 AM

no. just active in bursts

FreeMan

11:53 AM

@ThunderFrame I just saw the news. I'm sure you'll beat this into submission just as you have the VBA grammar! My prayers are with you and your family.

FreeMan

12:27 PM

@MathieuGuindon ThunderFrame probably won't be spending a lot of time stopping by here, maybe shoot him a quick email back letting him know we're all thinking of him. He may check email somewhat regularly.

Also, thanks for sharing that with us - this place does feel a bit like family after a while.

this

@FreeMan I second the sentiments.

Hosch250

1:23 PM

@SimonForsberg Unfortunately, one might be soon :(

FreeMan

this one looks interesting to the group here:

0

Q: How to design a Model in a Model-View-Presenter Pattern?

I want to store data in the following structure in a Model in order to use it in a MVP Pattern. I want to store a collection of collections of a custom class. The custom class has two properties. A string "name" and a Collection "members". This makes my property in my model a collection of a col...

excel vba excel-vba model mvp

@Comintern shouldn't that be updated to pour some syntactic sugar on me?

Mathieu Guindon

2:09 PM

@FreeMan yes indeed... but too broad for SO unfortunately

This is a very interesting topic that I'd easily be nerd-sniped into answering in-depth, however it's a bit too broad for the scope of Stack Overflow. I would recommend this article I wrote last year, and this follow-up; Model-View-Presenter is an excellent way to go about this, so go ahead and implement it - then when you have something that works, you can get extensive feedback & various enhancement ideas at Code Review. — Mathieu Guindon 3 mins ago

FreeMan

@MathieuGuindon is this one of the things that [programming.se] is for?

learns that the "[ <sitename> .se ]" shortcut doesn't work in chat

Mathieu Guindon

maybe. I never really got a grasp of the scope of that site..

All that filtering is going to be "fun" though. If you can model the data in XML format, you could leverage xpath queries to pull the nodes you need, and then "materialize" them into instances of some custom class. — Mathieu Guindon 14 secs ago

what they need, is LINQ. or an actual database.

KySoto

2:44 PM

mornin gents

Mathieu Guindon

hi!

KySoto

hows it goin?

@IvenBach enjoy the plethora of games i tossed your way?

Quack Exchange

3:07 PM

0

Q: Even with Arrays, UDF performance is slower than expected

Background I have the below UDF that opens a workbook (this workbook has extracted data from a queue within another app). I get the used range from this workbook and assign it to an array (aExtractedRange). One of the elements in this array has the workbook name that I need to open and get Applic...

array vba excel

Vogel612

3:19 PM

@FreeMan that site is called softwareeningeering now...

Software Engineering

^^ works just fine

FreeMan

@Vogel612 learns that the "[ <sitename> .se]" shortcut only works in chat when one specifies the proper <sitename>

IvenBach

3:52 PM

@KySoto I allowed myself a single splurge only getting 4 hours of sleep over the weekend. I’ve returned to responsibility and moderation now.

FreeMan

4:47 PM

interesting: I just did a Find all references. When the search results dialog came up, the top of the tab was aligned with the top of my monitor and the window header was off the top of the screen.

I hit Alt-Space-M then down arrowed until the window header was visible and I was able to move it with my mouse.

Might be worth an issue creation to ensure that windows do not appear higher than the top of the physical display space.

Simon Forsberg

@Hosch250 Wait, what?

this

2 days ago, by Mathieu Guindon

ThunderFrame gave us a whole lot of good time, tons of inspection ideas, ThunderCode™ proofing, the unforgettable spinning ducks, the face-of-the-project ducky, and so much more. Last night he emailed me to say he was up against an aggressive stage IV melanoma and heading to the hospital. Let our hearts be with him and his family.

FreeMan

Simon Forsberg

@this Oh holy...

FreeMan

@SimonForsberg yeah...

this

5:01 PM

@FreeMan fwiw, the red text was hard to read and should be cleaned up for the issue but yes I think that's an issue if you consistently open it with the header too high

FreeMan

@this well, yeah, it's ugly. What did you expect from a quickie screen shot? :)

this

don't put text on top of text?

FreeMan

I was using Greenshot. Haven't totally figured it out...

this

or just use boxes w/o text?

FreeMan

I can't say that it's "consistent", but it did happen...

this

5:04 PM

ah, normally I use arrows and put my text somewhere else

FreeMan

didn't give mahself enough space to apply common sense...

this

Maybe today you'll learn that yes, common sense can be expanded!

KySoto

@IvenBach lol, fantastic

FreeMan

6:16 PM

weird... the 2 current versions (prod & dev) of my main Excel workbook had odd corruptions in them causing my SQL queries to not work. I went to the most recent previous version & everything was working just fine.

I grabbed the last known working good version, re-did the changes from prod then dev & all is good.

May possible be related to Excel crashing on me several times today, too...

this code will make your head hurt:

0

Q: 35,000+ row loop

I'm a VBA novice but have had some help with some code I need from a friend. Essentially, there's 35,000+ rows of data. We are looking for cells that match a one of four different criteria in column H (from memory). If it meets one of these four criteria it slightly changes the data in the cell ...

vba excel-vba loops

Mathieu Guindon

@FreeMan NOT_SURE_WHAT_YOU_MEAN_THERE

FreeMan

GoTo Exactly

this

@MathieuGuindon SCREAMING_SNAKE_CASE_IS_TOTALLY_NATURAL

FreeMan

@MathieuGuindon you can gold-badge dupe hammer this one can't you?

Comintern

6:45 PM

@FreeMan Stop. Hammer-time. Done.

FreeMan

:)

KySoto

7:10 PM

@FreeMan TLDR, get a database

any time i see anything over 10,000 rows

i immediately jump to get a real database

and as not really that good as access is, it is more of a database than excel

Hosch250

If anyone tries to prevent me from getting a DB when I want one, they'll have to stop me at gun point.

Duga

8:01 PM

[rubberduck-vba/Rubberduck] BZngr opened issue #4448: Mock Parser - DeclarationFinder returns UDT variable references as Variant variable declarations

> Given a C# Unit Test scenario with the following VBA code:

```
Option Explicit

Private Type MyType
fld1 As String
fld2 As Long
End Type

Private mType As MyType

Public Sub Foo(arg1 As Long)
myType.fld2 = arg1
End Sub

Public Sub Goo(arg1 As String)
myType.fld1 = arg1
End Sub
```

And a call to `DeclarationFinder.Members` after parsing:
`var declarations = State.DeclarationFinder.Members(<moduleDeclaration>);`

In the `declarations` collection, `mType

this

@Duga uh, that was like hours ago?

Duga

[rubberduck-vba/Rubberduck] BZngr edited issue #4448: Mock Parser - DeclarationFinder returns UDT variable references as Variant variable declarations

Mathieu Guindon

8:25 PM

Interesting & unexpected case of implicit default member calls playing free games here

3

Q: using predefined names in VBA

The code is really simple and I'm just trying to copy values in from_range and paste them in to_range. But it's just not working out... Sub test14() Range("to_range") = Range("from_range") End Sub Before, After, not sure why everything in to_range is gone Desired, just want to overwr...

vba

Comintern

WTH? Is it getting confused because it thinks it's a Set coercion instead of a Let?

I think that's exactly what it is. The call to .Value needs to be on the RHS. It doesn't matter if it's implicit on the LHS or not.

Hosch250

8:46 PM

> Some consideration needs to be made of what we mean by "bonk". I'm going to say that it refers to the relatively sudden onset of severe fatigue, often accompanied by dizziness, "foggy brain", and/or muscle pains (not simple soreness). You're not just tired, you're really tired.

I guess I used to "bonk" once a week.

And I was kind of addicted to it.

On the other hand, I was raw muscle and bone, and I was never tired except on that ride...

I'll have to rag my mom about it a bit; she's really into healthy eating, but not athlete-level healthiness.

IvenBach

9:05 PM

Wish you the best with treatment Thunder.

We still need you here for your unit tests.

Duga

[rubberduck-vba/Rubberduck] MDoerner edited issue #4448: DeclarationFinder returns UDT variable references as Variant variable declarations

[rubberduck-vba/Rubberduck] MDoerner created comment on issue #4448: DeclarationFinder returns UDT variable references as Variant variable declarations

> This issue is also present with RD running in the VBE.

You can observe it by pasting the above code ant selecting `myType` in one of the two procedures. The status bar will show `Variant` as type.

Duga

10:02 PM

[rubberduck-vba/Rubberduck] lpnzgd opened issue #4449: Autocomplete not working

> For some reason, autocomplete is not working at all for me. I've checked the settings are all enabled (I think this is the default) and tried turning them off and back on, to no avail. Is there something I'm missing?

Version 2.2.0.3536
OS: Microsoft Windows NT 6.1.7601 Service Pack 1, x64
Host Product: Microsoft Office 2016 x64
Host Version: 16.0.8730.2175
Host Executable: EXCEL.EXE

[rubberduck-vba/Rubberduck] Vogel612 added label bug to issue #4449: Autocomplete not working

[rubberduck-vba/Rubberduck] Vogel612 added label feature-autocomplete to issue #4449: Autocomplete not working

[rubberduck-vba/Rubberduck] Vogel612 added label support to issue #4449: Autocomplete not working

[rubberduck-vba/Rubberduck] Vogel612 is playing around with a project card

[rubberduck-vba/Rubberduck] retailcoder created comment on issue #4449: Autocomplete not working

> Yeah, there's a bit of history here, the settings UI doesn't reflect the actual features - AC was temporarily disabled pending a rewrite, but the settings UI wasn't updated accordingly.

AC is being split into two distinct features: *self-closing pairs* (aka "SCP"), and *block completion*; there's an open PR for SCP, and it's coming along nicely but there's a bit of clean-up and refactoring that needs to happen before it's merged. Block completion will follow.
Ideally we'd ship v2.3.0 with

Duga

10:28 PM

[rubberduck-vba/Rubberduck] MDoerner created comment on issue #4448: DeclarationFinder returns UDT variable references as Variant variable declarations

> I think this is not an issue but a typo.

After looking at the code provided again, I had to realize that the variable `myType` in the procedures is an undeclared variable, and thus `Variant`. This code actually does not compile because of `Option Explicit`. (Note that the variable in the procedures is `myType`, not `mType`, which the VBE makes more explicit by pretty-printing it to `MyType`.)

After changing the variables in the procedures to `mType`, RD in the VBE correctly identifies t

Transcript for