VBA Rubberducking

5:11 PM

Hmm. Given this:

    [StructLayout(LayoutKind.Explicit)]
    private struct LParam
    {
        [FieldOffset(0)]
        public uint Value;
        [FieldOffset(0)]
        public readonly ushort LowWord;
        [FieldOffset(2)]
        public readonly ushort HighWord;
    }

does that imply it's forever 32-bit? Technically LParam is an IntPtr and therefore not a fixed size.

I'm failing to find a definitive answer to whether the high order / low order word changes between 32/64 bit

Mathieu Guindon

looks like it

could they make it 64-bit by adding a [FieldOffset(3)] public uint HigherWord?

presumably back when everything was 16-bit there was only a "lowWord" unsigned short in there

(I've no idea)

this

5:27 PM

yeah - and the fact they use word is confusing because historically it was variable sized but in actuality it hasn't been the case

e.g. DWORD is still 32-bit on a 64-bit OS

I only need to know if the high order word would be on a different position on 64 bit OS vs. on 32 bit OS or if it'll still fit a 32-bit unit anyway

Mathieu Guindon

wouldn't that break literally everything?

like, suddenly the LowWord contains both low & hi values

this

hm. you'd be right.

Mathieu Guindon

@this question: what's the current showstopper for the mocking PR?

IIRC user code can't be mocked yet?

(or not consistently)

this

given this code:

Public Sub Foo(Bar As Excel.Range)
End Sub

the TypeLib API correctly generates a TypeInfo describing this member with appropriate reference.

marshal it into .NET PIA, it goes "sorry, can't convert it"

as a consequence, the representing .NET type comes out missing this member.

Mathieu Guindon

and that's where PIA really got its name from

this

5:35 PM

Ideally, all I need to do is just fix this manual import but....

Hosch250

I know y'all aren't web guys, but do you know of any public chat roughly like this where I could talk to active web-devs?

this

1

Q: Get TypeBuilder from defined type in an AssemblyBuilder

Based on the other question, a Type cannot be converted into a TypeBuilder. However, we have a method that returns a AssemblyBuilder. It already has types built and defined. We can know that some types might not be correctly converted, however, there is no direct method to get to the TypeBuilder...

Hosch250

OTOH, I suppose I could wait a month until my new job. They've got some great web guys there.

this

they basically give me a builder that won't let me modify or even replace the type.

the only chat I've been in that resembles web would be the Nancy slack chat

Mathieu Guindon

this

5:37 PM

Not very webmaster-y if one of 3 nodes is red....

Hosch250

That's webmasters, not web devs.

Mathieu Guindon

not the same?

Hosch250

That's more like a combo between dev-ops and sysadmins looking at their homepage.

Mathieu Guindon

this

hmm, TBH, I think the overlap is huge

I don't actually know of webmasters who are exclusively sysops

Hosch250

5:39 PM

    Using Gmail, Facebook, Twitter, Google, or any other website which behaves like an application
    Features of browsers which are directly related to the use of a web application (Greasemonkey scripts for a web application, etc.)

Mathieu Guindon

then again there's probably a dozen chats on SO where a bunch of web devs hang out

Hosch250

Yeah, just thought I'd toss it out before I went fishing.

I want something where it's casual and people can talk about personal projects and languages and features and stuff in general.

this

Anyway, if anyone has an idea that might be less work than actually writing my own darned PIA converter from scratch, I'd be all eyes.

Given that it's holding up the core 3 transition (for different reasons, mind you), it might be a necessity anyway. Haven't made my mind up yet.

Hosch250

That question almost reads like an X-Y problem, but I don't know enough to know of what :(

this

I'm mainly frustrated that they'd hand out a AssemblyBuilder with all the types built already but not give me a way to get to the TypeBuilders.

I'm also annoyed that they provide event notifications for the conversion failure but no mechanism to respond to it, either.

Here: docs.microsoft.com/en-us/dotnet/api/…

all it does is "hey, look, ma, I peed my pants!"

and it doesn't even try to call the ResolveRef either....

Hosch250

5:46 PM

On another note, I was getting a bit worried yesterday. I just put 2/3 of my money in a 1yr CD, right? My family was talking about moving to SD yesterday, and I just got a new job that I can't work remote at, and I don't have any money outside of my emergency fund left (which is quite large--enough to live on for a year, but not enough to buy a place I could keep my dog at).

Apparently they aren't going to until my brothers are out of college, but that was going to screw me hard. Either I'd have to go with (fine by me--I want to move out there anyway, and it'll be cheaper if they do the heavy lifting with my stuff), or I'd have to buy a place with a crazy high interest rates and monthly payment.

But if I went with, I'd have to give up my job :(

Mathieu Guindon

@this can't help thinking this post needs some illustratory code

this

if there was some code to illustrate, I'd use it.

I suppose I can include the detail about the ITypeLibExporterNotifySink and how I get to know that there's an error but seem to be unable to do anything about it.

Mathieu Guindon

^ that would help I think

(plus an edit would bump the question back onto the home page)

this

Alright. When I have time, I'll try and update the post.

I probably should add COM tag. The question itself technically doesn't involve COM (it's purely .NET reflection, AFAIK) but who knows. Surely someone has had that problem before.

Mathieu Guindon

also if you tag with com, you'll get Hans Passant to comment something along the lines of "why are you doing this? you shouldn't be doing this!"

lol

/ com-interop

this

5:53 PM

Well, if he does, I'll just ask him "what about giving me an answer?"

it's a question-and-answer site, right? RIGHT?

Hosch250

No, it's a knowledge database.

Mathieu Guindon

but is it CC-BY-SA 3.0 or 4.0 now?

this

... in a question-and-answer format.

@MathieuGuindon no, it's DK_DC 1.0 now

Mathieu Guindon

@Hosch250 tbh SO feels more like a database of homework assignments these days

Hosch250

@MathieuGuindon I agree.

this

5:57 PM

well, it's bound to happen after you have a # of good questions answered, no?

The only way to get new good questions is to get new stuff or changes

Hosch250

Yep.

Mathieu Guindon

~~quick! let's make a new javascript framework!~~

Hosch250

@IvenBach I just read this and thought of you:

thecodelesscode.com/case/232

Hmm, I thought those oneboxed for some reason. Must just be the feed.

puzzlepiece87

@this I agree. Eventually everything gets documented.

IvenBach

@Hosch250 Huh? What have I taught you?

Hosch250

6:06 PM

That's not the point.

this

@puzzlepiece87 except for those obscure questions, at least.

Hosch250

The point is, don't become a mockingbird.

Make sure you become a monk :)

