VBA Rubberducking

Mat's Mug

12:07 AM

IT WORKS!!!

RubberDuck

12:21 AM

What works?! Lol

Found some good info on using a config. Still no luck on an example for a list of configs in a group though.

Mat's Mug

12:53 AM

@RubberDuck well sort of - still gotta work on multiple declarations per instruction, and enum members aren't working yet.. but it does pick up every single variable and constant in a module.

RubberDuck

1:07 AM

That's awesome @Mat'sMug. make-it-modern

Ahhh Ha! I think I get it now. I'll have to try this in the morning.

52

A: How to get a List<string> collection of values from app.config in WPF?

You can create your own custom config section in the app.config file. There are quite a few tutorials around to get you started. Ultimately, you could have something like this: <configSections> <section name="backupDirectories" type="TestReadMultipler2343.BackupDirectoriesSection, TestReadMu...

So I think I create a TaskListIdentifiers class that uses the actual values... Perhaps key/value pairs (for priority) and load them at runtime.

Yeah. That should work.

Mat's Mug

6:34 AM

@RubberDuck found a good name for the add-in... I think you'll like it :)

2

I'm shoving all extension menus under a Rubber&duck menu.

vba4all

9:37 AM

@Mat'sMug shouldn't this just be VBE Addin rather than VBA IDE Add-in?

RubberDuck

10:19 AM

@Mat'sMug that is... Awesome.

RubberDuck

10:56 AM

Monking @vba4all. Happy Monday to ya.

vba4all

@RubberDuck Cheeers and to you too

RubberDuck

=)

vba4all

and super excited awaiting a reply from a company that supports our ERP system... they are trying to tell me that a column in their sql table is of INT type while I already proved that it's of float type... trying to charge hours of support while the real problem/bug is how the html renders the page ... the entire conversation with them is hilarious - they try to tell me im a noob and i have no idea what i am talking about

RubberDuck

Don't ya love that? Personally, I'm a fan of the people that just assume that you're an idiot and feed you a ton of BS.

> No. I'm not going away until you fix your broken shit. I'm a programmer. I'm your worst nightmare Call Center guy.

I now have a dedicated support team with our software vendor. I submit more actual bugs in their software than anyone else.

I think it's mostly because I have a tendency to want to make things do things they just weren't meant to do though. =)

vba4all

11:17 AM

i totally hear you.. i am probably their most hated client - or at least i've made it top 5

they are not seeking bugs... if someone finds one its like "oh, shit we've got work to do..."

RubberDuck

Which is the completely wrong attitude. I'm always grateful when somebody finds a bug in my code.

> Oh crap! Let me go fix that! Sorry about that.

vba4all

exactly

RubberDuck

12:10 PM

Ahhhhhh man Mug..... why is the test engine responsible for showing the test explorer?

I get it, but.... that kinda sucks. Definitely going to take a minute to make it dockable.

RubberDuck

1:03 PM

Okay. Window shows, but I'm getting ActiveX errors. Will have to debug later.

Mat's Mug

2:53 PM

@RubberDuck ugh, yeah... there's a number of things I'm not too happy with...

RubberDuck

I get why you did it though.

I injected an instance of the testexplorer and the host control into the engine to get it to work, but I can't help but feel that the test explorer should listen for events from the engine.

Mat's Mug

yeah.. another name for TestEngine could have been TestManager ;)

RubberDuck

Yeah. The more I think about it.... TestExplorer should have an instance of the engine. Not the other way around. Maybe I'll work on that.

Mat's Mug

3:15 PM

I'm going to rename the app default namespace and registry keys to Rubberduck eventually.. then I'll make an About window, ...and then maybe I'll turn this image into a 220x250 community ad to post on Stack Overflow open source projects :)

RubberDuck

I forgot that they do that.

Mat's Mug

but first I need to get nested syntaxes to parse corerctly

I'm redesigning the DeclarationSyntax, I had it wrong. I need to consider it as a "HasChildNodes" syntax - so Dim a, b, c can produce a DeclarationNode with 3 child VariableNode nodes.

