VBA Rubberducking

Whenever I need logging functionality in .net, I use a logging framework, such as NLog. Obviously there's no logging framework for vba, at least none that I know of. As much as I love using NLog, the way it works, with targets, rules and loggers, was going to be way too heavy and complex for my ...

interfaces in VBA are definitely under-used. I bet half the people claiming VBA can't do OOP don't even know you can implement interfaces in VBA

Mat's Mug

4:17 AM

0

Q: How to hook up a COM event dispatcher?

The VBIDE API exposes the wonderfully cryptic _dispVBComponentsEvents interface (among others), which look like something that I could use to capture various interesting events in the VBE. So I implemented the interface in a class that intends to capture the event and raise a "normal" .net event...

c# com event-handling dispatch

Mat's Mug

4:31 AM

> MSDN is completely useless on the subject, and finding documentation about this is harder than finding the maiden name of the third wife of Henry VIII.

Mat's Mug

4:56 AM

@ThunderFrame this is serious COM stuff. if you can find documentation about how to implement a callback for an IDispatch in C# I'll definitely dig deeper into it... but I'm missing lots of low-level COM knowledge to make this work

Mat's Mug

5:18 AM

nevermind

this is going to work :)

I need to blog about this

now the question is, how do I manage all the IConnectionPoint instances

I need some IDictionary<IConnectionPoint, int>

Duga

6:10 AM

[retailcoder/Rubberduck] retailcoder pushed commit 5d19c44b to CodeExplorer: implemented dispatchers for VBProjects and VBComponents events. needs refactoring and further fine-tuning, but works.

[rubberduck-vba/Rubberduck] retailcoder created pull request #1141: Handle VBProjects and VBComponents COM events to merge retailcoder/Rubberduck/CodeExplorer into rubberduck-vba/Rubberduck/CodeExplorer

> Code Explorer still shows components that were supposedly removed, so RubberduckParserState.ClearDeclarations(VBComponent) and perhaps also RubberduckParserState.ClearDeclarations(VBProject) might not be working as intended (the tree view needs to be refreshed anyway, but then the component and its members remains there so something isn't right).

[rubberduck-vba/Rubberduck] build for commit 5d19c44b on unknown branch: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] retailcoder commented on pull request #1141: Handle VBProjects and VBComponents COM events

> Upon further testing, the declarations do get correctly removed from the parser state - the problem seems to only be with the Code Explorer tree view.

[rubberduck-vba/Rubberduck] retailcoder merged pull request #1141: Handle VBProjects and VBComponents COM events from retailcoder/Rubberduck/CodeExplorer into rubberduck-vba/Rubberduck/CodeExplorer

[rubberduck-vba/Rubberduck] retailcoder pushed 4 commits to CodeExplorer

[rubberduck-vba/Rubberduck] retailcoder pushed commit 7127cfcd to CodeExplorer

Merge pull request #49 from rubberduck-vba/next

sync with merged PR's in main repo

