VBA Rubberducking - 2018-07-11 (page 2 of 2)

« first day (1496 days earlier) ← previous day next day → last day (1684 days later) »

6:00 PM

There is another POV, though... If one's employees went to such length of circumventing the security.... should they be employees to begin with? This may end up as more of a HR issue than a technological issue.

6:10 PM

Any idea why GoalSeek is a hidden member of Range?

Mathieu Guindon

gosh I don't know how to say it anymore

@rholdren if "the auditor" and "the supervisor" are two different persons on two different computers that potentially have the same file-on-the-network opened, then you're trying to determine whether Application.Workbooks contains a file that someone else has opened, and for one more time, you can't do that. See if co-authoring features can help, or research how to make the workbook a shared workbook. — Mathieu Guindon 1 min ago

Is co-authoring the non-corrupting option for sharing workbooks?

I have bad memories of shared workbooks getting corrupted.

it's more than that. In theory, you can co-author on excel web and other on desktop excel.

Mathieu Guindon

@IvenBach co-auth is bloody amazing

and yes, shared workbooks suck

@MathieuGuindon .. unless you have VBA behind the sheets... ;-)

Mathieu Guindon

6:17 PM

if it's on O365, you don't :)

I've not tried co-authoring anything. It still seems like it would be stepping each others cells.

Mathieu Guindon

@IvenBach you literally see in real-time who's doing what where

it's like playing Call of Duty on a worksheet

@MathieuGuindon exactly. It runs on desktop but not on web. Oops! it's all screwed up! Ha-ha!

Never got into CoD

Mathieu Guindon

WoW then

6:18 PM

If the code behind are all javascript, then you're probably OK.

Never did WoW...

which is really why they chose JS in first place. Runs anywhere.

Mathieu Guindon

me neither

Is it like Quake, Doom, Wolfenstein?

I was more into MUDs and MOOs than anything else.

Mathieu Guindon

@this exactly. Excel on Android -> works. Excel on Mac -> works. Excel on Windows -> works.

@IvenBach IDK what MUD and MOO are, but the point is, you literally see other users' selections and changes as they're made

cross-platform

it's.. huge

6:20 PM

Sounds like its viable.

Mathieu Guindon

@IvenBach 1drv.ms/x/s!ApWSq7e7QUvmiT1Q2hwOrH_Nc99c

guess you never used Google Sheets.

that's where MSFT borrowed the idea from.

Mathieu Guindon

@this no one uses Google Sheets

:p

eeeeh. you'd be surprised. :(

@this Only briefly in college. It lacked any VBA so I couldn't PowerUser my way through problems.

6:23 PM

[rubberduck-vba/Rubberduck] Imh0t3b synchronized pull request #4171: German translations for Autocompletion Settings + Issue #4016

but they did have that co authoring feature for years

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) edited comment on pull request #4171: German translations for Autocompletion Settings + Issue #4016

Used it only to share info.

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4171?src=pr&el=h1) Report
> Merging [#4171](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4171?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/debf4a6bc809b79fa70428de598b6913b068905d?src=pr&el=desc) will **decrease** coverage by `0.01%`.
> The diff coverage is `20%`.

```diff
@@ Coverage Diff @@
## next #4171 +/- ##
==========================

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) edited comment on pull request #4171: German translations for Autocompletion Settings + Issue #4016

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4171?src=pr&el=h1) Report
> Merging [#4171](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4171?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/debf4a6bc809b79fa70428de598b6913b068905d?src=pr&el=desc) will **decrease** coverage by `0.01%`.
> The diff coverage is `20%`.

```diff
@@ Coverage Diff @@
## next #4171 +/- ##
==========================

Mathieu Guindon

@this cross-platform?

or just in browser?

6:25 PM

in browser

IDK if they even have desktop version. I've not used it for a while but that feature did exist years ago

i wouldn't be surprised if that's why Microsoft went "welp, time to do everything in JS"

Mathieu Guindon

/typescript

JS = a pig; TS = a pig with lipstick

[rubberduck-vba/Rubberduck] Imh0t3b commented on Rubberduck.Resources/Settings/SettingsUI.de.resx of pull request #4171: German translations for Autocompletion Settings + Issue #4016

Mathieu Guindon

$("#run").click(() => tryCatch(run));

async function run() {
    await Excel.run(async (context) => {

        OfficeHelpers.UI.notify("Your code goes here");

        await context.sync();
    });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
    try {
        await callback();
    }
    catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
    }
}

