VBA Rubberducking

Duga

00:01

RELOAD!

[retailcoder/Rubberduck] 9 commits. 293 additions. 120 deletions.

[rubberduck-vba/Rubberduck] 3 commits. 20 additions. 4 deletions.

[Vannevelj/VSDiagnostics] 1 opened issue.

Hosch250

02:42

There won't be any commits for a while. My brother has the flu now, and I have to finish midterms.

Just spent about 4 hours working on one midterm, I'm done with two others, another sounds like it will be posted tomorrow, and I'm not sure if my fifth class will have one.

Good news: I have spring break next week, so this is about as good a time as any to be sick.

Mat's Mug

Don't worry, there still will be commits ;-)

Hosch250

LOL, I should have said there would be none from me.

Mat's Mug

All good, school first!

Duga

04:17

[rubberduck-vba/Rubberduck] Hosch250 commented on issue #770: Setup using bitbucket

> Oh, OK. I won't remove it then.

Duga

06:09

[retailcoder/Rubberduck] retailcoder pushed commit a6518d40 to TypeInfo: removed all cancellation tokens, async and parallel stuff

[retailcoder/Rubberduck] retailcoder pushed commit ed2d9727 to TypeInfo: renamed public void Parse() to public void ParseSynchronous(); clarified intent of the public member in summary comment

[retailcoder/Rubberduck] retailcoder pushed commit da34c87e to TypeInfo: reinstated async parsing + resolving. declarations still get doubled-up when reparse is requested from inspection results toolwindow, but parser state works.

Mat's Mug

06:32

Ambiguous match in '(unknown)': 'Description'
Ambiguous match in '(unknown)': 'Description'
Ambiguous match in '(unknown)': 'Description'
Ambiguous match in '(unknown)': 'Description'

something isn't right

named parameters don't seem to resolve correctly

and I'm getting tons of debug output about unknown identifiers that should exist in the reflected COM type libs

I might have gotten some visibilities wrong, not sure what's going on exactly there

but Pitterling's code resolves in 3 minutes.

for some values of "resolves"

anyway, TTGTB

Duga

15:13

[rubberduck-vba/Rubberduck] thetopic opened issue #1178: Parsing to Double _vbe.Version with comma

> In the "**next**" branch with current sources.

File : Rubberduck.Parsing\VBA\RubberduckParser.cs Line 228

````c#
var preprocessor = new VBAPreprocessor(double.Parse(_vbe.Version));
```

If the decimal separator is a comma the `double.Parse` throw an exception.

[rubberduck-vba/Rubberduck] retailcoder added label code-parsing to issue #1178: Parsing to Double _vbe.Version with comma

[rubberduck-vba/Rubberduck] retailcoder added label bug to issue #1178: Parsing to Double _vbe.Version with comma

[rubberduck-vba/Rubberduck] retailcoder commented on issue #1178: Parsing to Double _vbe.Version with comma

> Good catch!

[rubberduck-vba/Rubberduck] thetopic opened issue #1179: AmbiguousMatchException : Lib Alias typeHint

> In the "**next**" branch with current sources.

I have the same kind of code that can be found here : #250

```vba
Private Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" _
(ByVal HKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, _
ByVal samDesired As Long, phkResult As Long) As Long
```

If I debug the current sources I have this exception : AmbiguousMatchException

File : Rubberduck.Parsing\Symbols\Declaration.cs Line 322

```c#
var metho

Duga

15:45

[rubberduck-vba/Rubberduck] retailcoder added label bug to issue #1179: AmbiguousMatchException : Lib Alias typeHint

[rubberduck-vba/Rubberduck] retailcoder added label code-parsing to issue #1179: AmbiguousMatchException : Lib Alias typeHint

[rubberduck-vba/Rubberduck] retailcoder assigned retailcoder to issue #1179: AmbiguousMatchException : Lib Alias typeHint

[rubberduck-vba/Rubberduck] retailcoder commented on issue #1179: AmbiguousMatchException : Lib Alias typeHint

> ugh. I'll revert to dynamic cast + invoke.

Mat's Mug

16:36

this is so cool

Chrismas007

@Mat'sMug Congrats on getting the ad slot!

Mat's Mug

:)

Pin Crash

