VBA Rubberducking

Duga

00:01

RELOAD!

[ckuhn203/VBEX] 22 commits. 2313 additions. 573 deletions.

[Hosch250/Rubberduck] 4 commits. 165 additions. 21 deletions.

[rubberduck-vba/Rubberduck] 2 commits. 16 additions. 7 deletions.

[Zomis/Server] 4 commits. 1 opened issue. 1 closed issue. 1 issue comment. 96 additions. 60 deletions.

Phrancis

00:20

@RubberDuck So, I have an in-person interview scheduled for next week at that software dev firm I applied at

Rossco

@Hosch250 I anticipated someone saying that :-)

@Mat'sMug @RubberDuck @cheezsteak Thanks for the thoughts guys. I was interested in whether anyone had a good reason for doing that. It seems weird when you have a statically typed language not to use it.

BTW I just got back after the interview now.

@Mat'sMug But wouldn't you be worried about changing all that code without unit tests?

Man, am I getting paranoid in my old age or what?

Hosch250

@Rossco I thought you said it was next week?

Rossco

I said 'in a couple'

which actually meant in a couple of minutes.

Which I'm not surprised you didn't understand ;-D

Hosch250

Oh, it is Phrancis next week.

Rossco

It was pretty rapid, I only applied for it 24 hours ago and the company emailed me back immediately.

Hosch250

00:28

Is that good, or suspicious?

Rossco

I was doing some quick swotting on their company and thats when I noticed the implicit typing thing.

Hosch250

It means either they are on the ball, or desperate, I guess.

So, how did it go?

Rossco

Well, it is a small company.

The thing is that I'm unemployed at the moment

and I need to get something going.

Hosch250

I wish I could get on a company I really cared about instead of doing nothing.

The only thing I would miss is not being with my dog all day.

I mean, I have RD and a couple other projects, but...

Rossco

My problem is that all the jobs I would like are in Auckland (== 1 hour drive from here).

Hosch250

00:31

Hmm, either move, work from home, or can't take them, I guess.

Rossco

But I ~~need~~ want to stay where I am until I finish this last paper at uni.

Hosch250

Actually, I've taken classes with people who drive about an hour away from where they work.

How long until you finish the paper?

Rossco

Actually its more like 1.5 hours or 2 with peak hour traffic.

@Hosch250 Paper starts tomorrow.

Hosch250

@Rossco OK, but I've had papers where I have 1 week-, or 5 weeks+ to get them done.

Rossco

Finishes end of October.

Hosch250

00:33

Oh, a couple months.

Rossco

Yup.

Hosch250

BTW, what are you taking again?

Rossco

computer science.

Hosch250

OK. Do you study algorithms and stuff?

Rossco

specifically this is a 2nd year paper about OO programming (in Java)

Hosch250

00:34

OK.

Rossco

Umm, I should do a paper on algorithms some time. I haven't so far.

Hosch250

Do you know anything about Bachelor final projects?

Rossco

this one may have algorithms in it. I'll let you know as it progresses.

Hosch250

I have a whole class my last semester with a final project.

Rossco

@Hosch250 not really. I'm not doing a major for CompSci so its not part of my degree.

Hosch250

00:35

I know nothing about IT, and I'm not really interested in it, so I'm not sure what or how I'll do.

Rossco

I'm sure with the work you've been doing with RD that a final's project will not be difficult for you.

RubberDuck

Most excellent @Phrancis! Good luck!

Hosch250

But that's CS, not IT.

Rossco

yeah, I know.

Hosch250

OK. Thanks for the encouragement.

Rossco

00:37

university is not as hard as the real world.

Hosch250

@Rossco So I've found very drily

Rossco

lol

RubberDuck

Amen @Rossco

Rossco

Is your project a solo or group one?

Hosch250

I have no idea.

Let's just say that at this point, I have no idea about anything.

Rossco

00:38

Group ones are terrible. Hopefully its solo.

Hosch250

I've finished my generals, and I've only taken 3 upper level classes.

RubberDuck

At least you have some experience working on software as part of a team.

Rossco

For sure. ^

Hosch250

I'm sure my project will have to involve networking or system administration or something.

Rossco

I had my first group project last semester and I ended up carrying the others.

Hosch250

00:40

@Rossco Don't you hate that?

Was there no way you could walk out and demand new teammates?

Rossco

Totes, and if its a group project its usually larger which makes it quite burdensome.

