VBA Rubberducking

Mat's Mug

01:05

@RubberDuck it's not crystal-clear. if the code isn't modified, as long as the host Office app is running. default instances are "reset" when an End statement is encountered (or manually specified in the immediate pane), or when the code gets modified in a way that requires recompiling, like changing an executing method's signature, or changing the type of a variable that's in-scope for example.

RubberDuck

02:19

Look at what I found.

retailcoder.wordpress.com/2014/10/05/…

Mat's Mug

02:44

that's me! :)

next post I think I'll write about that SqlCommand API...

RubberDuck

12:18

I think I'm going to write something up about using GIT with VBA soon.

Malachi

14:47

> Thing is, despite all its flaws, I have to admit I like VBA

???????

??????? @Mat'sMug

RubberDuck

I hate to admit it @Malachi, but I do too. It's like an old car that you fix every weekend, but have too many memories with to go out and replace with a new one.

2

Malachi

lol Right

Mat's Mug

15:32

@RubberDuck I'd love to read that! Do you commit an Excel workbook / add-in, or you export the classes and commit that? And then you download the latest version and need to remove and reimport every modified code file? ...doesn't sound like fun...

RubberDuck

16:15

@Mat'sMug I export them and import them, but I wrote code to do it from the "command line" in the immediate window.

The only part I haven't quite worked out is just plain removing them before importing.

Working on it though.

Oops. Nope. I did work that out. I forgot.

github.com/ckuhn203/VBEX/blob/master/Core/DevTools.bas

ptwales

16:36

You know you have "Core" and "core" folders?

RubberDuck

@ptwales Yeah. I don't know what happened or how to fix it to be honest.

ptwales

17:01

rename one and commit the changes? I can't sync that folder for some reason.

RubberDuck

17:24

I'll see if I can fix it after work.

Mat's Mug

17:37

That's brilliant!

ptwales

Sorry I haven't reviewed any of the Unit Testing Code yet. I have been busy with some coursera classes and searching for gradschools.

RubberDuck

@ptwales I've been busy too man. Getting ready to dig back into it though.

And thank you Mug!

Mat's Mug

17:56

@RubberDuck now how about wrapping it up with some ISourceControlProvider interface, with Commit and Update methods, and then implementing it for both GitHub and SVN? ...and then you can have toolbar buttons in the IDE to configure the provider, repository URL and actually update & commit straight from the IDE! :)

RubberDuck

Put those buttons I figured out to good use? Sounds like a good idea.

ptwales

18:26

@Mat'sMug There are about 4-5 Q's about your unit testing framework. Which one's need to be reviewed and in which order should they be done. I remember you backtracking on some of them.

Mat's Mug

18:37

@ptwales this one is the most recent question that involves the UnitTesting library:

9

Q: LogManager Tests

