VBA Rubberducking

Mat's Mug

00:05

The last part, (empl, emplDept) => ..., is the resultSelector.

The function has both sides of the join, and returns an anonymous type off values picked from both

Mat's Mug

00:37

I'll need a similar mechanism for GroupBy (anonymous types), too.

say, employees.GroupBy(e => new { e.Country, e.Department }) - creates a "grouping key" that puts all employee records with the same country & department under the same group.

I've had that problem before. SqlResultRow stores an undefined number of fields, and SqlResult stores the field names.

It's more efficient than storing the field names for every single row

but it's 2 types

RubberDuck

01:43

Another reason why VBA sucks.... stackoverflow.com/q/26394497/3198973

Mat's Mug

It's a ship design product called HECSALV. If there's anyone on SO with experience using it, I'll eat my shoes. — jake 33 mins ago

RubberDuck

That's how I feel when I have a problem with that automation language we use.

Mat's Mug

lol I was going to link the answer here and say "hey look at that, nice catch!" ..then I saw who wrote it ;)

RubberDuck

Lol. Thanks Mug.

Mat's Mug

No. If is a reserved keyword while Line is not. Probably because Line Input predates VBA as we know it. I believe it came from the old Excel Macro language. — ckuhn203 8 mins ago

and that's the reason you were talking about I guess!

wow

yeah, "reserved keywords" is a quite fuzzy notion in VBA

RubberDuck

01:52

That's the thing. It's not a keyword. It's a statement.

Like Option Explicit...

Ttgtb though

Mat's Mug

'night!

vba4all

07:38

morning

RubberDuck

11:46

Monking

ptwales

14:16

Are .xlam's just completely self contained projects? I can't use them like an external Library of classes can I?

RubberDuck

14:53

@ptwales You absolutely can use them as external libraries.

You have to set the class instancing property to Public Not Creatable and supply some kind of New or Create method though.

'Constructor
Public Function Create() As Enumerable
    Set Create = New Enumerable
End Function

You can't New an object from an outside project, so you have to supply a "Constructor".

Either via a default instance of the class, or a ClassFactory *.bas module.

ptwales

15:07

And then reference them like VBEX.List.Create?

it's active in the Add-In manager and I see VBEX.xlam but VBEX.List is not defined...

vba4all

may I ask?

ptwales

the immediate pane can access the members but another project cannot.

ask what?

On SO or SU?

vba4all

I have created an .xlam add-in and added references to ActiveX 6.1, scripting.dictionary, and others

now I have just opened Excel and added a new module in that new project

and I am trying to say Dim d as Dictonary

not recognized...

Ok, so I bring up references and scroll down for Microsoft Scripting Runtim

but it ain't there.

is that a known thing ("bug") ?

ptwales

15:22

idk

vba4all

15:37

ok i am not very keen on asking on SO but here it goes

0

Q: *.xlam add-in and missing references in a new VBA project / new Excel's instance with the add-in turned on

Repro: Start Excel and open VBE ALT+F11. Find Tools and click References. Scroll down til you find Microsoft Scripting Runtime and tick it. Save the file as Addin.xlam in the default location which should be something like: C:\Users\username\AppData\Roaming\Microsoft\AddIns Close this Excel...

i don't like having too many questions on SO cause "people" are fu*** morons sometimes and just downvote your questions for FUN (CAUSE IT's FREE) - they won't downvote an answer cause it costs 1 rep point... pathetic

RubberDuck

@Vba4all I know references are project specific. I'll look at your question in a minute.

@PtWales you have to add a reference to VBEX

You might have to browse to the file location.

ptwales

Add it as a reference like Scripting Runtime? I thought that was only for .dlls. All of the mediocre help I found said to add it as an add-in in manage add ins.

Options >> Add-Ins >> Go... >> Browse >> VBEX.xlam
checkmark OK
Active application Add-ids :> Vbex

that's basically the only instructions I found

also since when am I 1514...?

