VBA Rubberducking

Mat's Mug

02:55

0

A: Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables

Private cn As ADODB.Connection ' global due to being passed around Well that is one confusing comment. The visibility of cn is Private, its scope is therefore restricted to Module1. Was it globally scoped (with a Public, or the deprecated Global access modifier) in a previous version? I like ...

Mat's Mug

03:39

@RubberDuck fixed issue #42: Instruction now correctly accounts for indentation (until proven otherwise)

RubberDuck

10:45

Nice @Mat'sMug! I'm seriously hoping I can sit down and work for a bit this evening. It seems like every time I try the last few days something comes up. It's starting to irritate me a bit.

Mat's Mug

12:37

I think I'm getting to the point where I need to parse non-regular Expression syntaxes and create Assignment and Comparison nodes.

RubberDuck

Good grief I need to find some time to look at what you've been up to.

It feels like ages since I've looked at the repo.

It's probably only been a few days... lol

RubberDuck

14:51

Someone on SO is pushing-it.

9

Q: Using CreateDialog in VBA in an Attempt to Create Modeless Dialog Boxes

I want to create a modeless popup dialog in VBA 7.0. So far the most promising route seems to be CreateDialog. First I tried CreateDialogW and received Entry point not found for CreateDialogW in DLL. After opening the DLL, I verified this function was not listed. The MSDN reference linked abov...

enderland

@RubberDuck hmmmmmmmmmmmmmmmmmm I want to play with that, too bad I'm so busy as of late...

RubberDuck

I know, right?

500 REP bounty on it too.

Malachi

15:19

@Rubberduck How do I run the code?

RubberDuck

Put it in a module and call TstDialog() @Malachi.

Malachi

call it from one of the sheets?

trying to run it in Excel

I think I figured it out

RubberDuck

You could just run it from the immediate window, or just "play" it by putting the cursor inside of it and pressing the play button.

Or, Tools>>Macros>>TstDialog>>Run

Malachi

it goes straight to GetHiLoWord but there are no words........

hold on.....

Malachi

15:56

I give up, time to do my work.....

cheezsteak

What is the name of the MSForms reference?

RubberDuck

@cheezsteak Windows Forms 2.0 Object Library

cheezsteak

Crap I don't have it.

That seems like something that should exist.

RubberDuck

@cheezsteak Excel or Access?

cheezsteak

Excel

RubberDuck

16:02

Weird

cheezsteak

I don't have Access..

You sure its Windows Forms 2.0 Object Library

?

RubberDuck

Microsoft Forms 2.0 Object Library

Sorry.

Mat's Mug

That library doesn't play nice with VBA.. I read somewhere that it's asking for code to crash...

cheezsteak

Niether exist.

RubberDuck

What are you tlaking about Mug? That's the userform reference.

Mat's Mug

16:05

IIRC it's part of VB6

Might be wrong though

vba4all

16:17

Windows Forms 2.0 Object Library is added to the references as soon as you add a UserForm into the VBA project

is THIS new?? haven't seen that before

Mat's Mug

oh

vba4all

hey guys

this is briliant

got contacted by some work agency

Excellent Opportunity for a Full Stack Developer Salary £30,000+ Junior Full Stack Developer £60,000+ Senior Full Stack Developer Both including excellent benefits Based in Oxford

Full Stack Developer, Object Orientated, PHP, .Net, Java, Python, NodeJS, SQL, Open Source

Are you looking to work in a fast paced environment where you will be working with a large number of cutting edge technologies? Do you like working on code at all levels including UI, middle tier, backend and databases? Do you like producing high quality code and take coding practices seriously? If this sounds like you the…

my reply

Thanks a lot for the info but I am not a PHP developer and don't want to be one, therefore I believe the position is not for me.

I am currently working full time and I am not really seeking any new opportunities so please feel free to exclude me from your contacts.

her:

This is not a PHP below it says PHP or .Net. There is both a PHP and .Net Full stack role available.

me:

Full Stack Developer Requirements:
* Experience with PHP, .Net, Java, Python and NodeJS

It's a full-stack position. You should understand it will involve many technologies as it's not directly aimed at .NET only. I am only interested in .NET only positions. I wouldn't want to become a full-stack due to my personal preference.

enderland

@vba4all ".... unless you offer me £1500,000"

vba4all

her:

I do understand the role.

They are looking for several developers at the moment, a full stack PHP Developer, a full stack Microsoft developer and a full stack Java Developer.

enderland

lol HR :P

vba4all

16:24

now, tell me what is a a full stack Microsoft developer

hahha made my day

