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.
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.
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?
> 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.
I 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...
> 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!
@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.
> 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
@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"
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.
@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
> 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 ....
@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.
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.
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.
> 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.
> 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?
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.
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.
@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.
@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 :)
> 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?
Version 2.4.1.4729 OS: Microsoft Windows NT 10.0.17134.0, x64 Host Product: Microsoft Office x86 Host Version: 16.0.11629.20196 Host Executable: EXCEL.EXE
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.
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.
#ItDepends
@this Are you looking for a cell value, comment, formatting?
> "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.
@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
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.
> **What** RD tells me an annotation is present but an attribute is missing if I leave this in my code: `'@Description("")` I would like it to tell me the annotation is blank.
**Why** 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
> @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 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.
> **Rubberduck version information** The info below can be copy-paste-completed from the first lines of Rubberduck's log or the About box:
Version 2.4.1.4627 OS: Microsoft Windows NT 10.0.17763.0, x64 Host Product: Microsoft Office x86 Host Version: 16.0.11629.20196 Host Executable: EXCEL.EXE
**Description** 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.Clear
.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.
I 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...
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()
Sheet1.Cells.Clear
AddNumbersToCells
AddFormattingToLeftHalf
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.
@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. — GSerg32 mins ago
@IvenBach Item(index) is the collection's default property
I 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...
VBA Rubberducking
VBA Rubberducking