VBA Rubberducking

Mat's Mug

01:13

(crickets)

Phrancis

01:52

(further crickets)

RubberDuck

03:02

crickets are getting DAMN LOUD

Mat's Mug

03:14

/lurks

RubberDuck

Some how got to chatting about VB variable sizes next door.

Phrancis

lurking intensifies

RubberDuck

How? A duck showed up...

Okay okay. I'll go back to lurking

Mat's Mug

hey @Phrancis!

Phrancis

@Mat'sMug Yo

Got 2 new music projects, thank gods it's not freaking Xmas music again

Anything new?

Mat's Mug

03:28

a CR answer :)

Phrancis

Ooo ooo pimp?

Mat's Mug

!

Phrancis

Pimp... the answer...

Mat's Mug

> the ~~cake~~ exclamation mark is a ~~lie~~ link ;)

Phrancis

Oh.

I think this is a little silly, but overall... Yeah I like C# braces a lot better

> The spacing before opening parentheses is also jumping at me, screaming "REMOVE ME!!"

I tend to prefer a little more space rather than less... But take this from a DB monkey lol.

Mat's Mug

03:42

and.. edited. Thanks!

Phrancis

Nice

Mat's Mug

I think OP has a tab-instead-of-spaces issue with his post

Phrancis

I particularly hate Java "hugging braces" man that's ugly

Mat's Mug

WTH are "hugging braces"??

Phrancis

if foo {
  ...
} else if {  // THAT
  ...
}

Mat's Mug

03:45

lol

Phrancis

PHP had that shit too... ugh.

Mat's Mug

You see sharp ;)

2

RubberDuck

Yeah. Now that I'm up, I think I'll tackle that VBA question that showed up.

Mat's Mug

pun-intended

Phrancis

I think Java braces are OK, if you do put in a line break. As long as it makes neat little rectangles, ya know. But those hugging braces just cross the ~~streams~~ rectangles and it looks messy.

RubberDuck

03:54

> Whatever you do, don't cross the streams.

2

Mat's Mug

04:06

@RubberDuck if it's the one migrated from SO with the ghost user, it's on my radar as well ;)

RubberDuck

It is. Hope he shows up. I figure an answer might prompt him to.

Mat's Mug

or two!

RubberDuck

All in all it's not bad. I mean, it's bad, but I've seen much worse.

Mat's Mug

me too

I have "Options", "Naming" and "Error Handling" headings

been on it for a little while

Phrancis

I now have more rep on SA than on DBA... ^_^"

Mat's Mug

04:11

that's cooking.se?

Phrancis

0

Q: Reducing "gamy" flavor of turkey

I'm planning on smoking a turkey for the holidays. I have done that before, and it turned out good on the tenderness and flavor, but still had a very "gamy" flavor. I typically use either hickory or fruit wood (fruit has milder smokiness), lump charcoal, for about 8 hours at 200°F and finish it o...

Yeah, Seasoned Advice

Mat's Mug

smoke the ~~manual~~ turkey

Phrancis

Right :D

RubberDuck

No no. Deep fry the turkey.

Phrancis

Having your own smoker is an awesome thing. Got a small one for $60 at Lowes

Mat's Mug

04:14

that's pretty cool

Phrancis

@RubberDuck Kentucky Friend ~~Chicken~~ Turkey

RubberDuck

@Mat'sMug Whitespace and Comments headings here. Magic numbers is probably next.

That is cool.

Phrancis

@RubberDuck Never had fried turkey. Keep hearing about it though. And I usually reply with, "Have you ever had a smoked turkey?"

2

RubberDuck

Which is pure awesome.

and the correct response.

Phrancis

I sacrifice the skin in favor of the meat... fried turkey seems to be opposite, which seems ... odd

And I just twice misspelled "fried" as "friend"... creepy

Mat's Mug

04:18

> Have you ever had a friend turkey?

Phrancis

^^"

Oh, make that 3 times if you count the one I didn't catch and fix

11 mins ago, by Phrancis

@RubberDuck Kentucky Friend ~~Chicken~~ Turkey

