VBA Rubberducking

12:00 AM

@Mat'sMug Another reason to only assign the return value once (and at the end of your function/property), is that if your code errors, you don't inadvertently return the half-baked result.

Duga

RELOAD!

[retailcoder/Rubberduck] 50 commits. 32273 additions. 17055 deletions.

[rubberduck-vba/Rubberduck] 5 commits. 1 closed issue. 8 issue comments. 18 additions. 4 deletions.

ThunderFrame

My ideas might be half-baked, but my implementations are rock-solid.

shadowofsilicon

@ThunderFrame that's why i usually code my functions like that

Mat's Mug

@ThunderFrame oh, absolutely!

Mat's Mug

12:24 AM

“More good code has been written in languages denounced as “bad” than in languages proclaimed “wonderful” - much more.” - Bjarne Stroustrup

Tweeted by CodeWisdom on June 29, 2017 at 7:03 PM

@CodeWisdom *cough*

Tweeted by rubberduckvba on June 30, 2017 at 12:24 AM in reply to CodeWisdom

A.S.H

@Mat'sMug He must have VBA in mind :P

Mat's Mug

If that's the case then he's not following the tag on SO lol

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

4

ThunderFrame

@Mat'sMug more databases have been implemented in spreadsheets than in RDBMS.

Mat's Mug

LOL

#SadTruth

IvenBach

@ThunderFrame I did that before, I've since learned.

You can only work with what you know...

@Mat'sMug In his defense he does have 'VBANoob' in his name.

ThunderFrame

12:39 AM

@Mat'sMug An astonishing amount of the global economy runs on CSV files and recorded macros.

Mat's Mug

@IvenBach then there's no excuse for this:

> I am stuck with VBA until I can convince the IT folks that I am worthy of a Visual Studio license and scripting access.

ThunderFrame

@Mat'sMug Mindset should be: I saved the company the cost of 3 commercial licenses, and avoided over-engineering a solution, by learning to use the tools we already have.

Mat's Mug

^

IvenBach

If he's worked with VS he should have more of an idea how to do this, right?

Mat's Mug

Read the bottom part of my answer. That's a "nope".

> From your post and the nature of your question I suspect your class has.. what, 99 properties, one for each line on the invoice?

That's just wrong, and VBA (or the VBE) has nothing to do with it

IvenBach

12:58 AM

After reading it the idea of a collection of items makes a lot of sense. Making 99 properties and typing them out just feels like a terrible waste of time.

@Mat'sMug I love your hint by the way.

Home Time

A.S.H

still using "this"? @Mat'sMug. Thought you agreed on changing the name ?

I must say It's a beatiful one though.

ThunderFrame

@A.S.H meh, I think this is a good name

A.S.H

1:24 AM

@ThunderFrame we've had some discussion about the theme on SO. Everyone loved it, but only the name was somehow questioned IIRC.

10

A: Class "let" stuck in infinite loop

Your issue is here: Public Property Let t_acct(ByVal newval As Integer) t_acct = newval End Property That should be assigning the encapsulated field (tacct), not itself. I'm going to give you my secret recipe: whenever I create a new class module, I start with a private type: Option ...

ThunderFrame

@A.S.H @Comintern isn't here to defend himself. this wins.

taunts @Comintern

A.S.H

haha, but he wasn't the only one (almost)

I must say that I had thought about it at moments, and implented a few cases. Couldn't find a more eloquent name. I'm a convert.

eloquent and elegant

Mat's Mug

1:43 AM

=)

ThunderFrame

If you've been struggling with this for a year, you've surely worked out whether you're using VBA or VB.NET? — ThunderFrame 8 secs ago

A.S.H

2:04 AM

@ThunderFrame not sure i understood the question. TLDR...Programming is not my main occupation in reality, I'm faculty member and when I program it's mainly for research/experimentation. C++ for the core and Excel/VBA for results analysis. I also have some personal activity with a product (based on imaging) that I use in some events to make some additional income

(on a regular basis since 15+ years). Now to answer, I moved the reporting part to VB.NET for a few years but switched back to VBA. keyword for me is RAD. Nothing can beat VBA for RAD, IMHO.

ThunderFrame

@A.S.H Wasn't a question, or directed at anybody... Was just me quoting some snark I'd made on an SO question....

(hint - click on the "8 secs ago" link)

A.S.H

yeah but anyway, I seized the occasion to present myself :)

That was a funny comment btw

Hosch250

If RD can stand losing me and Comintern at the same time, I'd guess our bus factor is pretty good.

@A.S.H Have you been here before? I don't remember your name.

