VBA Rubberducking

Duga

12:01 AM

RELOAD!

[bruglesco/FleetCommand] 8 commits. 475 additions. 92 deletions.

[rubberduck-vba/Rubberduck] 6 commits. 1 opened issue. 6 issue comments. 192 additions. 114 deletions.

[Zomis/xploria-perms] 2 commits. 1564 additions. 482 deletions.

[Minesweeper] Games Played: 109, Bombs Used: 66, Moves Performed: 14506, New Users: 22

Mathieu Guindon

12:44 AM

52-48

Two Rubberduck contributors are now officially "racing" to complete two very complex but thoroughly awesome features. Which one will ship first?

Tweeted by rubberduckvba on April 12, 2019 at 7:44 PM

IvenBach

3:38 AM

@MathieuGuindon twitter.com/JonErlichman/status/1117610684211171328

You get to watch it yet?

Mathieu Guindon

Twice =)

IvenBach

3:57 AM

@Comintern how you been? Haven't seen you for a while.

Mathieu Guindon

@Comintern is here?

hm

Quack Exchange

5:12 AM

0

Q: Running an UPDATE SQL QUERY within VBA using DAO

I have a macro in Excel that updates a recordset using WHERE a primary key in MS Access matches a primary key in a UserForm label value. The macro is called from a Userform command button, this command button also writes about 10 different UserForm textbox values back to MS ACCESS and to a Sheet ...

FreeMan

10:59 AM

@IvenBach I dunno about "good", but it clicked better this time than in previous attempts. Of course, WFH today and left all my hand-written notes at the office...

@IvenBach the best response: twitter.com/Shnaps/status/1117616976388677632

I'm in that same boat with the exception of Snapchat - my daughter used that on her tours of Europe, it was the only way my wife & I could keep up with her

@MathieuGuindon says he was seen 1m ago, last talked 28 days ago.

FreeMan

11:15 AM

^well, OK, Mug doesn't say that, Chat says that...

Mug was just thinking that...

Weird... Working from home today. I'm on my personal desktop machine, connected to my work machine via VPN & RDC. The notification from my work machine to change my password (on Friday? yeah, right!) appeared in the notification tray for my home machine! I'm sure of that because of other notifications that were there that couldn't possibly have shown up on my work machine.

Duga

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

> When moving between codepanes during debug, I lose track of which one I am in because code explorer does not automatically highlight the active module (unlike the VBE). (Yes, the window titlebar updates, but sometimes I forget which project I am in... memory being what it is!)

Double clicking on a module is not a problem (though it would be nice to have it remember the last position a la #4700) since I just clicked on it. This is only really a problem for me during debugging.

I dont know

[rubberduck-vba/Rubberduck] Dob4115 added label enhancement to issue #4922: Code explorer does not automatically track "active" codepane

Quack Exchange

0

Q: VBA Outlook/Excel Object model

While coding, i've noticed a quite funny thing which, unfortunately, I wasn't able to google. That's why raising it here -- maybe you can help me. Below code, copied from MSDocs works fine. Sub MSCodeTest() Dim oApp As Object 'added by me Dim oNamespace As Namespace Dim oFolder As Folder Di...

vba excel outlook

Vogel612

@FreeMan you using windows default RDC?

because that might connect the event queue across the tunnel

Duga

11:44 AM

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

> Oh, not just for debugging... I want that *all the tiime*!

Thanks for taking the time to open an issue for this!

[rubberduck-vba/Rubberduck] retailcoder added label navigation to issue #4922: Code explorer does not automatically track "active" codepane

[rubberduck-vba/Rubberduck] retailcoder added label feature-code-explorer to issue #4922: Code explorer does not automatically track "active" codepane

FreeMan

@Vogel612 I am and apparently it does!

@QuackExchange @MathieuGuindon jinx!

Mathieu Guindon

@QuackExchange doesn't GetObject(, "Outlook.Application") look wrong? IIRC the 2nd parameter is for a version#, no?

M.Doerner

@Duga Figuring out which code pane is active at the moment is trivial. What is really hard it to observe at which point it changes. AFAIK, the API does not provide events for this.

FreeMan

OK, picking up on yesterday's git learning... I've created my repository on the network and populated it with my production code from my Excel project. I have created a clone from it on my local hard drive, as well. I'm thinking that my next logical step is to create a branch for the changes I'm currently working on in my development version of the workbook, export the changed code and commit it to that branch. That sound logical?

@M.Doerner Just run a polling loop. :D

M.Doerner

Not going to happen with COM interop. However, we might have a chance to intercept some window message.

Mathieu Guindon

11:57 AM

Yeah... nope - no polling =)