Mat's Mug

hahahaha

RubberDuck

=)

Phrancis

Hahahah

I had not, that's a good idea! The squeak is not "quite there" with refrigerated Monterey Jack, I must admit! Refrigerators tend to ruin cheese lol. — Phrancis 36 secs ago

Wisconsinite suggested I ordered cheese curds online...

Mat's Mug

saw that

Phrancis

04:33

Cajun cheese curds ... There must be something in the 10 Commandments about that ... Sounds evilishly delightful

RubberDuck

I miss buffalo cheese curds.

They're mozarella cheese curds. The only ones I can find here are from cheddar.

Phrancis

@RubberDuck I must say, cheddar from around where we are is suck

RubberDuck

Food in the midwest is suck.

Bland.

Phrancis

^^ THAT

Biscuits and gravy... what???

RubberDuck

I made fried potatoes the other day. My wife was like, we can't have that for dinner. I was really confused about that.

When dinner was over, she yelled at me.

> That wasn't fried potatoes. Only one of the ingredients was potatoes.

Mat's Mug

04:37

> friend potatoes?

2

RubberDuck

bahahahha yes. Friend potatoes.

Phrancis

@RubberDuck What else did she expect? lol

Mat's Mug

better than fired potatoes

RubberDuck

@Phrancis That's exactly what she expected. Potatoes.

And only potatoes. I laughed.

Mat's Mug

what was in there?

RubberDuck

04:42

Onions, peppers, bacon, eggs, garlic..

Phrancis

I'd throw in some chives, onions, maybe garlic for good measure. I mean, damn.

RubberDuck

   'free the memory
   Erase Values()
   Erase Errors()

Mat's Mug

huh?

RubberDuck

Yeah. wtf

Phrancis

@RubberDuck That sounds really good, in fact

RubberDuck

04:43

One of my favorites. Fill a family of 12 on $10.

It's in that new vba question Mug. No idea.

Mat's Mug

missed that

RubberDuck

Stopping to see what you covered before I go on.

Phrancis

@RubberDuck Just thought of a twist... grate some ginger and toss it in there, maybe a little Sriracha

RubberDuck

hmmmmmmmm

> A procedure should only have exactly 1 On Error GoTo statement, and exactly 1 error-handling subroutine.

Amen

Phrancis

Replace onions with leeks... Should get your wife really puzzled (and also should be yummy)

Mat's Mug

04:48

@Phrancis the new "monking": "what's cooking?"

RubberDuck

05:12

Okay. That was a bigger mess than I thought. I give.

Mat's Mug

> There's no such thing as bullet proof code. It's a mythical legend, a unicorn.

3

Amen

Phrancis

Unicorn...

Mat's Mug

@RubberDuck Dim compiles at module level??

RubberDuck

Let me double check, but yeah. I'm pretty sure.

Mat's Mug

I think you're right

I just ..always use Private

RubberDuck

05:20

Option Explicit

Dim aMember As Variant

Yup. It works. But yeah. Me too.

Mat's Mug

I hope ~~OP creates~~ our answers will make OP want to create an account

RubberDuck

So do I. OP has potential to be pretty damn good.

He's using WithEvents Mug.

Mat's Mug

and a Rubberduck tester :)

> I am a beginner coder

RubberDuck

> But projects which have referenced earlier versions of the ADO libraries always seem to have problems later on when ADO libraries change names (say, from “2.7” to “2.8”). In other words: ADO isn’t always a lasting solution. In fact, project references aren’t what they used to be for anything. Common Controls, for instances, no longer ship with Office, introducing a whole new set of headaches for us.

dafuq? This guy's worried about 2.7 & 2.8? Last time I checked it was version 6.1.

@Mat'sMug Yeah. Right?

context on the quote optionexplicitvba.com/2014/12/05/…

Mat's Mug

seen this?

1

A: 2014: What a ride!

FizzBuzz Attack! When Jamal posted his first Java question, a fizzbuzz, something happened in The 2nd Monitor, and then July saw a fizzbuzz implementation in just about every programming language out there... The invasion continued through August, and in September a Mug went on to smoke the ma...