Nah, there wasn't. I didn't mind too much. Like I said its the first time I've had to do it and I've done 40+ papers.

RubberDuck

So glad I skipped out on higher Ed. Got mine the old fashioned way.

Hosch250

@RubberDuck Be glad, and don't look back.

You know more about stuff than I will for years anyway.

Rossco

I'm only doing mine the second time around so I can get an interview rather than miss out because the HR manager doesn't see a qualification.

RubberDuck

Yeah. That's the hard part. No doubt.

Rossco

00:43

gtg now guys. Will catch y'all later on.

Hosch250

See you.

RubberDuck

Cya buddy.

Bah. Web is weird... What have I gotten myself into?

Phrancis

@RubberDuck Thank you!

RubberDuck

I hope you get it man. I'm tired of seeing SNAP errors in here... =;)-

Hosch250

@RubberDuck Got anything else ready to be tested?

Wait, I'll move Rename to the Builder.

RubberDuck

00:59

I've no idea @Hosch250. Haven't opened the project in a week.

Been absorbing a lot of info. Makes it hard to want to code after work.

FreeMan

01:40

@Rossco On a good day, my commute is 50 minutes. and hour drive isn't that bad, especially in Australia - it's a big island!

Duga

[Hosch250/Rubberduck] Hosch250 pushed commit 622c7b78 to next: Move Rename tests to builder

[rubberduck-vba/Rubberduck] Hosch250 created pull request #713: More rename tests, make rename tests use new builder thingie to merge Hosch250/Rubberduck/next into rubberduck-vba/Rubberduck/next

Mat's Mug

@Hosch250 honest, how do you find the builder thingy?

Hosch250

Not bad.

Sometimes it seems to need more data than I actually need to set up the tests.

Also, I had to add a line of code somewhere in there to let renaming a module work.

Set up the CodeModule.Name property.

Duga

[rubberduck-vba/Rubberduck] build for commit 622c7b78 on ???: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] retailcoder commented on RubberduckTests/Mocks/MockProjectBuilder.cs of pull request #713: More rename tests, make rename tests use new builder thingie

Hosch250

I don't know if you want to look into this, but in one test, I had to set up the VBProjects list thing.

@Mat'sMug Just mentioned that ^^

Duga

01:49

[rubberduck-vba/Rubberduck] Hosch250 synchronized pull request #713: More rename tests, make rename tests use new builder thingie

[Hosch250/Rubberduck] Hosch250 pushed commit 17ff5321 to next: Removed commented code

Hosch250

There, caught some dead code.

Also:

var projects = MockFactory.CreateProjectsMock(new List<VBProject>() { project });
vbe.Setup(v => v.VBProjects).Returns(projects.Object);

I don't know if you want to move that to the builder?

Duga

[rubberduck-vba/Rubberduck] build for commit 17ff5321 on ???: AppVeyor build succeeded

Mat's Mug

@Hosch250 that would be an easy one :)

Hosch250

I had to do that for the Rename Project test.

Now for a question.

The whole reason for mocking is so we don't have the whole blooming thing set up to fail in a zillion places for the test, isn't it?

Mat's Mug

@Hosch250 it does. however it's guaranteed to set up a mock IDE that can be navigated up and down its components - whatever the SUT does with it, should just work

Hosch250

01:52

Do we really want to set up a full IDE with everything we do and don't need to test a specific thing?

Mat's Mug

@Hosch250 I think a CodeModule object all by itself makes no sense. It has a VBE property that the SUT can call (or not), and that VBE property has a Projects collection that can be navigated back into the CodeModule we're working with.

I'm pretty confident that setting up a Mock<VBE> for all tests, will result in much more solid tests.

Hosch250

Well, if we have a full IDE set up, our tests are less likely to fail because we didn't set something up.

Mat's Mug

^^ and that

Hosch250

So, @Mat'sMug, you have it set up to let me test renaming a button on a form?

Mat's Mug

yup

Hosch250

02:02

OK, figuring that out now.

Mat's Mug

there's an example in my latest CR question :)

Hosch250

I see that.

Hosch250

02:13

var project = builder.ProjectBuilder("TestProject1", vbext_ProjectProtection.vbext_pp_none)
    .AddComponent("Class1", vbext_ComponentType.vbext_ct_ClassModule, inputCode)
    .UserFormBuilder("Form1", "")
    .AddControl("Button1")
    .Build().Object;

How am I supposed to get that, but build the project, not the form?