FreeMan

:D was there for a reason...

M.Doerner

However, associating windows with components is painful.

Fortunately, we have done that work already, right?

Mathieu Guindon

@M.Doerner "window activate" or something? IIRC we already have a method that turns a hwnd into a CodeModule

"ModuleFromHwnd" or similar

M.Doerner

I bet you simply get a focus message.

I know we should have it because we need it for SCP.

Mathieu Guindon

It does ring a bell =)

If all we get is a window-focus msg, then we need a component that tracks it and fires an event when it changes, ...but only if the window is a code pane or designer window

(we're probably getting the same focus message when a toolwindow gets focus)

Duga

12:09 PM

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

> Although finding the currently active code pane is really simple using the VBE API, tracking changes is not. The API simply does not expose events for the change.

However, we might be able do track the active code pane by intercepting window messages, propably the focus change message for the code pane. But that needs some investigation.

We do something similar already to enable the auto completion feature. In that case, we intercept the keystrokes and have to figure out which code pane

M.Doerner

That is also my idea. We would add a pane focus changed service other components can subscribe to.

FreeMan

and the wind whispers, "Avalon"...

M.Doerner

I think we are already rather close to be able to implement this; we just need to do a bit of research.

FreeMan

:( you're spoiling the joke...

Mathieu Guindon

@Duga hmm, IIRC we subclass the code pane and pick up keystrokes and fire an event - the AC handler simply works off the active codepane upon handling that event

M.Doerner

12:14 PM

Having our own code panes would make things easier, but we still have to support the code panes.

In break mode, we will probably have little chance to work with our own code panes.

Mathieu Guindon

^

M.Doerner

How do we subclass the code pane in the first place?

Mathieu Guindon

Hey wait a minute... is this not what we're looking for? github.com/rubberduck-vba/Rubberduck/tree/next/…

M.Doerner

Anyway, if we have them all sublassed, we just have to react to the appropriate message and get the active one.

Hm, we might just use the SelectionChangedService.

Mathieu Guindon

@M.Doerner github.com/rubberduck-vba/Rubberduck/blob/next/…

@M.Doerner that would probably work

Duga

12:24 PM

[rubberduck-vba/Rubberduck] salifm starred us

2

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

> Thinking a bit more about this, we already track the selection for the Rubberduck command bar. So, we should be able to use the SelectionChangedService to provide the information needed.

Quack Exchange

1:44 PM

0

Q: Improving speed of data clean process in vba

My does exactly what I want it to. However, being relatively new to VBA I feel it could be a lot more efficient - namely I think I have overused loops and worksheet functions which are slowing it down. At the moment it takes around 3 minutes for ~15k rows of data. At the moment it's more of a co...

performance vba excel

this

2:12 PM

emerges from the chamber Internet!!!

IvenBach

@FreeMan remember to make a new branch and check it out before doing any work. Lest you polute/cross the streams like I often do.

Vogel612

welcome back :)

IvenBach

@this echo?

this

That was fun - coming back home only to find that the internet was down sometime over the weekend and wait on the provider to send someone to get it fixed.

FreeMan

@IvenBach thx

IvenBach

2:13 PM

Irony is a cruel mistress. Good thing you were already without it.

this

-_-

KySoto

2:33 PM

for enums is it and or or

like vbOKOnly or vbCritical

this

or

KySoto

thanks

im trying to break the bad + habit

Hosch250

@IvenBach Just as long as it's not octirony.

(Yep, started The Color of Magic last night.)

M.Doerner

3:12 PM

@KySoto Always remember, you do bitwise operations with enums.

this

hmm should we be trying to compile the project on the loadup of RD?

the thing is that it doesn't actually parse the project even if it does compile the project.

but if the project isn't able to compile, they get that dialog asking whether to parse without compiling during the startup. Kind of annoying.

KySoto

3:42 PM

i just had to step into some nightmare land code

1700 lines of code

for one sub

its a save function that... does a bunch of crap beforehand

well... and while

Hosch250

@KySoto My life in JS at the moment, LOL.

KySoto

a bunch of with currentdb.openrecordset("table name here")

and a crapload of dlookups all over the place looking for data that could have been fetched in one transaction

one day i will break his dlookup habit

Hosch250

Break it with an iron rod :D

KySoto

hes technically higher up in the corporate food chain

not really, but kinda sorta is

im sorta the lead access programmer without the title

i just kinda do it

im the guy trying to make the standard all the rest of them work by, while keeping it to a level they can work with

baby steps

though i usually end up just making a bunch of library code that everyone else reuses

this

at least they're using it as opposed to re-inventing their string functions ten times over.

KySoto

3:48 PM

lol

yeah true

my programmer co-worker is getting to the point where he has started contributing to the library

IvenBach

@KySoto You now understand the "Code as if an axe murderer..." saying. Got your axe and ready to yell out "HERE's JOHNNY!"?

KySoto

xD

Generic and reusable

Hosch250

@IvenBach Wouldn't that be "WHERE'S JOHNNY?!" ?

IvenBach

@KySoto That's all they can do after you break their knee caps.

Hosch250

@KySoto If you make it too generic and reusable, it does so little useful that nobody uses it anyway.

KySoto

3:51 PM

lol

IvenBach

Did you ever see the Shining Hosch?

KySoto

yeah its not THAT generic

what are the module limits for code before it starts choking?

isnt it 64kb of compiled codE?

M.Doerner

Something like that number.

I guess it is exactly the maximum of an unsigned 16bit integer.

Just read again. No, the limitation is on the number of lines.

KySoto

oh ok

M.Doerner

The limit is about 64k lines with at most 1024 characters each.

KySoto

3:59 PM

i believe my largest code module comes up as 1600 ish lines

M.Doerner

I have larger ones.

KySoto

i started off trying to split jobs for the modules

M.Doerner

As long as each procedure or function is small, it is not that bad.

KySoto

yeah, most of them are 10-60 lines each

i dont know for sure

M.Doerner

Hm, doing the math, I think you can write a 32MB module. However, you really have to make an effort to do so.

KySoto

4:23 PM

lol

my biggest one is like 64kb

Mathieu Guindon

huh, I'm running a slightly outdated build (2.4.1.4647), and if I reference the Outlook type library, Application starts resolving to Outlook.Application...do you guys repro that with the latest build?

this

@MathieuGuindon I am sure that was long ago reported by ThunderFrame

Mathieu Guindon

really?

this

github.com/rubberduck-vba/Rubberduck/issues/3302

Duga

[rubberduck-vba/Rubberduck] retailcoder added label resolver to issue #3302: Outlook.Application resolves higher than host's Excel.Application

Mathieu Guindon

4:28 PM

of course he did

this

hmm. a bit unclear how that happens, though.

Both Access and Outlook does not have Global appobject like Excel and Word has

Instead, their Application are the appobjects

Mathieu Guindon

makes me wonder at what point library/reference priority is taken into account

this

hmm, not reproducing

I made an excel project, referenced Access and outlook both

Public Sub x()
    Dim a As Object

    Set a = Application
    Debug.Print TypeOf a Is Excel.Application
    Debug.Print TypeOf a Is Access.Application
    Debug.Print TypeOf a Is Outlook.Application

End Sub

True
False
False

duh, it's not the VBA itself - it's the resolver that's wrong

Mathieu Guindon

^

this

Read the description, ~~Luke~~ this

BigBen

4:36 PM

best practice question for the pond. should variables be Dimmed inside or outside a loop, or does it matter?

this

you cannot dim it after the first use. For iterators, it'll have to be before by necessity. For variables used inside the body of the loop only, I do it inside.

Mathieu Guindon

@BigBen inside - makes it easier to then extract that block into its own procedure :)

