Apparently this is this blog’s 100th article (!), and since Rubberduck is also about the future of Office automation in VBA, I wanted to write about what’s increasingly being considered a serious contender for an eventual replacement of Visual Basic for Applications. Just recently Mr.Excel (Bill Jelen) uploaded a video on YouTube dubbing it the… Continue reading Office-JS &…
Awesome article dude @MathieuGuindon feels like a dear diary from the other side of the void. Perfectly understood your position from where you stand with the web and the things I had see changing too. :) looking forward to part 2.
@BloggingDuck I don’t know how many VBA devs will be able to follow this post. Lambdas, anonymous types, and tuples oh my! None of that exists in VBA and is non-trivial to grok when you first encounter them.
> FWIW, I installed .5359 yesterday. When I fired it up this morning, I had no issues. I also got the notification that .5361 was available. I DL'd and installed. It too, opens without issue.
@M.Doerner would having the finder also eliminate the risk of inspection A reporting a result on a declaration X while a related inspection B not reporting a result (when it should) on the same because it now has X' instead of X? The other question is whether parse tree inspections always end up dealing with a declaration.
e.g. SELECT * FROM MyLinkedTable WHERE ColumnA = [Forms]![MyForm]![MyControl] get translated into a parameterized ODBC query SELECT * FROM dbo.MyLinkedTable WHERE ColumnA = ?
ditto for queries like PARAMETERS foo TEXT; SELECT * FROM MyLinkedTable WHERE ColumnA = [foo];
But for passthrough queries? You're on yer own, buddy.
When your query is in code, it makes more sense to use ADO anyway. However when it's dealing with bound forms, then you're dealing with DAO and thus need to work with it using expressions.
Note that SELECT ? FROM MyTable; or SELECT * FROM ?; or SELECT * FROM MyTable WHERE ID IN (?); are not valid ways to parameterize (regardless of how you execute the query.
A parameter does not mean "parse whatever it is and concatenate it into the SQL"
So those 3 examples will not work (or may work but not give the expected result)
I think you would have to actually concatenate the SQL in ADO for those 3 cases. If you want to parameterize it, you have to go a higher level using sys.sp_executeSQL and execute dynamic SQL within the query.
that syntax is, however, what the stolen borrowed code expects. It does the heavy lifting of turning ? into the appropriate supplied parameter, attaching that to the collection of parameters, and passing it along to the DB to be executed
right - the SELECT ? and FROM ? won't take a parameter. Those parts would need to be concatenated. The rest, however, will work.
I'm just trying to create a single Function that I can call to get data from a config table where there may be multiple columns that I need to get at different times. Instead of creating a Function for each column I might need to get.
I have a client config table with about 20 attributes per client. I don't want to have to write Function GetClientAttributeA(clientID), Function GetClientAttributeB(clientID). I'd much rather write Function GetClientAttribute(cliendID, attributeName)
sqlString = "SELECT " & attributeName & " FROM <table> WHERE ClientID = ? and pass along the clientID to the Execute() above...
granted, it means I've got to provide the proper attributeName in my code and there's a moral contract for me not to execute a SQL injection attack on myself...
@this Passing in the declaration finder into DoGetInspectionResults cannot help between inspections. After all, each requests its own instance in GetInspectionResults.
seriously, at that level, the dev (99.999999% chance dev = 'FreeMan') would be blowing up the dev table, because that wouldn't work and never make it to production...
> I think the title for this issue is very misleading. The inspection should also fire for object type parameters, but only if they are assigned to. That is not the case in the provided MCVE.
So, I guess the issue should rather be called. Procedure can be written as function should not return results if there is no assignment to the parameter.
@BloggingDuck mf'er... when i wanted to give up on JS, it comes back to bite me in the ass... lol
albeit a couple things i have to lookup outside of your post, very informative
i won't make a comment to the post (i hate doing anything that asks for an email), but will say it would be nice to touch more on the longevity of the system, specifically about Shared Workbooks and moving to Excel Online using Office-JS, whereupon EVERYONE has access in real time... which also puts the extreme negative that there is not a development->master->production hierarchy for the project (though that is probably ancillary given the scale of "most" projects)
SharedWorkbooks with events and the sort, specifically, moving to the online-system, is what i'd be hoping to have discussed regarding the longevity and implications. figured i need to specifically state the point after i kind of digressed in previous paragraph
greek is Kyrilloc (where the "c" is the french c with the tail, like in francois), which has a soft end... specifically in russian (not "all" cyrillic languages) the soft-sound is indicated by a small "b" subscript behind the letter
ya gavoreet po-rooski ... really weird to phonetically type that haha
alright, i just took way too much of a tangent and need to get back to work
not sure exactly what 'stacking views' means, but I was referring to making a view out of the CTE portion, then having multiple queries against that. If that's what you understood, too, and were addressing, then you've lost me
ah. I've just got a SQL query with a CTE. I now need multiple queries against the WITH table() as () portion for some quick reporting. I was thinking that the proper long-term solution would be to make a view out of the WITH table() as (), then do my multiple queries against that. However, this is a quick'n dirty one-time thing, so copy/pasta it is.
Yes that is true. That said, it generally does a better job of inlining CTEs than it does stacked views.
But make it complex enough, then it will just flail at finding the best transformation.
Besides, I'd rather read CTEs than look at 2-4 different views and trying to piece it all together.
@MathieuGuindon I just realized a question about VBA design. Would you agree that if you have a class that has no state, does it really want to be just a module?
@FreeMan Sorry missed that. My guts says that if there's no state, there's no need for instance, and instancing is a new complexity.
I'm sure you can have classes with no internal data. An example would be to receive or to fire events. But that's not what I am thinking of having a state.
@this in theory, yes - it's basically a static class. In practice, its members are now exposed as macros and UDFs, and if I don't want that, even with Option Private Module (which merely hides the members from view, but they can still be invoked)
Thinking about it - the Option Private Module prevents access from outside the project but not within. The host has the project so I guess it's "within". :\
@MathieuGuindon seems to be host-specific. Access says "no" there.
(but then again, the Application.Run implementations are messed up among hosts. Some won't like fully qualified name. Others won't like private access. That was a fun time.
jokes aside, people are resistant to change and if it "works" for their purpose, they see no need to change
"the data I entered isn't there" "then redo it"... continues to pull that file for a report like they've done for the past 18 years they've been at the facility
no, i'm not bitter... flashbacks to 'nam
back on the level, starting to learn typescript would be worthwhile? in knowing minimal JS, and TS being additional terms atop of JS (correct me if that's wrong), it should be similar and able to be used on most current JS platforms, right?
Is there any quicker method than using a for loop to find the first instance of a formula in a cell?
For Each dc In .Worksheets("testWS").Range(searchRange)
If dc.hasFormula() = True Then
formulaRow = Split(dc.Address, "$")(2)
formula = dc.formula
Exit For
End If...
I replied to an email JKP sent to the NDA mailing list about the script task pane, ...with a whole tutorial about, basically, getting started with TS, HTML and CSS ...in Script Lab. Got nicely slammed, but now someone at MS wants to put that tutorial up on ms docs
Back to the module-as-a-static-class vs. predeclared class --- I see a potential wrinkle in refactoring. Suppose I define 3 methods on the module. Months pass, I realize I need it to be class, so I convert it into a class and make it predeclared to make it compatible. Does it smell to keep the original 3 methods as part of the default instance's interface (e.g. pretend they are "static" even on an instance type)?
@this yes, work email. just had it work for me after: put in email, click "sign in", get 404, alt+left arrow to go back, ctrl+f5, click "get it now", put in email, click "sign in", then voila... if i just ctrl+f5'd following the link from Mat's post I received the 404, or didn't refresh and just followed the link
a class /default interface might expose a bunch of static/stateless methods and a factory method and there'd be nothing inherently wrong with that, assuming there's instance state to be held on whatever the factory method is outputting
..and that's where the explicit interface comes in, to keep the instance vs stateless stuff cleanly separated
In working to accommodate the changes to using MSTeams, which utilizes Office-online, I was attempting to download the ScriptLab addin from Microsoft Garage and received a message: "Office 365 has been configured to prevent individual acquisition and execution of Office Store Add-ins." when attempting to open the addin.
Would it be possible to get permission to access ScriptLab to determine if it’s feasible to move Collab/shared-workbooks to Office-online, using Typescript rather than VBA scripted activities? Testing early would be ideal compared to pounding…
we have 1 site in our org that is 100% teams atm (they're the dummy site)... they can't message or call people without starting convos on Teams, and only about 20% of those people have corporate cell phones, so they're radio silent on just about everything
we've had lots of talks about issues with teams in its current Development stage for production usage... but won't digress at this point, as i don't want to completely derail Iven's conversation about exposing himself to the factory
waiting to see how that turns out as i'm learning as they describe it
@BigBen the person who is leading the charge for training other sites how his (the test facility) utilizes teams keeps calling it "a child of sharepoint"... that should really throw red flags to the populous
@this <-- chiming in at that's still where i'm at. i've spent quite a bit of time just taking with you all about it, and Iven provided some wonderful examples that helped further my understanding
No question. Just in reviewing an online course suggested for me joins were stated as "advanced". At that point I almost discounted the idea of taking it. I'd rather repeat foundational topics ensuring I understand them.
@this tbf, if the "basic" user is still using "table1" references, etc. with a single report/view, and "intermediate" just has better naming and having multiple reports/views, then "advanced" would be join/union to create complex reports/views
The drag'n'drop GUI makes it easy and fast to setup joins and encourage one to just randomly mash buttons until it works without really thinking through the effect a particular join would have.
@this i'm stating that based on personal experience... i read abuot the items, but the beginner work didn't utilize those as they were deemed "advanced" activities
basically... chapters 1-3: bombard you with info about possibilities... chapter 4: teach you basic syntax... chapter 5: start incorporating single uses of terms... chapter 6-10: "advanced" use of combining terms/functions
4 is beginner, 5 is intermediate, 6-10 advanced. technically a single join falls into intermediate in the scenario provided
i think that ties directly back to your comment about a "low bar" for advanced, from typically book-structure, despite those being the simple things you just "do" now
i think i had a sql 2 book and the other was "learn sql overnight"
i only still have the one (gave it to my wife to help her learn about databases)
@MathieuGuindon i mean... yes... you saying it that way also makes me kind of question how i label "beginner, intermediate, & advanced" users, though context does always help
@MathieuGuindon assuming you were rhetorical, but as i said you made me question how i label... i consider that intermediate, because the person is using functions, but may or not understand why and the limitations... if they're using index/match i consider that pretty advanced, since people using that tend to understand "why" they're doing so.
okay, my point was a bit disingenuous - SUM and VLOOKUP might both be functions, but the concepts behind them are wildly different... it's easier to figure we're looking at a bunch of cells and we're going to add them up, vs conceptualizing what a "lookup" might be
@Cyril I'd put typical lookup usage of INDEX+MATCH under intermediate, and then under advanced I'd discuss how INDEX can yield an array, and what that means when you use it in, say, a named range expression
The other factor to remember is that lot of advanced topics requires broad background knowledge. That's why sometime they dub the curve as a hockey stick.
take "dependent dropdowns"; I have one (in-cell data validation list) listing sales reps, the cell is named SalesRepCode; then next to that there's a CustomerNumber cell with a data validation list that references this named range:
Agreed. I think that is an example of advanced use, especially because you're using other concepts like the table reference with the INDEX.
That said, I want to say that this really should be a VBA function. It gives me a headache just to look at it. :-p (I'm a bit sensitive to nested functions)
Is there any quicker method than using a for loop to find the first instance of a formula in a cell?
For Each dc In .Worksheets("testWS").Range(searchRange)
If dc.hasFormula() = True Then
formulaRow = Split(dc.Address, "$")(2)
formula = dc.formula
Exit For
End If...
VBA Rubberducking
VBA Rubberducking