yeah the HR people... I do understand the role.

yeah I fkn bet you do... :P

like my IT teacher from elementary school back in the day did - pronouncing MICROSOFT like this: MEECROSOFT

hilarious haha

even that

Full Stack Developer, Object Orientated, PHP, .Net, Java, Python, NodeJS, SQL, Open Source

what does Full Stack Developer (I assume a position) have to do with (Object Oriented - design pattern) , PHP ( programming language), .NET (framework), .... Open Source ( open source WHAT?)

hahhaa

RubberDuck

@vba4all HAHAHAHAHA... I've got a full stack over here......

vba4all

and she's seen my CV somewhere on the internet where I explicitly state that I hate PHP and I will never undertake any PHP related work

Mat's Mug

orientated

2

vba4all

yeah

hey guys I am hiring for a Overflown Stack Of Object Orientated Open Sourced Microsoft and JAVA developers.... anyone?

RubberDuck

JAVA? Sure, so long as it's not Java though. I only know JAVA.

Context.....

18

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

Meme: Java vs. JAVA Originator: Jamal(?) (to be determined) Cultural Height: The 2nd Monitor (as usual), but also in various comments scattered across meta. Background: Question with the word JAVA (instead of Java) in the title or body, is often an indication that the poster is a beginner and/...

cheezsteak

16:31

Object Oriental Desgin

2

There must be a blog post or some list of HR people not understanding Developement jargon.

That will be my breaktime.

vba4all

@cheezsteak javaworld.com/article/2072980/…

"Just as you and I have seen job ads calling for five years of experience in a technology that hasn't existed for five years, "

lol

or better

google.co.uk/…

titles are enough :P

"Human Resources suck cock in hell"

hahahha

gosh i need a proper job

got too much time on hands now and it's starting to feel like a one-man army, which is not enough to build really nice apps

cheezsteak

16:55

I was looking for a list of HR people requesting ridiculous things. Open Source Microsoft Development or 5+ years in HTML5 Development

vba4all

ah alright

cheezsteak

but interesting read

vba4all

Me: (final reply)

No problem.

Btw. It’s Object Oriented – not Orientated..

Cheers & Regards,
Michal

Mat's Mug

oooh

in-your-teeth

cheezsteak

> Wanted C/C++ Programmer with Linux Experience
> Opportunity to work in a large enterprise environment with significant career growth.
>
> Required skills include 10 years experience with ANSI/C, C++, TASM or related Assembly experience, Linux/UNIX kernel development, OOP experience, and at least 7 years writing device drivers.
> Salary $29,000 - $35,000 /yr.

Mat's Mug

17:10

no shit

I wonder if anyone does apply on such postings...

enderland

29k? lolz

hopefully that's not USD

in the United States

RubberDuck

@cheezsteak I make a lot more than that with just 3 years related experience....

They'll never find what they're looking for.

@Mat'sMug Just the desperate.

Mat's Mug

I was doing > $50K at my last job, working with a shitty VB6 code base

$30K for any programming position requiring any experience, is theft.

3

RubberDuck

> I've had Dean's List students from major engineering schools who couldn't solve a simple problem — and couldn't be coached into figuring out what to do. I have a young man working for me now who majored in Music Composition who rolled his eyes at the problem and said, "You probably don't want to do it that way—the new LINQ to XML tools make it easier...."

javaworld.com/article/2072669/…

> Nor does it mean that someone who worked her way through tech school, or who is self-taught from many fine programming books, lacks an understanding of Computer Science basics. Instead of asking for a degree, the interview process at these companies should include the question, "What's the last programming book you read? When? What did you learn from it?"

Mat's Mug

^^^ that

enderland

17:20

No one knows how to interview for programming positions, period

2

RubberDuck

Only other programmers do @enderland.

enderland

@RubberDuck that gets hard too but much better than HR that's for sure

Mat's Mug

yeah, and when my boss brought me in to hire this new guy, my first (and only) question was "is String a value type or a reference type?" - the guy said "uh, ..., well, ...value type...?" - and my former boss still hired him.

(he wanted to hire a .net guy)

enderland

In which language? :P

Ah

Mat's Mug

so they still have everything in VB6

enderland

17:24

But also keep in mind, does trivia like that really help? Sometimes trivia is stuff you don't know even if you are a good dev

Mat's Mug

IDK, but it shows you know the basics of the framework you're pretending to know... no?

enderland

I guess I'd say it depends, do you care more that the person knows that or if they can architect/design good code?

RubberDuck

I'm a fan of Fizz Buzz like problems.

enderland

you can teach trivia, you can't teach design/intelligence

RubberDuck

That ^

Mat's Mug

17:26

that place had no concept of what good code even remotely looks like

RubberDuck

Yeah, I'm not even talking about good code. Just the simple ability to code.

Mat's Mug

well he sure was better than his predecessor

RubberDuck

I recently reviewed code samples from 10 prospects. Only 3 correctly completed the task. Only 1 in an even reasonably smart manner.

And they still all stunk to high heaven.

Mat's Mug

well, who am I to talk.. seen the crap I've written on GitHub?

RubberDuck

LOL

Better than my crap on github......

Mat's Mug

17:29

meh, it begs for a major refactoring

well the Parser crap at least

seen it yet?

RubberDuck

Yeah. I've got bugs & issues to fix tonight.

Nah man. Not yet.

enderland

You guys are making me want to connect to this project

Mat's Mug

github.com/retailcoder/Rubberduck

^^ go right ahead :)

enderland

I have visual studio on my work computer too

So I can't even use that excuse

RubberDuck

lol..... fork away my man.

Mat's Mug

17:31

^^ fork! fork! fork!

RubberDuck

Can't use that excuse at home anymore either.

enderland

Though no git

RubberDuck

visualstudio.com/products/visual-studio-community-vs

Community kicks the shit out of the Express Editions.

enderland

Of its free. I have a Mac though

With... Windows installed

Mat's Mug

@enderland sounds like you're looking for reasons not to ;)

enderland

17:33

You guys are monsters :)

Mat's Mug

mwahahaha

RubberDuck

bahahhahaha

Nah @enderland, we just have years of work to do.

Mat's Mug

lol

enderland

Lunchtime though brb

RubberDuck

About 10...... joelonsoftware.com/articles/fog0000000017.html

Mat's Mug

17:34

me too

RubberDuck

Yeah. I need to get back to work. Cy'all in a bit.

vba4all

17:56

:18666871  thats a good one

    Enumerable.Range(1, 100).Select(
                        n =>
                        (n % 15 == 0) ? "FizzBuzz" :
                        (n % 3 == 0) ? "Fizz" :
                        (n % 5 == 0) ? "Buzz" :
                        n.ToString())
                        .ToList()
                        .ForEach(Console.WriteLine);

but that is technically a better one