BigBen

@MathieuGuindon that's what I thought but I only really asked myself the question today, thanks.

this

RE: resolving -- I have a feeling it's alphabetical

Mathieu Guindon

@this oh, no

BigBen

4:39 PM

@MathieuGuindon yes, thanks

Mathieu Guindon

Dim i As Long
For i = x To y
    DoSomething i
Next

^ the ideal loop

BigBen

yep.

Mathieu Guindon

(assuming DoSomething cares about i)

this

@MathieuGuindon No, I just invalidated that theory

Mathieu Guindon

phew

this

4:41 PM

basically it's respecting the priority -- just not the fact that Excel library has the #1 priority

Mathieu Guindon

lol

Tweeting Duck

4:51 PM

https://youtu.be/EQc3_2Kau6I

Tweeted by rubberduckvba on April 16, 2019 at 4:06 PM

Mathieu Guindon

@TweetingDuck #disruptor, I guess

mansellan

5:10 PM

@this Oh cool, you didn't get eaten by a Grue :-)

this

not yet

Hosch250

@mansellan Just by a tick.

Chrismas007

Anyone alive in here still?

Hosch250

@Chrismas007 Seems pretty active to me.

Chrismas007

Been a long time since I checked in :-D

Haven't been very active in VBA projects for a year or two