16:54

@Mat'sMug I'm not seeing any ads, would I need to log out and browse as anonymous?

Quack Exchange

0

Q: Toggle button that will hide/unhide columns within a range

I have the below toggle button sub that works; Private Sub Toggle_Unused_Click() Dim xAddress As String xAddress = "DR:FI" Call KeyOpen 'unprotects the sheet If Toggle_Unused.Value Then Application.ActiveSheet.Columns(xAddress).Hidden = True 'This returns a value to my notes tab...

Mat's Mug

@PinCrash nope. most of the time it's HNQ's showing up and A51 and other SE stuff.

you mean you have nothing showing up under the hot meta posts?

Pin Crash

@Mat'sMug ^^that?

Mat's Mug

huh wtf

and the home page?

nothing?

Pin Crash

Ah I see one on the home page

Pin Crash

17:38

Not sure if either of you guys have tried or had experience or anything to say about Cardshifter, but just in case...

in Cardshifter TCG, 1 min ago, by Pin Crash

I need 2-3 short, Twitter-length (preferably, actual tweets) about Cardshifter (any aspect, really) to fill in a small section on the landing page. If you do a tweet please include either @Cardshifter (the team/account) or #Cardshifter (tag for game itself)

Trying to finish up a nice landing page since the ads are up

Mat's Mug

> CardShifter: The weekend-challenge that dragged for a year

?

lol

Pin Crash

18:05

LOL

Damn it's going to be 2 years this coming August, unbelievable

Chrismas007

18:25

0

Q: How to make a worksheet function more efficient?

I am trying to make a worksheet function that loops through rows more efficient. Here is what I have - Dim ws as worksheet, wks as worksheet, lastrow as long, i as long Set ws = Sheets("Sheet1") Set wks = Sheets("Sheet2") With ws Lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row For i = 2 to L...

excel vba excel-vba

A CR question on SO?

Mat's Mug

if it works, could be

did you flag it?

@Chrismas007 the correct course of action is actually to VTC as too broad, and custom-flag for migration. A question must be off-topic on the site it's posted on, to be migratable.. but being migratable isn't an off-topic reason per se. the UI is misleading.

Duga

[rubberduck-vba/Rubberduck] autoboosh assigned autoboosh to issue #1178: Parsing to Double _vbe.Version with comma

Chrismas007

@Mat'sMug I see that now. I'll VTC as too broad in the future.

Duga

18:49

[rubberduck-vba/Rubberduck] ThunderFrame commented on issue #1178: Parsing to Double _vbe.Version with comma

> Parse just need the IFormatProvider overload? CultureInfo.CurrentCulture?

We should probably check other Parse and Format methods, to make use of IFormatProvider too?

[rubberduck-vba/Rubberduck] autoboosh commented on issue #1178: Parsing to Double _vbe.Version with comma

> I would have said CultureInfo.InvariantCulture.

Pin Crash

19:05

@Mat'sMug I have a "read uncommitted" query that runs in roughly 40 seconds (slow ass prod server) to select the keys that I need to update, then a transaction to update those rows that execs in 2-4 ms. Sound acceptable to you?

Mat's Mug

sounds like that table has no UPDATE triggers ;-)

Pin Crash

Yeah it doesn't, it's merely a receptacle for BizTalk to dump messages into

An agency acquired another one (both were customers at the time), one vendor has started sending transmissions for certain patients with a new message header ID, but the agencies' databases have not been merged yet so this goes and changes the message header ID back so the acquired agency can see the messages in their reports... PITA

if exists (select 1 from @MessagesToUpdate) begin
    begin transaction;
        update TELEHEALTH_VITAL_SIGNS_MESSAGE_HEADER set thvsmh_fileheader = @WillCareMH, thvsmh_processed = 'N'
        where thvsmh_id in (select thvsmh_id from @MessagesToUpdate);
    rollback transaction;
end;

That's the 2-4 ms part

Mat's Mug

uh

dude

you're rolling back everything

Pin Crash

Well, I'll change it to commit once I get it deployed, this is just for testing while I write the code ;p

Mat's Mug

lol

Duga

19:15