I should have started with this code. I wrote my logging API without writing unit tests, and since I recently wrote an automagic unit testing API I though I might as well go ahead and use it. So I wrote a MockLogger implementation of the ILogger interface, still with a default instance (like all...

this is the most recent question specifically about the UnitTesting library:

8

Q: Unit Testing - A Better Solution

Following-up on the Automagic testing framework for VBA review, I've refactored much of the TestEngine static class, and introduced a TestMethod class. Refactoring part of the TestEngine into its own class introduced new problems that needed new solutions; because the client code is using the As...

unit-testing vba library

the previous version:

9

Q: Automagic testing framework for VBA

Building on @RubberDuck's recommendations, I now have something I find... beautiful. I'm sure there's a couple of things left to polish - this site is about making great code out of good any code, right? This code requires trusted programmatic access to Visual Basic Project. 1. Client Cod...

unit-testing vba library meta-programming

my first version:

6

Q: A hacked-up testing framework

Inspired by this post, I wanted to be able, in any vba project I could be working on, to create a test class and write test methods. Like this: ThisWorkbook Workbook | class module (client code) Option Explicit Public Sub TestAreEqual() Assert.AreEqual 12, 34, "Values should be equal."...

oop unit-testing vba library

and it all started with @RubberDuck's post:

9

Q: Unit Testing in VBA

Unit testing in VBA is... lacking. (What isn't lacking in VBA though?) Since I've become more interested in unit testing lately, I decided I needed something better than Debug.Assert(), so I started building this framework. Currently there is a ton of functionality missing, but since I'm new to u...

oop unit-testing vba library interface

ptwales

I take it I can skip the original and the hacked up as they are no longer in use? But I need to read Automagic to understand Better Solution? Also wasn't that previously called "Final Solution"?

Mat's Mug

it was, but "Final Solution" happens to be a loaded term that I preferred to edit out to avoid controversy

Malachi

@Mat'sMug looks like they are at module level, so I now have

Set e = Nothing
Set columnD = Nothing

at the end of the module

Mat's Mug

@Malachi that smells

Malachi

wanna take a look at the code quick and tell me what more I can do to make it better?

ptwales

18:47

@Mat'sMug I figured that's why it was changed.

Malachi

Sub FindUnAssigned()
    With Sheets("FY13-Fix")
        Dim e As New Enumerable
        Dim lastARow As Integer
        lastARow = .Range("A" & .Rows.Count).End(xlUp).Row
        Dim lastBRow As Integer
        lastBRow = .Range("B" & .Rows.Count).End(xlUp).Row

        Set e.Collection = Range("A3:A" & lastARow)
        e.Merge (Range("B3:B" & lastBRow))

        Dim cell As Range
        Dim columnD As New Enumerable
        Dim lastCRow As Integer
        lastCRow = .Range("C" & .Rows.Count).End(xlUp).Row

Mat's Mug

the variables e and columnD are procedure-scope, you don't need to set them to Nothing - they die when execution exits the procedure ;)

Malachi

now I can't get it to work on a new sheet?

ptwales

because you named the sheet?

Malachi

because I am trying to run the code on a new sheet with new data

ptwales

18:49

Did you name it "FY13-Fix"?

Mat's Mug

move the code in a module if it's in a worksheet's code-behind, and then pass the sheet as a parameter - get rid of the With block

Malachi

oh wait

ptwales

Also I prefer to declare iterators like `cell` right before the loop so

Dim cell as Range
For Each cell in ....

Malachi

maybe my queries are working they way they should and there aren't any cases left over....

ptwales

Also you are doing .Range correctly when getting the rows but you aren't when you are getting values e.Merge (Range("B3:B" & lastBRow)) is implicitly ActiveSheet.Range not Sheets("FY13-Fix").Range

Mat's Mug

18:53

@RubberDuck is this any relevant? what do you code your VBIDE add-in with?

8

A: Build add-in for VBA IDE using VB.NET

It is possible you need to write a com addin using IDTExtensibility2 interface, select the shared addin project template from new project. EDIT Otherwise to create this addin from scratch you will need to do the following: Create a new project class library Add references to "Extensibility", ...

Malachi

@ptwales what do you mean?

e is column A and then I want to merge column B to e

RubberDuck

@Mat'sMug I'm one of those stars.

ptwales

e is an enumerable object. you are adding cells in(Range("B3:B" & lastBRow)) to your enumerable

RubberDuck

I was having trouble though, because there's no way to access the parent application (Excel, Access, Word, etc.).

So I ditched it. It's easier to write separate add-ins for each application.

Mat's Mug

you'll need VSTO / interop assemblies to do that

Malachi

18:56

@ptwales that is what .Merge function does. how am I not doing it correctly?

oh wait

RubberDuck

@Mat'sMug No. You don't need VSTO. You can use COM Interop.

Malachi

I see what you are saying now, I didn't do .Range

Mat's Mug

@RubberDuck yeah, the interop assemblies are called PIA... for a reason ;)

Primary Interop Assembly

RubberDuck

The problem is, the add-in sees the IDE as the application.

Mat's Mug

should be good enough, no?

RubberDuck

18:58

Yes and no.

I wanted to write one add-in for all applications.

It ended up being easier to just import it into different add-ins for each app.

ptwales

@Malachi I am just saying that .Range(...) => Sheets("FY13-Fix").Range(...) where Range(...) => ActiveSheet.Range(...) its a easy gotcha if you are not careful. If the specified sheet is not the activesheet then you will run into problems

2

RubberDuck

So, I have an *.xlam, *.addda, etc.

Malachi

we should ask Microsoft for the source code to Excel and write some functionality for it in something reasonable like C#

ptwales

But you can do Range(ws.Cells(..), ws.Cells(..)) is a range of ws

We should also ask it to support the Linux desktop.

Mat's Mug

@RubberDuck ...it's written in VBA?

RubberDuck

19:01

Yes. Now it is. I wrote the first version in C#, but it didn't work out for me.

Mostly because VBE doesn't have a parent application property.

Mat's Mug

I need to try this in C#

but why would you need to access the host app?

Microsoft.Vbe.Interop is the same for all Office apps right?

RubberDuck

I don't remember off the top of my head. There was a reason.

I haven't looked at it in a few weeks. I got distracted with that bug in my vbeProcedure class.

Malachi

lol

2

did you guys see all those flags for someone using a bad word in chat?

RubberDuck

No. I missed it.

Mat's Mug

gtg.. later guys!

Malachi

19:07

later

Malachi

20:35

0

Q: Checking for missing values in several columns of a spreadsheet

Basically I had 3 columns of Data Adjudicated Cases Not Adjudicated Cases All Cases and I wanted to get a list of all the cases that were missing from Column A and Column B, with a little help from @RubberDucky and his really cool Enumerable.cls (Found on GitHub in His VBX Repository) I used...

vba excel

RubberDuck

21:11

@Malachi Thanks for the plug.

If you guys get a chance, my writing could use a review too.

VBA and Git

Malachi

@RubberDuck nice I will add it to my reading material.

@RubberDuck randomlyle.blogspot.com

RubberDuck

@Malachi Is there a way to follow?

Malachi

@RubberDuck maybe follow me on Google+?

RubberDuck

@Malachi Oh cool. I'll do that in a bit then.

Cya buddy.

Malachi

later

RubberDuck

22:58

New user earned himself 50 bounty points.

2

A: Testing code with Debug.Assert

Your GetEquivalentCode procedure is flawed. The IsMissing() function requires a variant; it will not behave as you may imagine when passed a string variable. Optional parameters if not variants will be assigned their default values: passed or not, they will be there. Your initial test cases all...

Transcript for