Mat's Mug

call the ProjectBuilder method to build the form and add it as a component to the project, and get the ProjectBuilder instance

so

var project = builder.ProjectBuilder("TestProject1", vbext_ProjectProtection.vbext_pp_none)
    .AddComponent("Class1", vbext_ComponentType.vbext_ct_ClassModule, inputCode)
    .UserFormBuilder("Form1", "")
    .AddControl("Button1")
    .ProjectBuilder().Build().Object;

Hosch250

var project = builder.ProjectBuilder("TestProject1", vbext_ProjectProtection.vbext_pp_none)
    .AddComponent("Class1", vbext_ComponentType.vbext_ct_ClassModule, inputCode)
    .UserFormBuilder("Form1", "")
    .AddControl("Button1")
    .MockProjectBuilder()
    .Build().Object;

Like that?

Mat's Mug

yeah that

Hosch250

OK, did you test this?

Because it doesn't even get past the parser.

It says the form doesn't have one of the required method/property thingies set up.

Also, do you have the Properties property/method set up?

Because the Rename uses that extensively.

(Well, sort of.)

Dishes, BBL.

Mat's Mug

02:28

@Hosch250 nope

I haven't figured out properties yet

Mat's Mug

03:44

#707 could be worked on in the mean time ;-)

Hosch250

@Mat'sMug No repo.

Mat's Mug

in Access?

I think he means Access forms, not VBA UserForms

Hosch250

Oh.

I don't know the slightest thing about Access.

Mat's Mug

me neither. best of all, I don't even have it

looks like a job for @RubberDuck :)

Hosch250

I'll try.

@FreeMan If you send me a simple working (or rather, broken) example, or instructions on how to set one up, I'll see what I can do.

Mat's Mug

04:01

I'm off to bed - 'night buddy

Hosch250

Night.

Stack Exchange

06:23

Ramblings » VBA: Object Oriented VBA: Design Patterns: Complex Factory

posted on July 15, 2015 by p1ngp1ng

In my last post I mentioned the ability to create a factory at run-time by accessing the VBComponents collection. To do this you must first create a Reference to: Microsoft Visual Basic for Applications Extensibility 5.3 (C:\Program Files\Common Files\Microsoft Shared\VBA\VB6\VBE6.DLL) Having done this you now have access to the VBIDE library and, in particular, the […]

Duga

09:03

[rubberduck-vba/Rubberduck] ckuhn203 assigned ckuhn203 to issue #707: Code Inspection for "Procedure {x} is never used" doesn't check/find form events

Duga

09:23

[rubberduck-vba/Rubberduck] Ronaldvr opened issue #714: Parser error on valid use of Mid() statement

> The parser gives the error:
extraneous input 'if' expecting NEWLINE

on the following piece of code:`

```vba
Mid(f.buffer, f.buffer_pos + 1, 1) = Chr(I)
f.buffer_pos = (f.buffer_pos + 1)
If .buffer_pos >= &H4000 Then
``

Probably because it expects mid() to be a function. However in this case it is used to assign a value, which is valid vb(a)

Duga

10:05

[rubberduck-vba/Rubberduck] Vogel612 commented on issue #714: Parser error on valid use of Mid() statement

> If .buffer_pos >= &H4000 Then

This looks like you're either using a `With` or you meant to write `If f.buffer_pos >= &H4000 Then`...

Meehow

11:24

hello....

FreeMan

12:00

@Hosch250 I'll see if I can work up a sample today. Took a while to realize what you were referring to...

Hi, @Meehow

Duga

12:36

[rubberduck-vba/Rubberduck] retailcoder commented on issue #714: Parser error on valid use of Mid() statement

> @Vogel612 I doubt it would be that. Grammar isn't context-sensitive, *the resolver* identifies the context of an instruction.

It could be the parentheses around `(f.buffer_pos + 1)` but I can't be 100% sure until I repro. Will do that later for sure.

Stack Exchange

Ramblings » VBA: Object Oriented VBA: Design Patterns: The Singleton

posted on July 15, 2015 by p1ngp1ng

The Singleton Design Pattern: when you only ever want one instance of a class to be in existence at any point in time. The Singleton is perhaps the easiest pattern to implement in VBA and to do so you only need to perform the following steps: 1. Declare a static variable of the same type […]

Duga

13:37

[rubberduck-vba/Rubberduck] michaelvdnest forked us into michaelvdnest/Rubberduck

