VBA Rubberducking

Duga

00:00

RELOAD!

[rubberduck-vba/Rubberduck] 5 commits. 3 issue comments. 3670 additions. 4576 deletions.

[Zomis/FactorioMods] 15 commits. 2 issue comments. 756 additions. 137 deletions.

ThunderFrame

00:54

gosh - using 2.0.13 at work - I miss those spinning ducks

RubberDuck

And up for review. codereview.stackexchange.com/questions/168815/…

ThunderFrame

02:07

is it just me, or is it impossible to step a Workbooks.Add statement?

Sub test()
  Dim wbk As Workbook
  Set wbk = Workbooks.Add()
  Debug.Print wbk.Name 'Can't step to this line
  Stop
End Sub

ThunderFrame

02:24

huh - Unload Acrobat PDFMaker, problem goes away

Quack Exchange

03:25

0

Q: Validate data in spreadsheet checking for errors

Good day. I wrote this sub in Excel VBA. I am a novice, but I was able to get my code to work how I wanted it to. Unfortunately, I can see there is a lot of repetition, but it is too complex for me to figure out how to extract the duplicate if statements. I am sure there is a lot of other...

RubberDuck

03:37

@ThunderFrame that's seriously weird. I'm not sure I even want to know how that plug in could possibly create that behavior.

ThunderFrame

04:29

@RubberDuck I suspect the add-in is handling the Application.NewWorkbook or Application.WorkbookActivate events, and the debugger is relinquishing debug control to that code.

Duga

05:48

[rubberduck-vba/Rubberduck] ThunderFrame added label critical to issue #3115: Closing a host document causes Parse Error

[rubberduck-vba/Rubberduck] ThunderFrame added label vbe-events to issue #3115: Closing a host document causes Parse Error

[rubberduck-vba/Rubberduck] ThunderFrame added label bug to issue #3115: Closing a host document causes Parse Error

[rubberduck-vba/Rubberduck] ThunderFrame added label parse-tree-processing to issue #3115: Closing a host document causes Parse Error

[rubberduck-vba/Rubberduck] ThunderFrame opened issue #3115: Closing a host document causes Parse Error

> There would seem to be a caching issue with host documents that have been closed.

Steps to reproduce:

1. Start Excel
2. Ensure a default workbook (like "Book1") is open
3. Open VBE and allow RD to load and parse
4. Close the workbook
5. Refresh RD
6. Experience Parse Error

