« first day (1827 days earlier)      last day (1116 days later) » 

12:00 AM
[rubberduck-vba/Rubberduck] 7 opened issues. 2 closed issues. 16 issue comments.
[Minesweeper] Games Played: 157, Bombs Used: 116, Moves Performed: 22338, New Users: 12
It's funny, 10 years ago I might as well have tweeted that. The journey to database-ing thus began, with MS Access. Felt a bit dirty, like cheating on Excel.
12:42 AM
@MathieuGuindon You could ping him and try to point him on the path to enlightenment.
Nah, it looks like they are already on their way there
I mean, if you're coming from Excel and you know enough about databases to know what primary and composite keys are, the vlookup/join analogy does have some appeal.
1:20 AM
Makes sense. And again, not everyone has Office Pro with Access.
And a non-techie wouldn't know that SQL Server is free.
@MathieuGuindon Excel knew you need to get more out of the relationship and is open to new things.
and now Excel is best buddies with Access SQL Server
1:49 AM
That talk sums it up really well.
@MathieuGuindon watch that talk and you'll then be able to accept your title of Industry Disruptor.
clicks the play button
1 hour later…
2:52 AM
You accept your title yet?
3:06 AM
3:20 AM
I just need to use it every day. You'll just get used to it.
3:31 AM
^ psst Mug.
My secret is I didn't know anything till I started coming to the pond.
Everything about Excel I learned from Mug.
4:11 AM
^ not true
(but, thanks :)
1 hour later…
5:54 AM
Night pond.
1 hour later…
6:57 AM
Reading cpearson.com/excel/FindAll.aspx it strikes me as strange how there’s lots of header comments and If fooBar = True in his code. It that just a product of the time when Chip wrote the code?
7:53 AM
> No, that's the only finding in that sub.
> Yes, it has a 'surveys' sheet, and it was the active XLSM (not the .XLAM addin as I first reported) so indeed Activeworkbook would find such a sheet. I'll close this.
> Thanks, yes, I remember now, I was told before about that class. I had been confusing it with other userform events where Cancel is defined as Integer.
2 hours later…
9:39 AM
Q: Assign string to select cell

Adam CollierI have 2 combobox's combobox 1 lists company's combobox2 then pulls in data listing items sold by those company's I now need combobox2 to take the selected choice by the user, and put it into (sheet name)Branding cell A8 (this is the main sheet the users sees. this is my current code Private S...

1 hour later…
10:50 AM
> Just an FYI, here (exceldevelopmentplatform.blogspot.com) is a rather interesting blog about using python and VBA together. Recently, the author has been using python reflection to provide some amount of reflection in VBA. An interesting concept, certainly not the right tool for every problem, but I like that someone is playing like this!
11:07 AM
@IvenBach I think it's definitely a product of time -- the idea of refactoring your code was either too new or wasn't developed into a concept yet. We had the object-oriented paradigm but not so much the approach toward managing code changes well.
As a matter of fact, I was reading a book written in early 2000s and the attitudes was roughly the same; I went WTF a lot when they presented their procedural samples as "flexible" and "easy to adapt", among other descriptors.
11:49 AM
> Something is off about this inspection though - the passing tests indicate that it's basically issuing results for exactly the opposite of when it should be doing that: this inspection produces 100% false positives, ...and 100% false negatives.

I tweaked it a bit last night to fix the false positives (adjusted the tests accorsingly), and got failing tests for the two situations it *should* be issuing a result:

- Unqualified `Sheets` or `Worksheets` member call in the `ThisWorkbook` modul
@this the problem is that while the rest of the programming landscape spent the last 25 years evolving, VBA practices somehow remained frozen in time.. until some ducky came along
12:09 PM
@Duga not sure how the mocks can even be setup to make a passing test for this. ThisWorkbook is just a document module, not a workbook. I think I can have a patch done if the inspection can assume the workbook object is named "ThisWorkbook"
@MathieuGuindon I do wonder. I am inclined to think that if you looked at C/C++, or even PHP code at that period, they'd have been roughly similar
and PHP today is still crappy as ever.
@this yup
Speaking of crappy code.... I just wrote a stored procedure that refreshes an indexed view and a view on the indexed view to null out the unwanted entries from indexed view due to restrictions on the indexed view disallowing nullable expressions. #TotallyRubeGoldberg'ingIt
But yeah, you're right - VBA tooling has stayed the same, so I don't think there was any drive to update the standards. If we tried to require them to use .NET naming convention in '90s or even 2000s, I'd wager they'd laugh us out of the room because the tooling wasn't there to justify it.
12:21 PM
@this That almost sounds like the RD build process. I'm beginning to think that "Rube Goldberg" is your middle name!
Hi, I'd like to introduce you to my friend, @th "Rube Goldberg" is...
@FreeMan IKR? It's annoying.
I don't like building them. Those are guaranteed to make the next guy go "WTF?"
remembers that he was supposed to document his rube-goldberg-esque build process
you're not the only one that Rube Goldber'd the build process, though :D
12:39 PM
Usually it takes a team committee to get a Rube truly Goldberged.
@this honestly, I had to read that carefully about 4 times... I was going to ask "why a view on a view" before I finally got it. Sheesh, what a mess!
IKR? SQL Server's indexed views are totally FUBAR'd
I did so want to just use a stored procedure but it wouldn't perform as well. :\
I probably should put in a header comment..... shudder
@this there is nothing inherently wrong with a header comment, so long as it serves a good purpose, and when there's something bizarre going on that requires some head scratching to get your brain wrapped around, a comment that describes not only the what but the why seems entirely appropriate.
skipping the how, though, still remains a good idea, because that's likely to change over time while the comment prolly won't
12:55 PM
yes yes, totally agreed. It's just that I see it as "look at crappy code I wrote!"
1:08 PM
> Hy there,

I found the nodes in the config file::

But in the settings I didn't find any corresponding settings.
Can the Autosave only be activated in the config file?

greetings raohy
> We may or may not have removed that feature something around ... 12 months ago? Apparently #3008 left the settings in place when removing the feature from according to #2998, soo ....
> thx for the quick answer.
1:34 PM
@this but at least it says "there was a reason I wrote such crappy code - I had to!"
which is different than "I wrote crappy code because I didn't know any better."
point taken
@this, I've read my homework assignment chapters 1-3 (as recommended in the Intro), and I think I've got a basic handle on Ownership Chaining, but then again, I think I'm missing something substantial.
If I, as a sysadmin user, create a sproc, it's still owned by dbo (that's where everything resides - I'm a simple guy). Therefore when I run my sproc (from VBA land) to kick off the SSIS package, it will run as me, but ownership chaining will have it all owned..
Ah! it's a cross-database thing because the SSIS package is in SSISDB while my sproc to execute it is not.
^is that the issue?
I need to do more reading, then, right?
Yes, ownership chaining normally cannot extend beyond the database level
I thought there was a case where it can happen but I might be misremembering
I kinda wanna kick this over the fence to the DBA and say "set up all the permissions to make this work for me", but OTOH, understanding it myself & presenting it to him would involve a certain amount of satisfaction. :)
I could even ask for "approval" of the plan and give him a bit of an ego rub
> Ownership chaining never applies to access on server level, but for any operation that requires permission on server level, SQL Server always performs a permission check.

