VBA Rubberducking

Duga

12:00 AM

RELOAD!

[Minesweeper] Games Played: 191, Bombs Used: 137, Moves Performed: 21632, New Users: 41

RELOAD!

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

Feeds

1:27 AM

We're not not trapped in here with the coronavirus. The coronavirus is trapped in here with us.

Duga

4:54 AM

[rubberduck-vba/Battleship] RKattoula starred us

FreeMan

11:19 AM

After 6 hours, that's all we can come up with?

We've become a boring bunch...

Duga

12:13 PM

[rubberduck-vba/Rubberduck] MikeAndLisa created comment on issue #5423: Indent works on Current Module, but not on Current Project

> I think there is a workaround. Untick/Disable the Keys Ctrl M and the other items can be selected in the indent menu options.

Duga

12:58 PM

[rubberduck-vba/Rubberduck] daFreeMan created comment on issue #4883: Code Explorer sort options partially working.

> ```
Version 2.5.0.5394
OS: Microsoft Windows NT 10.0.17134.0, x64
Host Product: Microsoft Office 2016 x64
Host Version: 16.0.4924.1000
Host Executable: MSACCESS.EXE
```

I believe that I have just noticed a related issue - Items within a UDT are not sorted in the CE - they're listed in the order they're defined in the UDT in code _until_ I manually change sort order.

By default (and by my preference), `by Name` is selected as the sort order, and this is what I get for my UDT:
![202

[rubberduck-vba/Rubberduck] daFreeMan created comment on issue #5296: Explicitly sort CE Project nodes

[rubberduck-vba/Rubberduck] retailcoder closed issue #3332: RD needs a move to folder command

[rubberduck-vba/Rubberduck] retailcoder closed issue #5159: Drag and drop for navigation folders, please!

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

mansellan

1:19 PM

@Vogel612 do you know much about globbing patterns?

Duga

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot prereleased draft prerelease Prerelease-v2.5.0.5410

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot created draft prerelease Prerelease-v2.5.0.5410

[rubberduck-vba/Rubberduck] build for commit 5fec4adf on next: 61.15% (target 0%)

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot published prerelease Prerelease-v2.5.0.5410

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot created tag Prerelease-v2.5.0.5410

[rubberduck-vba/Rubberduck] build for commit 5fec4adf on next: 32.3% of diff hit (target 60%)

[rubberduck-vba/Rubberduck] build for commit 5fec4adf on next: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] build for commit 5fec4adf on next: 61.15% (target 0.00%)

Vogel612

1:39 PM

@mansellan define much?

mansellan

actually, nvm... I was trying to figure out a way to supply a list of named glob patterns as a command line param, for example: pattern1:*.dll,pattern2:(foo|bar).exe, and be able to parse it.

but I think it's too complex, I'm just gonna simplify my problem domain instead to avoid the issue.

Vogel612

usually you can chain globs with commas

so you'd have patterns=*.dll,(foo|bar).exe

though what you seem to have here is regexes

mansellan

yeah, the trouble is i need to end up with a Dictionary<string, IEnumerable<string>. It's too much for a CLI I think.

i.e. a dictionary of names, each with associated glob patterns.

Vogel612

welp

mansellan

It's too much for a CLI. Even if I could find something that worked, it'd be too confusing to use.

Hence, simplifyiing

I think the of people using still optional download groups in ClickOnce is ~0 anyway.

the VBE - it's right for me

1:50 PM

Is the parse tree viewer the best way to understand how declarations work?

FreeMan

@this all of a sudden:

I cheated and copied my git repository from my network to my local. I discovered that the .oasis file wasn't right, so I updated settings. I reopened Access and this.