```text
2017-07-10 15:11:31.1559;ERROR-2.0.13.32288;Rubberduck.Parsing.VBA.ParseCoordinator;Unexpected exception thrown in parsing run. (thread 28).;System.Runtime.InteropServices.COMException (0x80010105): Th

ThunderFrame

@Duga I really thought that had been solved once before?

M.Doerner

06:30

@ThunderFrame Do I correctly infer from the log that this is on 2.0.13 and not on the current build?

Duga

[rubberduck-vba/Rubberduck] BZngr commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> I did some experimenting with this issue(...slow night...). Hopefully what follows is new information:

When a procedure is selected, the `Target.Caption` property is assigned a string value that is comprised of the procedure name prepended with the selection coordinates and the procedure's parent scope.

The assigned value looks something like:
"L39C20 | MyXlsFile.MyModuleName.MyReallyLongSubroutine...<lotsmorecharacters>...Name(procedure)"

So, the `Target.Caption` property doe

M.Doerner

I will have a look with the current build when I get back home this evening.

Duga

[rubberduck-vba/Rubberduck] MDoerner commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> I think we should truncate the procedure name as much as needed to make the whole string fit the 255 character limit.

Moreover, we might also want to drop the file name in case we hit the 255 character limit si ce file names can be very long.

The result would look like `ModuleName.MyVeryLongProcedureN... (Procedure)`.

[rubberduck-vba/Rubberduck] MDoerner commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> I think we should truncate the procedure name as much as needed to make the whole string fit the 255 character limit.

Moreover, we might also want to drop the file name in case we hit the 255 character limit since file names can be very long.

The result would look like `ModuleName.MyVeryLongProcedureN... (Procedure)`.

[rubberduck-vba/Rubberduck] BZngr commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> To allow a 255 character procedure name allowed by VBA, everything including ModuleName. and (procedure) would need to be stripped.

[rubberduck-vba/Rubberduck] PeterMTaylor commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> Just to check an assumption I thought when reading this. Is it dangerous to consider offering chopping/trimming/reducing to met the total length with ellipses as in "..." on the end?

[rubberduck-vba/Rubberduck] BZngr commented on issue #3061: Refactor Rename does not respect whether a class actually implements an interface

> This issue is fixed by #3111

[rubberduck-vba/Rubberduck] BZngr commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> Comment removed as irrelevant - I initially misunderstood the proposed approach.

ThunderFrame

07:02

@M.Doerner Log is from my current PC (which is 2.0.13), but same error occurs on Next

Duga

[rubberduck-vba/Rubberduck] ThunderFrame edited issue #3115: Closing a host document causes Parse Error

ThunderFrame

@M.Doerner Updated the issue - seems to be when the workbook is closed programmatically.

Duga

[rubberduck-vba/Rubberduck] ThunderFrame edited issue #3115: Programmatically closing a host document causes Parse Error

ThunderFrame

i.e. closing the workbook programmatically seems to disable the VBE events?

Duga

07:29

[rubberduck-vba/Rubberduck] ThunderFrame commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> I experienced the same issue when selecting an external identifier that was (the maximum) 255 characters.

e.g.

`x = [SomeReallyLongExternalIdentifier....ThatIs255CharactersLong]`

[rubberduck-vba/Rubberduck] MDoerner commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> That is what I meant with _truncate the name_. We should drop the file name and then truncate the procedure name to make the whole string fit the limit.

It will be truncated in the display anyway, unless you undock the command bar and stretch it over several screens.

M.Doerner

@ThunderFrame Did you also open the workbook programmatically?

I mean in the same run of the program.

Duga

[rubberduck-vba/Rubberduck] ThunderFrame commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> @MDoerner Agreed - We can make best efforts to retain as much meaning as possible (eg. truncate the name, retain the type), but ultimately there should be a guard clause on the CommandBarControl Caption setter that limits the text to 255 characters. I wonder if the TooltipText is limited to the same 255 characters?

ThunderFrame

@M.Doerner no, just closed the default workbook using Workbooks("Book1").Close. I think I discovered the bug after programmatically closing an XLAM (as there isn't any other way to unload an addin other than switching IsAddin = False, then closing the workbook)

TTGH

Quack Exchange

08:01

0

Q: Please help me tweak the code. VBA

Good day everyone, I found this code in the internet and i want to use it. The current output is too long to be read esp when number of output exceeds 100. It shows cut part one by one so it takes too much space in screen.. I have been looking for programs in the internet but i cant find anythin...

vba

Mat's Mug

14:25

hi @A.S.H!

Duga

[rubberduck-vba/Rubberduck] retailcoder commented on issue #3082: Rubberduck crashes host when selecting very long procedure name

> Ugh. What we need is a darn status bar in that IDE...

Quack Exchange

0

Q: Decreasing complexity of loop & Find- VBA

I have written the following code; both Sheet 1 and Sheet 2 are rather big sheet with lots of data. Run this Macro is extremely time comsuming ( It has a high complexity I guess). I'm quite new at VBA and therefore having trouble make the code more effective. Sub Find() Dim rgFound As Range ...

vba excel

A.S.H

14:49

Hi @Mat'sMug

was there some misunderstanding?

Your comment puzzled me...

Mat's Mug

Huh, about Scott's return?

A.S.H

yep

Mat's Mug

I was just jabbing at his habit of answering in comments :-)

A.S.H

I was sure, but it was a coincidence

bc I have had with him a few days ago some "discussion" and his "comments" were involved. That's it :)

Mat's Mug

Ah! lol, nah every now and then I poke him about his comments that should be answers

A.S.H

14:57

lol yeah. :D

Kaz

@Mat'sMug I often put answers in comments, but only when the question is Off-Topic yet the Answer/Bug/Whatever is blindingly obvious and I want to soften the poster's first interaction with CR.

Mat's Mug

@Kaz harder to do on SO =)

Kaz

@Mat'sMug I avoid SO

Mat's Mug

I knew it!

@Kaz SO has been providing test code samples and interesting issues and traps and other inspiration for RD inspections - ~~I love~~ Rubberduck loves SO =)

2

A.S.H

@Mat'sMug fully agree. It should be a mine of gold for RD...

Mat's Mug

15:02

By 3.0 the only VBA questions will be from non-RD users :)

2

A.S.H

Dont say this on SE! :P

Mat's Mug

haha

IvenBach

15:51

Duh Check: deviq.com/tell-dont-ask is saying issue the command on the object. This means the implementation detail, not seen by the user, will deal with any checking if it's valid for it to be done?

Mat's Mug

yes

has more to do with API design than user interactions though

e.g. you're making an invoice. instead of calculating the total amount by adding up all detail lines' amounts, adding in the surcharges, deducting the discounts, computing the tax amount based on the customer's tax group from the invoice header... instead you expose a TotalNetAmount property on the invoice object.

Quack Exchange

16:55

0

Q: How long does it usually take to get code reviewed on this site?

I submitted this code yesterday, and I am just curious when it would be reviewed. I was looking forward to learning some new things.

vba

Mat's Mug

17:16

hi @AshtonMorgan!

Ashton Morgan

hi there

thanx for your comment on CR. hopefully, someone will be able to review my code today with some good refactoring ideas.

Mat's Mug

I'll probably have a look over lunch

have you heard of Rubberduck?

Ashton Morgan

ok, that would be sweet, thanks

i saw reference to it in SO. it's a vba application?

Mat's Mug

it's a plug-in we're making for the VBA editor

it's written in C#

Ashton Morgan

oh, that's cool. is this room for people who test it?

Mat's Mug

17:23

it's pretty quiet right now, but this is our "war room", where the project is being essentially coordinated from

couple of supporters hang around, too

puzzlepiece87

@AshtonMorgan I'm working on it too but @Mat'sMug 's answer will be better :)

Mat's Mug

@Duga (a chat bot) posts everything that happens in the project's repository here, along with a daily recap

@puzzlepiece87 only if I get to it ;-)

scrolls through the code

hmm

copy-paste much

GoTo!!

okay, I'll definitely take the time to shred that little piece of code then

Ashton Morgan

haha, be gentle please

Mat's Mug

aye, beginner tag helps with that

whatever you read in answers on this site, know that it's directed at the code, not the author of the code

well mostly anyway

Ashton Morgan

lol, ok, got it

Mat's Mug

17:30

you'll learn a TON on this site :)

Ashton Morgan

i was just going through some answered vba questions to see what some of the suggestions were for other posts

this site is just what i needed

Mat's Mug

as long as your code works as intended, you can put it up for review here

M.Doerner

Hm, I was also planning to write an answer when I get home.

Mat's Mug

I'm ok with taking the crumbs

Ashton Morgan

whats the story behind the rubberduck name

IvenBach

17:36

@Mat'sMug @AshtonMorgan I've been coming to this pond just over 5 months and have learned a great deal.

Ashton Morgan

is it cuz ur cleaning code, giving it a bath?

Mat's Mug

partly due to "rubber duck debugging", if you've heard the term

except this ducky talks back

Ashton Morgan

no, but i will google it

IvenBach

You explain to a rubber duck, literal Bert & Ernie kind of duck, and you'll see why your stuff isn't working.

When you say it out loud to another person thing become clear.

@Mat'sMug Part of design vs implementation? You don't care how it works just that it does?

It falls into the abstraction category I take it.

Ashton Morgan

that makes perfect sense. in fact, that has happened to me on more than one occasion

Mat's Mug

17:38

@IvenBach exactly

IvenBach

Abstraction, and the idea of it, continually becomes clearer the more I read.

Mat's Mug

@AshtonMorgan I don't even know how many of my un-posted SO questions I've answered just by typing up the question, describing the problem.

IvenBach

I need code more to have it become more than just the intangible idea.

Mat's Mug

@IvenBach you need to make another PR

:)

IvenBach

I'm too slow to keep up with the big ducks...

Mat's Mug

17:40

meh

you could implement one of the missing methods in that quickfix of mine

IvenBach

I've still a lot to learn. Not as much as a month ago.

Mat's Mug

not that it's a particularly easy one to tackle though

IvenBach

ANTLR and understanding that 1st has been my major stumbling block.

Mat's Mug

trust me, without Antlr Rubberduck was doomed in the egg!

once Antlr/parsing "clicks", you're pretty much set

IvenBach

I hope so.

Mat's Mug

17:44

gosh, almost 2PM.. I should eat something.

laters!

IvenBach

@AshtonMorgan Feel free to ask questions. This has been a superb group, only positive stuff comes from this flock.

Ashton Morgan

@IvenBach ok, thanks @IvenBach - right now I am just reading this book called "Excel VBA 24-Hour Trainer". It pretty much covers most the basics I learned, but I think helps reinforce so I don't have to google so much

@IvenBach it seems like the more I learn the more I realize I don't know

3

IvenBach

@AshtonMorgan I'd say that means you're trying to learn the correct way.

The larger the island of knowledge the greater the shoreline of mystery.

2

Ashton Morgan

@IvenBach hopefully. just seems like it takes a long time.

IvenBach

I think the relationship is something like ' radius : 2 ' or thereabouts.

I've been here ~5 months and just now getting a feel for my C# basics. Just keep reading and try to learn. Discouragement happens all the time but the sweet victory of succeeding should keep you going.

@AshtonMorgan chat.stackexchange.com/transcript/14929?m=38617257#38617257

Ashton Morgan

18:01

@IvenBach ok, will do, thanks

puzzlepiece87

18:43

@AshtonMorgan Posted my answer, I'll be here this afternoon if you have questions.

Mat's Mug

@puzzlepiece87 nicely done!

puzzlepiece87

@Mat'sMug Thank you!

FreeMan

Design Question: In one implementation of my IWebDownloader, I need a large number of text strings defined to be able to select from among the multitude of checkboxes that are available. What would be better than a large slew of Const x as String = 'something' declarations?

I could see using a Scripting.Dictionary with the "friendly name" (what's seen on the screen) as the .Key and the actual link ID as the .Item. Then I could populate it in a procedure on initialization.

Thoughts?

Mat's Mug

I'd probably go with the dictionary

puzzlepiece87

@FreeMan Not an expert, but that sounds like a reasonable solution to me.

FreeMan

18:54

The other (current) implementation doesn't need any checkboxen selected at all, so I'm not sure I want to put it in IWebDownloader itself, but in the implementation

thx.

Dictionary didn't occur to me until I started typing up the question... :) Everyone needs a RubberDuck™!

Mat's Mug

that's why so much of the historical transcript of this chatroom is just me talking to myself

4

puzzlepiece87

lol

FreeMan

I've been doing that since loooong before it had a name...

puzzlepiece87

@Mat'sMug Question about VBE behind the scenes: If you do Dim arrStrings() as String, arrStrings = Split("Hello, goodbye", ", "), is arrStrings actually a String or does VBE quietly convert to Variant?

FreeMan

BTW- I've now used RubberDuck™, so I think it is, right? ;)

I relinquish all rights to @Mat'sMug & @RubberDuck

Mat's Mug

19:03

lol

@FreeMan that would be a lowercase "d"

@puzzlepiece87 arrStrings is an array of String elements, Variant doesn't come into play here

typed arrays are a bit of a PITA though

fine in this case though ...I mean when the array comes from a Range

1

Q: Copy/PasteSpecial vs Range.Value = Range.Value

I've read a number of times on this site (and elsewhere) that it's best to avoid copy/paste if possible in VBA macros. For example, instead of doing this... For i = 1 To tbl.ListColumns.Count With tbl.ListColumns(i).DataBodyRange .FormulaR1C1 = "=2*1" .Copy .PasteSpec...

excel vba

^^ here's one for @ThunderFrame ^^

FreeMan

@Mat'sMug well, carp. :( You'd better grab that one for yourself then.

Mat's Mug

Rubberduck™

4

FreeMan

there ya' go! Starred for posterity, even... :)

puzzlepiece87

19:34

@Mat'sMug Thanks, I wanted to make sure after hitting some of the PITA situations.

RubberDuck

19:57

@Mat'sMug it's funny, I never thought I would use a visitor anywhere else, but here we are.

@AshtonMorgan welcome to the club.

2

Mat's Mug

20:09

@RubberDuck what tree are you visiting?

IvenBach

20:25

@Mat'sMug Duh Check: en.wikipedia.org/wiki/Chomsky_hierarchy is what ANTLR follows for its construction?

FreeMan

@Mat'sMug The Gittree, I believe.

2

Mat's Mug

20:41

@IvenBach no sure, possible. haven't looked very deep into how Antlr does what it does really.

RubberDuck

20:52

@Mat'sMug not a tree. An acyclic graph.

Mat's Mug

regrets asking

what's that?

M.Doerner

a directed graph without cycles

basically a tree where the branches can reconnect

Mat's Mug

ha, lovely

ThunderFrame

@Mat'sMug dammit, I'm going to have to outperform your answer....

Mat's Mug

:)

ThunderFrame

21:07

@Mat'sMug Timer returns a Single. hmm, does RD catch that inappropriate type declaration (implicit cast)?

Mat's Mug

FML

nope

puzzlepiece87

> You'll want to avoid copy/pasting when you're working with a relatively small number of cells, and/or if you're iterating individual cells.

Though, thankfully for lazy people like me, with relatively small numbers of cells the absolute delay of always using .Copy [Destination] is likely to not be too terrible, even though the relative delay is sizeable.

I definitely can wait 12 extra ms.

Mat's Mug

> Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation: 3.90625ms
Array read+write, single operation: 3.90625ms

except in that 12ms you have the time to process another 3 worksheets with other methods

> Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 19.53125ms
Setting cell values, single operation: 3.90625ms
Array read+write, single operation: 3.90625ms

or more

puzzlepiece87

21:23

@Mat'sMug In other words, the large relative delay is large, relatively speaking? :P

@Mat'sMug, separate topic, I have a chance to promote RD. May I take it?

Mat's Mug

why not?

where?

puzzlepiece87

0

A: VBA function works only in one worksheet

You need to fully qualify your references. When you use: total = Application.WorksheetFunction.CountIf(Range(codecol), var(i)) + total The VB Editor silently interprets Range(codecol) as ThisWorkbook.ActiveSheet.Range(codecol), which means the function only works on the currently active sheet. ...

I was just being careful, I didn't know if there's some careful balance going on about not overpromoting tools on SO. :) Just assuming I didn't know the entire situation.

Mat's Mug

I've seen SO answers promote RD, from users I've never seen or talked to :)

okay, once

puzzlepiece87

Still counts!

Also, since you mentioned it this morning: Scott Craner strikes again :)

I pretty much only answered that question for the RD promotion chance - I don't like too many of my answers to be Scott conversions.

I like that he doesn't answer dups beyond a friendly comment, even if he doesn't feel like dup-hammering himself at that moment.

Ehh, speaking of which, I should propose a dup for that question...

Oh wow, I didn't know RD had a web inspector!

2

Will include that in my comments

Mat's Mug

@puzzlepiece87 it's slow AF

puzzlepiece87

21:34

@Mat'sMug Yes, but it's a good selling tool.

"If you like this, you'll really like the sports car version!"

Mat's Mug

the plan is to eventually showcase indenter options, too

puzzlepiece87

^

@Mat'sMug I assume it's a busy day for you since you're still here, but if tomorrow's easier, can you give this one a look? stackoverflow.com/questions/45020999/…

Mat's Mug

sure

Names() = pNames

drop the parens

and

Public Property Get Names() As String()

puzzlepiece87

That takes me back to my original error that I was trying to solve by adding them, which is:

EditCategories.Names = arrEditCategoriesTemp <-- "Array argument must be passed ByRef", with arrEditCategoriesTemp highlighted, which doesn't make much sense because it's a local variable.

    Dim arrEditCategoriesTemp() As Variant
    ReDim arrEditCategoriesTemp(lngEditCategoryCounter - 1) As Variant

    lngEditCategoryCounter = 0

    For Each shtLoop In ThisWorkbook.Worksheets
        Select Case shtLoop.Name
            Case RejectTrends.Name, ErrorReference.Name, Other.Name
            Case Else
                arrEditCategoriesTemp(lngEditCategoryCounter) = shtLoop.Name
                lngEditCategoryCounter = lngEditCategoryCounter + 1
        End Select
    Next shtLoop

^ (lngEditCategoryCounter is pre-determined before that segment)

1. Use that loop to count how many category sheets there are

2. Redim arrEditCategoriesTemp() to the correct dimension so I can assign elements one-by-one

3. Use the loop a second time to do the assigning.

4. Save the array to the .Names class property.

Mat's Mug

21:55

> Welcome to the joys of typed arrays in VBA

puzzlepiece87

:D

Thanks, that's what started my initial question about strongly typed arrays, I figured that might be the final answer or that VBA might be converting behind my back. Thanks!

Peter M Taylor

22:13

Some food for thoughts about C# POCO plain old CLR Objects... fullstackmark.com/post/11/…

Mat's Mug

22:29

TTQW - at last

@DanielMcCracken you know, now I'm tempted to take these tests and implement them as Rubberduck unit tests, just to satisfy my OCD with having written code that uses GoSub/Return. — Mat's Mug 9 secs ago

#AnotherSubtlePlug

puzzlepiece87

22:42

It's paying off with stars :P

Mat's Mug

Stop hating #VBA for the wrong reasons. The editor sucks, get over it. VBA > VBE. Rubberduck will prove it. https://stackoverflow.com/a/44832532/1188513

Tweeted by rubberduckvba on June 30, 2017 at 12:22 AM

Tweets too

puzzlepiece87

Today is another day that proves internet points are meaningless :P

I gave a pretty standard CR answer and it's already worth more than anything I've written on SO.

(Not to mention vaulted it into my top 5 answers of all time)

ThunderFrame

23:11

@puzzlepiece87 you mean gamification isn't rewarding in and of itself? loses interest in, but attains self-doubt badge

ThunderFrame

23:30

@Mat'sMug why the variable?

Dim arr As Variant
arr = withRange.Value
withRange.Value = arr

could be

withRange.Value = withRange.Value

or better still:

With withRange
  .Value2 = .Value2
End With

Mat's Mug

@ThunderFrame because written in 5 seconds

=)

And I never used Value2

ThunderFrame

@Mat'sMug fastexcel.wordpress.com/2011/11/30/…

> Range.Value2
> This works the same way as Range.Value, except that it does not check the cell format and convert to Date or Currency. And thats probably why its faster than .Value when retrieveing numbers.
> So .Value2 really should be the default, and is definitely the one to use 99% of the time.

Transcript for