not too complicated actually

^ default template from ScriptLab O365 add-in

it's literally HTML+JS

user image

Interesting.

Mathieu Guindon

6:32 PM

<button id="run" class="ms-Button">
    <span class="ms-Button-label">Run</span>
</button>

ha, they integrated it with GH

user image

I need to play with this some time

that would be a good reason to install VSCode I guess

(editing in that tiny non-sizeable pane is annoying)

don't get me wrong - building JS API makes total sense. I'm just not so thrilled mainly because JS as a ecosystem is awful to work in compared to C# or even VBA.

Mathieu Guindon

yeah but it being TypeScript, IIUC, means you don't deal with the "flavor-of-the-month" framework craze

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

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) edited comment on pull request #4171: German translations for Autocompletion Settings + Issue #4016

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4171?src=pr&el=h1) Report
> Merging [#4171](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/4171?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/debf4a6bc809b79fa70428de598b6913b068905d?src=pr&el=desc) will **decrease** coverage by `<.01%`.
> The diff coverage is `20%`.

```diff
@@ Coverage Diff @@
## next #4171 +/- ##
==========================

[rubberduck-vba/Rubberduck] build for commit db533ffc on unknown branch: 52.44% (target 0%)

Mathieu Guindon

@IvenBach is it not letting you in?

17 mins ago, by Mathieu Guindon

@IvenBach https://1drv.ms/x/s!ApWSq7e7QUvmiT1Q2hwOrH_Nc99c

That part, I'm not so convinced. Also, it's not just the flavor of month that's the problem. The problem is also with the build process & tooling. You have lot of stuff layered in order to have a maintainable code to the point that if something goes wrong, you're miles away from the real cause.

6:40 PM

@MathieuGuindon Didn't click it,

Mathieu Guindon

TBH the real problem is that folks developing this have no idea what people do with VBA

IDK. Web API is totally a different world. You can't just go "hey, email this data" in that world.

You gonna use some other API to set up email, yet another API to retrieve the document from a shared repository (e.g. OneDrive), to do the equivalent of what is already accessible in Windows' filesystem & Outlook OM.

Mathieu Guindon

a number of assumptions shatter though.... if you're online, you don't need to email anything if your recipient can access the document with just a URL

^

yes, that, too

do you know if data connections work on the online version of Excel?

Mathieu Guindon

6:53 PM

hmm, haven't even tried adding a connection

I think they don't

wait no they might

there's a "refresh connection" and "refresh all connections" button in the DATA ribbon

realized that would make it much more useful than passing around a Excel spreadsheet (which is kind of weird when you think about it)

Mathieu Guindon

...but I can't see how to add a new one

want the report on latest data, go there, see the pivottable, done.

need to experiment.

Mathieu Guindon

it can connect to Azure

right makes sense that public accessibiilty is implied

for on-premises, that's not a problem if you set up a gateway

but obivously means getting your IT to sign off

Mathieu Guindon

6:56 PM

yeah

Azure-hosted is probably a better idea though

docs.microsoft.com/en-us/teamblog/…

Mathieu Guindon

8:02 PM

> Is there a way to activate the drawing tool but not actually draw it and allowing the user to complete the rest of the process themselves?

uh, yeah, let the user click the Ribbon button for it

#SemiAutomatic #VBA

8:39 PM

-1

Q: Macro that combines data from multiple worksheets

I have created a macro to combine data from different excel sheets into one final report. As it is obvious from the code provided below, there are very clear steps in the code. The code has no issues (it runs as intended) but it takes so long to execute. I have written many macros similar to this...

[rubberduck-vba/Rubberduck] carlossierra311 created comment on issue #4174: False Positives for Builtin Functions and Constants

> I just downloaded the [latest release](https://github.com/rubberduck-vba/Rubberduck/releases/download/Rubberduck-v2.2.0.3468/Rubberduck.Setup.2.2.0.3468-pre.exe) and the problem persists.

Version 2.2.0.3468
OS: Microsoft Windows NT 10.0.17134.0, x64
Host Product: Microsoft Office 2016 x86
Host Version: 16.0.10228.20080
Host Executable: EXCEL.EXE

[RubberduckLog.txt](https://github.com/rubberduck-vba/Rubberduck/files/2186128/RubberduckLog.txt)

@Duga that may be of interest to @mansellan I think it's similar to the ones he was seeing w/ VB6?

Yeah the COM collector is, uh, sporadic. For Common Dialog for example, doesn't collect any of the CoClasses... Not vb6 specific, seems to be certain libs

Need a few clear hours to investigate

Mathieu Guindon

might be related to the crash-at-startup I'm having lately (disabled RD for now)

8:55 PM

what I don't get is why now?

one'd think that with common dialog controls, the issue would ahve been long ago reported way before vb6 was even a gleam in the eye.

Mathieu Guindon

I'm crashing at startup on a blank Excel project, default libraries

default config too

hm. i think that'd be something else altogether.

personal workbook? other Excel add-ins? Power Query?

Maybe related to an Office Update?

@this yeah i'm starting to suspect that something has regressed. Gonna try some earlier builds to confirm.

we have zero tests for com collector?

9:08 PM

Idk, afk atm

codecov seems to say no. it's one big red spot o the parsing project.

Mathieu Guindon

@this and how do you test that?

I'm thinking the only reliable test is F5

write up some COM library, then assert that you found the objects.

Mathieu Guindon

and that ensures MSForms works?

@this last I checked, the number of declarations in Excel, alone, surpasses most VBA projects by about 20 to 1. If anything needed tests, it would be that :-/

9:12 PM

Technically that's more of an integration test but still pretty much automatic

No, we just need to write COM library that simulates the aspects we need to cover.

Mathieu Guindon

it's the VBA standard library that isn't processing

IMO F5-testing this is much more reliable than writing a bogus COM library

what's the difference?

hopes it isn't one of my PRs that broke it

it's basically parsing the COM metadata. You don't need to F5 to do that.

Huh, I thought we already found blame?

9:14 PM

No i'm not saying that it's the reason. I'm just observing that it's one big red spot in the whole parsing project.

@ThunderFrame wait wut?

The COM collector definitely needs some refactoring.

Currently, it is basically one big blob.

@this sure, but I think what @MathieuGuindon is saying is that we're parsing other libraries just fine. So having a test library is moot, unless it replicates the problematic part of VBA library

and given that project is about parsing, it hardly matters whether we are using a real COM library or bogus library as long the unit tests cover all different, especially the counterintuitive aspects of the metadata.

@ThunderFrame well we have the issue already reported.... so that'll be the first unit test, no?

This will be needed anyway when we start to use the typelib information in the parsing process.

9:16 PM

@mansellan I seem to recall you and @M.Doerner discussing it. I forget who took blame.

Ah no. One of my PRs was in the vicinity, but ruled out.

@this do we know which declaration is the problem? Seems to me like one fails, the library fails, so we don't yet know the problem

Mathieu Guindon

@ThunderFrame I'm not sure about that either. last I saw, none of the COM libs works... so @this might actually have a point

@ThunderFrame in at least one case, @mansellan managed to get declarations without names. That'd be one good unit test.

@MathieuGuindon hmm, could be. Once VBA fails, the others would present bad resolver results anyway.

9:19 PM

In the other case, yes, we'd have to diagnose the cause and then write a unit test before modifying/refactoring the com collector.

@this Yeah that's still a thing. I think the typelib API has a problem when some attributes are available but not others. Suspect OleWoo may be parsing the IDL directly instead of going via the API.

@this the permutations of COM declarations is vast, and you'd need to write the TLB in C++, IIUC, as you can't create COM standard modules in .NET.

But still new ground for me so may be entirely wrong.

@mansellan Last time I checked, OleWoo does not have the magical ability to pluck a COM library's IDL from nothing.... ;-)

IDL is to type lib what source code files are to the output file, basically.

@mansellan there's an unofficial TLB reference in the Books/articles Wiki

9:21 PM

Not seen the source, was guessing it decompiled it...

But sounds like I'm wrong :-)

@ThunderFrame I think we can just save IDL files and compile them into TLB without any implementations.

I started working through it a few years ago, before I started learning C#, so it was hard work in VBA.

@this sounds much simpler

@mansellan not really - more like it simulates the possible IDL output based on the metadata available via the type lib

But still a lot of attributes and aliasing and declaration types and Types.

Ah ok

9:24 PM

which is also why you may get different result between OleView and OleWoo, BTW. They are interpreting same type library differently.

@ThunderFrame the thought is that when we encounter a problematic defintion, we copy from that library's IDL (as inferred from OleView/OleWoo), mock up the GUID, add to our IDL and write an unit test around that.

We need not bother with implementation at all. (I hope!)

@this I remember reading somewhere that OleView is the result of years of fine tuning, and it's the official tool. OleWoo has no doubt had many releases too, so it's not trivial to get it right.

@this copy/pasta is my preferred productivity approach

@ThunderFrame I don't doubt it. That's why I had to tweak the OleWoo a bit more before I went using it for our build process.

It's much closer now, but I can't say how close.

Maybe, we could try whether removing the caching of data from the references again stabilizes the COM collector. It seemed to work when the test whether we already know the data were the wrong way around. However, I have no issues in my test projects. So, I cannot test this myself.

was caching something we added recently?

@M.Doerner worth a try, but I already did try it the other way around with no success. Always worth verification though.

@this been there forever, but with a bug that meant it was almost no-op. Filed a tiny pr to correct that.

9:34 PM

oh ok

Trouble is it seems to be the only thing anywhere nearby thats been touched recently

9:57 PM

@mansellan OleWoo is open source - you can see exactly what it does ;-).

I was planning on taking a look at it when I get home this evening. I'm thinking some obscure regression - this used to work just fine.

@Comintern yep, just haven't gotten around to it yet :-)

10:14 PM

[rubberduck-vba/Rubberduck] zspitz created comment on issue #344: Prevent infinite loops

> I've just lost 2 hours of work because of a forgotten .MoveNext while iterating over a recordset.

Crap. I completely forgot I still need to install Office in my VM.

[rubberduck-vba/Rubberduck] bclothier created comment on issue #344: Prevent infinite loops

> I'm sorry to hear that, but are you aware that you don't need Rubberduck to bail you out -- just use Ctrl + Break to break the infinite loop. That would let you get into the code, and either stop or move the execution to some other line and resume or even add MoveNext while in the break mode.

@ThunderFrame here's a improved version of DataVault that I think prevents access even from the immediate window, provided that it's housed in a project that's compiled. Gist Note that while the immediate can read the content of the vault, it can't get to the BaseAddress at all.

Of course, if you can put a breakpoint in the class module, then it's game over. But put it in a .*DE file or the equivalent, it shouldn't be possible (!) to dig at that information outside.

@Duga Smells of a gut-check that didn't get checked.

"This is taking a lot longer than I was expecting" would have been my first thought to check.

sadly, i've heard of some running processes that takes hours and think it's normal. :\

10:25 PM

[rubberduck-vba/Rubberduck] zspitz created comment on issue #344: Prevent infinite loops

> @bclothier Except that without DoEvents the VBE doesn't appear to respond to Ctrl+Break.

@Duga Hmm interesting. That hasn't been a problem; just tried it and I could break a loop without a doevents. But I'm on 2010....

^^ I've done this repeatedly.

(mostly by accident)

even on 2013/2016?

the linked SO thread seems to imply that 2013+ changed

By "this" I mean get trapped in a loop that ctrl-break won't exit.

Oh okay

i guess i must be lucky!

10:31 PM

The problem is that if the windows message queue isn't getting flushed, the VBE never gets the keypress event to precess.

IOW, it depends on the content of the loop.

It seems to be host specific and the tighter the loop the less chance of breaking.

in my test I only did Debug.Print Now() with no other instructions in a loop.

I know for sure if it was Sleep xxx, it would not be able to break until the Sleep has completed but usually holding it down works eventually.

Try it with a trash calculation instead and no output. Like maybe foo = 1 + 1. The Debug.Print might have an effect (it forces a VBE redraw).

Sleep takes the thread off the scheduler, so yeah - no time-slices to register the key-combo.

user image

10:41 PM

[rubberduck-vba/Rubberduck] zspitz edited comment on issue #344: Prevent infinite loops

> @bclothier Except that without DoEvents the VBE apparently no longer responds to Ctrl+Break in an infinite loop.

1 hour later…

11:52 PM

> ... design user interfaces that do their job so well no one notices them.

« first day (1496 days earlier) ← previous day next day → last day (1684 days later) »

Transcript for

Jul '1811

VBA Rubberducking

This chat has moved to Discord: discord.gg/MYX9RECenJ

antlr com-interop parsing rubberduck vb6 vba whiskey-tango-foxtrot

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile