VBA Rubberducking

Duga

00:00

RELOAD!

[rubberduck-vba/Rubberduck] 2 commits. 1 opened issue. 5 closed issues. 17 issue comments. 153 additions. 94 deletions.

[skiwi2/SEDENNInput] 7 commits. 358 additions. 278 deletions.

ThunderFrame

00:17

Wow! That clipboard HTML even renders in Lotus Notes. falls off chair

@Comintern It would be host specific, but we could possibly create our own Outlook PIA and edit the PIA to add a member that is implemented in Outlook.Application? That might let interop call a method defined in ThisOutlookSession VBA?

Comintern

Also an angle to check. I actually tried to create a PIA for VBE7.dll earlier, but it gave me a bunch of statics with no members.

Hosch250

00:42

@Mat'sMug Is it OK if clicking anywhere on the page after it is open closes it?

OK, @Mat'sMug, I'm at the point where you should be able to just start putting info in.

Basically, we need a lot of new images and text now.

8:10. TTYL.

Mat's Mug

02:35

So you have a menu item and a new page for each feature, that's just waiting for updated shots and content? Sounds good to me! ...I think I should hook up @Duga to that repo now...

Peter M Taylor

03:18

What is the ad for RD that @awgaya mentioned, I'm curious what it said.

Hosch250

03:29

@Mat'sMug I've only been working in my repo.

@Mat'sMug Basically, yeah. We need to make thumbnails and new pictures and GIF's of the stuff still, and write about it.

I'm going to look at connecting to the project now.

Basically, I'm going to need to make a test stub and run the refactorings against the code.

ThunderFrame

Gaaahhh, I'm working with a 60MB slightly malformed HTML file. I need to use Replace to turn it into valid XML, but I run out of String space/memory when using VBA.Replace or Regex Replace. Any ideas?

Hosch250

03:45

Use string replace in multiple passes with C#?

