The Lounge is rated Safe For Work. If you're about to post something inappropriate for a shared office environment, then don't post it. No ads, no abuse, and no programming questions. Trolling, (political, climate, religious or whatever) will result in your account being removed.
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point.
It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible.
No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall.
I can't believe how much of the world lives and breathes this stuff.
(but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
I semi-retired a few years ago but took up a job "in the business" looking after their EUC "solutions" - most of which consisted of recorded macros. Three years later and trying to introduce any form of governance is still like herding cats
Just today I was yearning for the days when I would just knock up a quick program to automate something - I sometimes even find myself wishing for VB6!
Good Tips '489 !
Writing add-ins seems to be the only way to automate Office 365. It is becoming the corporate office platform in a lot of places so indeed MS has done a Ripley on us corporate software developers.
If you do have to work in VBA -there are many toolsets to help write and maintain good code - MZ tools is my preference but Rubberduck · GitHub[^] looks good too.
Its an old but stable IDE -little Intellisense, no autocompletion - but that only helps the coding - not the thinking!
Export all your code modules, forms, spreadsheet content, formulae, formats to text files. Then you can do version control in mercurial, and inspection in Npp
Merging is an issue but you can see what changed and revert or branch.
As a fairly serious Access programmer, I feel your pain! The one complaint that I don't endorse, though, is about the limit on consecutive line continuations, which I must say I have never run into. On the whole, I feel that if you have to continue a logical line over more than a couple of printed lines, you should break up the logic, since it is likely to be difficult to understand if/when you come back to it later, let alone by anyone else!
Understandability isn't an issue, then, but I think you are right about what you should do. When I have the same sort of issue in Access VBA, I generally create a new Table (if the 'array' is likely to be needed again) or Recordset (if it isn't), which is pretty much the Access equivalent of entering the terms on the spreadsheet in Excel.
Yet another case of someone using excel as a quasi database.
It should have just been done in Access in the first place.
I, too, have done a lot in Access VBA - easy when you know how.
The problem I've found in Excel VBA is that they named most 'things' differently. I haven't done any VBA for Winword, but the same issues probably arise. No doubt that different teams did the initial design for each program.
Sorry, but it shouldn't have been done in Access in the first place. This is a spreadsheet I'm working on, and it includes text, formatting, multiple worksheets and it needs to be approachable and usable by a non-developer.
The categorisation part is a minor, minor part of this. I'm not a fan of changing the problem to suit the tools. I choose the tools to suit the problem.
I think the hardest thing for us is realising that sometimes we shouldn't write an app or dive into the technology 'just because we can'.
What I should have done is looked for an online service that does this and just used that instead. I'd be done 4 days ago.
We had a big meeting this afternoon about this: balance the ease and fun - and fairly hefty price tag - of writing solutions ourselves, vs paying the money and using something pre built that, if you actually do the suns, will be way way WAY cheaper in the long run.
But coding is a drug.
Last Visit: 27-May-20 19:03 Last Update: 27-May-20 19:03