Also, what is the reference to "this"?

A.S.H

@Hosch250 yes I have been here but I'm not as a "full member".

"this" is the name chosen by @Mat'sMug for the private data member in class modules.

Hosch250

Oh.

A.S.H

2:45 AM

I mean of the RD development team :)

Peter M Taylor

4:45 AM

@Hosch250 what did you mean by "bus factor" are we talking about maintenance or business?

IvenBach

Bus factor

The bus factor is a measurement of the risk resulting from information and capabilities not being shared among team members, from the phrase "in case they get hit by a bus". It is also known as the lottery factor, truck factor, bus/truck number or lorry factor. The concept is similar to the much older idea of key person risk, but considers the consequences of losing key technical experts, versus financial or managerial executives (who are theoretically replaceable at an insurable cost). Personnel must be both key and irreplaceable to contribute to the bus factor; losing a replaceable or non-key...

If something terrible were to happen to @comintern or @Hosch250, ie they died, and RD carried on they weren't that critical to development.

ThunderFrame

6:23 AM

and... I have RD approved and installed at work.

2

gosh, 2.0.13 seems so old

2

Peter M Taylor

6:43 AM

Congrats @ThunderFrame now we have meaningful issue requests...

Peter M Taylor

6:58 AM

Thank you @IvenBach for th background reading behind the word, now I have learnt something new today.

Duga

9:22 AM

[rubberduck-vba/Rubberduck] SystemsModelling opened issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

> Here is the code that can generate the error and how to avoid it:
Option Explicit

' an empty #IF clause causes RD to throw an error System.ArgumentOutOfRangeException: Length cannot be less than zero.
#If Mac Then
#else
' this construct is to avoid problems from #If Not failing because it's a number 1 not a boolean
#End If

'Rubberduck is OK with a non empty block
#If Mac Then
' Do Mac stuff
#Else
' Do Windows stuff
#End If

#If Mac = 0 Then
' this construct is to

[rubberduck-vba/Rubberduck] SystemsModelling edited issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

[rubberduck-vba/Rubberduck] SystemsModelling opened issue #3094: What are the "SLL mode failed" Warnings?

> Things like these. It ends up in Ready all right, but what are the warnings about?

2017-06-30 09:52:32.5399;DEBUG-2.0.13.32288;Rubberduck.Parsing.Symbols.SyntaxErrorException;;
2017-06-30 09:52:32.5399;DEBUG-2.0.13.32288;Rubberduck.Parsing.Symbols.SyntaxErrorException;Token: End Sub (L64C1);
2017-06-30 09:52:32.5529;DEBUG-2.0.13.32288;Rubberduck.Parsing.VBA.RubberduckParserState;Module 'clsDebug' state is changing to 'Parsed' (thread 19);
2017-06-30 09:52:32.5529;WARN-2.0.13.32288;Rubber

FreeMan

11:36 AM

@ThunderFrame Best reason yet! As I said, I'm not against it, was just wondering if there were reasons beyond "that's they way we've always done it", and the answer is a resounding Yes!

Duga

11:53 AM

[rubberduck-vba/Rubberduck] Vogel612 added label antlr to issue #3094: What are the "SLL mode failed" Warnings?

[rubberduck-vba/Rubberduck] Vogel612 added label support to issue #3094: What are the "SLL mode failed" Warnings?

[rubberduck-vba/Rubberduck] Vogel612 edited issue #3094: What are the "SLL mode failed" Warnings?

[rubberduck-vba/Rubberduck] Vogel612 commented on issue #3094: What are the "SLL mode failed" Warnings?

> The way I understood it, ANTLR (our friend and helper for parsing your VBA code) has different "modes" of how it attempts to understand the code you feed it.

These modes are the SLL mode and the LL mode. They work a bit differently internally, which means that the SLL mode is generally speaking faster, if it works, but sometimes fails on valid input. Opposed to that, the LL mode is slower, but it will only fail, if things are **actually** broken.

To leverage the speed of SLL and the corr

[rubberduck-vba/Rubberduck] Vogel612 added label antlr to issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

[rubberduck-vba/Rubberduck] Vogel612 added label parse-tree-preprocessing to issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

[rubberduck-vba/Rubberduck] Vogel612 edited issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

[rubberduck-vba/Rubberduck] Vogel612 added label bug to issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

FreeMan

12:09 PM

@ThunderFrame Yes, yes it does!

Still eagerly awaits a new release...

knows he should quit being lazy and kick in to help

3

Quack Exchange

12:25 PM

0

Q: Unoptized VBA macro