RubberDuck

05:28

I've not been keeping up with it, but yeah.

That was fun. I almost have a damn fizzubuzz badge from that weekend.

(and the lolcode)

@Phrancis are you around? I need a link to card shifter.

Phrancis

Of course

Github or chatroom @RubberDuck ?

RubberDuck

Both please

Phrancis

There is such a thing as a FizzBuzz badge? shakes head

Mat's Mug

And in 2015 we're all hoping to see an implementation written in Shakespeare (not really) — Mat's Mug 2 mins ago

RubberDuck

LOL. No. Not really.

Phrancis

05:36

Chatroom

in TCG Creation, 8 hours ago, by Phrancis

Site - Snapshots - Q&A - Github - Network - Waffle

^^ everything else

RubberDuck

Thank you very much @Phrancis.

1

A: 2014: What a ride!

Not just one, but two open source projects were born in the 2nd Monitor. We don't just review code around here. We write it too. A lot of the regulars in the 2nd Monitor began working on a card game called CardShifter around July. A dedicated chat room was created. Over in the VBA chatroom, w...

Feel free to edit in details guys.

Mat's Mug

SPAM!!

lol

RubberDuck

Evangelism, not spam.

hmmmmmmmmm VBA & Com @ticker?

Are you getting the active x error on the dev machine, or the target? — RubberDuck 18 secs ago

Phrancis

@RubberDuck Dude, that's awesome, thanks so much for the mention!!

in TCG Creation, 1 min ago, by Phrancis

@SimonAndréForsberg @bazola @skiwi @Marc-Andre Check this out!

Gods I love this place.

3

RubberDuck

Me too @Phrancis. Me too.

Mat's Mug

05:55

0

A: VBA Excel + COM on Windows 7 x64: ActiveX Component Can't Create Object

You need to tell COM how you're going to expose your class - write an interface for it. I'm not very volubile in VB, but I'm sure you could translate this C#: [ComVisible(true)] public interface IGiveMeSomeMeaningfulName { int Multiply(int value); } Then expose your class and use a ComDef...

Phrancis

06:23

Someone explain to me why "mi yodeya" (judaism.SE) graduated before CR... We have 7,000 questions more... No grudge but just curious...

vba4all

09:42

8

Q: Microsoft Excel ActiveX Controls Disabled?

I have some Excel worksheets that use ActiveX checkboxes to control certain activity. They worked recently but today started to give errors. I was alerted to this by a colleague, but it was still working on my computer. I checked his version of Excel against mine and his was newer. I noticed ...

excel vba activex

^ COMPLETELY FUCKED UP ALL SHIT AT WHERE I WORK!!! makes me super happy as people start realizing Excel is not an universal tool for everything... really hope M$ will not release a fix anytime soon!!

literally:

> LADY - Unfortunately the calendar boxes seem to be disabled in both forms. Please could you have a look at this.
ME - Ok. I know what has happened - "description".
- We have to wait for Microsoft to release another patch that fixes those issues….til then the CR file is unusable.
LADY - OK, any idea how long that may be?
ME - Nope.

is MS aware?

don't know. they probably already are aware

hahah

ohhh

> "they are waiting for the FBI to track down."

RubberDuck

13:18

OMG.. wow.

Unreal.

Way to really botch an update Microsoft..... It's the same one that borked Visual Studio 2012 I think.

news.softpedia.com/news/…

Mat's Mug

15:00

> Still on VS2012? Eh, let's push an update to break it, they'll move on to VS2013.

RubberDuck

LOL. Yeah. That actually wouldn't surprise me.

mwolfe02

15:23

I've been playing around more with Rubberduck the past couple days (my own work has suffered accordingly, thanks guys)

2

Mat's Mug

good to hear! any new bugs for us?

mwolfe02

I ran into some breaking changes in some of the recent revisions for pre-Office 2010

One is the interop for Access

version 9.0

Not sure if you're aware of the project, but you should look into NetOffice