foreach (var val in Enumerable.Range(1, 100).Select(n => FizzBuzz(Console.WriteLine(val);

        static private string FizzBuzz(int value)
        {
            if (value % 15 == 0)
            {
                return "FizzBuzz";
            }
            if (value % 3 == 0)
            {
                return "Fizz";
            }
            if (value % 5 == 0)
            {
                return "Buzz";
            }
            return value.ToString();
        }

Mat's Mug

some brackets are missing

I like that the first one uses a method group to output

RubberDuck

@vba4all What kind of linq black magic is that??

the first one makes sense. Can't wrap my head around passing Console.WriteLine into FizzBuzz....

Mat's Mug

2nd one won't compile

cheezsteak

You are missing some parens

cheezsteak

18:18

foreach (var val in Enumerable.Range(1, 100).Select(n => FizzBuzz(n)) Console.WriteLine(val);

perhaps? I haven't used linq but it seems similar to Scala

Mat's Mug

val is out of scope

foreach (var val in Enumerable.Range(1, 100).Select(FizzBuzz).ForEach(Console.WriteLine);

cheezsteak

(1 to 100) map {(n) =>
  if (n % 15 == 0) "FizzBuzz"
  else if (n % 3 == 0) "Fizz"
  else if (n % 5 == 0) "Buzz"
  else n} foreach println

RubberDuck

@cheezsteak That reminds me of Ruby in a way.

Is that my issue with Linq? Do I need to just think of it more like a Ruby pipe?

Hmmmmmm

cheezsteak

That was scala

It's object oriented functional programming.

RubberDuck

Yeah, but it reminds me of Ruby.

Mat's Mug

18:31

@cheezsteak I thought Scala was like Java on steroids

RubberDuck

1..100.DoEach{|x| puts x}

cheezsteak

@Mat'sMug Scala or Linq?

enderland

a better fizzbizz is making change and getting the best coin combination

2

cheezsteak

combonitronix

no

combinatorics

christ I have been making a fool of myself today

@Mat'sMug Scala is a functional programming language built ontop of Java. So its a functional programming language that has access to Java's HUGE data structure library.

Mat's Mug

wait, so Scala != scala??

cheezsteak

18:36

SCALA

Mat's Mug

> Scala is a functional programming language built ontop of scala

RubberDuck

ScAla =)

Mat's Mug

Scale A

RubberDuck

Scale, eh?

enderland

@cheezsteak you can do it via a fizzbizz like approach, though, as well as more complicated things

things not to search:

> access get dirty information

Mat's Mug

18:44

lol

RubberDuck

I think you're looking for Me.Dirty @enderland LOL

enderland

@RubberDuck no I want to know what is dirty. ;-)

RubberDuck

Okay. I'm not sure anymore. Are we being serious now? Do you want to know which records are dirty?

Mat's Mug

apparently everything that search string gives you ;)

try "modified" instead of "dirty'

enderland

@RubberDuck Something is making my form "edited" on load and I want to see what it is that causes it. I'm not sure what it is, though

I know the visualized record is dirty, but not sure why/what

RubberDuck

18:50

Hmmmm

Mat's Mug

implement INotifyPropertyChanged ...oh, nevermind.

RubberDuck

Add a watch?

enderland

Form_Dirty is never called. wtf hax

RubberDuck

And have it break when Me.Dirty changes?

enderland

that's not a bad idea

Thanks. lol.

Mat's Mug

18:53

watches are so underused

RubberDuck

@enderland You're welcome. Hope it helps.

enderland

I know. I'm so dumb for not using them but I don't use them enough to be comfortable using them (it solved this in about 1 second btw)

Mat's Mug

I don't use them often either

@RubberDuck can we add watches from the VBE object model?

enderland

Had a custom onCurrent method which specifically didn't do stuff for a variety of explicit form names... which I forgot to add the new one I have to (so it was firing and changing data)

RubberDuck

Hmmmm I don't know.

Mat's Mug

18:55

perhaps we could have Rubberduck add "auto-watches" for all identifiers in scope

although "in scope" is actually impossible to get

(or is it?)

enderland

@RubberDuck thank you. It's amazing how often you can use something and be aware of the tools involved and still not use them correctly

Mat's Mug

I wonder if the current line works when debugging

haven't tested enough of it

RubberDuck

Maybe if we can hijack the "Watches" window.

Mat's Mug

Nov 10 at 21:08, by Mat's Mug

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

2

meh, looks like VBE object model only helps with the code panes. i.e. yeah, we'd have to hijack the "watches" window..

put that in the "nice to have" column

RubberDuck

Only if we can execute a built in commandbarbutton action, and then hijack the dialog that pops up.

I'd say it looks unlikely.

RubberDuck

19:15

0

A: Should we sign up for Winter Bash 2014?

YES. I want a hat! [Obligatory text to reach the minimum answer length.]

cheezsteak

19:36

Is stackexchange now Team Fortress 2?

RubberDuck

19:56

@cheezsteak huh?

googles Team Fortress 2

cheezsteak

Team Hat Fortress

And they make an fton of money off of it.

RubberDuck

LOL

cheezsteak

20:11

So are these winter hats just achievements or can we actually do something with them?

RubberDuck

Just achievements.

Kinda like badges, but not.

cheezsteak

Bragging rights

RubberDuck

Yeah. That ^

Mat's Mug

Well you can actually wear them. Well, your avatar can.

enderland

My avatar works great for hats. Just sayin'

cheezsteak

20:25

@Mat'sMug Now it's interesting.

enderland

21:21

There's not an easy way to do code profiling in VBA to figure out what methods take longest to execute is there?

short of adding stuff to each method, that is

cheezsteak

Sounds like something for RetailCoder

Mat's Mug

@RubberDuck todo: add a performance profiler feature to [Rubberduck] :)

3

RubberDuck

@Mat'sMug No idea how to go about that, but YES.

Mat's Mug

@enderland the unit tests use a System.Diagnostics.Stopwatch to calculate how long a test runs for. You could make that function call inside a Rubberduck unit test, and the TestExplorer would show how many milliseconds it takes to run :)

@RubberDuck TBH, no idea either

it works for unit tests because they're public procedures in standard code modules...

the add-in is using Application.Run to launch the tests

enderland

I would LOVE knowing what methods take what itme without adding timestamping to everything

hmmmm

can you get a method name from a subprocess?

Mat's Mug

21:36

define "subprocess"?

enderland

I'm thinking something like:

create a class which basically records a "time" based on it's create/destruction

RubberDuck

Excel/SQL Server question.

Mat's Mug

@enderland we can hook into the VBE, not so much on the runtime/debugger

RubberDuck

If I link an excel doc to a sql server table, do changes get pushed to the server?

enderland

in the terminate method pass the time of execution to a global "profiler" class

@RubberDuck depends how you link it