When it comes to cross-database access, ownership chaining can apply. However, by default, ownership chaining across databases is turned off. We will look at this more in the chapter Cross-Database Access.
that's at the end of section 3.3
so there is an exception but it might not be useful nor desirable. I am sure I did the certificate signing thing.
1:49 PM
that part probably didn't sink in at the time because I wasn't thinking I was crossing DBs. After all, all my data is in one DB! hadn't registered until that "Ah!" moment that the sproc/SSIS pkg to be executed was in a different DB.
I'll continue my lesson.
> Since the above post I made my first (successful??) foray into C# and managed to create a class library for VBA called Kvp which eliminates the boiler plate code above. Kvp is ' Key Value Pairs'. Kvp has proved to be very usable and has now replaced all references to collections and scripting.dictionaries.
of course I read this (in the Intro) and it didn't sink in, either:
> If your access problem is only within the database, the only other chapter you need to read is the fourth, which discusses certificate signing on database level
which I would have been thinking applied to me
In my defense, I sat through 3+ hours of meetings yesterday and that will turn anyone's brain to mush for the rest of the day.
@FreeMan Exactly. If you are able to bring a laptop or tablet, you could just read in the meeting. :-D
Yeah... the laptop is at home. and they were expecting input from me and "hrm, err, ahhhh, what?" probably wasn't what they were looking for. :/
1:53 PM
c'mon. Think like Wally. If you go that way, they won't invite you to the next meeting. Win-win!
Of course, Wally somehow manages to never get fired for all his stunts...
@Duga branch is named squeak
^ & ^^ <-- LOL!!
of course, in reading my homework, I keep seeing the user name Michele and pronouncing it in my head as Me-shell.
May have to fire up a Beatles station on Spotify for today's run...
2:11 PM
> If you work for an ISV who ships a product to many customers, you could opt to sign all your stored procedures, triggers and functions during installation and drop the private key from certificate after installation
Why would you drop the private key from the certificate after installation? Is it somehow visible on the server to sysadmin?
You can backup a certificate w/ the private keys
and therefore use it to decrypt whatever was encrypted by that certificate
but don't you need access to the private key to decrypt whatever was encrypted? This implies that leaving the private key somehow leaves it exposed to being extracted? (i.e. can it somehow be selected unencrypted from metadata somewhere?)
That's exactly what ISV providers don't want - their customers decrypting their stored procedures or whatever.
I am not sure if the private key can be extracted but I know that if it's there, you can backup it with your own private key and regenerate all assets encrypted originally with the new private key. You can't do that if the private key isn't there.
2:28 PM
From the backup you can regenerate all the things that were originally encrypted, but with a new private key that you specify at the time of restore? Sounds like a major security flaw!
I get that this is a way around the "crap Bob left and he's the only one who knew the private key" issue, but that sounds like a bad management problem, not a server issue.
hm, I think you're forgetting this is a ISV scenario
e.g. they are installing their database (with some product) directly on client's on-premises server where client's DBA has full access
this is not like where you have a cloud subscription where you only have a web portal to manage the access.
2:59 PM
what in the heck
production query been workin for 6 months +
suddenly errors out
with a SUPER generic ODBC call failed
take a look in the errors object
thats the only error
i can open each of the source tables just fine
cant open the query
then similar errors pop up on 3 other applications
did the schema change?
yesterday everything was working
there are only 4 people who can make schema changes, i am one of them
no one informed me of making any, and i didnt make any
normally 2 of the 4 ask me
actually, everyone usually asks me
if the query depends ona view, you could try sp_refreshview (I think that's hte name, not sure off the cuff)
its not using views :/
they are all regular tables
in that case I'd relink and see if that makes a difference
3:06 PM
@this Even at that, being able to simply restore the DB and give a new private key to override the old private key? If I were an #EvilDBA about to leave and holding a grudge against the company, my last act could be to kick off a restore & provide a new, totally random private key while deleting all the other backups.
when i look at hte query that fails, i dont see any of that access corruption that you normally see if the schema breaks
thats a good idea
not that I would do such a thing!
now i need to figure out who screwed with the schema.... relinking fixed it
@KySoto what are the odds you'll find "Not ME!" was responsible?
@FreeMan Write to Microsoft?
@KySoto note that it can be something benign as changing the length on a text field or making a non-nullable column nullable.
3:14 PM
well... just trying to be sure I understand
ugh... I'm so fucking through with the PoS application I've been working on for a year now again
over the last few days some puzzle pieces fell into place and stuff worked for a while..
and now I'm working on getting the remainder of the "MVP" fixed and it's being a pain in the backside...
@IvenBach Nope, you didn't sound unappreciative at all. I didn't think my answers really qualified for a full review but I'll post them anyway because I care more about helping you :)
why is Excel so stupid today? It seems incapable of letting other windows getting the focus
3:36 PM
@this Have you tried turning restarting the entire office like @IvenBach did? xDxDxD
would be dead meat if @IvenBach had a mean bone in his body
You could say he would... wait for it... flip the switch on my life xD
3:49 PM
If there's no malice in it I'm all for humor.
I've a mean streak but that's reserved for Pineapples. You're safe.
@IvenBach Hopefully it made you laugh a bit but was probably a bit Too Soon
I was laughing about it later on that day myself.
wondering how long did it take them to restore the power?
4:09 PM
go figure
it randomly fixed itself
@this yeah, i know what you mean, though it looks like it was just something being dumb since it all of hte sudden started working.
@FreeMan fortunately it was SQL server itself being dumb... or unfortunately?
4:29 PM
@this Electrical company came out the following day. Within 5 mins of switching off breakers they had changed the meter and were told to test.
Everything was good working as expected.
> Not really a bug report, just an observation that might explain some delays.
This is like #1953
When RD reaches "Ready", Task Manager is still showing 25% CPU for Excel. After a minute it goes to 13% then 0% after three minutes. Memory went from 1.2MB at peak to 0.8MB.
So maybe I need to wait for 0% to be sure RD is stable?

OS: Microsoft Windows NT 10.0.17134.0, x64
Host Product: Microsoft Office x86
Host Version: 16.0.11629.20196
Host Executable: EXCEL.EXE
@IvenBach did the test include bumping the wall and checking that it doesn't pop out from its mount?
I wasn't in the electrical room when they put it back in. Nor have I gone in there.
For now I will just ostrich bury my head in the sand and claim it doesn't affect anything.
That makes all problems just magically go away, right...
then they didn't do their job...
that also means if you bump it again, you can said it's on them to actually test and their product is.... uh, a bit more sturdy.
4:36 PM
mumble mumble above pay grade mumble :walks-back-to-desk:
yesterday, by IvenBach
21 hours ago, by IvenBach
I've just discovered a new kid of relational join: the MeatBag Join. When you want to join an old table to a new one and the keys have radically changed, such that only a meatbag squinting at it can see the relationships.
Not recommended, query performance sucks :-)
@IvenBach new achievement to unlock: Break the glass door and pop the mount at same time
@mansellan sounds like it'd induce headaches
> Memory went from 1.2MB at peak to 0.8MB.
I don't think so
@mansellan Condolences.Offered();
5:18 PM
so Excel jockeys - for #reasons, I need to do random inserts into different rows on some lame worksheet.
I already have the key I can use to locate which row and therefore know which cell I need to put the value in.
but I remember Iven saying Range.Find doesn't work as advertised.
It does. It's that Range.FindNext() doesn't honor any SearchFormat that was previously applied.
Oh ok. Would that'd what you'd use to do random finds?
With Range.Find() you need to be aware that if Range is a multi-cell Range it will search within that Range. If it is a single cell Range it searches that worksheet.
@this Are you looking for a cell value, comment, formatting?
i'm just copying a bunch of values from one sheet to other
but they have to be inserted in the correct place, which is why I will use a "key" to locate which row a value need to be copied to.
Shouldn't have any issues. Searching for the insertion point in a single column which has unique PK should be just fine.
5:23 PM
would it be better doing it w/ Application.WorksheetFunctions.Vlookup? OTOH, I dont' see a way to specify which workbook to apply vlookup on
As part of a formula you can preface the VLookup arguments with a workbook.
Vlookup requires data to be sorted. Index(Match()) however, doesn't. I've become quite the fan of Index(Match()), though it's not particularly fast
hmm not sure that works.
since those 2 workbooks will never be linked and shouldn't be
Unless it's a QuickNDirty solution I always question why one workbook is referencing another.
#ExcelIsNotADB irregardless of what the advanced filter adds as a named range.
FreeMan is correct that Index(Match()) is a more robust option to VLookup().
@IvenBach #IvenStampOfApproval
5:37 PM
> "ready" parser state only means that the parser has completed - that's not the end of the road though. Upon reaching that state change, several components (e.g. toolwindows) *begin* work. Inspections start running if they're configured to run automatically; toolwindows (code/todo/test explorers, code metrics, etc.) start querying, processing, and aggregating the parser state data. When inspections complete, the toolwindow refreshes, which can be another resource consumption peak.