netoffice.codeplex.com

They provide version-independent interop assemblies for office applications

It's a .NET library with an MIT license

The intellisense is especially nice in the dev environment...

...it lists which Office versions support each method, property, etc.

Also works for COM Addins

Mat's Mug

interesting

mwolfe02

from the FAQ:
Do COMAddins also work version independent?

Yes, of course. This is a particular strength of NetOffice. See the code examples for example project for all Office applications but you have to create 2 Addins for 32/64 Bit support.

RubberDuck

Very interesting. Thanks @mwolfe02.

mwolfe02

15:34

No problem.

Your project is dovetailing nicely with something I'm currently working on.

Mat's Mug

what's your project?

mwolfe02

I'm trying to automate the generation of help documentation from an MS Access application.

Mat's Mug

hmm

RubberDuck

Interesting.

mwolfe02

Some simple stuff, like extracting the tooltips/statusbar fields from controls

RubberDuck

15:39

Like, table/query docs? I did something like that once.

mwolfe02

and outputting them in a wiki format

RubberDuck

Ohhhhhh. Interesting.

mwolfe02

No, more aimed at end-users

Also, allow for wiki-style help to go in the comments

and get extracted as part of the help generation

I'm thinking comments that start '? would get treated as wiki text

Maybe '/? '?/ for block help documentation

Mat's Mug

well I just found a bug with the "Function returns an implicit variant" inspection... false positives when one of the parameters is an array

@mwolfe02 that makes sense

mwolfe02

I've done some documentation in the past but it inevitably gets out of sync with the code over time

but if the user-documentation was right there staring me in the face...

...maybe I'd actually keep it updated. Maybe.

2

RubberDuck

15:43

Maybe =;)-

Mat's Mug

Rubberduck is more dev-oriented - once we get the import/export module feature up, we'll be able to maintain procedure attributes, so you can enter a little description for your methods, that shows up in the object explorer

mwolfe02

That's fine.

Are you planning on exposing access to the collection of procedures, etc.?

RubberDuck

Mug. We should see if we can't sync the attributes with a visible comment.

Mat's Mug

what do you mean?

@RubberDuck I was thinking exactly of that!

mwolfe02

So that I could do something like this...

Mat's Mug

15:48

'@description This method does XYZ

RubberDuck

read my mind

mwolfe02

For Each Proc In ThisProject.Procedures
     For Each Comment In Proc.Comments
          If Comment.Content Like "'?*" Then ...

RubberDuck

Oh! Like, expose rubberduck's parser to vba?

mwolfe02

Except that "?" is itself a wildcard, so I'd have to escape it, but you get the idea

RubberDuck

We talked about exposing some things.

mwolfe02

15:49

Yes, exactly.

Mat's Mug

@mwolfe02 the syntax tree will eventually be somewhat exposed through COM, for "reflection", yes

mwolfe02

Awesome.

RubberDuck

But in the meantime, I did some work on a pure vba wrapper to the code module and procedure objects.

mwolfe02

Oh?

RubberDuck

Yeah. VBEX github.com/ckuhn203/VBEX

Actually, technically I created a procedure object.

and wrapped up code module

mwolfe02

15:51

I do remember looking at that the other day.

I'm going to go take a closer look now.

If I have any other thoughts in the future I'll stop back in.

Oh, one last thing...

will the Unit Testing (and other dll-based requirements) be available via late-binding?

RubberDuck

Good question. Why not submit it as one? I don't think we know right this second.

mwolfe02

Ok. Will do. Thanks, guys. Keep up the great work!

RubberDuck

No. Thank you!

You've been bringing up lots of good stuff.

Actually, @Mat'sMug. Latebinding the assert class might just fix the assembly version issue.

Mat's Mug

@RubberDuck good idea!

@mwolfe02 I second the duck here, thanks @mwolfe02!

RubberDuck

Oops. What have I done. =;)-

2

6

Q: Can I use CR as an environment to foster an Open Source project?

