VBA Rubberducking

Duga

12:01 AM

RELOAD!

[banane-io/PDB] 7 issue comments.

[rubberduck-vba/Rubberduck] 1 opened issue. 1 closed issue. 14 issue comments.

[Zomis/Games] 19 commits. 2 opened issues. 3 closed issues. 4 issue comments. 1063 additions. 116 deletions.

[Minesweeper] Games Played: 189, Bombs Used: 100, Moves Performed: 23945, New Users: 15

IvenBach

12:36 AM

Spending 15 mins tracking down a RT91 because you forgot to include the Set keyword on the implementing member. :derp:

IvenBach

12:56 AM

@IvenBach Knowing enough to solve the root cause? Priceless.

Mathieu Guindon

3:35 AM

current status: got a user in the identity db, closing in on getting authentication to work =)

took way to long to realize I still needed EF and a db

basically GitHub authentication works now, but the handler blows up with the identity database

FreeMan

10:59 AM

@MathieuGuindon close enough for government work?

this

12:39 PM

@HackSlash you should have gotten an invite. I did my best to get it to not depend on specific environment as much as possible. Note that it was built w/ VS 2019 + .NET Fx 4.8.

@MathieuGuindon Waitaminute why do we need a identity db? I was hoping that oAuth would avoid that whole thing...

Vogel612

12:50 PM

@this you need some way to keep track of who's using the site...

call it a "mapping" of accounts

it's just that the identity db doesn't contain a password/hash, but instead it has an authToken

this

isn't authToken supposed to be volatile?

Vogel612

yes and no

you get an authToken and a refreshToken

the authToken is intended to be volatile, the refreshToken can be used to obtain another authToken&refreshToken

this

OK, then I don't understand oAuth. I was visualizing authToken to be a session-based variable that would need to be renewed when you start a new session.

Vogel612

the refreshToken is much less volatile to allow for longer-running authentication across sessions if desired

that reduces the number of user-interventions for authorization

this

5:44 PM

Just wondering. Out of habit I've been using Private Type T<module name> with a Private This As T<module name> but it occurs to me that the name is insignificant since we really only use the This and only need one instance. Is there any reason to not just call it Private Type TThis + Private This As TThis ?

IvenBach

5:54 PM

Not that I can think of. I always declare mine as Private Type THelper and Private this as THelper.

Since they're all private there's no naming ambiguity.

this

for all modules?

IvenBach

Whenever I have a class with backing fields.

this

Yeah, I was blindly following examples where it was different/unique to each module and just thought of it now.

FreeMan

Private Type @this is my favorite. :) Yeah, it helps if your code does what it says and says what it does instead of some generic boilerplate...

this

Pretty sure you can't have @this as an identifier. ;-)

Granted but when you copy'n'create a module with the intention of modifying it and providing a new name, you have to remember to also rename the private backing type.

IvenBach

5:57 PM

I create it anew each time so I don't copy-pasta-faceplant.

this

Love "copy-pasta faceplant"

(it's also arguably a code smell if you find yourself copy'n'pasting a substantial amount of the module)

IvenBach

Made it better.

FreeMan

6:09 PM

I believe I've identified @this. It seems to work...

this

So... you're saying I'm smelly?

FreeMan

Sure. I can work with that.

IvenBach

@this Thanks again for educating me about fully belts-n-suspenders qualifying members. Eliminates naming ambiguity for sure.

this

smells--;

Mathieu Guindon

6:36 PM

@this I've started more and more consistently using TState for that UDT

or TInternal ...haven't decided which one I like better yet =)

this

makes sense.

TInternalState - straddle that fence!

Mathieu Guindon

lol

gets more duct tape

Freeflow

8:27 PM

For private types I typically use upto 4, namely Properties, State, Using and Base with the corresponding variables as p,s,u and b.

p is for external properties, s is for any class state that needs to be managed, u is for module/class level shortcut references used by two or more methods and b is for the predeclaredid should it need to be managed

I've assumed that the use of this is a hangover from those languages that provide it and as such is a mishmash of p,s,u, and b. Of course I may be totally wrong, but p,s,u and b seems to work rather well for me.

BZngr

8:55 PM

@this @MathieuGuindon I selected T<moduleName> as the default to avoid resx entries to translate "This" into the various languages (the module name is already translated). That said...All it takes is some new resource keys if there is interest is a different standard name.

this

@BZngr I'm not sure I follow - how are you using a resx in a VBA project?

BZngr

I thought the above discussion was about the Private Type name applied to track internal state. The UDT generated by EncapsulateField refactoring.

Not so?

this

ah, no, not the refactoring in specific; just the general naming convention for the variable

BZngr

ahh...Ok...well, now you know why the refactoring generates what it does. :)