Hosch250

5:19 PM

Yep. How are you doing?

Chrismas007

:(

Hosch250

@Chrismas007 Heh, depending on what you are doing instead, that might be a good thing.

Chrismas007

Doing well. I've been gone so long I forgot how to put urls in comments

Hosch250

[text](url)

Chrismas007

@Hosch250 RIP me Thanks

Hosch250

5:27 PM

I was wondering with that first one, LOL.

Chrismas007

@Hosch250 haha gotta get that edit in

IvenBach

5:41 PM

@MathieuGuindon #IndustryDisruptor indeed.

@Chrismas007 Welcome to the pond.

Mathieu Guindon

lol xmas007 predates you by a long while haha

@Chrismas007 welcome back anyway :)

@IvenBach I love that you say this to every one, every time - don't stop!

IvenBach

It's my small contribution.

Mathieu Guindon

-3

A: Shell command is not executed

Stupid code! Please rewrite. You're a programmer? right?

Please nuke from orbit

Chrismas007

5:59 PM

lol thanks @MathieuGuindon and @IvenBach

IvenBach

If you have any questions about Rubberduck ask away.

Mathieu Guindon

Account suspended, wow

Hosch250

Not surprising.

IvenBach

SMH. That right there is one reason why SO should be mean/cruel.

Hosch250

SO should be mean/cruel to that. They should be matter-of-fact, but firm, about normal off-topic and dupes.

Mathieu Guindon

6:19 PM

and with that I'm 6 flags away from [badge:marshal] on SO

3

IvenBach

6:32 PM

Can't figure out how to get merged resource dictionaries to work.

Mathieu Guindon

they're picky

whatchagot?

IvenBach

I have two XAML files which each declare...

<viewModel:StopwatchViewModel x:Key="viewModel" /> in their resources.

I am following instructions for the last part of the book to combine the analog and digital versions of a stopwatch together.

The visual works but since they each have their own static resource they aren't in sync.

:derp: The book calls that fact out that they have their own view model.

My attempt to combine them still is valid.

Reading the manual about Mergede Resource Dictionaries has me to understand that creating one requires a XAML file with ResourceDictionary as its root element.

WTF... I swear that Resource Dictionary (WPF) wasn't an option when I tried 5 mins ago....

