VBA Rubberducking

Mat's Mug

00:02

Not necessarily.. but yeah one would be allowed to assume so

@Phrancis ah, I was missing TypeOf - that 2nd answer you got looks like the approach I had in mind

Phrancis

@Mat'sMug Yeah I had tried the second snippet but it had another error (can't test from home)

Got two good answers, guess I'll test out tomorrow and see which I prefer. They both seem like good answers

@enderland any particular reason you select m as prefix to mContact?

Mat's Mug

00:18

^^ so that I can tell you to remove it in a review ;)

2

Phrancis

Just feels like Hungarian naming

Mat's Mug

It's a scoping thing I think

Phrancis

Does VBA work in Office for OSX?

RubberDuck

It's definitely Hungarian and used as a scoping prefix, but some cough ducky cough would argue it's an okay kind of Hungarian.

Phrancis

What does it mean though?

RubberDuck

00:26

No. It's not scoping. Wtf @enderland? Lol

@Phrancis a lot of times you will see variables with an "m" at the beginning to indicate that it's a Module level variable.

I think Microsoft killed the docs, but that used to be their recommendation. m for module, g for global (public).

p for private

But obviously pMyString is a problem because p could stand for public or private and it's a real mess.

Phrancis

Why not just spell it out, like privateMyString ?

RubberDuck

00:41

Because there's mMyString??

This is why everyone hates Hungarian.

You can also make your code do a lookup against a table value somewhere you set, so you can turn this on/off without redeploying a new runtime environment (depending on your use case). I have mine setup to do a "checkIfDeveloper" function based on my login so any "debug" stuff only happens for me or other developers. — enderland 47 mins ago

Isn't that an awful lot of overhead @enderland? Do you notice a slowdown because of it?

Might be a good use for your profiler! =;)-

enderland

00:57

@Phrancis mmmmmmmmmmmmm because it's late (ok 7pm) ?

0

A: Is there a difference learning VBA on Mac vs Windows

Is there anything I'm missing out on not having a Windows machine to learn it on? You will find a few things considerably lacking in the Mac vs Windows editor. One of the main differences is the editor for Mac leaves a lot to be lacking. Many of the windows which are dockable in the Windows...

Phrancis

Excuses, excuses lol. You central-timers

enderland

I have no idea why I prefixed it with an m other than that's the key I hit and copy/pasted? :)

Phrancis

Fair enough lol

enderland

6

A: Access increase speed of DLookup by "caching" tables or other strategy?

If it's just single values then I'd be inclined to use a simple in-memory cache - Private mToolDesignerFullNameCache As New Scripting.Dictionary Function GetToolDesignerFullName(Criteria As String) If mToolDesignerFullNameCache.Exists(Criteria) Then GetToolDesignerFullName = mToolDesigner...

I should post the code I use for that, too

I did the test @RubberDuck and "profiled" it before hehehe

Phrancis

I've found myself prefixing pgSQL parameters with prm_foo and variables with var_bar and got frowned upon on CR because.

I think prefixes are fine, as long as they are descriptive enough to where the average programmer would know what they mean, without having to type out entire words like parameter_foo.

It's a contrast with TSQL and I'm really not a fan of the @ prefix, because it can apply to multiple things

Let alone @@

RubberDuck

01:19

Hungarian has its place. It should make bad code look wrong and good code look right. Very hard to actually do.

Phrancis

Wouldn't that be true for any kind of notation though?

Maybe not, but... I've seen enough of this garbage to have an axe to grind

Mat's Mug

The only appropriate use of Hungarian I've used is to identify out parameters passed ByRef in VBA, e.g. ByRef outFoo As Integer. It's moot in C# though, because the compiler forces the caller to specify the out keyword on out parameters.

Phrancis

SELECT p.crap
FROM Person_Information_Original p

Mat's Mug

@Phrancis funny, I systematically/consistently prefix all my TSQL variables wih @.

Phrancis

Well, that's what you're supposed to do, no? But I hate that parameters are also @

Mat's Mug

01:29

Are they not variables too?

Phrancis

I guess, technically. They are acquired quite differently though...

Mat's Mug

Yet in the scope of the proc, they're nothing less/more than variables ;)

Phrancis

Did we RELOAD yet... looks like it... here have a star

RubberDuck

Yeah. It's 8:30.

Or 20:30 pick your poison.

Wait a sec, don't you have to prefix TSQL variables with an @ ?

Phrancis

Don't think so

Mat's Mug

01:34

I think so

Phrancis

I'm wrong

> Assigning a name. The name must have a single @ as the first character.
- MSDN

Guess it's like $php;

RubberDuck

What's that mean? Looks like a string variable.

Phrancis

Nothing, just dicking around making syntax errors all over the place

Mat's Mug

that would be Dim php$

Phrancis

$php = 'Crap';

Mat's Mug

01:38

Can't have both the type specifier and the As part

Phrancis

Dim php
php = 'Crap'

^^ Right?

Mat's Mug

That makes it a Variant

(strings are delimited with double quotes)

RubberDuck

Yeah, b... That^

Phrancis

So you can change it to 'Poop' or 'Shit'?

Mat's Mug

Or 123

Phrancis

01:40

Oh wait, variables already do that

Ah

RubberDuck

Variant is a special type that is basically untyped.

You could say it "Quacks like a duck."

Usually it's better to type as string, long, etc.

Mat's Mug

works a bit like a C# dynamic -> PITA

Phrancis

So a variant will switch from string to int to decimal to datetime to wtf ever as it's called to do so?

Mat's Mug

it just takes whatever you throw at it. even an object

Phrancis

even a table?

(I guess a table is an object)

Mat's Mug

01:44

Dim foo 'As Variant
foo = "123"
foo = 456
Set foo = New Table

Phrancis

Sounds clunky

Mat's Mug

anything you declare without a type is Variant by default

so careful with multiple declarations on a single line

like

Dim foo, bar As String -> foo is a Variant -> common newbie mistake

Phrancis

Dim foo 'This is a comment'

^^ True?

Mat's Mug

yes. anything to the right of a single quote is a comment

Phrancis

Blech

WHY not use a less common thing like // or /* hi */

Mat's Mug

01:48

would you prefer ..

REM this is a comment

Phrancis

I think I actually would

RubberDuck

I forgot about that! Omg!

Mat's Mug

actually..

RubberDuck

I'm gonna start doing that to my co workers.

Mat's Mug

wait, does it still work in VBA??

it's the old BASIC comment syntax..

100 REM this is a comment
110 FOR i = 1 TO 20
120 REM do something
130 NEXT i
140 GOTO 100

^^ needed a GOTO

oh shit IT WORKS

RubberDuck

01:52

Well, it's still in .Net. I bet it's still in VBA...

msdn.microsoft.com/en-us/library/zzw0d942.aspx

Mat's Mug

it is

gotta update the parser now

REM TODO: this is a todo item

RubberDuck

Backwards compatible

I'll add the code suggestion ... fuck

Mat's Mug

and REM @TestMethod

damn, this is... fugly

wow

@RubberDuck it doesn't work at the end of a statement though (thank God)

ugh.. and the IDE makes it Rem... it looks even worse.

Phrancis

Ok, REM is pretty terrible, in hindsight

Mat's Mug

Adding code inspection: replace obsolete Rem comment with single quote comment syntax

RubberDuck

01:58

You got that one @Mat'sMug?

Lol @Phrancis

Mat's Mug

yeah

RubberDuck

Well, that was funny for a second.

Mat's Mug

github.com/retailcoder/Rubberduck/issues/58

Phrancis

Dim foo As String REM This is not really a comment?

6 mins ago, by Mat's Mug

@RubberDuck it doesn't work at the end of a statement though (thank God)

Mat's Mug

nope. REM was a full-fledged instruction, so you'd have to do this:

Dim foo As String: Rem this is a comment

: separates multiple instructions on a single line of code

Phrancis

02:02

That's horrible. Who thought that would be a good idea...

Mat's Mug

^^ @RubberDuck I'll have to check if we're covered for that

it's historical stuff

retro-compat

same reason Call still works

Phrancis

Okay, maybe single quote isn't so bad. Though I bet it can be a PITA working across languages, especially SQL...

Mat's Mug

you kinda get used to it

and VB.NET kept it!

(now I seriously wonder if Rem works in VB.NET)

Phrancis

Just try it damnit

RubberDuck

Yes. It's in the docs!

Mat's Mug

02:04

msdn.microsoft.com/en-us/library/zzw0d942.aspx

dammit

demoStr1 = "Hello" REM Comment after a statement using REM.

^^ they broke something

RubberDuck

Worst thing about vb.net comments is the XML doc.

Mat's Mug

worst thing about vb.net is vb.net

RubberDuck

'''<summary>

Mat's Mug

lol

Phrancis

microsoft-approved

Mat's Mug

02:08

VB.net is a mixture of too many things to please too many people. if you're going to do .net, do it in C#, and thank yourself later.

Rem is the ultimate proof.

Phrancis

VB.net must be a very modern language: Rem LOL

Mat's Mug

well it does support pretty much anything you can do in C#... and in VB6, and VB6 supports anything you can do in VB5, and in VB4, and most of what you can do in QBASIC, which does most of what you could do in good ol'BASIC.

Phrancis

Does BASIC support most of what you can do in X older language?

Mat's Mug

nope

you know that BASIC is an acronym right?

Phrancis

nope

Mat's Mug

02:15

Beginners' All-purpose Symbolic Instruction Code

Phrancis

Makes sense: Rem Especially why they made it an acronym

Mat's Mug

lol

and the V is for "Visual"

RubberDuck

Remark

Had to shorten it to save bytes.

Mat's Mug

yeah but back in BASIC, keywords were no more than 4 characters

(trying hard to find one with 5)

LEFT$ and RIGHT$

c64-wiki.com/index.php/Category:BASIC_functions

oh

c64-wiki.com/index.php/Category:BASIC-Command

GOSUB and RETURN

ok make it 8 characters

Phrancis

Sub Cigarette()
    MsgBox Now
End Sub

Mat's Mug

02:21

enjoy!

RubberDuck

I'm tempted to see how far I can push the old BASIC syntax now.

It was my first language. I had a VTECH with a single line 80 char display.

Mat's Mug

well the type specifiers are directly inherited from that

I learned with a C64 on a 14-inch color TV that was already 30 years old when I got it

RubberDuck

Punch cards would have been more efficient than coding on that thing...

You had a Commodore?!

Mat's Mug

yeah

RubberDuck

That's sweet.

Mat's Mug

02:25

I was like 12 or so, and found it collecting dust in a drawer

"is that thing still working?"

I used to play pac-man on it when I was like 5 or 6

1987 or so

RubberDuck

Where's my Nintendo?

That thing still works. =)

Mat's Mug

I sold mine to get a Super Nintendo

then I sold the Super Nintendo to get... can't remember. I wish I hadn't sold it.

I'd probably still be playing Kirby's Avalanche and Uniracers.

I had beaten the best time (published in Nintendo Power magazine) on the first track in Uniracers.

RubberDuck

I still have my SNES too. The kids and I played through Mario last summer.

Mat's Mug

awesome

the SNES Killer Instinct was ten times better than the crap they did on N64

@ticker heard of Avg()?

RubberDuck

12

Q: Visual Basic 6.0 Language Syntax

I've done a fair bit of googling, but I've been unable to find what I'm looking for. I am looking for documentation of the syntax to the Visual Basic 6.0 language. Specifically something that could theoretically be used to build a parser for the syntax. Does anyone know of such documentation? ...

Mat's Mug

02:39

actually, average ..and I should have read the post before bitching at it. I can't replace the image link with the actual image.. don't click the image too often, it gets NSFW

Phrancis

@Mat'sMug Anything you sell a Super Nintendo for is definitely a bad deal for you.

Mat's Mug

@RubberDuck this Ira Baxter guy might be helpful

@Phrancis it might have been a ...pizza. My teenage years were... teenage years.

Phrancis

-_-"

I use emulator with PS3 controller to play Ninja Gaiden on NES... and it still kicks my ass... after all those years...

►

Mat's Mug

FF1 still rocks my boat

and I'd love to play the first Dragon Warrior again

> A *Slime* stands in your way
*Attack*
*Slime* takes 5 DMG
*Slime* attacks
You take 1 DMG
...

Phrancis

I remember this... sadly...

Mat's Mug

02:47

Command? uh, laugh?

the music dude, the muuuuusic!!

classic... timeless.

My wife would say "annoying as hell"

RubberDuck

FFIII was the best. No idea why it took so damn long to get it here in the States.

I played 7 before 3...

Mat's Mug

I played FIII (VI) countless times, and never bothered with VII.

probably because I never had a Playstation

and I thought Square was being stupid for dropping Nintendo

Phrancis

FF VI (III US) is probably my best game of all time.

Mat's Mug

agreed

Phrancis

I always liked Cyan as my best character, though Celes and Terra were also awesome

Mat's Mug

02:58

I never renamed any character

Phrancis

^^ that

Mat's Mug

And ~~Mug~~ Mog was the best ;)

Phrancis

Huh?

You mean Mog?

Mat's Mug

lol

Phrancis

Yeah, w/e

Mog was unpredictable, like Gau, not a bad character, but it's a dice toss every fight

Mat's Mug

03:01

yeah. seriously, Sabin was my best. Bum Rush

Phrancis

Sabin is almost a hack

(as is Edgar)

Mat's Mug

how?

Phrancis

AOE things that kill all the things and cost nothing

Mat's Mug

oh, yeah

Phrancis

psst I almost always had one/both of them in my party

Mat's Mug

03:03

then there was Gogo

/free Ultima

Phrancis

That was a weird one, definitely OP

Nothing to do with storyline either

Mat's Mug

was a "hidden" character

there were 2 IIRC

can't remember the other one

Shadow needed an Exception, for his Throw command

and we're soooooo waaaaay off-topic now

loved that game

Phrancis

Not like the room is busy or anything

Mat's Mug

yeah

is it true there was a spot on the 2nd world map where you could land your airship and get the emperor to join your party?

I heard that somewhere, but never found the damn spot

Phrancis

I... don't think so

Here, I made this about a year ago, for fun. Should bring back memories.

To this day, I consider Kefka one of the most dislikeable and evil characters in any game. Kefka would own Sephiroth any day.

Kefka didn't need super-esper-mega-powers. Just a lot of cruelty.

RubberDuck

03:16

@Mat'sMug I found what appears to be a full grammar.

slps.github.io/zoo/basic/…

Phrancis

And I was planning on doing some work tonight ;)
Guess I better at least fix git issues

Mat's Mug

@RubberDuck bookmarked!

@RubberDuck doesn't seem to account for type specifier character in declarations

oh I had forgotten about the ! notation

oh and withevents isn't in the declaration syntax... wow

RubberDuck

He admits it has some issues, but check this out.

github.com/grammarware/slps/blob/master/topics/grammars/basic/…

Mat's Mug

same strategy for line continuation

concern about comments with line continuation isn't valid

oh crap, it is

VB.net fixed it

RubberDuck

Anyway. I'm out. I want to work on the config before work tomorrow.

Mat's Mug

03:31

k

Phrancis

Night @quack

Mat's Mug

03:43

now I really wonder if I've approached this the right way

RubberDuck

10:31

Grrrr 5 answers, it's a useful question. Not a single damn upvote.

1

Q: Check if cell is the last cell of Excel Range

I am looping over MyRange range. I need to find out if Cell is the last cell of MyRange. For Each Cell In MyRange.Cells If Cell = ' What should I write here? 'Do some stuff End If Next Cell I've tried this: If Cell = MyRange.Cells(0, MyRange.Count) Then But it gives ...

excel vba excel-vba

vba4all

11:21

what makes it a useful question?

the cell variable after the loop is the last cell

there should be no good reason to know if the iteration is the last one

when the loop exists the cell will point to the last cell used in a loop

i mean at least from my point of view, i would say its a bad design if you need to know the

* if you need to know if the current iteration is the last one...

that will check every single time... what if there were 2 mln cells to check... 2 million unnecessary checks

I've left ^^ as a comment

RubberDuck

That's a good point, but I can see where maybe I'd want to take special action on the last cell. I know there have been times when I needed to take a special action on the first item.

It also just frustrates me that people feel it's worth their time to answer, but it's not worth an upvote.

vba4all

that shouldn't be done from inside a loop IMO

RubberDuck

I think it's a useful question because it's not exactly an obvious answer.

Well, yeah. I can agree with that.

Good morning btw. =)

vba4all

hehe morning for another 25 minutes :P

im testing that quick sort and multithreading i linked to yesterday (blog.tkacprow.pl/excel-multithreading-vba)

RubberDuck

Heh... I'm just finishing my first cup of Joe.

vba4all

11:36

for me the quick sort on 2 mln items takes max 4 seconds in VBA

improved the quick sort

eliminated variants etc

a bit funny how the OP touches on C# and does VBA comparisons without actually testing and opitimizing the VBA first...

how good of a C# programmer are you going to be if you don't understand optimization - something that C# is basically all about

kk quick ciggy be back soon

and he (the op of the question you linked to) is a SENIOR software dev.... I wouldnt expect a senior to ask a question like that :P

RubberDuck

11:57

Well, yeah........ there's that. But we shouldn't judge OP. We should judge OP's question.

vba4all

yep true

RubberDuck

Time to get the kids ready. I'm looking forward to playing with XML Derserialization at lunch. I've got my xml and classes all set up. =)

reinventing-the-wheel

RubberDuck

12:45

@Mat'sMug ^

Mat's Mug

Nice!

So we now have strongly-typed configs :)

RubberDuck

Yes. The first one was easy. VS did most of the work.

Adding them will be a little more manual, but that's okay.

RubberDuck

13:01

Passing test. =) But......

0

Q: Unit test needs to read file. How do I make it portable?

I'm working on an open source project with another developer. I am working on a unit test that needs to read in a default custom xml config file. I am only guaranteed that the part of the path that is in the repo will be on his machine. How can I rewrite this test to make it portable? I don't wan...

c# windows unit-testing development-environment

RubberDuck

14:33

How did this get six stars? I didn't know we had that many people in this room. lol

Nov 17 at 18:44, by Mat's Mug

The more I learn, the less I know

Mat's Mug

lol

@RubberDuck a unit test shouldn't hit the file system

I wouldn't bother testing XmlSerialization, you can safely assume it just works ;)

RubberDuck

Yeah. I figured that out pretty fast. I don't think I need to test that part of the config I guess. I'll remove it before commit.

Mat's Mug

cool

RubberDuck

It's just easier to debug a test than to start up the add-in blah blah blah.

Mat's Mug

oh yeah

and it documents stuff

I need to write more Parser tests

RubberDuck

14:38

And I'm pretty sure I can create and inject instances of ToDoMarkers into tests I write for the actual ToDoList stuff.

Which I need to get reviewed as soon as I get this done. It's hard to test a control.

Mat's Mug

decouple the control from its data

RubberDuck

Any suggestions for going about doing that?

Mat's Mug

it's WinForms, I think Model-View-Presenter would be in order

RubberDuck

Okay. I'll research that.

Mat's Mug

8

Q: Down the rabbit hole with MVP

Following-up on this post where I implemented a Repository Pattern in vba to abstract ADODB and enable testing my Excel app without hitting a database backend; curious about how far vba would let me push inversion-of-control and loose coupling, I decided to grab the red pill, and see how deep the...

oop design-patterns vba dependency-injection mvp

:)

RubberDuck

14:44

So maybe a TaskList class that inherits from BindingList. Then instead of having a private RefreshTaskList method, TaskList should have a public Refresh method.

Yeah. Okay. I think I can do this.

err...... TodoList.

Mat's Mug

no need to extend the BindingList class - I think I'd just make a BindingList<ToDoItem>... but I might be missing pieces of info

RubberDuck

Well, I did that, but it seemed to be what's coupling it all together.

    private BindingList<ToDoItem> taskList;

        public void RefreshTaskList()
        {
            this.taskList = new BindingList<ToDoItem>();

            foreach (VBComponent component in this.vbe.ActiveVBProject.VBComponents)
            {
                CodeModule module = component.CodeModule;
                for (var i = 1; i <= module.CountOfLines; i++)
                {
                    string line = module.get_Lines(i, 1);
                    if (IsTaskComment(line))
                    {
                        var priority = GetTaskPriority(line);

Mat's Mug

ah, well, yeah. Extract it to a "model" class that exposes a public IEnumerable<ToDoItem> - and couple the view with an interface to the model (which you can mock for testing)

Phrancis

Sub foo
    MsgBox "Monking!"
End Sub

Mat's Mug

Sub bar()
    MsgBox "Monking!", vbOk + vbExclamation
End Sub

Phrancis

14:52

vbExclamation == Cancel button?

RubberDuck

Ahem Where's the scope identifier gentlemen?

2

Mat's Mug

lol

@Phrancis procedures are Public by default

RubberDuck

MsgBox "Monking!", vbExclamation, "Hi Phrancis"

Phrancis

Ah that's what vbExclamation is

Come to think of it, I know nothing about what scope is/means. I shall Google-Fu it today, after I BTW.Work for a bit

RubberDuck

Cya later @Phrancis, but I'll save you some googling. cpearson.com/excel/scope.aspx

RubberDuck

15:12

"The problem is that Implements doesn't support events." <--- this :'( — Blackhawk Nov 6 at 14:23

Yes. That ^

Mat's Mug

but in .net, interfaces can happily define events :)

cheezsteak

15:25

what is Monking?

RubberDuck

14

A: What's a Zombie? And what are the many other memes of Code Review?

Meme: Monking Originator: Morwenn Cultural Height: The 2nd Monitor Background: A morning greeting to the Monkey doing his monkey-business, in other words: monking Examples: A small chat search Variations: Monkernoon, Monkevening, Monknight, ... Important is only that it begins with Monk... ;...

Mat's Mug

15:38

@Phrancis if you have a clue, got a T-SQL question up: stackoverflow.com/q/27064948/1188513

Phrancis

@Mat'sMug I'm not sure if the TSQL variables are designed to survive being passed from one proc to another

Mat's Mug

but it's an out parameter... at least it's meant to be..

enderland

you never "select @id as "

I don't think setting @id=2 does anything unless you do something like "select @id = 3"

Mat's Mug

but I set @id = ...oh

enderland

;)

Mat's Mug

15:46

doesn't work still

i-hate-out-parameters

enderland

yeah I dislike them in SQL too

Mat's Mug

1

A: Out parameter is correctly assigned, but caller only sees a NULL. Clue?

You need to mention the @categoryId parameter as OUTPUT while calling the procedure. Call the procedure like this exec dbo.InsertCategory 'C1', 'Category1', 'Catégorie1', @ts, @categoryId OUTPUT;

caller needs to say "output' too

(can only accept in 2 minutes)

enderland

huh

I learn something all the time

Phrancis

@Mat'sMug why don't you just stick with what you know works lol

Mat's Mug

because I like learning new stuff :)

Phrancis

15:53

True

Mat's Mug

I learned to use the output clause in an insert statement :)

and out parameters

enderland

@Phrancis you will find, that once you get into programming you always want to learn more

RubberDuck

And there's always something more to learn, no matter how much you know.

Nov 17 at 18:44, by Mat's Mug

The more I learn, the less I know

Mat's Mug

my SO rep is climbing faster than my CR rep. Something is wrong.

enderland

$

RubberDuck

15:58

@Mat'sMug It's that damn ticker and the lack of new vb questions.

Mat's Mug

> Query executed successfully. 00:00:01

Transcript for