the program directory (unless it means Oasis' program directory) is on my local C: drive, and I've triple checked to ensure that nothing's read-only...

this

I've seen that on one of computers - usually to work around it, Access has to be launched as an admin and then it works.

I assume that uninstalling & reinstalling may also fix the issue.

FreeMan

or, that could be its really bizarre way of telling me there's a new version...

I'm on 3.4.17 and 3.5.20 is available.

either way, I guess there's gonna be a new install. :)

le sigh....

this

no, on other computers, I don't get that. It updates normally

Only one it does that, but it's also Access 2007 on Windows Server 2003 (I know...)

Cyril

something about this doesn't look right... but Return acts as "exit sub", is my understanding:

this

2:02 PM

what?

FreeMan

well, that's what it seemed to be telling me. Installed the update & no issues.

Will try to keep an eye on it in the future.

this

The update runs when you shut down Access

at least that's if it's doing its normal thing, not that "can't write" warning

Cyril

```
'in workbook_beforesave
application.enableevents = false
if not saveasui then
    application.enableevents = true
    return
end if
application.enableevents = true

FreeMan

oh wait... In setting up OASIS, since it seemed to have lost many settings, I think that I'd checked the automatic install updates. I wonder if it was trying to do that and running into write access errors trying to DL the file...

It was on startup, not shutdown, though.

seems to be happy with it's write access as of now. Moving on with my life...

the VBE - it's right for me

@Cyril I can't follow what that is doing lol

this

2:06 PM

uh, that shouldn't be legal.

I would think it'd throw an error Return without GoSub

FreeMan

^ that

Cyril

i don't get an error with Return

the VBE - it's right for me

I don't even get what it is trying to do.

Cyril

if there's no saveas ui then it allows it to save; it exits the sub

FreeMan

RD Refactor question: I've just created a UDT that contains all the parameters I'm passing to a function. Is there a RD refactor that will change the 6 params to 1 param of the new type and change all references, or do I do it all by hand?

this

2:09 PM

couldn't it be because you have either On Error Resume Next or Application.DisplayAlerts = False ?

Cyril

the later use will be to modify the saveasui to go to a specific location, but until then, i still need "save" to function as intended, so am using Return

the VBE - it's right for me

So if there is SaveAsUI it wants to reject the regular save?

this

yeah, no. It won't work like that. That's a error, I'm pretty sure.

FreeMan

@Cyril if that's the before save event, then exiting your function should return control to the regular save event handler, no?

unless you specifically cancel the save event

it's not instead of save

the VBE - it's right for me

@FreeMan there should be!

FreeMan

2:11 PM

^ since I just ran into this, I can only agree!

Cyril

the literal code working, without problems:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    If Not SaveAsUI Then
        Application.EnableEvents = True
        Return
    End If
    Application.EnableEvents = True
End Sub

just "feels" wrong, and i can't put my finger on it

FreeMan

does it ever drop into the If <true> portion?

Cyril

@FreeMan yes, but events are not enabled before that, so macros will not run (nor UDFs, which is the biggest problem)

this

When in doubt, step through the code.

FreeMan

also, what the heck is that doing? you disable events, then conditionally enable events or you enable events

I mean, you enable events whether the condition is true or not. Why bother? I'm really lost...

why not:

Cyril

2:14 PM

maybe this will be more appropriate, as it's more clearly the intention:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    If SaveAsUI Then
        Me.Save
        Application.EnableEvents = True
    Else
        Application.EnableEvents = True
        Return
    End If
End Sub

FreeMan

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = True
End Sub

effectively the same

OH!!!! hard to debug partial code

this

That doesn't make sense still

the VBE - it's right for me

Replace Return with Cancel = True

Cyril

i was just focused on the "not saveasui" part, sorry freeman

FreeMan

so you're trying to prevent Save As by making it just a Save?

the VBE - it's right for me

2:14 PM

You are trying to reject the save.

Cyril

prevent saveas

this

4 mins ago, by FreeMan

it's not instead of save

Cyril

in the second code, i removed the "not"

the VBE - it's right for me

So if they are not saving through the UI just Cancel = True to reject it.

this

FYI - it's important to note that SaveAsUI and Cancel can be modified for different behavior.

e.g. SaveAsUI = False and Cancel = False => save without prompting

FreeMan

2:16 PM

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Application.EnableEvents = True
        SaveAsUI = False
End Sub

the VBE - it's right for me

Wait lol does that mean he could just set SaveAsUI = True to force the prompt?

FreeMan

Always enable events, always cancel Save As let it continue to process Save

this

Yeah, sure.

Cyril

yes, @theVBE-it'srightforme

this

TBH, I don't understand why we need to monkey with Application.EnableEvents

The fact that we are, makes me think it's not used the way it ought to be used.

FreeMan

2:17 PM

ass-u-med that it was disabled previously & needed to be enabled on save

Cyril

i had an issue where the saveas prompt kept spamming if someone exited the host, prompting the "do you want to save" dialogue box

the "yes" kept reopening that dialogue box, until you hit cancel or no, then it would perform the save

once i disabled events, it allowed me to circumvent that unintended behaviour

this

OK, I don't think that should be necessary

Keeping in mind I'm not an Excel expert but I'm pretty sure all you want to do is just set SaveAsUI = False

then voila, no prompts

and it gets saved

the VBE - it's right for me

He doesn't want saves unless there is a prompt?

Cyril

@this got the intent... no saveas, just save

the VBE - it's right for me

If SaveAsUI Then
Me.Save

I read that as only save if it was saved through the SaveAs prompt?

this

2:21 PM

No.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        SaveAsUI = False
End Sub

That's it.

the VBE - it's right for me

classic vba

this

wait wait

Cyril

in using I still get the prompt:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    SaveAsUI = False
End Sub

Just tried this and still get the prompt, as well:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    SaveAsUI = False
End Sub

this

it's a ByVal

the VBE - it's right for me

oh lol

this

2:22 PM

Gosh, why do I remember SaveAsUI being a ref?

the VBE - it's right for me

so if that is the case he just wants to set cancel to true instead of return

isn't it?

Cyril

technically i could put this into the WorkBook_Open():
`Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False`
the "enable events thing will still be an issue, regardless (can always saveas without enabling events)

the VBE - it's right for me

I'm still really unclear as to what behavior you want to happen.

this

Ahh, Word's is byref

Cyril

@theVBE-it'srightforme intent: allow a workbook to only be saved, not allowing saveas.

this

2:24 PM

Excel's is byval

the VBE - it's right for me

now that is classic office vba

this

Therefore:

the VBE - it's right for me

@Cyril oh...so yeah if SaveAsUI is true then set cancel = false

and worry about enable/disable events in workbook close not workbook before save

(waits to be wrong)

this

@Cyril if you use Application's WorkbookBeforeSave instead, does cancelling and save avoid the UI?

Cyril

@theVBE-it'srightforme just tried that above with the last blurb, right before This stated it's ByVal, not ByRef

@this will try with just cancel=true

this

2:31 PM

I think you still have to sae the workbook itself, even though you are canceling it.

Cyril

oh mother... yes, that worked, ben... i was muddling with the saveasui which appears to not be necessary

this

If SaveAsUI Then
  Wb.Save
  Cancel = True
End If

This would allow normal save operation to happen when it's not via UI

the VBE - it's right for me

That also lets you save the workbook wherever you want programmatically without having to use the saveas ui.

Cyril

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
End Sub

and yes, can use the "if saveasui" to dictate a particular location, @theVBE-it'srightforme

the cancel = true still allows me to save, just prevents a saveasui from showingup

the VBE - it's right for me

Yes it just completely rejects the user save inputs and lets you handle it yourself.

Cyril

2:35 PM

fun times

the VBE - it's right for me

@this did you see my question on understanding declarations?

Cyril

thanks all for helping fix that issue

the VBE - it's right for me

:thumbsup:

this

@theVBE-it'srightforme Sorry, had plenty of fun. :)

Parse tree analyzer helps you understand how the grammar is structured

There is some correspondence between the grammar and the declaration so it may be helpful.

Exactly what did you want to understand?

the VBE - it's right for me

How declarations are structured I guess.

this

2:37 PM

But there's not really a structure?

we have them in one big bucket and they are more or less an unit of their own

the VBE - it's right for me

I mean, like, how many declarations go into this line of code:

Sheet1.Rows(2).Interior.ColorIndex = 1

It is contained in like Sub Foo1()

this

I see three.

Sheet1, 2 and 1

the VBE - it's right for me

I get there is a declaration for the module that holds Sub Foo1() and then a declaration for Sub Foo1() but it's opaque after that.

this

basically if it's a variable or a literal, then it's a declaration

actually i should clarify -- the Sheet1 in this code would be an IdentifierReference

since the Sheet1 is already declared somewhere else

Dim x As Long
x = 1

This code snippet above has 2 declarations. That's the x in the Dim line.

then we have 1 as another declaration (a literal constant)

The 2nd x counts as a IdentifierReference, which refers back to the Declaration of the x from the Dim line.

the VBE - it's right for me

okay I think this makes sense

declaration should be taken literally - it is where the identifier is declared in the VBA syntax as a unique thing

this

2:46 PM

Yes. IDK if you already read the inside rubberduck article

that might help

the VBE - it's right for me

so that is why resolver passes once to get all declarations and then a second time to get all references?

the one on the blog?

I don't think so?

this

it's 2 part articles

rubberduckvba.wordpress.com/2017/07/05/inside-rubberduck-pt-1

the VBE - it's right for me

reading

this

rubberduckvba.wordpress.com/2017/07/19/inside-rubberduck-pt-2

the VBE - it's right for me

the second one is similar to the wiki article on it

(?)

this

2:50 PM

Yes

i think wiki came after, not sure but the 2 articles were more of a big picture; wiki has some more details.

mansellan

TFW you're adding more TODO markers than lines of code...

Cyril

'//TODO add note about plan

the VBE - it's right for me

@mansellan outlining is an essential part of the creative process!

this

//TODO: add more TODOs

the VBE - it's right for me

@this second one reads closer

at any rate I think I kinda get it now

I guess the only thing I would need to ask is if declaration finder works with identifier references

but I would assume that is a yes

this

2:54 PM

Hm, Declaration has a IdentifierReferences collection

I can't remember off the cuff if the finder does any finding based on the references.

the VBE - it's right for me

oh well nvm that is even easier

FreeMan

TFW you jumped into coding without making sure what the heck branch you were on and without pulling code into Access.

#WhereTheHeckAmI????

the VBE - it's right for me

I guess I remember seeing that now that I think about it and not knowing what it meant.

@FreeMan version control = effort

FreeMan

version control = changing old habits

FreeMan

3:24 PM

@this - here we go with the OASIS questions again...

I just imported all source. I click import again and it shows nothing is different.

I click export and it shows everything needs to be exported. Is this because of the difficulty with the dates?

I do have Use object-date as filedate unselected.

Ah, your recommendation is to have that enabled. tries that

still shows everything needs to be exported

FreeMan

3:48 PM

I'm also getting this in my <report>.def files:

despite having Delete 'NoSaveCTIWhenDisabled' set

mansellan

3:59 PM

hey @this, you about?

FreeMan

4:18 PM

Private Type TmyUDT
  foo As String
  bar As Long
End Type
Private Function fFooFoo() As TmyUDT
  Dim udt As TmyUDT
  udt.foo = "Foo"
  udt.bar = 7
  fFooFoo = udt
End Function
Private Sub sFooFoo(ByRef parm As TmyUDT)
  parm.foo = "Foo"
  parm.bar = 7
End Sub

The Function to set these makes more sense than a Sub with a ByRef parameter, does it not?

BZngr

4:31 PM

I would agree. That said, a Function that passes Foo and 7 in as parameters looks better (to me). Without params, fFooFoo looks like it should be a Property - but there is no UDT field to return.

IvenBach

Is there any way in VBA to set where a page break occurs? I can't figure it out and manually setting them is eating up time I need to apply elsewhere.

FreeMan

@BZngr This was an MVCE - I am passing a couple of params to the real function to determine what to populate myUDT with

BZngr

^

FreeMan

@IvenBach in Excel?

I've got a code example iffen ya wants one

@BZngr tyvm, btw

IvenBach

I'm setting the Worksheet.PageSetup.PrintArea property but when ~~trying to set the~~ I realize I have to use an entire row/column for it to work I feel like a DERP.

FreeMan

4:40 PM

you're welcome

IvenBach

I'd rather ask and appear/feel stupid for a finite period of time than never ask and remain stupid.

FreeMan

:)

FreeMan

5:22 PM

Whew! I think I finally sorted out my git...

I find it somewhat amusing that TortoiseGit's animation is a bit... off...

Also, a bit confused how it Enumerates 90 objects, but only Counts 89...

Vogel612

FreeMan

Well, hello there!

IvenBach

5:40 PM

@FreeMan I don't wanna know what the tortoise is doing to that directory.

FreeMan

he's doin' flips from the world to the folder. He just gets 'is 'ead cut off on occasion...

IvenBach

6:02 PM

@IvenBach Nope. RTE-1004: Unable to set the PageBreak property of the Range class.

:derp: cause I was doing it wrong.

Mathieu Guindon

The page setup API is pretty borked. Make sure the sheet is in pagebreak preview mode when you manipulate page breaks

HackSlash

@FreeMan I used to use TortoiseGit until I found GitExtensions. I have never looked back and must now sing the praise of our new savior: gitextensions.github.io

FreeMan

6:18 PM

@HackSlash TG is integrated with OASIS-SVN which works very nicely for getting source code out of and into Access, so I'm gonna run with it. At least for now.

Thanks for the suggestion. At some point in the future, when I'm more comfortable with git in general and, possibly, no longer doing (much) VBA, I may take a look at alternatives.

HackSlash

SVN? Blasphemer!

I am using this open source project to get code out of Access projects: github.com/joyfullservice/msaccess-vcs-integration

It's quite a hack and I'm sure your paid software works better

FreeMan

I think it was originally designed for SVN, but git integration (via TG) is the main and preferred SC solution now

HackSlash

@FreeMan if you don't mind my asking, what do you use for backend updates? To push from Dev to Prod?

FreeMan

source code? I export from my Dev db, then import into the Prod db (doing that right now!)

database? using SQL Server. I write scripts for any structure changes and to insert/update config records as necessary, then run them when it's time to migrate

HackSlash

Sorry, I mean a schema update for the backend. My backend has no code in it.

ok ok

FreeMan

6:27 PM

Was just discussing that with this yesterday, if you want to review the transcript.

HackSlash

that with this. I actually understand what you are saying. A sentence that only makes sense here.

IvenBach

@MathieuGuindon I'm wading in the borkness right now. Can't set a PageBreak if it's the extremity of the page. So much ~~learning~~ Face+Keyboard = WTFing right now.

FreeMan

@HackSlash indeed! :)

IvenBach

@MathieuGuindon would Function IsFooBar(...) as Boolean be a code smell to you? Do you think Function FooBar(...) as Boolean to be more 'correct'?

FreeMan

@IvenBach If isOkToContinue Then reads more grammatically correctly than If OkToContinue Then, but I'd think either would be OK and would convey intent.

IvenBach

6:39 PM

I'm at an inflection point where i think IsFooBar HasFooBar CanFooBar is a code smell. The prefix could be removed. I just don't know if it should.

FreeMan

ah

how 'bout If ItIsOkToContinue Then? :D

Vogel612

why not just CanContinue?

prefixes are useful to some extent and these specific prefixes communicate semantic information about the nature of what the value means

as such I think they're okay

Mathieu Guindon

@IvenBach Is/Can/Has prefixing is a form of HN encoding a Boolean data type... But, they are actually improving readability IMO... and they're official naming guidelines, so =)

Note "guideline" != dogma

I do find "CanContinue" better than "OkToContinue", but objectively either works - it becomes a case of applying consistency across naming style in a project then

IvenBach

6:57 PM

Understood.

CanContinue feels better than OkToContinue. Plus you save an extra character.

<sarcasm>That alone means its better.</sarcasm>

FreeMan

holy carp! OASIS is taking forever to import code! I'm updating my Prod.accdb from the network and, well, back at this message at 14:19 (my time) I started in import of 50 modules (free version). It's still going...

I hope it'll finish in time for the automated run at 01:00 tomorrow morning...

whew! complain about it and it finishes...

TTFN!

Vogel612

9:09 PM

eurgh.... the project for work is an infinite rabbithole of undocumented unexplainable complications

Mathieu Guindon

9:21 PM

@Vogel612 I wish... gosh, I just sent an email reply to someone complaining about merged cells in the Excel export of an SSRS report ...because it makes it harder to make pivot tables and summaries in Excel :facepalm:

Vogel612

merged cells in excel are one of the features that bite literally everyone in the backside

and no you don't wish ...

as it is I'm wondering why the standard defines the root element of an XML document to have an anonymous complex type derived from the type that specifies all the actual things

Mathieu Guindon

Fair enough, but then why wasn't the pivot summary in the spec for the SSRS report in the first place?

Vogel612

maybe they assumed you wouldn't be able to automate that?

Mathieu Guindon

looks at that other SSRS report with subtotals and year-over-year comparisons ...I guess.

Vogel612

we're both trying to mindread at this stage :)

 <xs:element name="Definitions">
  <xs:complexType>
   <xs:complexContent>
    <xs:extension base="tDefinitions"/>
   </xs:complexContent>
  </xs:complexType>
 </xs:element>

^ ~ARGHHH

Mathieu Guindon

9:26 PM

I... ok you win lol

Vogel612

I mean .... WHY couldn't it be simple and just be <xs:element name="Definitions" type="tDefinitions" />?

but NOOOO the standard needed to build in extensions to the root type of the whole complete standard in addition to all the existing extension points

Mathieu Guindon

looks like it's class Definitions : tDefinitions?

Vogel612

yea, in a way, but it's actually more complicated than that...

it's class Anonymous : tDefinitions { elementName="Definitions" }

and Anonymous types can't substitute for Objects during serialization

Mathieu Guindon

~actual wtf~

Vogel612

but for some reason somewhere in the serialization process here sometimes a Definitions instance is stored in an Object field that's marked as "@XMLAny" (which is basically "throw arbitrary XML in here"), but the marshaller chokes on that and dies

Mathieu Guindon

9:29 PM

someone was having lots of fun with xml serialization

Vogel612

yea

Mathieu Guindon

my sympathies, if it helps =)

Vogel612

and I'm having lots of not fun trying to untangle the mess because I need to shoehorn a related standard that has some conflicts with the original standard into a model that encompasses both standards because reasons

it doesn't help that the storage for data is partially implemented inside about three different interfaces and at least four different utility classes

I'm not even exaggerating :/

I basically have one repo for the first standard and one repo for the second standard

and I need to somehow unify them

while trying not to break the client code which is literally storing objects by first serializing them before passing them to the repository

because the repository also needs to support storing arbitrary files, while keeping track of their media types and nobody considered it a horrible idea to store objects from that standard using the "store arbitrary files" mechanism

which is why the current implementation of the second standard only implements a non-conflicting subset and does that by deserializing the serialized content in-memory, then transforms it and then reserializes it using YAML instead of XML

~sigh

mansellan

9:47 PM