Mathieu Guindon

    <UserControl.Resources>
        <ResourceDictionary>
            <all your other local resources />
            <ResourceDictionary.MergedDictionaries>
                <ResourceDictionary Source="..\..\Views\Common.xaml" />
            </ResourceDictionary.MergedDictionaries>
        </ResourceDictionary>
    </UserControl.Resources>

IvenBach

Maybe give me a few mins to derp through this and maybe figure it out.

:gasp: did the derp get lucky?

@MathieuGuindon Why does it have that format with the nesting?

When I F12 on UserControl.Resources the Resources property is of type ResourceDictionary already.

Why does it need to be nested within another?

Mathieu Guindon

6:56 PM

so that ResourceDictionary.MergedDictionaries can be accessed

tbh it's not exactly obvious. good question.

IvenBach

Without the nesting you're right. There is no way to access ResourceDictionary.MergedDictionaries but something about it that way doesn't make sense to me.

<UserControl.Resources>
    <ResourceDictionary>
        <viewModel:TimeNumberFormatConverter x:Key="timeNumberFormatConverter" />
        <converters:BooleanVisibilityConverter x:Key="visibilityConverter" />
        <ResourceDictionary.MergedDictionaries>
            <ResourceDictionary Source="Common.xaml" />
        </ResourceDictionary.MergedDictionaries>
    </ResourceDictionary>
</UserControl.Resources>

Is the nesting of XAML elements akin to using Add() in code?

I think that's part of my disconnect is still not fully understanding how XAML and code work an a 1:1 side by side use.

Mathieu Guindon

@IvenBach yes

well, for ICollection types anyway

fwiw BooleanVisibilityConverter probably belongs in Common.xaml ;-)

IvenBach

I've already finished this example and am pushing past its limits.

Thanks again Mug. MergedDictionaries are something RD can use especially with the image sizing. Those are scattered in a few places.

Back to reading.

IvenBach

8:05 PM

Duck check: Is it normal to have xaml not compile only because a x:Name="foo" property is set?

Mathieu Guindon

depends on what kind of tag

IvenBach

I think I may be closing in on the problem with me RD not building at work.

Mathieu Guindon

cool!

IvenBach

<UserControl x:Class="AnimatedBee.View.AnimatedImage"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
             xmlns:local="clr-namespace:AnimatedBee.View"
             mc:Ignorable="d"
             d:DesignHeight="450" d:DesignWidth="800">

Hosch250

@IvenBach Yes, I have that regularly.

IvenBach

8:08 PM

How do you resolve it? Unload/Reload?

I've tried that without luck.

Hosch250

It happens whenever I define a usercontrol and set the name on it for the control purposes, then try to give it a different name in XAML where I actually use that control.

No, I have to remove the name.

IvenBach

Do you need to do any build before adding the name back in?

Hosch250

Sometimes, if you use name, you can change it to x:name and get it to work, but not always.

Mathieu Guindon

you really don't want an x:Name unless you really need one

IvenBach

@MathieuGuindon For my education what's the difference?

Hosch250

8:10 PM

Nope, I've always just had to permanently remove it from one or the other.

Mathieu Guindon

525

Q: In WPF, what are the differences between the x:Name and Name attributes?

The title says it all. Sometimes it seems that the Name and x:Name attributes are interchangeable. So, what are the definitive differences between them, and when is it preferable to use one over the other? Are there any performance or memory implications to using them the wrong way?

.net wpf xaml name-attribute

Hosch250

Sometimes, I can get around it with a level of redirection, like adding a control around the usercontrol and putting the name on that; works if I just have to hide/show it.

Vogel612

@IvenBach the x: is an xml-namespace prefix

IvenBach

@Hosch250 When you do get it working and then Clean the solution does it ever go back to not compiling?

Hosch250

Not unless I put the name back in.

Vogel612

8:34 PM

So I'm completely changing the Settings registration now...

wish me luck

Mathieu Guindon

@Vogel612 good luck!

Hosch250

@Vogel612 See you when you crawl out of the rabbit hole.

Vogel612