[rubberduck-vba/Rubberduck] autoboosh created pull request #1180: fix .Parse culture dependency (fixes #1178) to merge autoboosh/Rubberduck/doubleparsefix into rubberduck-vba/Rubberduck/next

Pin Crash

(I don't have a test copy to play with, so I test in production)

It's the only part of the query that acquires any locks, and it's never going to be more than about 10-20 records at a time (running once per hour)

ThunderFrame

Testing in Production is a HIPAA recommendation?

Duga

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

[rubberduck-vba/Rubberduck] autoboosh commented on issue #1178: Parsing to Double _vbe.Version with comma

> Here's a fix for number parsing methods. Do we need that for string.Formats as well, as you mentioned @ThunderFrame? Perhaps just the string.Formats that do not format a string specified in the resources file?

Pin Crash

3

awgaya

20:02

@Vogel612, do you want to make a translation manager like lingotek.com/translation_management_system?

together I mean, it would be a good exercise in java fx

we could have concepts like translation projects, that in turn have translation sets, which have translations, resx files would merely be an import/export feature

could associate tasks with projects, track progress of what translations are done etc

Vogel612

that's overkill...

awgaya

it wouldn't have to support everything ofc :P

Vogel612

If you're goint to start that thing, I'd be happy to help, but I can't and won't male any promises

awgaya

yeah np

Mat's Mug

FWIW I'm going to use your tool soon, @Vogel612, to get the French translation up to date

awgaya

20:14

see, our first customer! :P

Mat's Mug

I'll make an announcement on Rubberduck News when the {en-US} captions are finalized and the thing is ready for translation

IIRC there's still a few captions missing

or misplaced

awgaya

when is v2.0's release date again, sometime in may?

Mat's Mug

shooting for mid-May yeah

BTW I'm this close to get the parser under control

awgaya

under control?

Mat's Mug

yeah, it's kinda wild right now

parser state goes crazy

awgaya

20:20

how come?

Duga

[rubberduck-vba/Rubberduck] retailcoder closed issue #1178: Parsing to Double _vbe.Version with comma

[rubberduck-vba/Rubberduck] retailcoder merged pull request #1180: fix .Parse culture dependency (fixes #1178) from autoboosh/Rubberduck/doubleparsefix into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] autoboosh pushed commit bfcaad00 to next: fix .Parse culture dependency (fixes #1178)

[rubberduck-vba/Rubberduck] retailcoder pushed commit 6228d705 to next

Merge pull request #1180 from autoboosh/doubleparsefix

fix .Parse culture dependency (fixes #1178)

Mat's Mug

@awgaya because each parser task is trying to determine whether or not it should trigger the resolver task

that's changing when I merge my changes

parser tasks are responsible for parsing, period.

Vogel612

wait... there's no centralized callback waiting for all of them to ckeck back?

Mat's Mug

now there is

in my branch

well, sort of

I'm handling the "StateChanged" parser state event, and launching a resolver task when the state gets to "Parsed"

Rossco

Hey all, how is it going?

Mat's Mug

20:25

makes sense?

hey @Rossco!! long time no chat!

Rossco

Yeah, I was going to say that.

Vogel612

@Mat'sMug yea in a way...

Mat's Mug

..

Duga

[rubberduck-vba/Rubberduck] build for commit 6228d705 on next: AppVeyor build succeeded

Rossco

When you guys are all in here, I'm at work!

Vogel612

20:26

I'd have an instant solution in JS

@Rossco LIES! I'm in here all day

okay not all day...

Rossco

LOL

Its true, I do pop in some times and see you online.

Vogel612

but it's close enough!

Rossco

Usually there is no one else though, so it says, 'Last message posted 4 hours ago...'

I thought I would come and see what the progress has been on the RDA

Mat's Mug

anyway, so all I need to do now is to add support for cancellation

Rossco

Seems like a lot, since I have no idea what anyone is talking about.

Mat's Mug

20:29

lol

awgaya

don't worry, we never know what mat's mug is talking about either :p

3

Mat's Mug

hahaha

crap

Rossco

Have some users changed their handles?

Vogel612

nope.

there's new people on the project.

well you could count Pin Crash, fka. Phrancis, but I don't think he counts as RD Developer

Rossco

@Vogel612 Yeah, I wondered that. I don't see much from @RubberDuck, is he still around?

Mat's Mug

20:33

he is

sorta

Rossco

Ah ok.

np, well he will get a ping from me referencing him now :D

I see that you have some code which can find all the types in referenced com dlls. I remember posting a link to an article talking about how to do that. Did you use it?

Vogel612

Last thing I heard was that he was doing stuff on a microcontroller...

Mat's Mug

@Rossco I think so... maybe

It's not finished though. The resolver isn't seeing the Debug module (class?), I'm iffy about how I identify events, and parameter types aren't looked up yet

Rossco

Ok, I was just interested if you still had the link to that article.

I lost it... :(

Also, I'm AFK in a meeting in a minute. But I'll be back after.

Mat's Mug

20:51

@Rossco I used this

RubberDuck

@Rossco kind of. Been terribly busy at work and at home.

So, I'm around, but not really actively developing atm.

Mat's Mug

like, ~~Ghostdoc~~ Ghostduck

:)

@RubberDuck what happened with that home, speaking of home?

RubberDuck

Just signed a new contract today.

Turns out it was being flipped, and by law they had to wait 90 days to sell it.

So, now the auditor goes out and assess the value.

Hopefully, it's worth what we offered so the bank funds the loan.

Should know something in like a week.

Mat's Mug

great!

RubberDuck

Crossing my fingers. Tired of living with the in laws.

Mat's Mug

21:05

ouch! I hope all goes well :)

I can't wait to start home-shopping.. I hope to get a house before the kids start school..

Vogel612

and here I am, attacking my monetary reserves to finance university

RubberDuck

You're a programmer, why do you need to attend university?

I mean, unless you're getting an MBA, then by all means, carry on.

Rossco

21:21

@RubberDuck I know how that feels. I have had a dry spell recently.

RubberDuck

I'm doing plenty of dev, just nothing fun.

Unless you count a crash course in embedded motion controllers as fun.

Rossco

@Mat'sMug Ah, I found the article that I liked: codeproject.com/Articles/523417/…

Haven't done anything with it, but it looks promising.

Mat's Mug

I so want to go home and code right now

2

Rossco

^ ...???

Mat's Mug

I'm at the office, trying to get myself into the mood to set up yet another SSRS report

Rossco

21:30

Ahhh, I can understand.

SQL makes me hurt all over...

;-P

Mat's Mug

hey at least it's not SEQUEL

(because yes, I get to "play" with that crap too)

RubberDuck

@Rossco ahh. That's pretty cool. Mug's been getting into a bit of that lately.

Rossco

Yeah, I reviewed some of the commits he's been making last night. I was interested to see that.

Mat's Mug

7

Q: Milking a COM type library: "fun" with COM reflection

Once upon a time, there was a duck that wanted to know where and how user code was calling into the VBA standard library and Excel object model. To match the rest of its API, the poor little duck had to dig through pages and pages and pages of MSDN documentation, and instantiate a Declaration obj...

c# reflection rubberduck com

there's always this one up for review ^^

Rossco

I didn't like the statically defined assembly types and references - except as an interim measure.

Mat's Mug

21:40

@Rossco the last paragraph of that codeproject article is rather interesting

> Many articles on the web incorrectly say that you must reference a COM interop assembly to get rich type information in .NET, such as Microsoft Support article 320523 and StackOverflow posts "How to enumerate members of COM object in C#?" and "Get property names via reflection of an COM Object". Some more advanced articles say you should work with ITypeInfo directly such as "Inspecting COM Objects With Reflection" and "Obtain Type Information of IDispatch-Based COM Objects from Managed Code".

> Unfortunately, using ITypeInfo directly involves lots of manual interop work, and it doesn't give you a System.Type instance. However, using the TypeToTypeInfoMarshaler as discussed in this article is much easier, and it provides the rich type information in a standard .NET Type format.

I used the ITypeInfo way

Rossco

Yeah it is very interesting.

I like the multiple references to other articles as well. Its always worth reading a few other opinions.

Mat's Mug

but, will that approach work for Rubberduck's purposes?

Rossco

Not sure but it could save you all the hassle in translating the types.

Mat's Mug

more or less

Rossco

although you have to translate from .net types to vba types.

Mat's Mug

21:43

^^

still, I'm bookmarking it

Rossco

I did look at your ReferencedDeclarationsCollector class and then got overwhelmed by the large amount of code...

Mat's Mug

lol yeah, I need to split it up

it's just a big loop really

Rossco

The answer from RobH looks like a good start.

Mat's Mug

implemented already :)