FreeMan

13:51

@Hosch250 Really simple sample Access DB created that shows the issue. How do you want it transmitted to you?

FreeMan

14:12

OK, you should be able to get it from here:

drive.google.com/folder/d/0B3Ctki7FAhFlVkswWkJHWlQ0YU0/edit

Duga

[rubberduck-vba/Rubberduck] daFreeMan commented on issue #707: Code Inspection for "Procedure {x} is never used" doesn't check/find form events

> You should be able to find a very simple Access database with a single form and a single button here:

https://drive.google.com/folder/d/0B3Ctki7FAhFlVkswWkJHWlQ0YU0/edit

When you open the DB, the form should open and a message box stating "Form loading" will appear. When you click the button you will see a message box stating "You clicked the button".

When you run code inspections, you'll see that both of the procedures to display the message boxen will be flagged as unused.

[rubberduck-vba/Rubberduck] Ronaldvr commented on issue #714: Parser error on valid use of Mid() statement

> @Vogel612 : I did ineed use a with in the code itself, but added the variable again here in the snippet for readability: and then I omitted it once which actually did not improve the readability then.. sorry about that...

FreeMan

14:29

BTW - I had to create the db, email it to myself, upload it to G drive on my phone, share the link, email the link back to my work computer, then post the link from there. Why? Because work firewalls all file hosting sites - Google Drive, Dropbox, Box... you name it we can't have nice things. :/

Duga

[rubberduck-vba/Rubberduck] retailcoder commented on issue #714: Parser error on valid use of Mid() statement

> No repro with 1.4.3 and this code:

Sub DoSomething()
With f
Mid(.buffer, .buffer_pos + 1, 1) = Chr(I)
.buffer_pos = (.buffer_pos + 1)
If .buffer_pos >= &H4000 Then Debug.Print .buffer_pos
End With
End Sub

Or this code:

Sub DoSomething()
Mid(f.buffer, f.buffer_pos + 1, 1) = Chr(I)
f.buffer_pos = (f.buffer_pos + 1)
If f.buffer_pos >= &H4000 Then Debug.Print f.buffer_pos
End Sub

Howev

[rubberduck-vba/Rubberduck] retailcoder added label support to issue #714: Parser error on valid use of Mid() statement

[rubberduck-vba/Rubberduck] retailcoder added label code-parsing to issue #714: Parser error on valid use of Mid() statement

Mat's Mug

@Duga if VBA can't handle it, Rubberduck won't handle it either.

Duga

14:47

[rubberduck-vba/Rubberduck] Ronaldvr commented on issue #714: Parser error on valid use of Mid() statement

> OK, the code compilesand works. here is the complete sub