3 minutes
5:48 PM
> Note: toolwindows refresh on ready state whether they're visible or not
@this do you remember me talking about my self updating applications?
had a function that downloaded hte latest versions of my code libraries and whatnot?
i recently modularized it and set it up so i can remotely update an application to the newest version of the modules, or just put modules into it that it is missing
it will work with my application tracking application
so i can go through and shove updated modules into the applications that are not up to date
and have it automatically update hte change logs
im excited
6:14 PM
So, if I've got @this right, I deploy the SSIS package and it goes into SSISDB by default. I (as a non-sysadmin user) do not have rights to run that, so I create a sproc that runs it for me. That sproc still doesn't have the correct rights (since it's running as me), so I create a user that does have the rights to run it.
I then create a certificate and sign the sproc with the cert thereby granting me rights to execute the sproc with the effective rights of the user who does have the rights to execute it.
7:01 PM
Yey. Should have Office 365 installed before end of day today.
> **What**
RD tells me an annotation is present but an attribute is missing if I leave this in my code:
I would like it to tell me the annotation is blank.

I paste blank description and module description annotations and fill them in later. This would clearly show where I intend to have annotations but have not yet written them. Additionally, if RD ever includes a feature to auto-add blank description annotations to every procedure and module (to encourage
gee, excel macro shortkey must be a ctrl + a letter?
@this myexcelmoments.wordpress.com/2019/06/06/… is my article and I advocate Ctrl+Shift+<Key> since all Ctrl+<Key> combos are used up.
@KySoto nice!
@FreeMan that's the idea, yeah. With certificate signing, you still need an user, albeit that user isn't a human one
@IvenBach missing something. How do I make it go ctrl + shift, not just ctrl?
7:23 PM
> @Description() could conceivably trigger IllegalAnnotation, since the argument can be considered as required. Seems that would be a more general solution that involves simple tweaking of an existing inspection, rather than a brand new one that only looks at one specific annotation type. Thoughts?
@this the key is case-sensitive
@this yeah that :)
> That would would. It technically is illegal since the annotation doesn't generate an attribute.
hmm the macro does not work unless it steps through - is there a quirk WRT opening a workbook and trying to write to it?
how are you opening it?
set wb = workbooks.open(...)?
7:34 PM
host is access?
no this is just plain old Excel macro
for a quick'n'dirty job
and now it's nothitting the breakpoints
Apr 2 at 19:57, by this
I still think you should have a coffee accident.
haha. Full circle it has come.
7:37 PM
hmm except that's client's machine, not mine
If it's not hitting break points save the Excel workbook close then reopen.
I have it periodically do that to me.
that's what I just did
Still to no avail?
but still got same symptoms RE: not working if not stepping
it works when I step through
Can you step into it F8 then Run F5 and does it continue?
7:39 PM
I guess Excel took some etiquette pointers from VBIDE
that's more or less what I did
Have you tried turning it off then back on?
I honestly can't think of a time where F5 didn't work as it's supposed to. Well besidesghost breakpoints being hit...
this is reminding me all again why I don't do excel macros
Excel is a fickle and harsh mistress at times.
in Access that's where you'd go "compact & repair"
in Excel that's where you go "export all, wipe clean, re-import"
yeah, it's just as if the code stops all of sudden at Workbooks.Open(...)
Debug.Print "opening workbook"
Set TargetBook = Application.Workbooks.Open(SelectedFile, ReadOnly:=False)
Debug.Print "selecting sheet"
Set TargetSheet = TargetBook.Worksheets(1)
The first Debug.Print prints. The second doesn't.
7:50 PM
Just been reading up on the configuration system in net core. It's very cool, streets ahead of the old System.Configuration
technically owned by ASP.Net Core team, but with (deliberately) no hard ties to it at all
@IvenBach bwahahah That's what you thought. It was a nice thought though.
actually it's netstandard not net core
If we ever wanna rebuild the ducky's configgers, we should consider it. Could strip some boilerplate.
@this does someone else have the workbook open? That'll likely stop it cold. I've had that happen, and there's a popup buried somewhere saying as much, but it's always hidden behind other windows. I usually don't see it until I've killed the processing and am trying to close out Excel.
I think I figured it out
I do not think the trick of Ctrl + Shift macro shortcut is compatible with Workbooks.Open
it's as if the workbook was opened with shift down, then the execution just drop dead at that point.
That is despite the fact that I already released the shift since there's a file dialog for user to select the workbook
Ctrl+J and Ctrl+M are two infrequently used combos if you can't use shift.
IIRC Ctrl+M is for quick/flash fill.
8:03 PM
@this interesting
ctrl+J it will be then
whoever has that key shortcut #yerscrewed
That may be the last holdout.
luckily, i only need one stupid lousy marco for the quickn'dirty job
8:18 PM
@this hm interesting
1 hour later…
9:19 PM
> **Rubberduck version information**
The info below can be copy-paste-completed from the first lines of Rubberduck's log or the About box:

OS: Microsoft Windows NT 10.0.17763.0, x64
Host Product: Microsoft Office x86
Host Version: 16.0.11629.20196
Host Executable: EXCEL.EXE

I was working on an old macro book and I decided to upgrade it to the most recent version. The RubberDuck displayed "Unexpected error" in the toolbar. Then I got this unhandled e
@MathieuGuindon just to show I'm not making it up - a MCVE:
Public Sub Derp()
    Dim dlg As Office.FileDialog
    Dim SelectedFile As String

    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    With dlg
        .AllowMultiSelect = False
        .ButtonName = "Drool"
        .Title = "Duh, pick a Excel file, whatever"
        .Filters.Add "Dumb and dumber", "*.xls;*.xlsx"
        If .Show Then
            SelectedFile = .SelectedItems(1)
        End If
    End With

    Debug.Print "Selected file: " & SelectedFile
    Application.Workbooks.Open SelectedFile
assign it any ctrl + shift shortcut, see it fall on its face.
assign it a ctrl only shortcut (or run it manually), it works.
9:34 PM
That's DL-worthy, no?
@this Congratulations: You found the marble in the oatmeal. You get to drink from the fire hose!
You're a lucky lucky boy
10:07 PM
Anyone fancy giving a decent answer to this:
Q: Why is VBA function giving compile error for collection but not for string

JohnI am trying to pass a collection to a VBA function but I'm getting a compile error I cannot seem to fix. This is a simplified example that gives the error. Sub test() Dim fooString As String Dim fooCollection As collection Set fooCollection = New collection useString (fooStr...

How do you report the behavior that this just MVCE?
I get you should give a return value and assign but don't know why an object in the signature leads to a complaint whereas as a simple datatype (string) doesn't?
Option Explicit

Private Const FooNumberFormat As String = """foo"""

Public Sub Testing()


    Dim withoutSearchFormatAndFind As Range
    Set withoutSearchFormatAndFind = UsingFind(False)
    Dim withoutSearchFormatAndFindNext As Range
    Set withoutSearchFormatAndFindNext = UsingFindNext(False)
    Debug.Print withoutSearchFormatAndFindNext.Address
    Debug.Print "Find() and FindNext() without 'SearchFormat' match: " & (withoutSearchFormatAndFind.Address = withoutSearchFormatAndFindNext.Address)
I took the time to write up an MVCE for own Find() vs FindNext() discovery.
@IvenBach ? That an OP question?
It's for RD
@QHarr That's referring to this's MVCE Public Sub Derp() ...
10:10 PM
@QHarr My knee-jerk reaction is collection doesn't have a default value.
10:23 PM
Writing you up an explanation.
@IvenBach That's true
Almost done with my example.
@DavidZemens You are almost correct. (fooCollection) is trying to call fooCollection.Item() because it's the default method of Collection, and the Item() method requires an index argument. It's that argument that is missing. — GSerg 32 mins ago
@IvenBach Item(index) is the collection's default property
#Words. He didn't supply it with the default argument it's expecting.
@MathieuGuindon Making it appear like I can explain better than I actually do.
The more examples I see of default members causing confusion the less I like them.
10:44 PM
@IvenBach esp. when you see VBA top contributors like David being confused as well
I don't feel like mine should get the answer. I just felt that kindergarten coloring the example with an image would further clarify this issue.
19 hours ago, by IvenBach
Everything about Excel I learned from Mug.
^ While not entirely true this answer and how to explain I did learn from Mug.
Default. Member. Two words that should never have been placed together.
@IvenBach Thanks. Already upvoted. Made sense the default bit. Should have realised it the moment I was thinking simple yes object no
11:09 PM
@MathieuGuindon I suppose I could post - just wasn't sure if it was just a quirk everyone knew about but me
11:51 PM
Enjoy the weekend @pond.

« first day (1827 days earlier)      last day (1116 days later) »