[rubberduck-vba/Rubberduck] retailcoder pushed commit 1a0c3d62 to CodeExplorer‌: fixed double-parse startup issue (ref #1033)

[rubberduck-vba/Rubberduck] retailcoder pushed commit 8145e792 to CodeExplorer‌: removed deprecated VBProjectParseResult class

[rubberduck-vba/Rubberduck] retailcoder pushed commit 5d19c44b to CodeExplorer‌: implemented dispatchers for VBProjects and VBComponents events. needs refactoring and further fine-tuning, but works.

[rubberduck-vba/Rubberduck] retailcoder pushed commit 151bfad4 to CodeExplorer

Merge pull request #1141 from retailcoder/CodeExplorer

Handle VBProjects and VBComponents COM events

Mat's Mug

TTGTB

Hosch250

Night.

Duga

[rubberduck-vba/Rubberduck] build for commit 151bfad4 on CodeExplorer: AppVeyor build succeeded

ThunderFrame

6:48 AM

COM events.... From Ahhh, that's how they work to commit, in 6 hours!

Duga

7:20 AM

[rubberduck-vba/Rubberduck] sshikov commented on issue #1126: Indent current procedure produces garbage

> Duplicate of #1061

[rubberduck-vba/Rubberduck] sshikov closed issue #1126: Indent current procedure produces garbage

Duga

9:01 AM

[rubberduck-vba/Rubberduck] sshikov opened issue #1142: Is it possible to implement inspections/refactoring as scripts?

> I mean, Rubberduck scripting extensions, written in VBA itself (but not restricted to), having access to ANTLR parse tree, for example.

Just an idea.

Duga

9:57 AM

[rubberduck-vba/Rubberduck] ckuhn203 commented on issue #1142: Is it possible to implement inspections/refactoring as scripts?

> Sounds related to #404.

There's also an issue somewhere about exposing the parser via a COM interface so that we could access the parse tree and symbol table from VBA.

In short, yes, it's possible, but a pretty large undertaking.

Duga

10:36 AM

[rubberduck-vba/Rubberduck] ThunderFrame commented on issue #866: HotKey mappings

> There should be a default mapping, but we should absolutely make the mappings user-configurable.

Duga

11:15 AM

[rubberduck-vba/Rubberduck] ThunderFrame opened issue #1143: Determining a selection or module content change

> I recall seeing references to key hooks, presumably to catch keyboard shortcuts, but also to capture edits to a module and/or changes to the current text selection.
While key hooks will need to be the approach for keyboard shortcuts, there are a number of downsides to keyhooks for tracking code and selection changes:

1. Code edits can be performed without keystrokes (eg. Copy/Paste, Mouse-drag, Event handlers, interface implementations, Replace/Replace All, VBE and 3rd party addins, etc)

Duck Exchange

12:25 PM

0

Q: Retrieves Data From Various Excel Sheets Online, sorts, edits, and Analyzes said Data

Thanks in advance! I'm fairly new to Excel VBA so I welcome all criticism and advice! This is a routine I wrote to streamline analysis I need to do that originally took 1.5 hours and now takes about 4 minutes total run time. I'm fairly certain this part of stack is for code review, but if this...

performance beginner vba excel

Mat's Mug

@ThunderFrame actually it went from there to "crap, that's just the VBIDE API interfaces being redone, there's nothing useful here" to "I can't do this" to "there's gotta be a way" to intense Googling, to "I gotta ask on SO" to intense Googling, to "oh wow, I was so close" to "IT WORKS!!!" to commit ;-)

ThunderFrame

12:45 PM

interesting docs behind that original github link... netoffice.codeplex.com/wikipage?title=Tec_Documentation_English

Duga

2:14 PM

[rubberduck-vba/Rubberduck] retailcoder commented on issue #1142: Is it possible to implement inspections/refactoring as scripts?

> I don't think we'll expose the ANTLR parse tree itself. But, we can (and eventually, *will*) expose a "reflection API" so that `Declaration` and `IdentifierReference` objects can be browsed/iterated in VBA client code - the API could also expose parser state events to notify clients of a change.

That API is a project all by itself though.

Mat's Mug

2:47 PM

0

A: How to hook up a COM event dispatcher?

Am I on the right track? Yes. What you have in an event sink - you're missing a bit of code to register the sink with the COM servers. The VBProjects and VBComponents interfaces implement (somewhere very deep) the IConnectionPointContainer interface - you need to use that to collect IConnec...

Duga

3:39 PM

[rubberduck-vba/Rubberduck] Pitterling commented on issue #1033: High CPU usage when Parser state retruns Error

> I just tried the latest commit and see a lot of RuntimeBinder Exceptions, like these:

`Exception thrown: 'Microsoft.CSharp.RuntimeBinder.RuntimeBinderException' in System.Core.dll
("'Rubberduck.Parsing.Grammar.VBAParser.ConstSubStmtContext' does not contain a definition for 'LPAREN'")`

[rubberduck-vba/Rubberduck] retailcoder created pull request #1144: merge latest changes to merge CodeExplorer into next

[rubberduck-vba/Rubberduck] retailcoder merged pull request #1144: merge latest changes from CodeExplorer into next

[rubberduck-vba/Rubberduck] build for commit 151bfad4 on next: AppVeyor build cancelled

BUILD FAILURE!

[rubberduck-vba/Rubberduck] retailcoder pushed 14 commits to next

[rubberduck-vba/Rubberduck] retailcoder pushed commit 6dd9e37b to next

Merge pull request #1144 from rubberduck-vba/CodeExplorer

merge latest changes

[rubberduck-vba/Rubberduck] retailcoder commented on issue #1033: High CPU usage when Parser state retruns Error

> @Pitterling that's my bad - I merged from my fork into the CodeExplorer branch - I intended to merge into Next... merged now, please pull the changes... when the DeclarationType is DeclarationType.Const the IsArray getter shouldn't even try to access the ParserRuleContext anymore.

[rubberduck-vba/Rubberduck] build for commit 6dd9e37b on next: AppVeyor build succeeded

Duga

5:18 PM

[rubberduck-vba/Rubberduck] Pitterling opened issue #1145: Parser Exception

> Hi, during parsing i'm facing several exception throws. They always look like this:

> Exception thrown: 'System.InvalidOperationException' in System.Core.dll
Exception thrown resolving 'vbFnPivotItemPri': System.InvalidOperationException: Sequence contains more than one matching element
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at Rubberduck.Parsing.Symbols.IdentifierReferenceResolver.Resolve(ICS_B_MemberProcedureCallContext context)

[rubberduck-vba/Rubberduck] Hosch250 commented on issue #1145: Parser Exception

> Thank you for the bug report. Could you provide the failing code so we could see exactly what our parser breaks on?

[rubberduck-vba/Rubberduck] Hosch250 added label code-parsing to issue #1145: Parser Exception

[rubberduck-vba/Rubberduck] Hosch250 added label bug to issue #1145: Parser Exception

[rubberduck-vba/Rubberduck] retailcoder commented on issue #1145: Parser Exception

> That's a resolver bug - I *think* I've seen it too, but I can use some help coming up with the minimal VBA code required to cause it. The exception is thrown on [this line](https://github.com/rubberduck-vba/Rubberduck/blob/next/Rubberduck.Parsing/Symbols/IdentifierReferenceResolver.cs#L731):

var identifierContext = context.ambiguousIdentifier();
var member = _declarations.Where(d => d.IdentifierName == identifierContext.GetText())
.SingleOrDefault(i

[rubberduck-vba/Rubberduck] retailcoder assigned retailcoder to issue #1145: Parser Exception

Duga

5:49 PM

[rubberduck-vba/Rubberduck] Pitterling commented on issue #1145: Parser Exception

> @Hosch250 would it be sufficient just to provide the failing module? There are a lot of dependencies ..
Any way to figure out .. which part the parser is failing?

[rubberduck-vba/Rubberduck] Hosch250 commented on issue #1145: Parser Exception

> A minimal, verifiable example would be best.

Duga

6:09 PM

[rubberduck-vba/Rubberduck] retailcoder commented on issue #1145: Parser Exception

> I *think* this would trip it:

**Class1**

Private Type TSomething
Foo As Integer
End Type
Private this As TSomething

Public Property Get Foo() As Integer
Foo = this.Foo
End Sub

The identifier `Foo` exists twice in the module - once under `TSomething.Foo`, and once under `Class1.Foo`. Since all the `Where` call is checking for is that the `parentType.ComponentName` ("Class1") matches, and that the `identifierName` ("Foo") matches - the `Single

[rubberduck-vba/Rubberduck] Pitterling commented on issue #1145: InvalidOperationException in resolver

> @Hosch250
[Rubberduck-Parser-Error.xlsm.zip](https://github.com/rubberduck-vba/Rubberduck/files/145178/Rubberduck-Parser-Error.xlsm.zip)

I extraced the failing part and the dependencies .. in total more then 20 modules ...

[rubberduck-vba/Rubberduck] Hosch250 commented on issue #1145: InvalidOperationException in resolver

> Thanks. We will look into it.

Mat's Mug

yet I'm pretty sure I can repro the problem with less than 10 lines of code

Hosch250

6:34 PM

Well, you can fix your problem and see if the fix works for his.

Duga

[rubberduck-vba/Rubberduck] Pitterling commented on issue #1145: InvalidOperationException in resolver

> @retailcoder the code snippet is causing a different exception

``Exception thrown: 'Rubberduck.Parsing.Symbols.SyntaxErrorException' in Rubberduck.Parsing.dll
Rubberduck.Parsing.Symbols.SyntaxErrorException: extraneous input 'End Sub' expecting {END_PROPERTY, NEWLINE, REMCOMMENT, COMMENT, ':', WS}
at Rubberduck.Parsing.Symbols.ExceptionErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e) in C

Mat's Mug

6:49 PM

oh come on

Duga

[rubberduck-vba/Rubberduck] retailcoder commented on issue #1145: InvalidOperationException in resolver

> lol, that was a typo. I meant to End Property, not End Sub!

Phrancis

There's gotta be a better way of doing this...

'Items containing specific senders:
If InStr(item.SenderName, "partnerlinksupport") > 0 _
Or InStr(item.SenderName, "SQLAdmin") > 0 Then
    'Containing containing specific recipients:
    If InStr(item.Recipients, "intdev-all") > 0 Then
        'Containing specific words in subject line:
        If InStr(item.Subject, "error") > 0 _
        Or InStr(item.Subject, "exception") > 0 Then
            item.UnRead = False
            item.Move destFolder
        End If
    End If
End If

Vogel612

guard-clauses and connecting the nested ifs through Ands

that's the two possiblities.

Phrancis

Guard clauses?

Vogel612

Turn the If-Conditions around and return in case they don't apply

see also [flattening arrow code(blog.codinghorror.com/flattening-arrow-code)

Phrancis

7:04 PM

I could also use GoTo :D

(just kidding)

Phrancis

7:14 PM

'First skip items do Do Not meet the criteria:
If Not InStr(item.SenderName, "partnerlinksupport") > 0 _
Or Not InStr(item.SenderName, "SQLAdmin") > 0 _
And Not InStr(item.Recipients, "intdev-all") > 0 _
And Not InStr(item.Subject, "error") > 0 _
Or Not InStr(item.Subject, "exception") > 0 _
Then
    'Do nothing
Else
    item.UnRead = False
    item.Move destFolder
End If

Better?

Mat's Mug

'Do nothing

smells

Phrancis

What's a better alternative?

Mat's Mug

reverse the condition and do something instead?

Phrancis

:|

Read up^

15 mins ago, by Vogel612

Turn the If-Conditions around and return in case they don't apply

Mat's Mug

If InStr(item.SenderName, "partnerlinksupport") > 0 _
And InStr(item.SenderName, "SQLAdmin") > 0 _
Or InStr(item.Recipients, "intdev-all") > 0 _
Or InStr(item.Subject, "error") > 0 _
And InStr(item.Subject, "exception") > 0 _
Then
    item.UnRead = False
    item.Move destFolder
End If

Phrancis

7:17 PM

Ah ok, sure

Mat's Mug

you might want to extract a function to clarify what that condition does

Phrancis

Would this make sense for exceptions to the rules:

'First check items that meet some criteria:
If InStr(item.SenderName, "partnerlinksupport") > 0 _
Or InStr(item.SenderName, "SQLAdmin") > 0 _
And InStr(item.Recipients, "intdev-all") > 0 _
And InStr(item.Subject, "error") > 0 _
Or InStr(item.Subject, "exception") > 0 _
Then
    'Exceptions here:
    If Not InStr(item.Subject, "QueryDatabases - Supplies Check") > 0 _
    Then
        item.UnRead = False
        item.Move destFolder
    End If
End If

@Mat'sMug If I did that, would I need to copy all this boilerplate into the function too?

Dim nameSpace As Outlook.nameSpace
Dim inbox As Outlook.Folder
Dim destFolder As Outlook.Folder
Set nameSpace = Application.GetNamespace("MAPI")
Set inbox = nameSpace.GetDefaultFolder(olFolderInbox)
Set destFolder = inbox.Folders("_ErrorDump")

Mat's Mug

copy? or move?

Phrancis

Hm, maybe it is move, not sure

My Sub signature now is Sub MoveToErrorDump(item As Outlook.mailItem), then the boilerplate, then the If conditions

Mat's Mug

only item would be needed it seems, though

Hosch250

7:28 PM

And for Pete's sake, indent those conditions so I can see where the If condition starts and ends.

Phrancis

Is Sub == function?

Mat's Mug

sort of

Hosch250

NO.

Sub is sort of like a void in Java. Function's return a value.

Mat's Mug

a Sub is a procedure without a return value (aka void)

a Function is a procedure with a return value

Phrancis

OK. I wouldn't need a return value if it's just marking an item unread and moving it then

Good to know, I had no idea of the distinction (though I knew functions usually return something, from writing a bit of JS)

Mat's Mug

7:30 PM

right. but what I meant was Private Function GoodNameHere(ByVal item As MailItem) As Boolean

yeah.. you gotta understand where it came from, to understand why it's called that way

Hosch250

@Mat'sMug Should we release an inspection to warn of empty procedure/property/if/loop blocks with 2.0?

Phrancis

I was just gonna do Sub HandleErrorMailItem(item As Outlook.mailItem) is yours better?

Hosch250

We could do a quick fix later (it could be quite difficult to get all the edge cases), but an inspection shouldn't be that hard.

@Phrancis Well, yours is implicitly public.

But otherwise, yours is better if you don't need to return anything.

Mat's would work better if you were going to return whether marking it as read was a success.

Phrancis

Will having all the Outlook application boilerplate Dims called each time put more strain on Outlook? (considering it could be called thousands of times per hour)

Although... I guess it would be called for each mail item anyways eh...

'First check items that meet some criteria:
If InStr(item.SenderName, "partnerlinksupport") > 0 _
    Or InStr(item.SenderName, "SQLAdmin") > 0 _
    And InStr(item.Recipients, "intdev-all") > 0 _
    And InStr(item.Subject, "error") > 0 _
    Or InStr(item.Subject, "exception") > 0 _
Then
    'Exceptions here:
    If Not InStr(item.Subject, "QueryDatabases - Supplies Check") > 0 _
    Then
        HandleErrorMailItem (item)
    End If
End If

Sure looks cleaner

Hosch250

OK, do you know the precedence of those And and Or operators?

Phrancis

7:38 PM

I'd think it's If (A Or B) And (C) And (D Or E) but haven't tested

Hosch250

Nope.

And has higher precedence.

If (A) Or (B) Or (C And D And E) Or (F)

Phrancis

WTF

Hosch250

I've never met a language with Or having higher precedence than And.

Phrancis

Except VBA?

Hosch250

I thought you might think that, it is a common problem. So, you need to add parenthesis to make the Or statements have higher precedence.

Phrancis

7:41 PM

Oh, didn't know you could do that; that's what I normally do in SQL

Hosch250

@Phrancis Sorry, I'm exhausted and not thinking straight.

Phrancis

If (InStr(item.SenderName, "partnerlinksupport") > 0 _
    Or InStr(item.SenderName, "SQLAdmin") > 0) _
And InStr(item.Recipients, "intdev-all") > 0 _
And (InStr(item.Subject, "error") > 0 _
    Or InStr(item.Subject, "exception") > 0) _
Then

Like that?

Hosch250

Yup, should work.

Phrancis

Sweet, thanks for letting me know :)

ThunderFrame

7:59 PM

@Phrancis also, your InStr will be case sensitive, unless you add an Option Compare Text statement at the top of your module, but it's safer to explicitly set the text comparison in the InStr function.

eg. If InStr(1, "Foo", "FOO", vbTextCompare) > 0 Then

Hosch250

Doesn't VBA have a String.Contains method?

ThunderFrame

you're looking at it

Hosch250

OK.

Mat's Mug

lol

4

Q: A more readable InStr: StringContains

Consider the following: If myString = "abc" Or myString = "def" [...] Or myString = "xyz" Then In C# when myString == "abc" the rest of the conditions aren't evaluated. But because of how VB works, the entire expression needs to be evaluated, even if a match is found with the first comparison....

strings vba vb6

ThunderFrame

Once upon a time, InStrRev, Split and Join didn't exist in VBA, but iirc, did exist in VB.

@Phrancis - Note the overload of InStr that accepts a vbCompare argument, seems to require the first parameter to be the start position.

Phrancis

8:10 PM

@ThunderFrame What is the first argument 1 for?

ThunderFrame

@Phrancis ^ "the first parameter to be the start position"

Phrancis

Oh, right (sorry)

Phrancis

9:12 PM

Does this look right?

If Not (inbox.Folders.item(i).Name = "_ErrorDump") Then
    inbox.Folders.Add ("_ErrorDump")
End If
Set destFolder = inbox.Folders("_ErrorDump")

Wish I had a test environment lol

Mat's Mug

Then write a method that implements your logic without the side-effects, that you can test :-)

Phrancis

Maybe just have it pop-up a notification or something like that?

Mat's Mug

hmm

looks right anyway

Phrancis

The other conditions appear to work fine

Mat's Mug

Ctrl+G then

?item.Recipients

Phrancis

9:27 PM

Run-time error 5, invalid call or argument...

Mat's Mug

does ?typename(item.recipients) output "String"?

it could be an array or something

Phrancis

Nope, it's of type Recipients... let me try something

Mat's Mug

I'm not all that familiar with the outlook object model..

Phrancis

Looks like I'm gonna need to foreach

And parse a Recipient.Address

3

A: Getting mailaddresses out of recipients of an mailItem

I assume this is Outlook 2007+. Have you tried the Address Property? For Each Recipient In mai.Recipients MsgBox Recipient.Address Next Recipient This should print the email address of each recipient.

Suddenly got a lot uglier

For Each Recipient In item.Recipients
    If InStr(item.Recipient, "intdev-all") > 0 _
    Then
        'First check items that meet some sender or subject criteria:
        If (InStr(item.SenderName, "partnerlinksupport") > 0 _
            Or InStr(item.SenderName, "SQLAdmin") > 0) _
        And (InStr(item.Subject, "error") > 0 _
            Or InStr(item.Subject, "exception") > 0) _
        Then
            'Exceptions here:
            If Not InStr(item.Subject, "QueryDatabases - Supplies Check") > 0 _

Doesn't work

Looks like I might need a PropertyAccessor

msdn.microsoft.com/en-us/library/office/ff868695.aspx

Phrancis

9:53 PM

WOO it worked!

Transcript for