```vba
Private Sub BinWrite(ByRef fFile As BinFile, ByVal intChar As Integer)
With fFile
Mid(.buffer, .buffer_pos + 1, 1) = Chr(intChar)
.buffer_pos = (.buffer_pos + 1)
If .buffer_pos >= &H4000 Then
Put .file_num, , .buffer
.buffer_pos = 0
End If
End With
End Sub

``

Mat's Mug

ah, now I have a repro

Vogel612

[bug][status-acknowledged]

Duga

[rubberduck-vba/Rubberduck] retailcoder added label bug to issue #714: Parser error on valid use of Mid() statement

Mat's Mug

^^

Duga

[rubberduck-vba/Rubberduck] retailcoder removed label support from issue #714: Parser error on valid use of Mid() statement

[rubberduck-vba/Rubberduck] retailcoder removed label code-parsing from issue #714: Parser error on valid use of Mid() statement

[rubberduck-vba/Rubberduck] retailcoder added label vba-grammar to issue #714: Parser error on valid use of Mid() statement

[rubberduck-vba/Rubberduck] retailcoder assigned retailcoder to issue #714: Parser error on valid use of Mid() statement

Mat's Mug

14:55

got it

Vogel612

already found?? that was fast

Duga

[rubberduck-vba/Rubberduck] retailcoder commented on issue #714: Parser error on valid use of Mid() statement

> @Ronaldvr found it, thanks!

It's a grammar bug, specifically with the `fileNumber` grammar rule:

fileNumber : '#'? (ambiguousIdentifier | INTEGERLITERAL);

The rule is expecting an identifier, so until I fix the grammar to make it expect a `valueStmt` (which may cause other unsuspected issues - we'll see!), a temporary fix could be to introduce a local variable to hold the file number - this code parses fine:

Private Sub BinWrite(ByRef fFile As BinFile, ByVal intChar As Inte

Mat's Mug

@Duga #WhyContextMatters

Duga

15:37

[rubberduck-vba/Rubberduck] Ronaldvr commented on issue #714: Parser error on valid use of Mid() statement

> Thanks for the quick follow up @retailcoder! The workaround will do for me for the moment thanks!

Hosch250

16:06

OK, @Mat'sMug, I have a repo on 707.

The inspection appears to work right - RD can't see the event being fired from the form, it appears.

This is all we have in the VBE:

Option Compare Database
Option Explicit

Private Sub Command0_Click()

  MsgBox "You clicked a button!"

End Sub

Private Sub Form_Load()

  MsgBox "Form is Loading"

End Sub

RubberDuck

16:34

hola

Mat's Mug

hi!

FreeMan

yo!

RubberDuck

Did I miss anything this morning other than #714?

@FreeMan got it. Will check it out.

@StackExchange That is NOT the singleton pattern....

Hosch250

@RubberDuck I already checked it out a bit.

See my notes ^^

FreeMan

@RubberDuck What @Hosch250 said...

@Hosch250 there

Mat's Mug

16:39

@RubberDuck and their "factory" wasn't a factory either

I'm quite disappointed with that blogger

shall we show 'em how it's done?

FreeMan

@Mat'sMug yes

please...

Mat's Mug

lol

FreeMan

whachulaughingatmug?

yeah, I really need to start learning more stuff. When I started programming, "objects" were pinned to the wall on a calendar.

RubberDuck

16:56

I'm already drafting a post in my head to rebuttle that one @Mat'sMug. Lol

Mat's Mug

> A singleton can only ever be instantiated once

anything that can be New'd up won't be a singleton

it's not up to the user to decide whether or not to use a "factory" to instantiate it

I'd go the usual way, and make it a PublicNotCreatable type in a separate library

but then, what's complicated isn't to ensure there can be only 1.

what's complicated is ...making that type useful in any way!

RubberDuck

I just thought it was absurd to raise an error instead of returning the existing instance.

Mat's Mug

@RubberDuck that too

Stack Exchange

18:52

0

Q: Renaming mulitple PDF files

I have to modify an existing VBS script that downloads a PDF from a remote location and renaming, then moving to a source directory on another server. Now, we are introducing a new PDF file from the remote location and it has to be renamed and moved to another source directory on another server....

vbscript

Phrancis

20:05

@Mat'sMug / @RubberDuck around? Got a SQL Server question

Rossco

23:29

@Phrancis you still around? Still have question?

RubberDuck

Hey @Phrancis. Sorry buddy. Here now. Kind of at least.

Phrancis

No worries. Just came across a scenario at work that got the gears turning a bit, but I don't really know how to approach it

Say I have two tables:
1) ContractId PK, SequenceId PK, ProductId
2) ProductId PK, ProductDescription

Rossco

Yup

Phrancis

Notice the ProductId in the first table has no FK constraint

Rossco

Yup

Is there a reason for that?

Phrancis

23:33

So let's say I want to either update or insert into (1), but make sure that the ProductId exists in (2), is there a way to do this within a transaction?

@Rossco Bad design

Ported from Access DB to MS SQL DB when the data sets grew too large for Access.

Rossco

Well you will need to select the pid from 2 at some point to create the insert into 1

So select pid into a variable and then check if it is null

Throw an exception or rollback transaction if it is.

Phrancis

Something like IF @pid IS NULL ROLLBACK ELSE INSERT INTO Table1 VALUES (12345, 1, @pid); COMMIT; I guess?

Rossco

Or if you already have the pid and just need to confirm it exists then use an if exists...

Phrancis

That's closer to my use case ^

Rossco

Yeah I thought so. Obviously it would be exceptional for the key not to exist.

Phrancis

23:39

You'd be surprised ;)

Rossco

Lol

Phrancis

When you're working with bleeding edge VB6 applications, and spreadsheets shared by multiple users on a team that know nothing about databases requesting data changes...

The key checks are done at the application level rather than at the DB level, which I hate. I had one just today that was flagged as an underwriter exception because of a non-existent pid that I didn't notice. It's in the DB, but when you try to pull the record at the application level, it's conspicuously absent, with no errors or anything like that.

Rossco

What a headache. Hey listen bud, I've got to go. I hope that helped

Transcript for