this

All long help threads should have a sticky globally-editable post at the top saying 'DEAR PEOPLE FROM THE FUTURE: Here's what we've figured out so far ...'

puzzlepiece87

@IvenBach Mind if I ask you a quick question while you're here?

IvenBach

@puzzlepiece87 Yeah. I'm just lurking in chat right now.

I don't want to become a monk. I'd rather be a coder that can get things done in a timely manner.

puzzlepiece87

6:12 PM

@IvenBach I have 40 classes, all of which use some subset of 5 methods. If you wanted to do some setup procedure on all of them, which of these three would you do:

1. Reflection to see which subset of methods they have
2. Make them all implement an interface with the 5 methods, but make the methods empty when not needed
3. Neither

I'm not far enough along in C# to know best practices there. I was just learning reflection, for example.

Mathieu Guindon

@IvenBach you know that "code review" comic with the 2 doors and multiple expletives coming out of one? what the comic doesn't show, is the vestible with signs "<= Cowboy Hats" on one side, and "Monk Robes =>` on the other ;-)

@puzzlepiece87 reflection is pretty much a last-resort thing

this

having hard time envisioning a buddhist monk waving his cowboy and yelling "yee-haw!"

IvenBach

@puzzlepiece87 ^ I feel like that's more of a Big duck question.

puzzlepiece87

@MathieuGuindon Good to know! Would you pick 2 or 3?

Mathieu Guindon

#ItDepends

what are the methods?

puzzlepiece87

6:16 PM

@IvenBach I both respect your answer and tease you for feeling that way about every question xD

IvenBach

@this Pretty easy for me if you name him Rex Chessington.

Mathieu Guindon

if each method can be extracted into its own interface, that's what I'd do

interface implementations throwing e.g. NotImplementedException are a code smell

this

@IvenBach haven't seen him in a buddhist monk robe, however.

IvenBach

@puzzlepiece87 I'll keep feeling that way since my primary development isn't C#.

Although thinking about it I don't think I'll ever feel like I'm a legitimate coder.

puzzlepiece87