Weird that worked. Where did you find the documentation for that?

Mat's Mug

16:05

Add the add-in; then when Excel has it loaded, you can reference it from the "add references" dialog

RubberDuck

@ptwales I have no idea where I found the docs on it. :-/

@vba4all Verified and extremely weird.

> Don't scroll down

Mat's Mug

^^ that

ptwales

16:33

Oh Snap I don't think Application.Run can reference a signature outside of the project.

If my project references VBEX and calls VBEX.seq.Filter("IsBlackListed", fileList) But then VBEX doesn't reference my project so Application.Run("IsBlackListed", ...) cannot find "IsBlackListed"

RubberDuck

16:49

Ouch.....

I've been kicking some a$$ on SO today

0

A: Why Use Integer Instead of Long?

Integer variables are stored as 16-bit (2-byte) numbers msdn Long (long integer) variables are stored as signed 32-bit (4-byte) numbers msdn So, the benefit is in reduced memory space. An Integer takes up half the memory that a long does. Now, we are talking about 2 bytes, so it's no...

What about CallByName @ptwales?

ptwales

Isn't that only for objects?

vba4all

really? memory? these days?

ptwales

If I had a floating Module method how would I call that with CallbyName?

vba4all

17:05

what does floating mean in this context?

ptwales

Just a public method in a module of a current project.

vba4all

hey whos the "moderator" here?

i tried to delete my own message and i cant

i can only flag it for a mod's attention...

ptwales

This means that my attempts to extend Mat's Delegates to reference to include existing functions will fail.

Noctrine

Messages can't be deleted in chat after a certain amount of people post or time passes. Unless by mod.

rolfl

ello ello

vba4all

17:08

@Noctrine oh sorry didn't know that

@Noctrine it's just that I've said something that kind of broke the context of what the other guys were talking about and i wanted to remove it... that's all

sorry about the trouble I've caused

rolfl

Does not seem to be much trouble that I can see.

Noctrine

No trouble. Anyway, your friendly neighborhood mod is here - so I'll leave it to him. *away*

RubberDuck

codeguru.com/vb/gen/vb_misc/algorithms/article.php/c7495/…

How VB6 Stores data^ Pointer hacks!

vba4all

Noctrine cheers see ya

@RubberDuck solve that: stackoverflow.com/questions/25328975/array-as-a-class-member

I've tried using CopyMemory but uhm... not a great solution

RubberDuck

17:24

Hmmm... That's a damn fine question. I wonder if blackhawk is still active.

enderland

seen 22 mins ago

vba4all

@RubberDuck yeah I see him occasionally

are any of you MVPs?

RubberDuck

@vba4all Just at my office. =)

vba4all

haha i could say the same thing about "mine"

i've put an application in for an MVP but yeah I guess I am shit and not doing enough for the online community to be awarded...

enderland

I'm not good enough to be an MVP I don't think..

vba4all

17:36

really?

i've seen some MVPs on linkedin giving terrible advices... i mean saying properly bad stuff

enderland

Maybe then, idk

vba4all

also some people say they are MVPs but then they will not appear on the MVP site

I think anyone in this chatroom deserves an MVP

2

things we've been doing to/with VBA are just incredible

it's like if you know OOP and apply it within VBA you can pretty much solve any other VBA/Excel related problem, couldn't you?

ok im off for the night, ttyl

enderland

18:03

@vba4all oh yeah. Being able to do OOP in VBA lets you do really cool things -- and avoid the typical "sub creep" that causes larger VBA projects to be unmanageable

RubberDuck

18:17

@enderland I'll admit that I only recently figured out how cool Interfaces can be.

But I still curse everytime I want to properly inherit a class.

enderland

I've not ever used interfaces :P

But I create lots of classes

I want to inherit, too... meh

RubberDuck

Good grief.... have you guys seen this?

6

Q: Searching for an empty row in a given destination