Rossco

nice.

Pin Crash

21:45

@Mat'sMug For some values of "fun"?

Mat's Mug

yeah

Mar 5 at 14:15, by Mat's Mug

I'm ambivalent about how much fun I'm having :)

Rossco

Way to quote yourself. <3

Pin Crash

Mug is very quotable

4

Rossco

Real quick question: I'm trying to write a unit test to confirm that I have the correct metadata on types in the assembly under test. So I want to use reflection.

The assembly is referenced by the unit test project and I want to iterate the types in the assembly.

Mat's Mug

@PinCrash reminds me of this answer

> [...] and IDataManager (really? out of all names?) is your actual repository.

– Mat's Mug 5 minutes ago

Rossco

21:51

A lot of the time I see typeof(object).Assembly used to get a reference.

But I don't want to do that (most types in the assembly are not public).

I've got this:

	Assembly.GetCallingAssembly()
	.GetReferencedAssemblies()
	.Where(a => a.FullName.Contains("MyAssemblyName"))

But I end up with an object of AssemblyName and its not very useful.

Any better ideas?

My Reflection-fu is not very strong.

Mat's Mug

the .where will get you an IEnumerable<Assembly>, so you'll need to project a selection here.

add a .select call, like, .SelectMany(a => a.GetTypes()) or something like that ...there has to be a method you can call to get an IEnumerable<Type>