RubberDuck

And I need to figure out why Assert is suddenly raising an error.

I didn't frak with that stuff... so I'm confused.

@Mat'sMug Smart.

Mat's Mug

AssertClass? at runtime?

the reference to RetailCoderVBE.tlb is only automagically added to the active project when you add a test module from the TestExplorer [Add] menu, if it helps...

RubberDuck

Yeah. Maybe the reference is broken.... that's possible. I can't look at it until later though.

All that stuff is on my other laptop.

vba4all

3:44 PM

hey what are some best OOP practices?

RubberDuck

Oh! and @Mat'sMug I added a Menu base class to abstract the FindInsertionIndex and AddButton methods.

vba4all

0

Q: Best practice when validating class properties in VBA?

I haven't worked much with classes, so I think this is a beginner's question. I have a class that has the following property: Private pAvtalsslut As Date '''''''''''''''''''''' ' Avtalsslut property '''''''''''''''''''''' Public Property Get Avtalsslut() As Date Avtalsslut = pAvtalsslut En...

RubberDuck

Hmmm..... kind of depends I guess.

vba4all

exactly my point

generally getters/setters exist so you can validate data coming in/ going out

but I couldn't classify best practices.... could you?

RubberDuck

Well, the question is too broad, and perhaps unclear. It depends on the purpose of the class. If it's specifically for use with spreadsheets, then I would put the validation inside the getter/setter and make sure it's providing a valid value.

The big but here is if you plan to use the class generically, then it would be surprising to return an empty variant rather than zero....

In this case, I would imagine he wants to use it exclusively with spreadsheets, so letting the class do it would reduce redundant code and runtime errrors.

Now, is that a best practice? Beats me...

vba4all

3:50 PM

haha

yeah when someone asks how to make a variable in VBA

you don't answer with an OOP meta programming solution haha

2

Mat's Mug

@RubberDuck nice!

RubberDuck

@vba4all We do in this room. =;)-

vba4all

2

Q: Am I overkilling questions with solutions that are too complex for the OP to understand?

Lately, I have quit my SO's professional-debugger position caused by lack of time and a desire to answer only more challenging questions.... Anyhow, I have noticed that I may be overkilling the questions with solutions that are too complex(not really though) for the askers to understand...or may...

discussion answers

it was hard to adjust to the idea but now i fully agree with the below statement:
> Always tailor your answer to the asker's level of expertise. You wouldn't answer a simple question about method polymorphism in Java with a treatise on Object-Oriented theory, would you? No, you wouldn't.

so that's why I started backing off questions that are too broad to start off with...

RubberDuck

@vba4all don't you dare stop writing long in depth answers. I never would have learned Interfaces if you just wrote short, here's the code answers.

2

enderland

People upvoting answers which are meaningful in the VBA tag would probably get better answers on the whole. Part of the reason I don't even bother is it's not worth it, not just from the "give me teh codez" perspective but also from the perspective of "meh, it's not goign to get me meaningful rep anyways"

Mat's Mug

4:08 PM

@enderland that Workplace.SE answer of yours was awesome... almost made me create an account there ;)

enderland

@Mat'sMug Thanks :)

I definitely "shine" more on Workplace than with actual coding

vba4all

@RubberDuck haha thanks

IMO getting 20K rep from participating in VBA tag only is probably equivalent of 200K rep in C# lol

2

enderland

@vba4all more than 200k probably

Mat's Mug

@vba4skeet

2

vba4all

hahaha

Mat's Mug

4:14 PM

I wish there was more VBA posted on CR

(even now that the tag is badgeable)

vba4all

yeah C# vs VBA top rep users

sid at 2.9K as #1 vs skeet at 131.4k

Mat's Mug

codereview.stackexchange.com/tags/c%23/topusers

vba4all

131.4/2.9 = 45 times sid's rep gained from vba

@Mat'sMug show off...

Mat's Mug

codereview.stackexchange.com/tags/vba/topusers

enderland

probably not in terms of rep, that's just upvotes

vba4all

4:16 PM

;)

Mat's Mug

I can't believe I'm still at the top of both lists... well the last 30 days is more representative :)

vba4all

off-topic for here but something I am trying to learn and understand the last couple of days

developer.valvesoftware.com/wiki/Source_Multiplayer_Networking

i want to come up with a calculator (could even be excel based) to grab some input from the user and then calculate the best settings

enderland

for what inputs? like an optimization problem?

vba4all

yeah, like each user plugs in his FPS average or have a dll injection into the engine to grab that automatically

monitor refresh rate

and a bunch of other user-related data

+ network

then calculate the best network settings for the best interpolation

enderland

how will you evaluate the inputs? will you have a bunch of points to try to build a model from? or what?

vba4all

4:22 PM

minimize the loss and choke % in packages sent over the network

enderland

are you going to do this realtime, changing settings/etc?

vba4all

yeah first I am doing a fake one

enderland

(my master's work is in optimization so at least I can help here hehehe)

vba4all

so i set the inputs manually

then ill probably make a dll to catch the stats real-time and update the settings

lots of memory work and api calls but hey... something more interesting then answering the "how to copy cells from sheet1 to sheet2 based on some conditions" for a thousand time

enderland

It would be a lot easier if you could actually control all this via your dll so you could have logic find the best set of settings without requiring the user to manually change lots of htings

basically you have inputs and your output is FPS

vba4all

4:25 PM

@enderland that's exactly what I am aiming for

enderland

it's probably easy to get the FPS but can you easily change the inputs too?

vba4all

@enderland quite straightforward because the Source game engine allows to type exec myConfig.cfg in the console

enderland

Bwahahaha that's good :)

vba4all

so you basically write to a cfg an execute exec cfg OR even better just write the cfg and have the source engine load the cfg file automatically adding launch options

like it's done in any Java or C# console app with the ` Main( args[] ... )`

that :

rate "128000"
cl_updaterate "101"
cl_cmdrate "101"
cl_interp "0"
cl_interp_ratio "1"

is all you need to write to the cfg

and then load it with the start up

obviously with adjusted values

RubberDuck

Not sure I should have answered this, but I think it was some good guidance.

0

A: Best practice when validating class properties in VBA?

Generally speaking, it's best to let the class validate the data before getting or setting a property, and then raise an error if the data doesn't meet specifications. This way you don't have to repeat that validation code all over your code base anytime you use your class. However, in this cas...

Very CR kind of answer...

> Forget A & B. Let's do C.

vba4all

4:39 PM

shouldn't have

or....

the idea of MyClassReaderWriter is good

but then you would expect a ITypeExpected or something instead of a Variant

and things get complicated and too broad for a single answer

that's why I don't answer those :P

RubberDuck

Yeah. That's why I didn't get into the code.

vba4all

if there was a 500+ bounty on it I wouldn't sleep tonight but hey you're gonna get at best a few upvotes

RubberDuck

Eh, I don't do it for the rep. Although it does get frustrating to put a ton of effort into an answer that never gets a single upvote or "thank you".

enderland

I use "creationdata" classes sometimes which have all the validation in the class with the data

RubberDuck

lunch time, @ducky's out for a bit.

vba4all

4:45 PM

i mean is like 1000 characters in an answer enough to say it's rather a lot of effort answer ( maybe not a lot of effort but at least SOME worth an upvote ) ??

try your own

277 for me less than 5 upvotes and over 1K characters in a body

that's a pretty rough query obviously but I HOPE you see my point

Mat's Mug

Excel array function help anyone? I need a MAXIFS formula, so I entered this:

{=MAX(IF(SAMSQL[MaterialCode]=$A5,IF(SAMSQL[ModelCode]=$C5,IF(SAMSQL[Components‌]=$D5,SAMSQL[Weeks (26)],0),0),0))}

and it's returning 0... but I'm expecting 17

enderland

the only time this won't return 0 is if SAMSQL[Components‌]=$D5 is true

Mat's Mug

which is the intent ;)

so the formula looks right then

enderland

Also why are you doing an array function for this?

Mat's Mug

is there a built-in MAXIFS formula that I can use?

enderland

4:59 PM

I don't think so, you could write a custom VBA function though

makes it way cleaner and easier..

(depending on size of your spreadsheet I guess)

Mat's Mug

I think I'm going to compute it at the source, in the SQL query.

enderland

That's probably a lot better if you are going to have non-expert excel users use it too

Mat's Mug

^^ that

enderland

I've been burned before writing clever and awesome formulas only to have people who are like "what is vlookup" use it

I'm hesitant to even use match/index in spite of them being better as a result... enough people have no clue on v/hlookup anyways

Mat's Mug

I'm automating manual data entries on a report that the user is spending 3 hours weekly to produce.

and yeah, it's a "what's this veelookup?" user ;)

RubberDuck

6:19 PM

> So the last dev made a mess and they need somebody to fix it?

Silence on the line.

> Thanks but no thanks. Have a great day.

Mat's Mug

@ticker just by the title, OP is clueless

oh wait, it's actually about encoding

RubberDuck

eh, to answer my question. I changed system locale to slovenian and now it works well. guess I was fancy at setup and went with US — bbwolff 14 mins ago

Mat's Mug

@RubberDuck mind if I rename Task to ToDoItem and call "Task List", "ToDo Items"?

RubberDuck

No. That's fine.

Wasn't a missing reference by the way. I've not a frakkin' clue what I broke. =)

Didn't have long to play with it before the recruiter called though.

Mat's Mug

8 mins ago, by RubberDuck

> So the last dev made a mess and they need somebody to fix it?

^^ that?

RubberDuck

6:28 PM

Yeah... She gave me three different job descriptions in 10 minutes.

Turns out they really wanted a System Business Analyst, but didn't want to call it that. Which means they didn't want to pay for it.

Mat's Mug

wait till you have the famous "Rubberduck" VBE add-in on your resume

RubberDuck

No thanks. I'll stick it out at my current shit job.

LOL

It's going to be famous too. It's going to replace MZ Tools eventually. =)

Compliment it for now, but..... sooner or later one stop shop.

Mat's Mug

I'd really want to think of it as some "ReSharper for VBA"

unit tests, todo items, refactorings, code inspections, ...

RubberDuck

Snippets.... I'm going to figure out legitimate snippets.

I should be able to hijack the native windows and install a key listener...

Mat's Mug

yeah! select some code, right-click selection, select "Wrap with..."

oh

keyboard shortcuts would be AWESOME

RubberDuck

6:33 PM

I'm still chewing on it, but the more I look at dockablehostwindow the more I know it's possible.

Mat's Mug

I'd love to hijack IntelliSense and code autocompletion too

RubberDuck

That's exactly what I"m thinking for snippets.

Well..... not hijack it, but mimic it.

I don't think I can hijack the intellisense.

Mat's Mug

yeah.. wishful thinking mug

RubberDuck

Keep wishing. What are you thinking?

Mat's Mug

type "prop" + TAB ~> get a stub public property getter and setter (Let)

^^ not likely eh

RubberDuck

6:38 PM

Yeah... I think we may have to settle for Alt+K >> "prop" + TAB

Alt + K is the snippet shortcut right?

Mat's Mug

but.. right-click, "Insert..." ~> "Property" ~> prompt for name ~> done

RubberDuck

Yeah. I don't think I can just straight up highjack it, but I'm pretty confident I can listen for a key shortcut and then mimic it.

Very true.

The thing about that is I have to take my hands off of the key board.

Mat's Mug

not if your keyboard has a "right click" button ;)

RubberDuck

Good grief @ticker...... GOOGLE IT.

dismiss

Mat's Mug

lol, I dismissed that one without even looking at it

I can't wait to have enough of the grammar implemented, to be able to start working on the "Extract Method" refactoring

select the 3rd nested If..Else block in a procedure, right-click, "Refactor", "Extract Method", a dialog pops up; enter new method name, verify parameters (automagically detected already) and return type (also automagically detected) and procedure kind (guess what, automagically detected also), click Ok ~> done.

3rd nested If..Else block is now replaced with a procedure call to a new method

RubberDuck

6:45 PM

That would make me more likely to want to muck around in someone else's ~~code base~~ disaster area.

Mat's Mug

the cherry on top would be a "Post on Code Review" button, with a feature that implements Simon's CR post builder, for VBA.

RubberDuck

I was just thinking of the Git integration...

Mat's Mug

oh yeah, and source control made easy, too!

so-many-cool-features

MZ-Tools: deprecated

RubberDuck

I need to find a real dev job so I stop trying to make vba a respectable thing....

I'll have lots of good thing for my resume this time next year.

2

Mat's Mug

I'll push the namespace renames tonight, do you still have pending branches?

the readme will need a revamp, too

(facepalm) the dockable TestExplorer, of course

RubberDuck

6:52 PM

@Mat'sMug Yes. I do, but I'll deal.

I've been keeping my master branch up to date, so I can merge local before making a pull request.

Mat's Mug

oh cool

RubberDuck

git-ftw

Mat's Mug

I need to start working on branches, been working off [master] all along

RubberDuck

I made that mistake at work when I moved everything into a repo...

Regretted it dearly.

Mat's Mug

good thing the diff tool I'm using makes merging / resolving conflicts not so bad

kdiff

RubberDuck

6:54 PM

I had pending changes in a master branch, and then I suddenly needed to publish a bug fix. It was a mess.

Yeah. I like kdiff.

Mat's Mug

so I merged your pull requests, and then committed my changes to a new local branch

RubberDuck

Okay. Sounds good.

Just try to remember that Master should always build.

@ducky made that mistake at work too..... lol

Mat's Mug

yeah.. it builds.. I'd like to keep it will only fully-implemented & working features, too. I have a half-working parser in there..

RubberDuck

That's probably a good call too Mug.

CRUD

Mat's Mug

what's that?

RubberDuck

7:00 PM

Now I have to translate that date by hand.....

It's this stupid archaic, never should have been language......

_GN_               If POGStat <> "Live" Then
_PS_                    cycleNum = GetValue("Planogram","Desc 31")
_PS_                    Add to message list {MessageNumber="10"} {MessageDescription="Cycle = " & cycleNum} {MessageSeverity="Information"}
_GN_
_GN_                    If cycleNum <> "N/A" AND cycleNum <> "NONE" Then
_PS_                         verKey = GetValue ("Planogram","DBVersionKey")
_PS_                         Add to message list {MessageNumber="10"} {MessageDescription="VersionKey = " & verKey} {MessageSeverity="Information"}

that upgrade isn't coming soon enough

ptwales

> My code is so bad that I should fell bad about it? jk (I already know its bad)

0

Q: Excel VBA: Using variable in loop to define a Range next to the current cell

In WorksheetA I have a column name Config, and I want to look up for a specific values, and if this value is found, the copy the respective configuration from another worksheet named WorksheetB (that is basically an index/list of different configurations) So, in WorksheetA is found the value "J...

excel vba excel-vba

RubberDuck

Everytime someone uses .Select in vba Santa kills a puppy.

LOL

_PS_ {MessageDescription="DaysDifference" = EffDate - masterEffDate}

That ^ outputs False

wtf..........

Mat's Mug

7:18 PM

@RubberDuck except when it's LinqEnumerable.Select(Delegate) ;)

ptwales

How wells is that Delegate class working?

Mat's Mug

pretty nicely.. if you like code as magic strings :)

RubberDuck

I once had a manager call me a wizard and accuse me of black magic.

5

In a good way of course.

Mat's Mug

@RubberDuck pinned!

RubberDuck

LOL. Nice.

ptwales

7:28 PM

Conundrum: I transformed SortedList into Set. Except Set is a reserved name. What should I call it?

Mat's Mug

is it still sorted? SortedSet?

actually it sounds like what [us .net converts] refer to as Enumerable, no?

ptwales

It's still sorted but it has functions like Union, Intersect, Difference, Symmetric Difference, etc

RubberDuck

It's a set..... ugh...... ummmm...........

Mat's Mug

SunSet

RubberDuck

It's a PTSet. There problem solved.

Mat's Mug

7:35 PM

MySet

SomeSet

FauSet

ptwales

Enumerable allows duplicate elements, a Set does not. SortedSet is nice, i don't think I will be able to beat it.

SetObj

RubberDuck

SubSet (as in the yellow kind)

Mat's Mug

IteratableSet

ptwales

Could this question go on programmers.stackexchange?

Mat's Mug

Q: What do you call a Set class when "Set" is a reserved keyword?

could be

RubberDuck

7:37 PM

Sounds legit, but they don't much like VB over there......

So, just be prepared.

Mat's Mug

^^ that

ptwales

Eh, I will just give them more reasons to hate VBA

ptwales

7:57 PM

well a downvote and a star off the bat. Had to restructure the question so it wasn't just "Name this for me"

Boom Downvote removed!

feels good for someone to tell you that you stopped sucking.

3

RubberDuck

Anyone want to overengineer an answer?

0

Q: MS Access VBA Create a immediate window popup or simulate one

I have a code with a recursive fonction that checks into a folder for folders and write the name, ID, and depth of the folder in my database. The process is repeated until all folders are in the database (usually 200 folders per projects). When I run the code with my code window open I can see w...

vba excel-vba ms-access console immediate-window

Mat's Mug

@ptwales link?

ptwales

@Mat'sMug programmers.stackexchange.com/questions/262353/…

Most of the original question is lost.

enderland

This would not help at all if the strings are as long as trix indicates and make it even worse.. — enderland 7 secs ago

ptwales

Eh I'm calling it a SortedSet

Mat's Mug

8:08 PM

Do Events is the trick to displaying progress mid-stream. You have to give the OS time to repaint the screen. — RubberDuck 45 secs ago

@RubberDuck true - that will also make performing an action longer than it needs to be ;)

RubberDuck

Yeah. I know there are performance implications, but it's already a long running process appearently. Better to let the user know it hasn't crashed.

This is the best answer I've ever seen on SE btw.

3

A: Semantic or structure - which should get preference for variable naming?

SHUZAN held up his staff and waved it before his monks. "If you call this a staff," he said, "you deny its eternal life. If you do not call this a staff, you deny its present fact. Tell me just what do you propose to call it?" If it's a variation or embellishment of a stack, I'd be ...

enderland

8:30 PM

heh interesting error message

Mat's Mug

wow

ptwales

@RubberDuck I'm not a fan of programming parables.

RubberDuck

hahahaha I am.

ptwales

8:51 PM

It's like we are getting closer to following "the spirit of the machine"

warhammer40k.wikia.com/wiki/Machine_Spirit

Mat's Mug

9:08 PM

Quote of the day:

> I've been doing VBA for about 2 months, I know how most stuff works.

enderland

where was that

I've been doing it several years and know not how most stuff works

Mat's Mug

9:23 PM

I've been doing VBA for about 2 months, I know how most stuff works. BeforeI update the .mdb on our server, I usually comment de debug.print in the code. Everything seemed perfec with my textbox form until I got an error saying the string is full about halfway in the process. I need to find another method. — trixrabbit 1 hour ago

> wHi, wLow, LoLo, HiLo, LoHI, and HiHI are all long variables.

And you need better variable names.

And VBA needs unambiguous bitwise operators

RubberDuck

9:42 PM

@Mat'sMug I tried to scare him out of doing anything stupid....

Ahhhhhhh now I understand better. What about adding each line to a listbox instead? That way you only have one line at a time. I don't think you really want to get into hijacking the IDE. — RubberDuck 1 hour ago

Mat's Mug

lol

I think if you really want to log > 200 lines, they should simply be written to a text file. Keep. It. Simple.

RubberDuck

I linked him to my progress bar. I don't think the user really needs to know what's going on, they just need to know that it's doing something.

rookie-mistake

I just got access to the SO low quality queue.... It's scary in there.

Mat's Mug

10:07 PM

TTQW

later!

(TTGBTWOTRD)