Then manually go through and fix any problems (which, if done correctly, shouldn't take too long).

ThunderFrame

All the fixes are automatable. C# probably not an option here.

Hosch250

Why not?

C# is much more powerful than VBA, and from the sounds of it, you have a stand-alone file...

Or, if you don't know C#, try VB.NET. Still more powerful than VBA, but less of a learning curve.

ThunderFrame

Code runs everyday on user PC, where deployment/ registration is hard.

Hosch250

How many users?

Oh, you mean you have to distribute the code for them to run?

ThunderFrame

A few dozen users, global locations.

Hosch250

03:51

You could always package the C# thing as an executable with the file location hardcoded (or ask them for it). They would just have to double-click to run it.

ThunderFrame

Hmm, maybe.

I was thinking of using byte arrays and CopyMemory, which I'm hoping is a lot faster than Mid statement

Hosch250

How much memory do you have anyway?

If it is really that much of a problem, I'd probably only load part of the file at a time and save it to the disk every time I make a change.

ThunderFrame

8gb, but only 32-bit Office. But some of the Replace calls do 60,000 replacements each.

Hosch250

Read it in one line at a time with C# and make any relevant changes. Then rewrite it to disk and read the next line.

ThunderFrame

Hmm, when you suggested multiple passes in C#, is that because C# would also struggle with a file that size?

Hosch250

03:56

No. I thought you were running on something like 2GB.

If I were you, I'd read it into an array of strings and iterate over each string making replacements and putting the changed one back into the array.

Then save the lines into the file.

ThunderFrame

Yeah, I think I'll try that, or something like it.

Hosch250

I've never done that with a 60MB file, but it should work and only take a minute or so, tops.

ThunderFrame

Yeah, a minute is kind of expensive :-/. Another 15MB file is almost instant using Replace, but it falls under the memory threshold.

Hosch250

I'd guess it could handle it in 10 seconds.

I've only done it with files around 50-60KB tops, so...

I can do about 50 of them in about 50milliseconds, and opening/closing is the most expensive operation, so...

ThunderFrame

Yeah, the reason I need it as XML, is because after a transform, it's about 30% of the size, without losing any important info.

I already wrote a SafeReplace function, using Mid statement, but it is sloooow.

Duga

04:21

[rubberduck-vba/Rubberduck] retailcoder pushed 24 commits to next (only showing some of them below)

[rubberduck-vba/Rubberduck] retailcoder pushed commit a68be5fa to next: made inspection resource tests independent of current culture

[rubberduck-vba/Rubberduck] retailcoder pushed commit 573339dd to next

Merge pull request #96 from rubberduck-vba/next

sync with main repo

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

Merge pull request #97 from rubberduck-vba/next

sync with main repo

[rubberduck-vba/Rubberduck] retailcoder pushed commit 9690fbc8 to next: made TestRunEventArgs a class, derived from EventArgs.

[rubberduck-vba/Rubberduck] retailcoder pushed commit fb86e372 to next: put all inspection tests in "Inspections" test category

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

Merge pull request #98 from rubberduck-vba/next

sync with main repo

[rubberduck-vba/Rubberduck] retailcoder pushed commit 1a387f82 to next: Merge branch 'next' of github.com/retailcoder/Rubberduck.git

[rubberduck-vba/Rubberduck] retailcoder pushed commit 0d241d45 to next: resolved merge conflicts

[rubberduck-vba/Rubberduck] retailcoder pushed commit f1537439 to next: Merge branch 'pull101' into next

[rubberduck-vba/Rubberduck] retailcoder pushed commit 71fcacdf to next: resolved conflicts

Mat's Mug

bookmark:

3900

A: Force Git to overwrite local files on pull

Important: If you have any local changes, they will be lost. With or without --hard option, any local commits that haven't been pushed will be lost.[*] If you have any files that are not tracked by Git (e.g. uploaded user content), these files will not be affected. I think this is the right w...

Hosch250

Ohh, that has a lot of votes.

Mat's Mug

wait what just happened here

Duga

[rubberduck-vba/Rubberduck] build for commit 71fcacdf on next: AppVeyor build succeeded

Mat's Mug

that's great but IIUC these commits were already in [next]

Hosch250

04:31

@Mat'sMug Probably because you force-committed.

IIUC, rebasing and force-merging, or whatever, rewrite the entire history of the branch.

Duga probably just posted the last few, or something.

Mat's Mug

976 passing, 21 skipped? do I have all tests?

Hosch250

Sounds about right.

We passed up VSD.

2

I remember not so long ago when VSD had 200 more than us.

ThunderFrame

D'oh. Stupid vbTextCompare was stupidly slow. VbBinaryCompare runs super fast.

Hosch250

04:50

> 39 dead crocs

> As I’ve already said, guard duck blew up all the neighboorhood’s crocs, so it’s really 58, 57 if you don’t count larry, who undied.

Night.

Mat's Mug

night!

Duga

[rubberduck-vba/Rubberduck] retailcoder opened issue #1572: With block variable references creates dupes

> Member calls inside `With` blocks shouldn't create an identifier reference for the parent - e.g. here `L41C10-L41C20` is the `With` block variable, the *parent context* of all member calls in the `With` block:

![image](https://cloud.githubusercontent.com/assets/5751684/15492387/d1df7bae-2148-11e6-8718-04c5b0eeccf6.png)

For the compiler, yes, that dot is a member call on the *parent context* - but for the user it looks funny. Either make the selection on the dot, or don't make With block

[rubberduck-vba/Rubberduck] retailcoder added label bug to issue #1573: Range() function resolves to Range class

[rubberduck-vba/Rubberduck] retailcoder added label code-parsing to issue #1573: Range() function resolves to Range class

[rubberduck-vba/Rubberduck] retailcoder opened issue #1573: Range() function resolves to Range class

> This will trip the *implicit reference to ActiveSheet* inspection:

Public foo As Range

That's a false positive, this instruction is using `Range` as a type, not as a global/static method.

Fix is easy: the inspection shouldn't look at *classes*, but at *functions*.

Problem is, it resolves to a `Class` here too:

Range("B1").Select

...which makes `.Select` resolve to nothing.

I think things be easier if `Range` resolved to the function when it's in a function call con

[rubberduck-vba/Rubberduck] retailcoder added label code-inspections to issue #1574: UntypedFunctionUsageInspection doesn't see the value

[rubberduck-vba/Rubberduck] retailcoder added label bug to issue #1574: UntypedFunctionUsageInspection doesn't see the value

[rubberduck-vba/Rubberduck] retailcoder opened issue #1574: UntypedFunctionUsageInspection doesn't see the value

> doesn't see the $ signs at least: the inspection is tripped regardless of whether a call is made to Format or Format$.

Mat's Mug

05:17

I'm out

Duga

[retailcoder/Rubberduck] retailcoder pushed 14 commits to next (only showing some of them below)

[retailcoder/Rubberduck] retailcoder pushed commit 1dbe676e to next: translation: added translation for "Parser Errors", one typo fix in German translation (#1559)

[retailcoder/Rubberduck] retailcoder pushed commit e063c79f to next

Speed up parsers (#1560)

* clean up file statements (fixes #1487)

* consider members in supertypes as members of enclosing module (fixes #1489)

* remove temp fix (should be fixed with #1489)

* add support for Circle and Scale special forms (fixes #1498)

* fix foreign names (#1521)

* speed up all parsers by using the "two-stage parsing" approach

* reenable "function return value not used" inspection

* clean up

[retailcoder/Rubberduck] retailcoder pushed commit 9bd8d081 to next

Fix TypeOfIs expression resolver (#1562)

* clean up file statements (fixes #1487)

* consider members in supertypes as members of enclosing module (fixes #1489)

* remove temp fix (should be fixed with #1489)

* add support for Circle and Scale special forms (fixes #1498)

* fix foreign names (#1521)

* speed up all parsers by using the "two-stage parsing" approach

* reenable "function return value not used" inspection

* clean up

* fix typeof expression resolver

[retailcoder/Rubberduck] retailcoder pushed commit e6e991d0 to next

Configure logging (#1563)

* clean up file statements (fixes #1487)

* consider members in supertypes as members of enclosing module (fixes #1489)

* remove temp fix (should be fixed with #1489)

* add support for Circle and Scale special forms (fixes #1498)

* fix foreign names (#1521)

* speed up all parsers by using the "two-stage parsing" approach

* reenable "function return value not used" inspection

* clean up

* fix typeof expression resolver

* configure logging

[retailcoder/Rubberduck] retailcoder pushed commit 1143a567 to next

Changes for #1225 ExtractMethodRefactoring (#1564)

* Wrap CodeModule. Add RD Interface

* Run AutoFormatter over file

* fix xml comments

* Add a method for ParseString for a module.

* Heavy refactoring of ExtractMethodRefactoring #1225.
Provisionally remove GUI interaction as it's not required. Add stubs for tests.

* Marker for needing to validate the MEthodName and increment.

* #1225 : Add checking for Multiple NewMethod signatures. Start work on ExtractMethodSelectionValid

[retailcoder/Rubberduck] retailcoder pushed commit 4688c804 to next

#1225 fix the IExtractMethod for InSelection (#1567)

* Wrap CodeModule. Add RD Interface

* Run AutoFormatter over file

* fix xml comments

* Add a method for ParseString for a module.

* Heavy refactoring of ExtractMethodRefactoring #1225.
Provisionally remove GUI interaction as it's not required. Add stubs for tests.

* Marker for needing to validate the MEthodName and increment.

* #1225 : Add checking for Multiple NewMethod signatures. Start work on ExtractMethodSelectionVal

[retailcoder/Rubberduck] retailcoder pushed commit 257257c6 to next: fix documents/forms not marked as default instance variables (#1570)

[retailcoder/Rubberduck] retailcoder pushed commit f4d7638e to next: replace debug with logger (#1571)

[retailcoder/Rubberduck] retailcoder pushed commit 71fcacdf to next: resolved conflicts

[retailcoder/Rubberduck] retailcoder pushed commit bd09a9b0 to next: added AsTypeName to the context-sensitive commandbar

Gareth

05:56

@ThunderFrame FSO... FileStream... I've used it often for massive files.

just read part, then replace, then write to a different file.

Gareth

06:49

hi @PeterMTaylor

@Chris a game ninja no less

nice

Peter M Taylor

Yo @Gareth nice to see you shortened your profile name. You look more causal. Shame we couldn't join you for a VBA housewarming though. :)

Zak

Monking @all

Peter M Taylor

Good late afternoon here heading home on the public train system. A good morning to you @Zak

Gareth

@PeterMTaylor thanks. Yes, was thinking about it.. could shorten further to @GnT

But T is the initial of my ex...

now @zak is nice and short but \

z is bottom left keyboard... you must have a nice strong left pinky @Zak

:_

:)

hi @zak

Zak

Hi @Gareth

Gareth

06:57

@PeterMTaylor last night I first went swimming. Then had to go over to the old house, empty out a ton of boxes, and vacuum the whole ground floor. This is england, so that isn't so big... maybe 4x6m^2

Peter M Taylor

Just a thought @ThunderFrame is loading a 60mb file crazy to upload into a database that either a c# or a temporary table with SQL queries could do? The end result that modified before writing to a file. Just a long way around than @Hosch250 suggests

Gareth

VBA can handle that with a filestream.

just read and write line for line. of if it's just a long string, read Byte Arrays

Day job calls. Cheers all

Zak

What is your day job?

Gareth

c#/sqlserver/excel developer. Currently working on Market Data system

and now learning MongoDB

Peter M Taylor

Nice.

Peter M Taylor

07:15

Well my train ride is over, may pop in for a browse later. See ya

ThunderFrame

07:35

@Gareth @PeterMTaylor it's driving me crazy. I'm throwing all my tricks at it, including file streams, chunking, byte arrays. My office just upgraded from 2010 to 2013, and everybody is getting out if memory issues. I'm beginning to think there's a buggy install.

@Gareth do you ever use the ADO Stream? Like FSO, except they work with binary too.

I'm going to try my code on 2010 tomorrow, and sanity check.

Peter M Taylor

07:57

As Frankie goes to Hollywood once said..."relax! Relax! If you want to do it!" I am sure you will find a solution. If your takin the ado approach, you're either a) likely you have this large file linked to to a OODB connection which either an SQL query could fetch those statements you a mid$ a lot. B) Or allow a loaded database can be resolved thru a number of easier SQL statements for VBA or c# processing for Xml statements

All the best on that one. 👍🏻

ThunderFrame

08:20

hmmm, no, the document is coming over XMLHttpRequest. I save a copy of the Response stream, but then I do replaces on a copy of the ResponseText.... I'll dropi it until 2010 gives me some hints...

Gareth

08:42

support.microsoft.com/en-gb/kb/2779852

classic!

if you ever get out of memory issues and are using excel in any of these ways....

LOL

including : copy and paste ??

ThunderFrame

11:07

LOL - yeah, I fixed a copy..paste issue the other day. Worked for years under 2010, didn't work under 2013.

Microsoft used to recommend 32-bit office, but I think they must have turned a corner at some point. It finally feels like 64-bit should be the preferred version...

I guess now that SmartIndenter works in a 64-bit host, 64-bit office should be considered the standard.

Peter M Taylor

11:31

oh sorry @ThunderFrame, right! completely mis understood, processing XMLHttpRequests from the browser into the program.

ThunderFrame

@PeterMTaylor I'm using the MSXML.XMLHTTPRequest from VBA, so no browser in sight.

FreeMan