it doesn't matter whether they're public or internal

reflection doesn't care about access modifiers :)

Rossco

@Mat'sMug I get IEnumberable<AssemblyName>

Hosch250

@Rossco Long time no see!

I've been thinking about you lately.

Mat's Mug

ah, I see. GetReferencedAssemblies returns an AssemblyName[] that's why

Rossco

@Hosch250 Cheers, good to see you also.

"see" that is. :D

Mat's Mug

22:05

@Rossco you need to actually load the assembly:

39

A: How to get all types in a referenced assembly?

Note that Assembly.GetReferencedAssemblies only includes a particular assembly if you actually use a type in that assembly in your assembly (or a type that you use depends on a type in that assembly). It is not enough to merely include an assembly in the list of references in Visual Studio. Maybe...

foreach (var assemblyName in Assembly.GetExecutingAssembly().GetReferencedAssemblies()) {
    Assembly assembly = Assembly.Load(assemblyName);
    foreach (var type in assembly.GetTypes()) {
        Console.WriteLine(type.Name);
    }
}

Vogel612

@Mat'sMug shouldn't you recurse that down to load all loaded types?

Mat's Mug

probably

that'll load System as many times though

Rossco

@Mat'sMug Perfect, thank you.

Huh, if you have a reference in a project that isn't used anywhere. Does the dll output have an actual reference to that project or does the compiler 'optimise' it out?

Mat's Mug

the compiler removes it

Rossco

that's very 'helpful' of it...

Mat's Mug

22:16

why bloat an assembly with a crapton of references that aren't used anywhere?

Rossco

Try in general, just not in my case.

Edit: In general, just not in my case.

Don't know what I was writing the first time...

Mat's Mug

22:31

lol

Chrismas007

22:54

@Mat'sMug Will code review review an Excel Formula for improvements?

Vogel612

@Chrismas007 there's precedent IIRC

found it:

2

Q: Making an iterative formula shorter in Excel

I have just typed out very long code in Excel that just repeats itself 17 times, but each time with different cell references. Is there a more concise way of writing my code, perhaps by using another column of cells? The code that is repeated is: =IF(OR(ISBLANK(X2),ISBLANK(Y2)),0,X2/VLOOKUP(YEA...

excel

Chrismas007

Well it is my question

0

Q: CountIf With Filtered Data

I was researching a way to count the number of zeroes in a column of data, even if the data gets filtered. I found the following solution: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0) Where, B2:B18 is the total list of data and "Pea...

excel excel-formula

@Vogel612 I'd like a simpler solution.

Transcript for