you can link either way I believe

Mat's Mug

21:38

@RubberDuck I'd never do that

RubberDuck

@Mat'sMug Oh, you can hook the runtime, but it's really close to the metal and not something I want to mess with.

Mat's Mug

wait, we can do that??

RubberDuck

@Mat'sMug I don't want it to make changes is the problem.

Yeah. We can.

I dont' want to learn.

Mat's Mug

got docs?

lol

hardcore WIN32API?

RubberDuck

everythingaccess.com/vbwatchdog.asp

@Mat'sMug No, but there's this source code that I don't grok.

Oh. Duh! I can just restrict write access to the table itself.

enderland

21:42

@RubberDuck just make the query read only

RubberDuck

@enderland Huh? How?

enderland

One of the settings makes this clear I think when you link an Excel/ODBC table

Mat's Mug

I don't understand what you're trying to do

[Data] > [From SQL Server...] > [select view] > done.

enderland

iirc if you link atable one of the options basically is a "allow updates" option

Mat's Mug

@RubberDuck did you download it?

RubberDuck

21:43

@Mat'sMug Right, but if someone (presumably me, accidentally) changes the data in the sheet, does it get pushed back to the server?

Mat's Mug

(I'm still at work)

RubberDuck

Because, that's a no no.

Mat's Mug

@RubberDuck nope

RubberDuck

Yeah. I looked at it once. Couldn't grok it.

Okay, awesome. Thanks @Mat'sMug.

enderland

you can link it so it updates, though, FYI

Mat's Mug

21:45

bookmarked, will get back to it later.

enderland

what was the VBA construtor code you guys were playing with?

I think I read that here, have a great use case for it

Mat's Mug

@enderland a set-only property actually. I think it's a smell.

enderland

@Mat'sMug Ah. I wanted to do something like:

> Dim mProfiler as new Profiler("MethodName")

Mat's Mug

5

Q: Parameterised Constructors

Finally, Parameterised Constructors are possible in VBA. Background: One rainy day, I came up with an idea: take a static class and expose one property as default (like it was done in the Reverse for each loop for the .Item() property. This time however, I thought I'll make a PieceOfMe() Get Pr...

oop classes vba excel constructor

sorry memory failed me. not a set-only property. a property getter (marked as default property) with side effects. Still a smell IMO

enderland

Yeah there's no chance I"d remember what I did if I ever had to look at that again...

Mat's Mug

21:51

I think the safest way to mimick a constructor in VBA/VB6 is to use factory methods

enderland

I just got a creative idea for basic code profiling

Mat's Mug

spill the beans :)

what is it?

enderland

I'm working up an example. I'm not sure how useful this will be but I'm hopeful

Mat's Mug

@ticker downvoted. do your Google dude.

RubberDuck

Does the SO Low Quality Queue ever empty???

Mat's Mug

22:03

Yeah. When Hell freezes over.

enderland

This is neat

Mat's Mug

22:19

oh look at that, a vba post!

enderland

I think this is actually a quite useful approach, assuming nothing is totally broken

Mat's Mug

I'll take a look later, promise :)

enderland

Cool. It's rough, but intentionally so as the implications of doing this mean modifying every method I want profiled

ahhh I wish I had been smart enough to think about this a few months ago!

Mat's Mug

that's what CR does to everybody ^^

enderland

Seriously. Simple solution to a super obnoxious and complicated problem? waaaaaaaaaaaaaat

assuming I am not missing something really obvious

Mat's Mug

22:25

> Use GUID's for row identifiers for all rows for projects with offline/synchronization requirements.

ah, so that's what they're good for!

Stack Exchange

2

Q: VBA Code Profiling

I have wanted a way to do code profiling in VBA for quite some time. It becomes very complicated to figure out what methods are actually being executed for how long and how often in complex Access applications. A key reason this is complicated is that many form events or function calculations ha...

optimization vba

cheezsteak

@RubberDuck I posted a reply to this using VBA Extensibility library instead of the route OP was going.

0

A: Using CreateDialog in VBA in an Attempt to Create Modeless Dialog Boxes

I don't want to detract from the in depth and well researched but there are possible work-arounds to dynamically creating modeless dialog boxes in VBA. That was the original problem before the asker bravely dived down the rabbit hole with CreateDialog. So this answer is for the original problem...

Mat's Mug

22:40

in The 2nd Monitor, 12 secs ago, by Mat's Mug

TTGH - later!

RubberDuck

23:21

Props for that @cheezsteak. Looks like someone kinda got it working, but not really.

Oh! Hi @StackExchange!

Transcript for