@MathieuGuindon I have an ActiveFixes class with a property for each fix class, and each fix class can possibly have (I was simplifying a bit) a Setup method, a Settings worksheet property, and some others I can't remember because I'm not at my workstation since I'm on vacation >.<

IvenBach

6:20 PM

Mug. How'd you get over your own imposter syndrome? I need some tips for my own.

Mathieu Guindon

@IvenBach lol... you don't [get over it].

2

puzzlepiece87

And instead of having 40 if (ActiveFixes.Fix1 != null) {} groups, I was trying to figure out how to have one loop that doesn't call any methods/properties that don't exist.

Mathieu Guindon

> with a property for each fix class

that's a problem

c.f. Open/Closed Principle; what if there's a new fix class, you need to add a new property?

puzzlepiece87

That's what I've been doing xD

Since I had about 10 of them lol

Mathieu Guindon

you want to quit doing that and put them into a data structure instead

some List or array, Hashset, whatever

IvenBach

6:23 PM

@MathieuGuindon :+1: Spoken like a true #IndustryDisruptor.

puzzlepiece87

@MathieuGuindon Sounds good, looking up C# data structures now. Thanks for winding me up and pointing me in the right direction.

Just needed a little hint to know where to go next.

Mathieu Guindon

imagine if the IInspector interface would need to be modified every time we implemented a new IInspection!

this

What? We don't do that?!?

Mathieu Guindon

lol

this

But how will we know what inspections there are?!?

Mathieu Guindon

6:24 PM

~~#magic~~ constructor injection ............... and yes, reflection

IvenBach

> Many client-specific interfaces are better than one general-purpose interface.

From the I in SOLID.

Mathieu Guindon

yep

puzzlepiece87

@MathieuGuindon (joking) Ah I see, reflection for thee but not for me :P

this

Coming from VBA, there's a heavy bias toward making one module do everything so it's self-contained.

You want to overcome that and accept the zen of 1000 files

I exaggerate but the idea is right - you want each "module" to do only one thing and do it well.

Mathieu Guindon

@puzzlepiece87 ah, but there's a difference between using reflection to determine whether an object contains a given method (i.e. querying its interface), and using reflectino to get an enumerable of all types in a given namespace that implement a particular interface :)

@IvenBach that's the principle by which an ideal interface has one single member

e.g. IComparable -> CompareTo

IvenBach

6:27 PM

Strive for the ideal while living in the messy real world.

Mathieu Guindon

bingo

this

But let's not commit the opposite error of creating an interface for every single method.

The keyword here is "responsibility"

or "scope" if you prefer.

Mathieu Guindon

yeah. once again, abstraction should be the key

IvenBach

#InterfaceAllTheMethods!

puzzlepiece87

@MathieuGuindon Oh yes, I was experimenting with the former. Well, both.

this

6:29 PM

I think a good telltale sign is that if you're having a problem with using interface X because it needs to be in state Y before you can use it, then the abstraction is likely broken.

puzzlepiece87

I was doing the bad one to loop through all the ActiveFixes properties to test if each one was set to an object, then the good one to see if the object had methods.

this

(not sure if that helps)

Hosch250

Jun 26 '16 at 22:06, by Hosch250

RegexAssistant (the project) targets .NET framework 4.5.2. You need to target 4.5 to be compatible with the rest of the project.

I'm getting all nostalgic today.

Mathieu Guindon

huh

lol

Hosch250

Reading the timeline, and that's a bug that would still give me trouble today.

Proud that I caught it :)

puzzlepiece87

6:31 PM

By the way, did I understand your all's chatter correctly? Is Rubberduck switching from something to .Net Core 3?

Mathieu Guindon

2019-10-26, Rubberduck turns 5. How shall we celebrate that?

@puzzlepiece87 no, just the meta-stuff that builds Rubberduck

Hosch250

@puzzlepiece87 Not yet, but as soon as we can figure the path forward out.

@MathieuGuindon Get it to Core so I can work on it again?

:P

puzzlepiece87

@Hosch250 The benefit of this switch is that RD will be more crossplatform?

Mathieu Guindon

@Hosch250 cool. So first you make Office run on .NET Core?

Hosch250

@MathieuGuindon Ummm, what's the pay?

Mathieu Guindon

6:32 PM

@puzzlepiece87 RD dev, yes

@Hosch250 probably pretty good, if you can relocate to Seattle WA

