@Comintern I always cite this (when reading between the lines) as a brilliant example of why most bosses love people who can develop things quickly and efficiently in a spreadsheet.
IMO, VBA's only killer problem is that it doesn't scale. (along any kind of metric. LoC, Complexity, Requirements, Auditing etc.)
But this also means that you can provide an awful lot of value to individuals and small groups in very little time by building 80% of a complete solution in VBA, and letting them handle the rest of the 20% in a spreadsheet.
I googled something like "how to automate spreadsheet", found out about VBA, ordered "Excel VBA for Dummies", read it cover to cover over 2 days, started automating spreadsheets.
Took about 12 months before I was any good at it though ^^
@Mat'sMug Coincidentally, I found CR about 11 months in ^^
@Mat'sMug Apparently, that's all there is. I do like dailyWTF, but I split their VBA stories roughly 50/50 into: "Yes, that really is ****** up", and "This is being disparaging about software that isn't new and shiny and "conforms to best practices" but that actually solves real problems for real problem that creates real value".
One of the best examples of Spreadsheets Done Right I've ever seen in real life was when I spent a week in BlackRock's London HQ as a spring internship during my degree. (BlackRock manages some $5 Trillion of investments). They have an entire department of people in the Analytics division whose sole job is to build spreadsheets-to-order for other people in other departments.
Public Sub SubWithManyParameters(p1 As Range, _
p2 As Range, _
p3 As String, _
p4 As Range, _
p5 As String, _
Optional ByVal p6 As Long = 1, _
Optional ByVal p7 As Long = 2, _
Optional ByVal p8 As Double = 0.1)
'Code that does stuff
End Sub
It's called 2 different ways. One via a calling sub that's accessed by one button, part of a sequence of calls, and another is by a button than only does the Sub itself.
The ranges are variables because they have formulas in them. The p3,p5 strings are the values the p2,p4 cells need to become (respectively) as they iterate through.
This has nothing to do with using Variant data types. The OP's literals will both be promoted to Double, as will the result of a / b prior to being passed to Int. You're actually doing a narrowing cast when you assign to Single, and the only reason it works is because you're lowering the precision of the calculation. — Comintern7 secs ago
@JohnnyForbes the point is that between maintaining a 338-character regex and a String.split call, I pick the String.split eyes closed. Always write code as if the next person to take it over was a psycho axe-murderer that knows where you live. — Mat's Mug1 min ago
I'm trying to wrap my head around stuff that has 0 (zero) documentation and nobody really knows what it's doing.
I now understand why it's better to have it be maintainable & readable & slow. Then optimize it and keep it neat & tidy. Rather than it's FUGLY like mah sistuh but run's like none othur.
Welcome to SO. As it stands, this question really isn't answerable in a question and answer format. I'd take a look at how to ask, and then edit your question to include (at very least) a specific problem, attempts you made to code against the problem, and a description of your data (if relevant). — Comintern1 min ago
Do I really type that slow, or are my updates lagging today?
@Hosch250 the actual inspection implementations... you know, at this point it wouldn't even be hard to put them into their own assembly - and get ninject to inspect the whole install directory for other assemblies that might contain IInspection implementations...
> You can't throw both errors - there can be only one active handler at any given time. The example is contrived to begin with - if you have a procedure that requires multiple error handlers, you need to refactor. In a topic about "Best Practices", the example is already a fail. Resuming to an Exit Sub instruction is just silly, and Resume is unstructure flow control. If "unpredictable flow of control" is to be avoided, Resume makes it worse. – Comintern 34 secs ago
error handling best practice in the excel-vba tag should point users to the error handling topic in the vba tag, and maybe have some small section that is specific to error handling in Excel, like using CVErr and handling ODBCErrors.
> It was never "broken" to begin with. This is how cargo cult programming begins - some unsubstantiated anecdotal description of "this happened to me once, so it's always a good idea", and then people assume that because they saw it online that it must be true. TBH, the example should probably just be deleted - the VBA tag's documentation is much clearer and more accurate. – Comintern just now
> This doesn't seem to be much of an issue anymore. The inspections select the right range, and the refactorings, which now use the rewriter as of #2928, all work.
@Hosch250 glad you like it! How would you go about increasing the abstraction level, to make a nice rewriter API that's easy to use for new fixes and refactorings?
@Mat'sMug any chance to perhaps explain how much technical debt this assumption may be if true when mentioned "let's avoid reparsing whenever possible"
VBA Rubberducking
VBA Rubberducking