Let's suppose that I have a personal project which I think might interest more people, and given my low position in the learning curve, want to make it open source. Besides posting pieces of code in CR for support and some discussion, am I allowed or expected to get extra-benefits from it, in th...

discussion scope

Mat's Mug

16:06

lol

Mat's Mug

16:17

github.com/retailcoder/Rubberduck/issues/…

@RubberDuck how about a separate COM-visible assembly? We don't actually need to redeploy it with every release...

RubberduckReflection.dll

RubberDuck

Yeah. Something like that.

Mat's Mug

namespaces-ftw

RubberDuck

I don't know enough about how to do that at the moment though. I'll have to research it.

Mat's Mug

we'll just need to extract the interface+implementation into a new project

the C# code doesn't actually use the AssertClass anyway

RubberDuck

Seems simple enough.

3

Q: How to compile one project into multiple dlls in .NET

I am trying to compile one .Net c# project into multiple dlls. But here is the thing. This is my project structure. Bin Dir1 Dir2 File1.cs File2.cs myproject.csproj I want to compile this project into File1.dll and File2.dll. Both File1.dll and File2.dll use code in various .cs files tha...

c# .net visual-studio-2010 csc

Mat's Mug

16:31

I'll do that with ReSharper... doing it manually will involve namespace changes, and manually doing that is a total PITA

If you don't mind

RubberDuck

I don't mind at all. Go for it.

Mat's Mug

I think it can remain in the installer.... VBA might not tell the difference if it's referencing the same version of the same file with the same progid

Otherwise the setup project just got a bit more ~~complicated~~ fun :)

Phrancis

Open Source Projects on CR

@RubberDuck @Mat'sMug For the user who asked that question, in case you are interested

mwolfe02

16:50

@RubberDuck and @Mat'sMug: I was reading up on another project I used, Excel-DNA, and I found some better information on the NetOffice version-independent interop assemblies: exceldna.codeplex.com/…

It turns out that their main benefit is in providing version information for which properties, methods, etc. are supported in which versions.

RubberDuck

Kind of moot for us. The VBE hasn't changed in 15 years... lol

mwolfe02

The downside is that you then have to distribute NetOffice with your app.

Ha, true.

But the other office apps have changed

I know you are referencing the Access interop assembly, but I don't know what you are specifically using it for at this point.

The reason I was looking into ExcelDNA is because I used that in a project I wrote.

I was able to call my final project using late-binding via CreateObject()

RubberDuck

@mwolfe02 It's for the Unit Testing.

mwolfe02

I don't seem to be able to reference my project via early-binding, but I never really tried to set it up.

RubberDuck

The unit testing code has to call Application.Run where application is the host application. So, each supported application requires a reference.

mwolfe02

16:58

I was hoping I would come across what I did to enable both early- and late-binding of my dll, but apparently I did not actually have that set up.

That makes sense with the unit testing.

RubberDuck

Yeah... I wish Application shared a common interface... that was a pain in the neck.

mwolfe02

And if you are only using Application.Run that should be supported all the way back for each office app.

RubberDuck

Yeah... that's... an interesting situation. Some of them take Project.Module.Method calls. Some take Module.Method, and still others take Project.Method....

mwolfe02

Well, each one is a different application.

Maybe if they were all part of the same company there would be more consistency

RubberDuck