Could you review this code please? Macro Sub Contain_Copy() Dim ranger As Long Dim lastrow As Long Dim FromSheet As Worksheet, ToSheet As Worksheet Set FromSheet = Sheets("C") lastrow = FromSheet.Cells(Rows.Count, "N").En...

Duga

12:52 PM

[rubberduck-vba/Rubberduck] MDoerner commented on issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

> I will have a look at this tonight.
Judging from the log entry, this is on the release build 2.0.13. So, it might be that this has been resolved already as part of the changes to the preprocessor since then.

Regarding the suggestions for the log entries, I agree that adding the component name to the log message would make sense. However, adding the line of code is basically impossible because any exception but the syntax exceptions from antlr have no idea about the code of the module.

Duga

1:40 PM

[rubberduck-vba/Rubberduck] rubberduck203 commented on issue #3094: What are the "SLL mode failed" Warnings?

> Here's the relevant doc FWIW. antlr.org/api/Java/org/antlr/v4/runtime/atn/PredictionMode.html

Quack Exchange

0

Q: Speeding Up My Current Code Inventory Management Planning Screen

I first want to apologize for dumping a lot of code into here, but I have been stuck on this issue for days. I was assigned to build a planning screen wherein I could pull corresponding information regarding Sales, Production, and Inventory. As a preface to all of this, this code works for me and...

performance vba excel

Duga

1:53 PM

[rubberduck-vba/Rubberduck] shadowofsilicon opened issue #3095: No RD menu item in context menus

> Rubberduck menu is not injected into any context (right-click) menus. See screenshot.

I am successfully running a recent build of the Next branch,
Excel 2013 64-bit
Windows 7 64-bit