It simply searches for an empty row in a given destination. If it is empty, it performs certain actions (copying, pasting, etc.). If it is not empty, it goes to another row. I managed to do it with dozens of If formulas, but doing it in such a way seems really ineffective. Moreover, doing it i...

performance vba excel

enderland

apparently, I'd upvoted an answer :P

RubberDuck

@enderland They're handy when you have an actual use case.

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Arrow code in VBA ^

Mat's Mug

18:44

Uh, indented that way? Shoot the author!

ptwales

@Mat'sMug did you see my comment on Delegates?

nvm

RubberDuck

> Impressive code, I would be willing to maintain this even though I have not done VBa in ages

That makes me feel good.

Mat's Mug

:)

@RubberDuck where's that?

RubberDuck

Thanks! I can't take all the credit though. I got some great advice on my original post. — RubberDuck 36 secs ago

ptwales

groups.google.com/forum/#!topic/comp.lang.lisp/VAKbxfoys6o

Mat's Mug

18:58

@RubberDuck ah, good catch in the other answer - you should search for "Const " or "Dim " (with a trailing space)

@ptwales ...tempting... but I don't know LISP :(

ptwales

I played with the idea of it before but I know it's a lot of wasted effort.

RubberDuck

@Mat'sMug I should also make sure it's not commented out. =)

Do you think it's reasonable to move all of that logic to CodeModule and construct the procedures?

More like....

Dim proc as New procedure
proc.Signature = x
proc.Type = y

It's really bugging me, but maybe what I've got isn't all that bad after all.

@ptwales Interesting thread though.

Mat's Mug

19:17

That's a good question

RubberDuck

Well @ducky. That's well and good, but then if the module gets changed, your vbeProcs are out of sync.

hmmmmm

An extension module?

VbeExtension.IsSignature(line)

VbeExtension.IsDeclaration(line)

Mat's Mug

Doesn't look too bad, ..but could turn into a dumping-bag of helper functions

RubberDuck

That's what I'm worried about Mug.

Mat's Mug

19:34

How about a CodeModuleParser class?

RubberDuck

@Mat'sMug I like the way you think.

It's all in the name. It's still essentially a static class, but the name makes it crystal clear what it does.

Mat's Mug

19:53

And it might help refactoring/moving other parts in there too ;)

RubberDuck

It might.

It could potentially loosen the coupling between codeMod and procedure too.

Potentially.

I've gotta find some time to muck with it.

ptwales

20:55

@RubberDuck It gets fairly dark. That read put me in a bad mood. Many people just told him to quit is job.

RubberDuck

@ptwales I guess I didnt' make it down that far.

> Maybe you should consider a change of employer. Your
current one seems to be limiting your productivity in ways you're not
particularly enjoying. Rather than pitting your intelligence against
management's, why not find management that will allow you to use all of
your skills in service of corporate goals? Just food for thought; no
need to reply with your reasons for remaining in hell.

ptwales

21:09

> reasons for remaining in hell.

> in hell.

Also I totally just had the same problem that vba4all had with the references.

Microsoft Internet Controls was at the top of the list.

enderland

?

what problem?

ptwales

5

Q: *.xlam add-in and missing references in a new VBA project / new Excel's instance with the add-in turned on

Repro: Start Excel and open VBE ALT+F11. Find Tools and click References. Scroll down til you find Microsoft Scripting Runtime and tick it. Save the file as Addin.xlam in the default location which should be something like: C:\Users\username\AppData\Roaming\Microsoft\AddIns Close this Excel...

excel vba excel-vba excel-addins vbe

enderland

OH, ha

enderland

21:40

0

A: "File not found" at the execution of any VBA code in Access 2007

I had a problem similar to this. A blank "File Not Found" error. I turned off AutoCorrect and after clicking through several prompts/warnings which had me concerned, I then reopened the database and the error went away. When reopening the database the problem was resolved. I suspect this will ...

hey look at that answer to a question I just googled, I'm glad I added it there 4 months ago, I forgot that's how I fixed that problem... heh

Transcript for