Yeah..... I know. =(

hahahahaha... Microsoft? Consistent?

The only thing Microsoft does consistently is bork updates.

mwolfe02

17:02

and ruin Hungarian notation

I think Joel Spolsky is still bitter about that

joelonsoftware.com/articles/Wrong.html

2

RubberDuck

I reference that all the time on CR.

RubberDuck

18:41

Stop it @ducky. Get in get out. Do not refactor this entire project so you can unit test it.

Mat's Mug

lol

RubberDuck

Sad thing is I should, but I don't have time to.

Simon André Forsberg

20:41

@Mat'sMug I want to see @Duga! :)

Mat's Mug

@SimonAndréForsberg ok, ok.. hold on...

Duga

Ping: Practicality beats purity.

3

Simon André Forsberg

That's the spirit ^^

Mat's Mug

this means it worked?

go ahead and star Rubberduck, I want to see a notification!

github.com/retailcoder/Rubberduck

Duga

[retailcoder/Rubberduck] Phrancis opened issue #115: Testing Duga

> Just a test for @retailcoder !

Mat's Mug

20:56

#yay!

RubberDuck

I'm out of stars!

That's cool!

Duga

[retailcoder/Rubberduck] Phrancis closed issue #115: Testing Duga

[retailcoder/Rubberduck] Phrancis commented on issue #115: Testing Duga

Mat's Mug

no, that's awesome!

2

Duga

> Works great!

4

Mat's Mug

@SimonAndréForsberg you... gave it a soul?

cheezsteak

20:58

@Duga What is this?

I am scared now

Phrancis

A feed from Github

Duga

[retailcoder/Rubberduck] Zomis starred us

Simon André Forsberg

@Mat'sMug No I didn't :)

RubberDuck

Yet

Mat's Mug

@SimonAndréForsberg ah, @skiwi did!

Simon André Forsberg

21:03

@cheezsteak it's a bot that we're using a lot in another room, I just added a feature to it so that it can be in multiple rooms as well. It will inform you whenever something happens on the github repo.

@Mat'sMug If anyone did, it must have been skiwi!

Ah, yes. I see it now. Soul.java, there it is...

2

Mat's Mug

ha! I knew it!

RubberDuck

hahahah nice.

Ah man. Now everyone will know when I'm slacking and not working on Rubberduck..

2

Simon André Forsberg

21:25

@RubberDuck @Duga doesn't notify when stuff does not happen. It only notifies about what actually happens.

RubberDuck

@SimonAndréForsberg That's my point!

The lack of notification is a notification.

Simon André Forsberg

Besides, you can always say that you're working on a commit, it's only the actual commit times that are available. And those are only revealed when you push :)

RubberDuck

hahahaha okay then.

Simon André Forsberg

@RubberDuck Well, @Duga hasn't said anything in the TCG room about the actual Cardshifter project for quite a while now, and there has indeed not been much progress there lately. However, @Phrancis and @Marc-Andre are have been working on the website so @Duga informs about that these days.

RubberDuck

I hate it when life gets in the way of code.

Mat's Mug

22:09

0

Q: How can I sort a VBA MatchCollection by value of SubMatches(n)?

I am relatively new to programming, and I wrote a Microsoft Word VBA macro that extracts a "parts list" from a patent description (the text of the active document), where each part reference in the list is identified in a rudimentary way as anything that looks like a numeric or all-caps alpha ide...

regex vba sorting ms-word

^^ roll your own collection and sorting algorithm, buddy!

RubberDuck

lol

Mat's Mug

22:31

Better:

Wait a minute... are you confusing l with 1? Did you type this using a typewriter? Anyhow, I suggest you type Option Explicit at the top of your code. — Jean-François Corbett yesterday

Phrancis

This is sacrilege:

in The Frying Pan, 1 min ago, by Preston Fitzgerald

Oh I don't really care to reduce heat. I just think beer is gross.

in The Frying Pan, 1 min ago, by Preston Fitzgerald

But hey. More moldy bread water for you, eh?

RubberDuck

23:00

@Mat'sMug I've been burnt by that before! (Not my code, a colleagues's). It took me almost an entire afternoon before I declared Option Explicit and found it in a heartbeat.

Good grief! Why isn't Option Explicit the default?!

Mat's Mug

23:28

@RubberDuck so that people like us can implement code inspections and quickfixes that see it missing and add it!

RubberDuck

Ha! True that!

I know it's Python and all, but this poor soul is confused about how to copy excel data.

0

Q: Write to a new, modified Excel file from an original one

I noticed that I've a HUGE bottleneck in this following function of mine. I can't see how to make it faster. These are the profiling test results(keep in mind that I'm using a PyQt GUI so times can be stretched): cProfile Results def write_workbook_to_file(self, dstfilename): self.populate...

python python-2.7 excel

Open Source Projects on CR

Transcript for