![no_rubberduck_menu](https://user-images.githubusercontent.com/28154402/27738437-f1087502-5d70-11e7-8379-3e46c7a76434.png)

[rubberduck-vba/Rubberduck] retailcoder added label critical to issue #3095: No RD menu item in context menus

[rubberduck-vba/Rubberduck] retailcoder added label user-interface to issue #3095: No RD menu item in context menus

[rubberduck-vba/Rubberduck] retailcoder added label bug to issue #3095: No RD menu item in context menus

[rubberduck-vba/Rubberduck] shadowofsilicon commented on issue #3095: No RD menu item in context menus

> Should mention, works on Excel 2016 32-bit on Windows 10 64-bit.

Duga

2:07 PM

[rubberduck-vba/Rubberduck] retailcoder commented on issue #3095: No RD menu item in context menus

> Ugh. I think I removed the menu-creation log entries... or does .13 still log them? In any case I'm pretty sure the debug output would show menu creation details... if you run the debug build from Visual Studio, and bring up the "Output" (debug) toolwindow, there could be a hint or two there.

[rubberduck-vba/Rubberduck] retailcoder commented on issue #3095: No RD menu item in context menus

> Ugh. I think I removed the menu-creation log entries... I'm pretty sure the debug output would show menu creation details... if you run the debug build from Visual Studio, and bring up the "Output" (debug) toolwindow, there could be a hint or two there.

Mat's Mug

@Duga duh, .13 doesn't log them - that's the version running on your own machine, dummy mug..

Duga

[rubberduck-vba/Rubberduck] shadowofsilicon commented on issue #3095: No RD menu item in context menus

> Results from the Output tool window in VS2017:

```
'EXCEL.EXE' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_64\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'EXCEL.EXE' (CLR v4.0.30319: DefaultDomain): Loaded 'c:\Users\electassy\Documents\Rubberduck\RetailCoder.VBE\bin\Debug\Rubberduck.dll'. Symbols loaded.
'EXCEL.EXE' (CLR v4.0.30319: DefaultDomain):

shadowofsilicon

@Duga nothing stood out to me

puzzlepiece87

2:34 PM

@Mat'sMug Oh, is it not a good idea to have a class with a ton of properties? What do you do instead?

@ThunderFrame I beat you by a week :)

Mat's Mug

@puzzlepiece87 it's not the number of properties, but the fact that they're Line01, Line02, ... Line99 - it's begging for a data structure.

Duga

[rubberduck-vba/Rubberduck] cncplyr starred us

3

puzzlepiece87

@Mat'sMug Ah thank you

Probably not, but does anyone have a history of wrestling with Excel's Remove Duplicates feature? I restarted and I'm still seeing some pretty frustrating behavior.

Oh oh, maybe it's because calculation is turned off?

No :(

Fixed it with Trim.

Kaz

2:50 PM

@puzzlepiece87 Trim is one of those things Like Ctrl+S I just do out of habit these days.

Mat's Mug

@puzzlepiece87 "[excel][vba] remove duplicates": 587 results

so, yeah :)

puzzlepiece87

3:19 PM

@Kaz Just for the record, it ended up being the thing where some cells have hidden, stuck numberformats that won't fix or change until you paste over them, Trim was just the way I accomplished that this time.

But yes, Trim is helpful in general.

And I use =A1+0 for results that are stuck as text when they should be numbers.

Kaz

@puzzlepiece87 Interesting. I just use VALUE()

Or NUMBERVALUE if you have modern Excel.

puzzlepiece87

3:36 PM

Yours is good too, I didn't know about that :)

Mat's Mug

I hate worksheets filled with =+A1 and other funky hacks.

there are functions, use them :)

puzzlepiece87

Well said.

If it makes you feel any better, I don't leave format hacks in my worksheets - I paste them over the original data.

(And curse Excel for letting its formatting get stuck)

Mat's Mug

protip: don't use text formatting. ever.

puzzlepiece87

That's easy to say until you need to concatenate, right?

Mat's Mug

no. just don't. use. text formatting.

2

ever.

puzzlepiece87

3:51 PM

You have been warned.

Mat's Mug

NEVER put a Date into a String. Be it in VBA, SQL, PHP, C#, Java, or anything. Treat dates as dates; no ifs, no buts. A String can only ever represent a date, not be a date. — Mat's Mug 1 min ago

another one warned

Kaz

@Mat'sMug I think it's fine in output tables.

I'm especially fond of replacing zeroes with "-" in tables.

And occasionally adding "K","M","B" formatting etc. to large numbers.

Mat's Mug

@Kaz fine, until you need to lookup something

puzzlepiece87

@Mat'sMug I recently converted all my strDates to dateWhatevers, I'm on board.

Mat's Mug

Hungarian Facepalm

4

puzzlepiece87

4:06 PM

2.0.13 bug/incomplete as feature - you can't disable the Hungarian inspection.

Mat's Mug

reported and fixed already

there's also a bunch of inspections missing from the settings

puzzlepiece87

@Kaz I think you can do this particular one with a numberformat, but not the KMB.

@Mat'sMug That will clear up 70% of my inspections :P

Mat's Mug

actually.. I think you can edit it into the XML config, and it will just take it

puzzlepiece87

The other two big ones that I am going to work on doing better are for implicitly public methods, and broken encapsulation.

I feel like I must have misunderstood that from what I learned from you for minimal methods and OOP.

Either that or I'm not organizing my modules in a typical way.

Mat's Mug

    <CodeInspection Name="HungarianNotationInspection" Severity="DoNotShow" InspectionType="MaintainabilityAndReadabilityIssues" />

^ try adding that under the <CodeInspections> tag in your configuration

puzzlepiece87

4:09 PM

When you have a top level method that calls a method, do you usually keep that called method in the same module?

Mat's Mug

#ItDepends

puzzlepiece87

#ItDepends

Mat's Mug

usually my top-level code instantiates an object that's responsible for carrying out some task, and then calls a member on that object.

Vogel612

@Mat'sMug soo ... I'm implementing a system where I need do save arbitrarily typed data into the same field in a table, because I need to allow building objects. ...

puzzlepiece87

For me, I've been nesting them basically. So anything that the user triggers is M000, M100, M200. And then anything that M000 calls is M010, M020, M030. Anything M010 calls is M011, etc.

Vogel612

4:10 PM

Am I still not allowed to store stuff as string?

EBrown

@Vogel612 Only dates.

puzzlepiece87

@Mat'sMug I need to learn to do that then.

Vogel612

the data will be including dates, numbers, text, ...

generally arbitrary data.

Mat's Mug

@puzzlepiece87 pretty sure I'd cry if I saw a VBA project with such module names.. not happy joyful tears :)

woah

Thank you very much, you are a legend. That makes a lot more sense to me now :) — OliEshmade 2 mins ago

first time I'm dubbed a legend

puzzlepiece87

@Mat'sMug I'm keeping it on for now to keep the pressure on for #TeamNoHungarian.

@Mat'sMug If it makes you feel any better, they all have names after them

Like M000TopLevel, M010QueryProcessing, etc.

But that probably doesn't make you feel better.

Mat's Mug

4:13 PM

assessment: correct =)

IvenBach

@Mat'sMug There's a first for everything.

puzzlepiece87

I did that as pre-RD module organization, I know RD has module folders, etc. But I also need to keep my modules accessible to people who don't have RD to keep the bus factor as high as possible.

EBrown

@Mat'sMug I found your GD.SE incarnation.

Mat's Mug

lol

oh wow

"the codes aren't unique in the table, there's a row for every foobar associated with each code" - "nah, they are unique, but Mr.X lay it out such that you can see every available permutation" note to self: need to get better at explaining "normalization"

IvenBach

4:40 PM

@Mat'sMug It finally clicked last night. The Lexer/Parser info you fed me finally makes sense to me.

Mat's Mug

YAY!!

Brandon Barney

How would you guys suggest going about learning Full-Stack development for free? Any good resources? I need something I can practice on while learning.

Mat's Mug

define "full-stack development"

Vogel612

@BrandonBarney go build your own app

Brandon Barney

I know full-stack as the databases powering the front end. Is that completely wrong?

Mat's Mug

4:48 PM

dunno

"full-stack dev" means whatever the HR department wants it to mean IMO

Brandon Barney

Didn't know thaty

that*

Basically, my goal is to learn at least a few languages/tools that support learning databases and queries so that I have a foundation to work with as I move into Data Science. I would also love practical uses for Python that I can apply to my own work.

I just havent found any good resources for learning how the different languages/tools work together. I have learned beginner Python, R, and I have played with C#, but I want to get serious about developing my skills in these areas, with a focus on gaining a portfolio.

IvenBach

@BrandonBarney That's where helping with RD updates comes in.

Brandon Barney

@IvenBach It is very difficult to apply C# to my job, and thus it is difficult to convince my boss that C# for RD is 'professional development'.

IvenBach

It's during work time?

Brandon Barney

Somewhat. I have a lot of flexibility in my job to learn new stuff (since every time I get a new task I just automate it), but I have to be able to convince someone, if they ask, that it is of value to the company.

I have some time at home (~2-4 hours a night) but that time is limited by homework/adjuncting.

IvenBach

4:54 PM

I see.

I'm envious of your free time.

Mat's Mug

@BrandonBarney how is C# difficult to use at your job? sell it as "VBA with a non-idiotic syntax and multithreading capabilities" to your boss, IMO that's a done deal ;-)

Brandon Barney

Its what can happen when your job requires Excel and you get really good at VBA :). My most recent project literally turned four 2 hour (minimum) tasks into about 10 minutes each.

Mat's Mug

my latest SQL feat took a 3rd-party inventory update script that ran for 36 hours last year, and turned it into a stupid simple script that completes in 16 seconds

Brandon Barney

@Mat'sMug When your IT department won't let you install any apps you may be able to make on other machines, it is difficult to convince your boss that what you make will be usable :). I have considered making a Add-In for just my team, but he wants to move to convincing our data group that we need SQL as is.

Mat's Mug

eh, I've had sysadmin privs for too long

4

Brandon Barney

4:58 PM

I wish I could get privs like that. I hate limited privs lol.

Mat's Mug

being hired directly by the CEO kinda helps I guess :)

2

Brandon Barney

The end goal though is to develop a strong set of skills for a hopeful career in Data Science, so I need more languages, database skills, statistics, and god knows what else.

I imagine it would! lol

Kaz

@puzzlepiece87 "£#0.00,, "M""

IvenBach

@Mat'sMug This proves @Mat'sMug has a charmed-wing-up-on-the-rest-of-us kind of life.

Mat's Mug

lol

IvenBach

5:01 PM

'Uh yeah... About not having admin privs...' BAM 'Thanks boss!'

Mat's Mug

CEO to CTO: "if he needs something - anything - you provide it to him"

got me Visual Studio and 2 SQL Server instances

I didn't want to abuse, to I'm using my own perpetual license of R#

IvenBach

"This duck gets stuff done we didn't know we needed. Keep him fed and away from everyone else..."

2

Mat's Mug

(using RD's R# license at home)

@IvenBach pretty much

IvenBach

So long as you keep RD going that means you get R#? Nice.

Mat's Mug

need to renew yearly, but yeah

IvenBach

5:04 PM

I'll learn enough to contribute substantially.

Mat's Mug

speaking of which... I'd hate to do this, but I think I'm going to have to revoke @Comintern's key if he doensn't show up soon-ish

Brandon Barney

Anyone got a quick link to a resource on Qualifying workbooks and such? Looking for a VBA noob.

Mat's Mug

3

Q: Why does Range work, but not Cells?

I'm trying to move some data from one workbook into another by assigning the values from one range to another. When I use the normal Range syntax to specify the destination range (Range("A1:B2")) my code works, but if I try to use the Range, Cells syntax (Range(Cells(1,1),Cells(2,2))) my code doe...

vba range cells

fresh from my "dupe targets" SO bookmarks :)

Brandon Barney

lol

I need to start a folder for resources like that. So often I find a question where it could be answered much faster with a link lol.

Mat's Mug

I think I made that folder when I got my ~~gold VBA tag badge~~ dupe hammer on SO

Brandon Barney

5:11 PM

lol

facepalm I have literally been sitting here for a little over half an hour while my macro pulls data I need. It finishes and I check the file...I didnt include the column with the data I need. Literally wasted all that time lol.

puzzlepiece87

10/10 have done that before

Brandon Barney

It is one of the downsides to automating my work. It makes me so lazy sometimes that I forget to doublecheck stuff lol.

Oh well, still faster than pulling it manually.

IvenBach

@puzzlepiece87 We've all been there, except for @Mat'sMug that is...

FreeMan

@puzzlepiece87 this isn't status-by-design???

puzzlepiece87

@FreeMan Yup, that's exactly what I was saying :) I forgot status-by-design so I said bug-as-feature

Brandon Barney

5:24 PM

I am not unconvinced that he hasn't just somehow hacked the rep system.

puzzlepiece87

Imgur blocked at my work, but Jon Skeet?

Mat's Mug

627 answers and counting

Brandon Barney

Holy crap lol

Mat's Mug

Mat's Mug, Quebec

22.2k 4 40 90

vba excel-vba excel c# ms-access wpf access-vba

Vogel612

hmm ... it seems I was less effective..

175 answers and 3.8k rep

Mat's Mug

5:28 PM

42

A: Implementing String.Format() in VB6

I couldn't find one anywhere, so I made my own: Public PADDING_CHAR As String Public Function StringFormat(format_string As String, ParamArray values()) As String 'VB6 implementation of .net String.Format(), slightly customized. 'Tested with Office 2010 VBA (x64) Dim return_value As St...

^ best-voted answer is a selfie...

Brandon Barney

Yet I post very thought out answers with detailed explanations and my OP's just never mark anything as the correct answer.

Lame lol.

Mat's Mug

just keep at it

FreeMan

@BrandonBarney Yeah, I often get that.

Mat's Mug

one of these 40 silver badges is [tenacious]

> Zero score accepted answers: more than 5 and 20% of total.

Duga

[rubberduck-vba/Rubberduck] rkapka synchronized pull request #3069: Created RedundantByRefModifier inspection

[rubberduck-vba/Rubberduck] build for commit 40323fa3 on unknown branch: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] rkapka synchronized pull request #3069: Created RedundantByRefModifier inspection

[rubberduck-vba/Rubberduck] build for commit e5c24b60 on unknown branch: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] MDoerner commented on issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

> I just tested it on the current development build: with the new preprocessor, this problem does not exist anymore.

[rubberduck-vba/Rubberduck] MDoerner closed issue #3093: Empty Conditional Compilation block throws System.ArgumentOutOfRangeException

shadowofsilicon

5:54 PM

@BrandonBarney Our IT department has our computers locked down tight, but then has us run all development software inside a VM with admin privileges. Practical but kinda silly.

Duga

[rubberduck-vba/Rubberduck] retailcoder merged pull request #3069: Created RedundantByRefModifier inspection from rkapka/Rubberduck/next into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] rkapka pushed commit 19d41cc1 to next: Created ObsoleteByRefModifier inspection

Mat's Mug

@Duga and so begins a new era - from next release onward, fixing a RD inspection result can spawn a new, contradicting RD inspection result.

now we can live up to being dubbed "ReSharper for VBA"

Duga

[rubberduck-vba/Rubberduck] build for commit bccaf035 on next: AppVeyor build succeeded

Mat's Mug

expecting GitHub support issues and Stack Overflow rubberduck questions asking why

I should probably blog about it

FreeMan

My 1 gold is Unsung Hero

>Zero score accepted answers: more than 10 and 25% of total.

I hadn't realized it was that bad!

Mat's Mug

6:06 PM

aw

that's the gold version of [tenacious]

IvenBach

@FreeMan Lets single man-tear out

Don't worry we recognize your contributions here at this pond.

Mat's Mug

@FreeMan Awarded 16580 times, if it makes you feel any better ;-)

FreeMan

6:20 PM

awww... gee thanks, guys.

@shadowofsilicon Makes sense - run what you need to, but easy to blow it away if you screw up.

shadowofsilicon

@FreeMan works fine until you want to use the latest version of the 3D modeling software - can't update the version on the host machine and no 3D support inside the VM

FreeMan

weeeellll... it ain't perfect...

Mat's Mug

gosh.. good thing this guy didn't do RD's splash screen

#Obnoxious

probably only there to annoy, too

IvenBach

That looks like a nice pond, almost as good as ours here.

2

Mat's Mug

Curious, what do you need a splash screen for? Are you doing any actual processing, or just displaying a "hi" screen for the heck of it? Take Excel's own splash screen as an example. — Mat's Mug 5 secs ago

I bet he's showing the form, running a timer, then hiding the form

#KillWithFire

EBrown

6:43 PM

@Mat'sMug What was it @RubberDuck was saying about bad programmers everywhere?

Brandon Barney

It isnt even a good photo lol. Its grainy and just way too much. Jesus.

And don't get me started on "We Are" which he somehow managed to mess up twice in a single phrase...

IvenBach

@BrandonBarney Non-native english speaker.

Brandon Barney

Fair enough.

IvenBach

I was, long ago, highly critical of people not speaking English properly. I lived in a Spanish speaking country for 2 years and learned the language. Changed my perspective of others learning additional languages.

FreeMan

My mother was an English major in college, so I learned to be quite precise in my word choices. It's actually pretty easy to identify a non-native speaker from a native speaker who just doesn't speak it well.

IvenBach

6:54 PM

How well do I rate on your gauge @FreeMan? My caveman vocabulary fails me with RD tech stuff constantly.

FreeMan

You're quite good. I recall a few corrections made early on, but, I also try not to be too picky.

IvenBach

Reading has served me well since I was a lil' one. Writing is where I fail fast and often. Thanks for not being too harsh. Knowing there's well versed English major types like you and @BrandonBarney makes me more conscious of what I say.

Brandon Barney

lol

IvenBach

We've all got pet peeves. Just use 'strName' and @Mat'sMug is sure to quack at you.

FreeMan

"Grammar Police" is probably my #1 reason for SE edits.

EBrown

7:11 PM

@Mat'sMug Also, that screen resolution.

Mat's Mug

@EBrown aye, looks like 1998's 800x600 doesn't it?

EBrown

@Mat'sMug The image is 1024x732, which means it's 1024x600 probably, I didn't even know that was possible.

Suspicion confirmed, that window is 1024x600.

Mat's Mug

wow.. shouln't it be 1024x768?

EBrown

That's a tablet resolution for a Kindle Fire 7", Nook Color/Tablet, Galaxy Tab 1-3 7"....

Mat's Mug

oh

EBrown

7:21 PM

Poor guy

Mat's Mug

7:33 PM

FINALLY FIGURED IT OUT!!

0

A: Insert text into the background of a cell

You can use a custom number format to specify a light-grey text to display when the cell value is 0 - Color15 makes a nice watermark color: [Black]000000;;[Color15]"(order number)";@ No messy shapes, no VBA, and the watermark disappears when the value is actually filled up.

> asked Aug 12 '13 at 21:41

#NeverTooLate

Brandon Barney

Wait, are you specifying multiple rule types in one rule?

I didnt know that was possible.

Mat's Mug

customer number format spec is positive;negative;zero;text

Brandon Barney

I had no clue that was even a thing. That is mind blowing lol.

Mat's Mug

'tis

puzzlepiece87

That's how they do it for red negatives, I'm guessing.

Mat's Mug

7:44 PM

yup

IvenBach

@Mat'sMug Just now learned that?

Sorry if that came off sounding harsh. I'm just a bit surprised you weren't already familiar with number formatting.

Mat's Mug

@IvenBach I fiddled a bit with it before, but achieving an actual watermark was still on my todo list :-)

I find it rather crazy that the answers to that 4yo question involve all kinds of weird hacks that don't achieve the real thing

IvenBach

I thought the same, unless they wanted the watermark to persist. The OP didn't state either way though.

Mat's Mug

to me "watermark in a cell" stands for e.g. the "ask a question" watermark

some gray text that disappears when you actually type something

a number format is definitely the most graceful way to achieve this in Excel

ugh

Sorry. I just reached the point where I just systematically downvote any/all questions involving run-time error 91. Clearly you haven't even tried. — Mat's Mug 8 secs ago

puzzlepiece87

8:01 PM

Note that the Sheets collection contains Worksheet objects, but also Chart objects, which will make your code throw another type of error if your workbook contains any. If you want to iterate worksheets, iterate the Worksheets collection. — Mat's Mug 1 min ago

^ And now I have another mass correction to do....

Mat's Mug

that's definitely an Excel-specific RD inspection waiting to be implemented

puzzlepiece87

I'll try to remember to github propose an inspection for that.

Mat's Mug

(/another pet-peeve of mine)

Kaz

@Mat'sMug It seems unclear if the OP wants the watermark to persist after stuff is entered into the cell.

puzzlepiece87

Speaking of possible pet peeves....

Mat's Mug

8:05 PM

@Mat's Mug you are so wise and talented. I am eternally grateful for your sagely guidance. TY heavenly Mat. — Sean Kelly 58 secs ago

flagged

puzzlepiece87

So I got a lot of additional performance by avoiding passing large arrays byref to methods. For those times, I just moved the array calculations out of the nested methods to eliminate the need to pass them. But if I have a workbook and I want to try to avoid hits from passing it, should I just make a public variable?

Mat's Mug

huh

puzzlepiece87

Sorry

So I'm thinking about doing this:

Mat's Mug

passing an array literally just passes a pointer to the beginning of the array (as far as I understand it)... size shouldn't matter at all

puzzlepiece87

Oh

Maybe it was something else I did - I thought that was it.

Sub FillInDollarsByLineOfBusinessEdit(ByVal shtCategory As Worksheet, ByVal wbkReportBeingAdded as Workbook) <-- I'm guessing, following the same logic, that this shouldn't pose any problems even though wbkReportBeingAdded is massive?

Mat's Mug

8:09 PM

you're passing an object pointer, by value, i.e. a copy of a pointer to an object

size makes no difference whatsoever

FreeMan

@Mat's Mug you are so wise and talented. I am eternally grateful for your sagely guidance. TY heavenly Mat. — Sean Kelly 5 mins ago

That's snarky

Mat's Mug

and flagged as not constructive

FreeMan

yeah, sorry, reading through and didn't notice that you'd already mentioned that...

puzzlepiece87

@Mat'sMug Thanks.

@Mat'sMug ... that's not what I've heard :P

Mat's Mug

lol

puzzlepiece87

8:13 PM

I'll pay you back for the .Sheets tip - I'll run a speed test on my program with a large nested ByRef array and without to confirm.

Mat's Mug

cool

IvenBach

8:35 PM

@Mat'sMug You've acquired another admirer through your application of subtle charm.

Mat's Mug

Give Rubberduck a shot - we've written about half of the code inspections to pick up common mistakes that regularly pop up as Stack Overflow questions. The other half is just because we're (the Rubberduck dev team) a pedantic bunch =) — Mat's Mug 5 secs ago

let him admire us then

IvenBach

Your quest to one day be the VBA answer to most SO questions is duly noted.

2

Kapol

Hi

Thanks for merging my pull request. I feel like a boss now.

5

I have corrected a the similar ImplicitByRefParameter inspection to handle interface implementeations correctly. I have also made it an IParseTreeInspection

I guess I should create a new issue on GitHub and relate it to the one I have resolved, right?

IvenBach

@Kapol You can use 'Closes Issue #0000' and that auto references it. I'm not well-versed on the specifics of it.

Kapol

Thanks, but I don't think this is the case here

the second inspection hasn't been fixed along with the first one

I have the code locally, ready for commiting

IvenBach

8:46 PM

Duh Check: Reading docs.microsoft.com/en-us/dotnet/standard/base-types/… and 'A parameter of a delegate is compatible with the corresponding parameter of a method if the type of the delegate parameter is more restrictive than the type of the method parameter, because this guarantees that an argument passed to the delegate can be passed safely to the method.'

This means that a derived class fed into the delegate would work for the parameter method since it would possess all the members the base class would?

Kapol

@IvenBach Yes

IvenBach

Or would it just be the case of int being used for the delegate when a long is used for the method parameter.

Kapol

int and long are not related to each other via inheritance

so this won't work

but if you have Animal and Cat : Animal

then you can pass Cat to a delegate that represents a method with an Animal parameter

because this method will call Animal members and Cat has all of them

it's therefore safe for the compiler to allow you do this

*to do this

puzzlepiece87

@Kapol Way to go!

Kapol

so basically you can have an Action<Cat> delegate and pass a method that takes an Animal parameter

when invoking the delegate, you have to pass in a Cat

at that point the underlying method will be called

this method requires an animal, so a Cat can be safely passed in

@puzzlepiece87 thanks!

I'm going to stick around for a while :-)

maybe I'll have a chance to commit some more serious stuff in the future

@IvenBach I hope what I wrote makes sense

puzzlepiece87

8:55 PM

@Mat'sMug My tests confirm what you're saying.

I'm getting much larger random (network performance? available processor bandwidth?) variance within the same methods than I am for differences between methods.

Putting all my ByRef arrays back inside their methods. Thanks for the headsup.

It must have been something else I did.

Transcript for