this

Yeah

Duga

9:09 PM

[rubberduck-vba/Rubberduck] mansellan opened issue #5505: Add a Windows menu

> **Justification**
Increased discoverability of Rubberduck features.

**Description**
Currently, the various Rubberduck windows are accessible from many different locations. We should additionally provide a central collection of them, in the form of a `Windows` submenu from the RD main menu.

Resharper follows this (common?) design pattern:
![image](https://user-images.githubusercontent.com/25420409/83072048-ffaeac80-a065-11ea-88ea-7884aafd9daf.png)

The above entries are all available

[rubberduck-vba/Rubberduck] mansellan added label enhancement to issue #5505: Add a Windows menu

[rubberduck-vba/Rubberduck] mansellan added label user-experience to issue #5505: Add a Windows menu

[rubberduck-vba/Rubberduck] mansellan added label user-interface to issue #5505: Add a Windows menu

[rubberduck-vba/Rubberduck] mansellan added label difficulty-01-duckling to issue #5505: Add a Windows menu

mansellan

@Duga Seems like lately, I open far more issues than I close :-(

HackSlash

9:24 PM

@this, Thanks for sharing the AddinManager. I've been looking at it and I don't understand how it's supposed to function. The registry stuff and thepost build script is all very confusing. How far did you get with this project? I can't tell where you left off.

Duga

[rubberduck-vba/Rubberduck] mansellan removed label user-interface from issue #5505: Add a Windows menu

this

it is able to call the OnLoad ribbon and thus load the custom ribbon.

However, you need to manually run the registry script to register the COM stuff

You should be able to use the Start to start an instance of Access in debug, and it should hit a breakpoint on the OnRibbonUILoad procedure.

HackSlash

Wow! Thanks! So I don't need to write my own registry keys? The ones in the template will work? It seems to be missing one reg file and one bat file...

this

9:39 PM

what reg & bat?

it's in Registry folder

HackSlash

Oh, it builds the reg file so the bat I am missing is "UpdateRegistryScript.bat"

this

no that's a debris

I meant to get rid of it, sorry

HackSlash

k

this

To clarify, the version you have right now, it doesn't actually run the registry script on build

so you need double-click the .reg file to import the keys

HackSlash

So the postbuild event makes the reg file. Then installation is importing the reg and regasming the dll?

this

9:42 PM

right now, there's no "installation" yet

so you double-click reg file once. Then afterwards, when you build & run the project, it'll just work

the reg file would not change between build normally but it'd have to be if we added new COM types.

HackSlash

9:53 PM

Hmm, the Debug\\AccessAddinManager32.tlb files are certainly not there after a build

this

you're on a x64 system, right?

HackSlash

yeah, x64 is missing too

Feeds

Hello and welcome to Millibar Millibarn Attometer, an advice show for the Planck era.

this

Hmm I'll be committing a change shortly

reg.exe is pretty dumb

HackSlash

10:08 PM

#BlameMicrosoft

My goal is to poll all installed addins, then search them for ribbons and load any ribbon found on an Access Addin project. However, I can't find any way to list installed addins. Excel has an Application.Addins collection where Access does not...

That way, this wrapper could be installed with any Addin and it works for all of them

I might have to just search the per user addin directory

this

~~It's Application.COMAddIns~~

Spoke too hastily

HackSlash

I looked at that. That is for COM addins, like the wrapper. Not for ACCDA files loaded in the "Add-in Manager"

yup

this

IDK if htis will help: Application.WizHook.OfficeAddInDir

HackSlash

That is very interesting indeed! Undocumented features! #BlameMicrosoft

this

10:26 PM

yeah i've (ab)used Wizhook here and there

@HackSlash I think I got it to work now

try and pull from latest.

the latest now will automatically unregister, then update the type library and then re-register the registry keys as a part of the build

HackSlash

ok, thanks!

I've never seen WizHook before. There are a ton of strange things in there.

this

Just as a FYI - you can choose to use Regasm but I didn't because Regasm stupidly writes to the HKLM and the registry script writes same keys but to HKCU instead.

google Access Wizhook documentation - there's a PDF floating out there

HackSlash

Oh! That is nice. I do like per user

yeah, I've tried some of the things in the pdf and none of them have worked so far

this

you did set the key in wizhook first, right?

some of functions won't work unless you set the key

HackSlash

That is some blackmagic

this

10:34 PM

Not really. It's just security through obscurity. I guess they didn't mean for everyone to use the WizHook which was just for internal use but of course, some one got too nosy and figured out the "key" and voila!

HackSlash

So is there any benefit to using the wizhook addindir over Environ("AppData") & "\Microsoft\AddIns\"

?

this

Honestly, I don't know. If it was possible to some how customize the Addins path, then it might be possible that the Wizhook will give more accurate answers.

Do they yield same result? Haven't tested.

HackSlash

Yeah, I am wondering if maybe it changes in different versions

They do in my version

but I'm not testing on old versions, that other users may have to deal with

this

OK. So, my thought is that it'd be both a way to expose ribbon and to improve the addin manager.

Right now the built-in add-in manager is utterly broken and stupid.

I know some are limping around by launching Access in admin mode, installing the add-in, then restarting Access normally because you can't install new ACCDA in user mode. (I bet it's all down to the fact it wants to write to HKLM but can't unless it's privileged)

HackSlash

I am, in fact, installing ACCDA for user mode in our Version Control project

this

10:41 PM

so I was envisioning this would replace the stupid built-in and give them a way to install ACCDA per-user, without all that admin escalation bnllsh!t

HackSlash

I just use HKCU

I can install my wizard menu stuff per user this way without admin

this

Right, you wrote your own reg script or something?

HackSlash

This is what I am working on: github.com/joyfullservice/msaccess-vcs-integration

Yeah, he decided to use IWshRuntimeLibrary.WshShell

Which has registry functions in it

He was doing the admin song and dance and I just fixed that with my last PR

this

right. That works.

But that's about fixing the add-in manager itself. We're discussing about discovering the ribbons.

HackSlash

It works fine the way it is but I want a ribbon

So what did you want to fix about the add-in manager?

this

10:44 PM

The reason why I bought up the add-in manager is because I don't know if AddIn folder can be used per-user and I don't want to deal with that, either, so I was thinking the manager would scan both the addin folder and its own Addin folder to discover ribbons

HackSlash

Hmmm. Well then maybe you could benefit from what I just recently got working!

`Private Function GetAddinRegPath() As String
GetAddinRegPath = "HKCU\SOFTWARE\Microsoft\Office\" & _
Application.Version & "\Access\Menu Add-Ins\"
End Function`

That is where I am storing my addin and it's working

this

doesn't that just add to the AddIn menu?

rather than expose its own ribbon tab?

(also that can be added to the .reg file)

HackSlash

Right, still need a fix for the ribbon. That is where the COM add in comes in

this

Anyway ---

HackSlash

So now you see my master plan. Scanning the HKCU addin folder and loading all ribbons

this

10:47 PM

Once it finds all files that has a USysRibbons table (I know there are other ways to load ribbon but for now, KISS and limit to searching for USysRibbons specifically), we'll need to use XPath query because I assume it is not legal/possible to change ribbons on demand, so we need to basically merge all ribbons from different files.

HackSlash

You can load all ribbons on demand as long as they have their StartFromScratch = False

this

so we can only extract the certain elements from the ribbon XML and incorporate it into one master ribbon.

That's the thing, I'm not sure how you'd add a ribbon after the fact....

HackSlash

I have code for that

one min

this

from the COM AddIn, I mean.

public string GetCustomUI(string RibbonID)
{
    return Resources.RibbonUI;
}

AFAICT, this is the only opportunity for the COM addin to supply its ribbon.

HackSlash

I would do it the same way I do natively, using interop

this

10:50 PM

One more other point - how do you load a ribbon that is not specifically asked for?

HackSlash

'---------------------------------------------------------------------------------------
' Procedure : LoadRibbons
' Author    : Adam Kauffman
' Date      : 2020-05-20
' Purpose   : Load all the ribbons defined in this database
'---------------------------------------------------------------------------------------
'
Public Sub LoadRibbons()
    On Error GoTo ErrorHandler
    With CurrentDb.OpenRecordset("USysRibbons")
        .MoveFirst
        While Not .EOF
            Application.LoadCustomUI .Fields("RibbonName").Value, .Fields("RibbonXml").Value

Fairly simple

this

hmm. I suppose you can call that in the COM Addin --- _application.LoadCustomUI should work still.

HackSlash

Correct, So instead of CurrentDB we would pass in the project of the found addins

this

still do not see how that handles the "main" ribbon where they want to add a tab or whatever

HackSlash

The ribbon XML dictates where the ribbon goes.

As long as they all have unique names they would show up as different tabs

this

10:53 PM

Obviously, but only one can be in use at a time?

HackSlash

I don't think so. I know you can load all sorts of customUI from different providers. We would have to test it

this

in order to have that, I don't think you can have 2 different XMLs

it has to be one merged XML

HackSlash

It is only if someone sets StartFromScratch = True that you have a problem. That ribbon would kill the other ones

this

which is another reason why we'd need XPath -- to ignore the original value of startFromScratch

since in this context, startFromScratch simply doesn't make sense

HackSlash

Right. A user who wants to lock down their UI with a start from scratch ribbon will want to hide us anyway

Maybe we should let them do it

Here, have a footgun

this

10:57 PM

I don't follow - they want to provide an add-in and in process lock down the design environment?

I guess because in my mind, an Access add-in are for other developers, not for users.

so it just doesn't make sense.

HackSlash

That is the purpose of startfrom scratch. It overrides other stuff to provide a custom user experience

this

and that is perfectly fine when you're delivering an application, but not an add-in

HackSlash

The addin loader shouldn't care. It should just load whatever it finds\

I agree with your sentiment

this

sure it can load whatever it finds but in order to get that layout I described above, we'll have to merge all ribbon XMLs into one big XML

in which case, we'll have to ignore the startFromScratch anyway

HackSlash

Well, we can test that. As soon as I get this working at all.

It's end of day for me

this

10:59 PM

OK!

to confirm - were you able to get it to build and register?

HackSlash

I think? It runs access on start but I still haven't hit any breakpoints.

I put breakpoints OnLoadRibbonUI and OnConnection

mansellan

@this is there a valid use-case for distributing a compiled Access database without worrying about installing runtimes? Like in corp environments where you know the base image includes a runtime?

HackSlash

Hmmm, I see the registry entries

I see the TLB

mansellan

(sorry to interrupt!)

HackSlash

Is ok

@mansellan I would think it totally depends on your deployment model. If you know the client already has Access then you leave it up to their IT dept. I wouldn't presume it is safe to install a runtime.

Maybe make it an option and ask the client?

mansellan

11:12 PM

No I mean is there value in just sending out an (updateable) .mde and hoping it works. I suspect in some environments that might be all that's needed?

Because you know that everyone that receives it will have the correct runtime

HackSlash

They either need office installed or the runtime. I would think that most people have office installed and don't need the runtime

mansellan

(just trying to find out what the MVP looks like...)

@HackSlash IIUC many Office SKUs don't include Access?

But maybe they include the runtime?

HackSlash

Oh, yeah. Maybe peasant SKUs don't... Who is the userbase?

Maybe just include a link to the runtime next to the download.

Tell them, you can use this with the free runtime! (Some assembly required, batteries not included)

Or you need to make an installer with a dependency check

mansellan

I think I'm not explaining well. To solve for the general case of distributing Access, you need to allow for the fact that maybe the target knows nothing about Access, so you need to install stuff. Either by telling them to, or doing it for them.

HackSlash

ok, so the installer with dependency check is what you want

Only installs runtime when needed. Then puts the DB in the user profile, makes shortcuts so they can find it. Etc

mansellan

11:17 PM

But is there is an easier case where you know the pre-reqs are there, and you can just ship them an MDE? I ask because it's been ~10y since I last did Access dev.

HackSlash

Do you know the pre-reqs are there? Or do you think they know nothing about access? Because like I said, you can do either. You can give them a link to the runtime or you can make an installer that does it for them

Or both

Give them two download options. installer or just file

mansellan

Sorry, context: I think I can ship Access databases (especially mde's) from my ClickOnce packager. It's not a standard installer, it's sandboxed. So pre-reqs are... tricky.

Not impossible, but I'd like to tackle them later.

HackSlash

I started to make a click-once installer until I found this: peterssoftware.com/aps.htm

Now I use that for deployment. (A heavily modified version)

It requires that you already have access install figured out

like I said, that is for the IT department

mansellan

I made a clickonce packager, for command-line and Azure DevOps.

HackSlash

Azure DevOps... and Access?

mansellan

11:22 PM

Sure, it can package any executable.

HackSlash

Like your front end is Access and your BE is in the cloud?

oh ok

mansellan

No, more like you can send your MDE into ADO and have it package and distro it.

BE would be whatever it is atm

HackSlash

Well, I think you can do prerequisites with clickonce if you want: docs.microsoft.com/en-us/visualstudio/deployment/…

mansellan

Yep, it does pre-reqs. But it has no definitions OOTB for Access, I'd have to write some. And @this scared me with how many options are available for that...

HackSlash

Yeah, not sure if this left but I need to go. I'll be back tomorrow!

mansellan

11:26 PM

OK thanks

Quack Exchange

1

Q: VBA Code for Updating Various Work Bookmarks?

I am relatively new to VBA and have watched a fair few tutorial videos/read a couple blogs to start getting my head around it. I've tried to write/edit my first code for work using example code from this page https://www.datawright.com.au/other/word_bookbark_vba_code_sample.htm and also a couple ...

Transcript for