I hope to finish before easter, actually.

because I won't be able to work on RD for three weeks after April 27th

Hosch250

Oh, it shouldn't be that bad, should it?

Vogel612

and probably not for another week after..

@Hosch250 assuming I find the time to work on it properly.

It's 11PM now, and I basically just ate dinner and want to be in bed soon-ish ...

so for now I hope I get the registration into a state where I can start the VBE without it blowing up and then head to bed

but I'm pretty sure I broke quite a few things

Hosch250

8:52 PM

Good luck.

Vogel612

and generic registrations seem to be a bit of a pain

Hosch250

Ha.

Vogel612

> Castle.MicroKernel.Handlers.GenericHandlerTypeMismatchException: Types Rubberduck.Settings.Configuration don't satisfy generic constraints of implementation type Rubberduck.SettingsProvider.XmlPersistenceService1 of component 'Rubberduck.SettingsProvider.XmlPersistenceService1'. This is most likely a bug in your code.

this

I had to do something similar-ish for refactoring stuff.

using .SelectService or something

Mathieu Guindon

> This is most likely a bug in your code.

Vogel612

8:59 PM

yea, I think I need to explicitly specify the interface I'm expecting there

Mathieu Guindon

"gee thanks"

Vogel612

Fixed that one, now to reinstate all the other dependencies I broke

hmm ... weird..

it's missing a dependency that should be registered just fine ...

Okay, can y'all help me spot the error?

container.Register(Classes.FromAssembly(assembly)
    .BasedOn(typeof(ConfigurationServiceBase<>))
    .WithServiceFromInterface(typeof(IConfigurationService<>))
    .LifestyleSingleton());

because for some reason, it seems none of the services are correctly registered with their generic type

this

9:24 PM

I think you must close the interface's type

that's what I had to do w/ refactoring

Vogel612

do elaborate?

Mathieu Guindon

the generic types must be constructed?

i.e. ConfigurationServiceBase<Thing> rather than ConfigurationServiceBase<>

this

lemme see if i can find that section...

mat has the right idea

Vogel612

but the generic type is different for each implementation

this

github.com/rubberduck-vba/Rubberduck/blob/next/Rubberduck.Main/…

Mathieu Guindon

9:27 PM

hmm, so, either use reflection to construct the generic types (pain), or extract a non-generic interface out of the generic one if possible, make the generic interface extend the new non-generic one, and factor out generics from the registration?

this

No, that's what you use WithServiceSelect for

Mathieu Guindon

cool

this

to provide a closed generic type that can service this particular implementation.

Mathieu Guindon

much more sane than constructing generic types with reflection

this

the docs implies that it can handle generics out of the box but in case of a generic interface, it doesn't seem to work, so I had to do this manually with the WithSelectService

Mathieu Guindon

9:35 PM

ttqw

Vogel612

9:49 PM

hrngh... it still doesn't work..

this

what does it say? same error or?

Vogel612

nah, i'm getting different ones over and over now

hang on ...

it... seems to have worked

well ... that was easier than expected:

return type.GetInterfaces().Where(iface => iface.IsGenericType && iface.GetGenericTypeDefinition() == typeof(IConfigurationService<>));

this

10:09 PM

:+1:

that looks very similar to the refactoring dialogs' - probably could be refactored into a procedure....

Vogel612

yea, I have the advantage of not also wanting to do a WithServiceSelf()

though it should be possible to chain these...

this

makes me wonder why that wasn't done out of the box.

if my memory serves, I did google a bit but got suggested by some SO thread to do what you just did.

Vogel612

  //var face = type.GetInterfaces().FirstOrDefault(i =>
  //    i.IsGenericType && i.GetGenericTypeDefinition() == typeof(ISettingsViewModel<>));

  //return face == null ? new[] { type } : new[] { type, face };
.WithServiceSelf()
.WithServiceSelect((type, hierarchy) =>
{
    return type.GetInterfaces().Where(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(ISettingsViewModel<>));
})

Vogel612

10:36 PM

soo ... umm ... was that intended?