(probably pretty good if you can't either)

Hosch250

I bet I make more here after considering cost of living.

Actually, though, I think at least part of the Office team is in Fargo.

puzzlepiece87

@MathieuGuindon Ah okay, cool. So you all can do everything you want in .Net Core 3? My impression was that it was kind of still catching up to .Net Framework in capabilities.

@MathieuGuindon By bringing back the giant rubber duck harbor tours lol

Mathieu Guindon

oh, crap, lol

@puzzlepiece87 in theory

@Hosch250 Excel product team is in Building 33 AFAIK :)

Hosch250

TBH, I like Word and Outlook and PP more than Excel.

Mathieu Guindon

now that's a low blow. PP?!!

Hosch250

6:37 PM

Yeah, it's pretty cool.

I never use Excel.

I used Word extensively in college, and OneNote.

I use PP for presentations.

But never Excel.

Mathieu Guindon

makes a grid in PowerPoint with textboxes

Hosch250

Outlook is awesome. I love the custom filters.

this

@MathieuGuindon how would one use it?

Mathieu Guindon

@this just like Excel

!

cries

this

but but I'm in the audience... I don't have the keyboard

Heck, if I'm a presenter, I'm probably holding a clicker, not a keyboard

Mathieu Guindon

6:47 PM

pfff, details

headerSheets = Array("COVER", "SCOPE", "SUMMARY", "Updated Hours EST", "RATES")
headerSheets.PageSetup.CenterHeader = "&B &12 PROPOSAL" & Chr(10) & Chr(10) & " &08 " & Filename

this

lol

Mathieu Guindon

^ @M.Doerner I've yet to update my RD build, but to your knowledge are we flagging this .PageSetup member call?

Hosch250

Jul 27 '16 at 23:21, by Comintern

Ugh. My corner cases have corner cases.

LOL.

Jul 28 '16 at 20:02, by ThunderFrame

I think it's been over a week since we had a grammar bug!

this

7:05 PM

i imagine the grammar back then was.... unrefined.

IvenBach

Any reason why a VBA macro won't write out information until after the VBE has been opened?

this

write out to where?

IvenBach

Worksheet.

dropbox.com/s/gzkno8fo2vtsi1g/… specifically the Create 50 sheets button.

Thought it may have just been a fluke but it's occurred on 3 different machines I've tested it with.

this

7:25 PM

did you enable contents?

IvenBach

Yep.

When I was creating the workbook there was no issue with it. I've not seen this behavior before.

this

i vaguely remember this happening many moons ago but ....

you're not using VBIDE API, right?

IvenBach

No. This is all internal to Excel.

It's like the WS.CodeName isn't recognized until the IDE is opened.

this

TBH I can't remember how/why. It was back when Office 2010 was new, I think.

You could try and decompile the workbook, maybe?

IvenBach

Not sure how to do that.

this

7:31 PM

(need to download a third party tool if you don't have it)

VBA decompiler, I think it's called

mansellan

fwiw I'm finding a few gotchas in Core 3 WPF - nothing major, just silly little differences in layout handling which are difficult to boil down to an mcve. been chasing one for a couple of days now...

this

Pretty the problem isn't with core 3 but with WTF. ;-)

mansellan

^

Mathieu Guindon

7:50 PM

@IvenBach never seen that

IvenBach

I unluckily find the marble in the oatmeal often.

this

You're like VBA's Typhoid Mary

IvenBach

Stuck in If ws.CodeName = vbnullstring then stop and sure enough stop get's hit. When the IDE opens up ws.CodeName is not vbnullstring.

I swear it's the cosmic rays.

Duga

[rubberduck-vba/Rubberduck] MDoerner created comment on issue #5087: Ignore Once QuickFix doesn't work with line continuation - gives no reason why

> It is completely possible to change the annotation scoping yet again. However, I am a bit worried that using logical lines might degrade the performance of the resolvers. (That would have to be tested thought.)

Actually, it is really simple to determine the physical lines belonging to all logical lines. If we store the information the information about the first physical line of a logical line in a b-tree or simply in an ordered list, it should be reasonably fast to do a reverse lookup. (T

[rubberduck-vba/Rubberduck] MDoerner edited comment on issue #5087: Ignore Once QuickFix doesn't work with line continuation - gives no reason why

> It is completely possible to change the annotation scoping yet again. However, I am a bit worried that using logical lines might degrade the performance of the resolvers. (That would have to be tested thought.)

Actually, it is really simple to determine the physical lines belonging to all logical lines. If we store the information about the first physical line of a logical line in a b-tree or simply in an ordered list, it should be reasonably fast to do a reverse lookup. (The depth of a bi

[rubberduck-vba/Rubberduck] zspitz created comment on issue #5188: External type definitions for VBA

> @MDoerner

> My main headache around this is how to prune the maybe references based on the information from deeper down in the binding path.

My first thought on reading this was "Typescript doesn't have this problem." Then I realized that Typescript forces you to typecast `(ActiveWorkbook.Sheets(1) as Chart)` in order to get `ChartArea`. And VBA doesn't have any sort of typecasting beyond assigning to a variable,.

[rubberduck-vba/Rubberduck] bclothier created comment on issue #5188: External type definitions for VBA

> Just a thought - the first implementation could stop at the first translation. At that point, we would be able to generate new inspections "AmbiguousTypeResult" for unbound calls with suggestions for creating a strong-typed variables. They would have to do fix it one by one in the chain but at least it would be a first step in the right direction.

[rubberduck-vba/Rubberduck] MDoerner created comment on issue #5188: External type definitions for VBA

> My problem is not how to do it in VBA. My problem is that I will have to filter the list of possible resolutions (in our internal data) based on information gathered further down the resolution path. That is something that is simply not necessary right now, because of how VBA works.

[rubberduck-vba/Rubberduck] zspitz edited comment on issue #5188: External type definitions for VBA

> @MDoerner

> My main headache around this is how to prune the maybe references based on the information from deeper down in the binding path.

My first thought on reading this was "Typescript doesn't have this problem." Then I realized that Typescript forces you to typecast `(ActiveWorkbook.Sheets(1) as Chart)` in order to get `ChartArea`. And VBA doesn't have any sort of typecasting beyond assigning to a variable,.

If this functionality would be limited to the types of method argument

[rubberduck-vba/Rubberduck] zspitz created comment on issue #5188: External type definitions for VBA

> @bclothier But if someone is used to writing in chained methods, then forcing creation of new variables is a little uncomfortable. Other languages have more natural typecasting syntax, so it makes more sense.

[rubberduck-vba/Rubberduck] retailcoder created comment on issue #5188: External type definitions for VBA

> > if someone is used to writing in chained methods, then forcing creation of new variables is a little uncomfortable.

I would say "just too bad". If someone is used to writing chained late-bound method calls, IMO Rubberduck's job would be to tell them "this member call here, is late-bound - you *want* a variable here".

When that someone gets used to intellisense and compile-time validation for every single one of their member calls, *they'll never look back* is what I'm willing to place

[rubberduck-vba/Rubberduck] bclothier created comment on issue #5188: External type definitions for VBA

> Chained calls isn't the problem per se. The problem is when the chain includes variant/object which effectively renders the code unverifiable at the compile-time.

IMO, an unverifiable code is a bug time bomb and yes I do encourage creating a strongly typed variable.

A common pattern I see in Access is to do stuff like:

`Me.MySubForm.Form.SomeControl.Value = "foo"`

It's verifiable up to the point of `Form`, which returns an `Access.Form` which is generic and has no specific inform

Mathieu Guindon

8:15 PM

it's the implicit late binding, not the chaining, that's problematic

this

^

That's a mind-blowing lesson that RD can't quite tell people about yet

Mathieu Guindon

yeah

another one is this one:

Turning off calculation and screen updating will have little to no impact whatsoever if your code is efficient in the first place. Systematically turning them off amounts to shoving all code inefficiencies under the proverbial carpet. Don't do this. Write efficient code instead - avoiding Select and Activate being the FIRST thing to do. IOW the footnote should be the answer, and the answer should be the footnote. — Mathieu Guindon 11 mins ago

this

many people think early-bound => "I haz a reference!"

Mathieu Guindon

^

70% of respondents said late binding means CreateObject

("member calls against Variant/Object" was one of the choices)

this

hmmm a fun poll question to ask: Can you convert a runtime error into compile-time error? Yes | No

Mathieu Guindon

8:18 PM

even more fun with "should" instead of "can"

this

the problem is that "should" imply "can"

I'm thinking they don't even know it's even a thing.

Mathieu Guindon

right

hopefully "(Excel MPV book title)" readers will make it to the last chapter

;-)

hopes the thing will sell enough copies to earn me a coffee

IvenBach

Those that want to learn will make it to the last chapter.

I think of Application.Visible = False as putting a spoiler on a car. It makes it go faster.

Mathieu Guindon

@IvenBach and magenta & yellow stickers!

this

more importantly -- the racing stripes!

Mathieu Guindon

8:30 PM

lol was going to say "stripes!"

this

This is the model for us to strive for

Mathieu Guindon

lolwut

IvenBach

IRMAGOURD!!! MY EYES!

Ducky has more self respect than that.

Mathieu Guindon

I'd go with a black R8 against that clown

IvenBach

That horrendous image is going to stay on my screen now...

Mathieu Guindon

8:33 PM

actually my Sentra probably beats it

IvenBach

Must get more chat to scroll it off screen.

Mathieu Guindon

IKR

Vogel612

gotta love having autocollapsing oneboxes.

this

Pretty sure it's a handicap, not an asset

Mathieu Guindon

weight or air resistance?

(both?)

this

8:34 PM

both

I can't see how it'd help anything

IvenBach

:+1: F12 for developer tools and delete that image node. Problem solved.

Mathieu Guindon

now imagine following that "car" on the highway

this

I apologize profusely for profaning your eyes.

Mathieu Guindon

#KeepYourDistances

this

maybe that's the real use? Stop people from tailgating it?

IvenBach

8:36 PM

That's what a box of nails is for.

this

that might get you sued, though.

But they can't sue you for having a fugly car, right?

Mathieu Guindon

unless the huge thing stitched to the back falls on my car, then...

IvenBach

Box of nails is one of my thoughts I don't act on.

Mathieu Guindon

phew, it's out of sight

IvenBach

My what a small monitor you have mug. It's barely passed the halfway mark for my screen.

Mathieu Guindon

8:38 PM

lol my monitor is happy with landscape orientation

M.Doerner

I think there are valid use cases for turning off calculation and doing things in an invisible instance.

this

4 mins ago, by Vogel612

gotta love having autocollapsing oneboxes.

@M.Doerner Definitely, but as a first resort?

Mathieu Guindon

@M.Doerner oh, absolutely! it's the constant recommending of doing that regardless of everything absolutely horrendously inefficient going on in the code that gets me annoyed :)

M.Doerner

One use I have is for testing an Excel tool.

Sure, if you do not have to make several copy operations that cause tons of formulas to recalculate, it will not really help.

IvenBach

Does that include TB of workbooks?

Mathieu Guindon

8:44 PM

typically it's things like hammering a worksheet with Range member calls in a loop, like deleting rows - e.g. folks constantly recommend iterating the rows backwards (For...Next) to fix the correctness, and then address the perf issue by turning off app events, instead of using For Each to iterate the object collection, Union to merge the Range they want to act on, and then deleting the unioned range.

repetitive dereferencing of the same Worksheet and/or Range objects over and over and over

IvenBach

To be fair a lot of VBA users aren't aware of Union.

Mathieu Guindon

as if declaring a local variable couldn't possibly make things faster - but it's more code

@IvenBach fair, but the top users are, and I still see them recommending backward loops

IvenBach

That's not good.

M.Doerner

Hm, I do that myself and I never had performance issues.

Mathieu Guindon

do what?

M.Doerner

8:48 PM

Loop backwards to delete.

Mathieu Guindon

it works, but requires app state toggled to work well

otherwise every deletion prompts a recalc

For vs For Each is an excellent read on a tangentially related topic

M.Doerner

If you just have like 3000 rows without formulas, it is basically instantaneous.

Mathieu Guindon

ok put it this way: you're starting at the bottom and you right-click on the row heading for each row you want to delete, selecting "delete".
or, you start at the top, hold Ctrl down, and left-click the row heading for each row you want to delete, expanding the selection - when you reach the bottom, you right-click the selected range and delete the rows. The former hits the worksheet many more times than the latter, and if app state is toggled off, it only makes a difference with the former - the single-delete-step way won't make any difference at all.

and if the deletion is conditional on the value of some cell and you're iterating a Range, it'll be an order of magnitude faster to iterate an array instead, and make a single delete operation on the worksheet.

but yeah, if there's nothing to recalc, then the only difference is the worksheet reads and number of member calls

#FunFacts: if you do VLOOKUP(thing, Sheet2!A1:Z10000, 26, FALSE) in not-365 Excel, modifying any cell in Sheet2!B:Y will cause a recalc, even if they're irrelevant to the lookup.

(not sure if that perf tweak shipped in Excel 2019)

M.Doerner

Does this also apply to MATCH?

I never use VLOOKUP.

Mathieu Guindon

9:03 PM

no, MATCH doesn't suffer this :)

(one more reason to prefer INDEX+MATCH over VLOOKUP)

M.Doerner

VLOOKUP has a range syntax and really annoying restrictions.

Mathieu Guindon

to be fair, VLOOKUP has seen a lot of enhancements in the past year (probably what lead to XLOOKUP being introduced)

M.Doerner

However, I would still like to know what went through the head of the person not making exact match the default for MATCH.

Mathieu Guindon

was probably the same person that thought making Option Explicit an option disabled by default was a good idea

I suppose the era of sensible defaults came after that

this

9:19 PM

I'm half-impressed that in spite of all the malwares that was flying around, nobody managed to turn a computer into a thermonuclear warhead with everyone playing it fast and loose everywhere.

C/C++: we don't need no steenkin' type safety! VBA: we don't need no steenkin' Option Explicit!

Question: is Access the only host to create a class module based on document (Access.Form / Access.Report ) that extends the interface?

I just added a form button to a sheet in excel named Button1 and Sheet1 does not get a Button1 member.

(or maybe I did it wrong?)

Mathieu Guindon

@this Office host, probably - VBA host, can never know for sure

@this form buttons don't do that. ActiveX buttons do

"Form Controls" => Excel.TextBox

this

Ah yes

why doesn't the form controls have that behavior?

Mathieu Guindon

"ActiveX Controls"=> MSForms.TextBox

IDK. They're basically just shapes you can attach to a macro

this

but on Access, I add an Textbox, I get an TextBox1 member which is of Access.Textbox type, that also implements Access.Control. Nothing to do with forms.

Not seeing why they can't do it w/ a Excel.Textbox.

Mathieu Guindon

it's entirely implementation-dependent

they could have, but didn't

this

9:32 PM

Yeah, it just seems to me odd they'd not bother doing it.

Mathieu Guindon

Form controls in Excel can have spaces in their names

this

Probably in name of security..... through obscurity.

Well, here's the annoying thing about Access controls. You can have a textbox named My Pretty Little Textbox and that is a valid name from Access designer but within VBA, it gets a code name of My_Pretty_Little_Textbox.

then you have people naming controls Pcs. Sold, which translate to Pcs__Sold, IIRC. bleh.

Mathieu Guindon

had they gone with MSForms, Access code would have never needed that many square brackets

ttgh

this

they should never have allowed spaces, period.

but alas, it was the era of before sensible defaults

"let's allow lusers to put in spaces because they hate not having spaces!!!1!"

Vogel612

@this that's somewhat unfair to C++

this

9:43 PM

The way I recall it, type safety was something you opted into on C++

just like how Option Explicit is disabled by default, one must take discipline to use type-safe operations in C++

Vogel612

that's not strictly wrong, but modern C++ is usually written with typesafety

this

I've heard that but I was referring to the 90s.

Vogel612

because raw reference handling and similar shenanigans are discouraged in literally every styleguide

Duga

9:55 PM

[rubberduck-vba/Rubberduck] MDoerner commented on Rubberduck.CodeAnalysis/Inspections/Concrete/IndexedDefaultMemberAccessInspection.cs of pull request #5166: Default member access inspections

M.Doerner

From the top of your head, does anybody know a VBA type with a non-parameterized recursive default member for Let coercion?

M.Doerner

10:09 PM

@MathieuGuindon Do you also want module tags around examples with only one module?

Duga

[rubberduck-vba/Rubberduck] MDoerner commented on RubberduckTests/Inspections/ImplicitDefaultMemberAccessInspectionTests.cs of pull request #5166: Default member access inspections

Mathieu Guindon

10:29 PM

@M.Doerner good question.. at first I would have said "nah, no module would mean it's a std.module"... but then a module name (and type) is useful to make all examples clearer.. so yeah, it's probably better to have an explicit module element in all examples.

@Duga lol

Duga

[rubberduck-vba/Rubberduck] bhavikbansal starred us

2