Any process you ever enable in code will be pushed to limits you never dreamed possible. This is exactly where discoverability can come into play - someone starts by copying 50 rows and using that to get their job done, and may never realize there are other ways (CSV export, etc).
If code can "recognize" when something like this is being done and provide documentation on other ways, it can help people learn new methods.
Now I'm imaging Clippy popping up when you copy a ludicrous amount of data to the clipboard. "You appear to be using a cludgy method, may I suggest something better?"
Of course, I'm guilty of things like this. I have one excel macro that splits data from a giant stream* (one monolithic CSV, actually) into discrete units, graphs them, and then sorts the graphs for printing. It makes a lot of use of the clipboard, and god help you if you try to copy/paste something while the macro is running.
*Giant enough that at one point it grew to where I overran my Int and had to upgrade some of the code to Longs :V
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim intRetval As Integer
' TODO maybe respect some of the properties on Assistant to let the user disable this
If InStr(Target.Formula, "VLOOKUP") = 0 Then
Exit Sub
End If
' TODO is one use really "excessive"?
Assistant.Visible = True
With Assistant.NewBalloon
' TODO I should probably do ".Mode = msoModeModeless" but I don't feel like writing a callback
.Text = "It looks like you're trying to implement a relational database in a spreadsheet."
.Labels(1).Text = "Get professional help"
.Labels(2).Text = "Just let me generate excessive technical debt without help"
.CheckBoxes(1).Text = "Don't show me this tip again"
.Button = msoButtonSetNone
.Animation = msoAnimationGetAttentionMinor
intRetval = .Show
' TODO make the user's choices actually do something
End With
End Sub
When I worked for a company where corporate used it, the problem was that people would come to me for help. M is pretty easy to learn so it wasn't a big deal for me. But I am a software dev with database experience, so I'm not sure who the target market is here--it's not the pure Excel users. Like, they wanted tables from a shared folder appended on each other and joined to another table. In pure Power Query you can do this without too much trouble, but you need to know what you are doing. These people have trouble with VLOOKUP already (I mean the string/int problem is unintuitive, so I'm not talking about that), so this is an impossible task for them.
I'd say it's for the better: sometimes you want to stay in the same tool, and it's the tool's job to interpret your intent.
Imagine you are editing a markdown file and have a picture in your Windows clipboard.
IMHO, the proper action would be to output base64 encoded jpeg data, even if it's "partially recreating the same functions", as it makes a copy-paste of screenshots from say the snipping tool (Win Shift S) seamless.
BTW, github doesn't support base64 encoded jpeg or png data, under the excuse that SVG could have security implications (which is true but not applicable for png/jpg) so don't bother trying it over there...
It's fun help a "savvy" Excel user understand how a relational database can reduce their byzantine VLOOKUP-based fever dreams into simple joins. Typically if somebody can grok VLOOKUP-fueled nightmares they can grok SQL pretty quickly and will actually appreciate what it can do for them.
They did, they called it Salesforce.com
Salesforce admin is a pain in the ass. There's mandatory updates 3 times a year that require low level software development expertise in order for the admin to understand what will break, building new functionality essentially requires tens of thousands of dollars of consultant development, and god help you if you want to run a report that involves objects that don't have a direct parent-child relationship.
I'm an analyst, and because I know how a database works, Salesforce stuff gets dumped on my desk all the time. I actively seek out alternative systems for everything it does, and would dearly love to see us drop this eye-watering expense.
If you're veering from defaults, you're doing Salesforce wrong. Even Salesforce's own Services team these days refuse to write custom code that'll break beyond default features.
In my experience that was guaranteed if any of the clients connected other than by ethernet.
I've not done it but I understand that it's not difficult to have an Access front end for a SQL Server backend rather than the default mdb or accdb backend.
All being Microsoft products the migration to SQL server is easy as I understand it.
I find it hard to understand why large companies would not adopt this approach.
I've done more "development" of Access-based applications over the years than I'd care to admit and, as a menu-driven front end for interaction with a client/server RDBMS (SQL Server, Postgres, etc) I think it's good cheap solution. Visual BASIC for Applications (VBA) can even do some powerful (and arguably ill-advised) stuff like directly calling Win32 APIs. It's an awesome prototyping tool in my experience.
The typical Access solution I see in large corporate environments is something knocked-together by non-IT people. Once it gets to the level of needing to be upsized to a client/server RDBMS back-end the "real" IT department comes in and demands a purpose-built solution. Usually bureaucracy, disdain for Access, licensing costs for the client/server RDMS, taking away file servers, etc, end up killing it.
Upon upgrading from Windows XP, Access was removed from the environment AND existing access databases were shoved into a database farm.
But any new business needs for something similar IE "Hey IT, we want a little app just to store XYZ business info" fell on deaf ears - the response was always "Hire IT Company to build it and hand it over." which of course would cost tens of thousands rather than a handful and thus priced it out of existence.
Which meant of course the business people started finding solutions OUTSIDE of IT Support purview....
Doesn't everyone make daily backups in production environments?
Also, aren't all backups made "just in case"? No sysadmin ever arrives at the office, thinking "Today that accountant is going to mess up the database, I can just feel it in my bones, better make a backup this time" (unless they think that every day...).
You are right on the backup thing. It's just funny to me because access databases on shared drives corrupt very regularly. It's less of it "just in case", and more of a monthly necessity
Microsoft already has built that web-based access data-layer, it's called Dataverse - http://aka.ms/dataverse https://www.youtube.com/watch?v=iSKVxx_ghbw
One day, I asked him what it was and he showed me. He was running a series of excel macros that took over 96 hours to run, and fed a critical business process that was worth a few million dollars.
I sat down and took a look at it, and in about 3 days implemented a prototype in SQLite and a small Perl script that ran in about 8 minutes. Basically, it was a series of sql joins and some regex. My colleague, a project manager, literally hugged me and was near tears.
Some devs later took that prototype and refactored it to run on a normal production platform.
So we end up building the feature in Excel. With large (for Excel, say 100K-500K rows) data sets, it gets pretty slow. Or it'll get too complex, so we'll put it into our own Postgres DB.
Eventually, it'll get big/complex enough that we prioritize using development resources to automate the feature.
To an extent, this has become a standard part of our rigor for deciding whether or not to build features. You say you need X, but you aren't doing X today. Can't do you do that with an SQL pull and then doing all the business logic in Excel, and then creating the entries by hand? In some cases, the answer is no, we need it to be part of a larger pipeline, etc., but for a lot of things we do, building it by hand proves that the feature is needed, and also helps us work through the edge cases and makes writing the stories/BRD much easier. It also takes pressure off the dev team to try and intuit the true requirements.
checks latest
64mb spreadsheet that amounts to one gigantic, ever slower pivot table.
I feel called out. Maximum number of rows in a VLOOKUP in personal spreadsheet is like an array of 2x1000. In an earlier job, it was 13x39000. Excel automatically increases the range when you add rows in the middle. Then I split my data per year, & it was like 13x5000.
Copy/paste works flawlessly through the VDI into the putty session, so I usually just uuencode my blob, and copy it to destination cluster and uudecode there, instead of uploading the file to the VDI, then pscp to the bastion host, and then scp to the destination cluster. (I know of zmodem, port forwarding and such, none of this is allowed...)
Once upon a time at $workplace we had a production and development LAN and we had two PC’s sitting on our desks, one for prod and one for dev.
Now there was a special SMB share to move stuff between the two networks but trying to copy something from one Pc to the other was tedious.
So of course I wrote a magic clipboard utility that used a file on that SMB share as the clipboard “storage”. Running the utility on both PCs allowed me to use copy-paste between the two PCs…
might i introduce you to sed and awk?
Honest question though, as I have no experience with utilities like that: Could those tools together manipulate e.g. the data on the left to the graphs on the right?
depending on your windows, you might be able to take advantage of WSL or as a last resort run cygwin. maybe there are other methods of using awk/sed/grep type utilities as well, but I am not familiar.
i still remember the day when someone watching me struggling with large text files and took pity on me by showing me the light of the holy power of awk/sed/grep. it was taking minutes to make each search/replace edits and I had several of them. it took mere seconds for the shell commands to process the same thing.
I disagree with sibling comments that copy/paste is a hacky solution. It's a very natural one for lots of users and there's no actual fundamental reason not to be able to accommodate it.
This is a "Microsoft refuses to make composable software" problem.
There was a long discussion on memory management before they just stuck a max size on it.
This can get annoying though if the user is deliberately doing something that ends up triggering these messages.
Not me. And that’s not a brag. It is probably not a productive way to think using modern toolsets. But in my world, if you can copy a few things, you should definitely expect someone to copy a shitload of things or at the very least, document clearly and with rich error messages what went wrong.
Likewise, I tend to get nervous when I can’t explicitly handle out of memory conditions, and the problem is I’m a Go programmer. It kind of bothers me that there’s no standard way to deal with that on my end with built-in data structures such as maps and strings. This is probably sheer paranoia because most modern systems have plenty of memory… But it still leaves me uneasy.