> Workbooks.Open is also side-effecting: successfully opening a workbook makes that workbook the new ActiveWorkbook, and so global state is affected by its execution.
:+1: for mentioning side-affecting behavior.
@theVBE-it'srightforme I'm one of the lesser contributors here. If you stick around for a while you'll learn a lot.
Wait, is it .Sheets() or .Worksheets()?
The first thing to note, is that they aren’t language-level keywords, but member calls. If you don’t qualify them, then in the ThisWorkbook module you’ll be referring to Me.Worksheets (i.e. ThisWorkbook.Worksheets), and anywhere else in the VBA project that same code be implicitly referring to ActiveWorkbook.Worksheets: that’s why it’s important to properly qualify member calls. Worksheets is a member of a Workbook object, so you explicitly qualify it with a Workbook object.
Cha-cha-cha-Chaining Calls
... we cast it to a usable compile-time interface (that’s Worksheet), ...
--> we cast it to a usable compile-time interface (whose known type is of Worksheet),
~~~
... but now you know why, how, and when to dereference a Worksheet object into a local variable
---> After all that's explain above you now know:
- *why* the recorder is doing this (It blind-pig captures actions without understanding)
- *how* it works the way it's written (with all its nasty implicit laden pitfalls waiting to be ask about on StackOverflow)
^ @MathieuGuindon a couple more. These are the #Simple things that a lot of VBA coders never figure out.
This is the stuff that makes me so glad I found RD & this pond to help me understand.
PS. I still don't fully undertstand why .doccls exports can't be imported back in. From what I understood they are owned by the host application, whereas (Class | Standard) Modules, and Forms are owned by the IDE. That's why they have the distinction.
@theVBE-it'srightforme Iven is out self-flagellator-in-chief... he's doing great, but doesn't realize it ;-)
7
@theVBE-it'srightforme if you're at 1 rep, a downvote won't affect you. Otherwise an upvote scores +10, and a downvote hits -2 (and if it's an answer then the downvoter took -1 as well)
If you know the column is G and all the data is contiguous, then just loop through the rows and check the cell value directly:
Dim rows As New Collection
Dim sheet As Worksheet
Dim lastRow, i As Integer
Set sheet = ThisWorkbook.Sheets("imported d")
lastRow = sheet.Cells(1,7).CurrentRegion.Rows...
I mean there's no problem with that, really. just make sure your puppets remain in their own silos and never interact with each other in any way, and you're good.
Context
I'm currently working on a form in excel for teachers which they can use to write tests which we will create inside a learning platform. So we want to know which questions the teachers want to ask.
In order to keep everything simple we are using radio buttons for the teachers to mark th...
Holly sh*t this sentence... :-D Really hard to understand/translate.
A default member access hides away which member is actually called. Although it is apparent that some call is made in the case of an indexed default member access being explicit is usually better for readability.
Dim coll As Collection
Set coll = [...]
Dim item As Object
Set item = coll(2)
' this is actually a call to the indexed default member:
Set item = coll.Item(2)
@SonGokussj4 ^ this should be an example if I understood the sentence correctly
LOL. A true story: when we moved across states, we did pack some stuff and shipped them via USPS. We shipped 15 or so boxes. We got 14 of them approximately 2-3 weeks ago. Hmm, one's missing. Call USPS office and they're all "We're sorry. We'll document it."
Document it? Turns out they don't even track their packages so they literally have no idea where it could be.
We take it for granted that it's gone forever. Fast forward a year or so, that box appears at our door. We're all WTF?
There was no apparent reason for the delay -- the addresses on the box were clearly legible and all. Someone said that it probably ended up in Bangkok, Maine since that's where all undeliverable mail go to die.
True story: My aunt once knitted me a sweater and shipped it to me for my (10th?) birthday via UPS (which tracks every package by default). The package got lost and somehow they couldn't find it. She has refused to use UPS ever since. Literally anything but UPS.
DHL isn't big in the US but they are in the rest of the world. Our F1 tickets are shipped via DHL (no shock, they're the official "world-wide partner"...)
And I dislike how Amazon is a major competitor in 3 different markets--retail, logistics, and IT. At least MS and Google and Apple are pretty much just computer hardware/software.
The only shipping I take umbrage to is "SmartPost" where you ship via UPS/FedEx/Whatever and they take it to the local post office instead of your door. I'm sure it's much cheaper for the shipper, but it takes 24 hours (or so) to get it to the post-office, then a week to get it to your mailbox.
TBH, UPS dropped a package off next door just last week. I was WFH & heard them on the porch. Went outside & talked to the guy - he said "we weren't sure what the right house number was, so we did drop something off next door yesterday".
TBF, the house next door has no house number on it, and we'd lost but not yet replaced 1 digit from ours, so there was room for confusion for someone new to the area.
He walked next door and knocked on the door. The somewhat psycho/deranged lady next door didn't answer even though she was obviously home.
She did bring it out to me the next day when she saw me go by.
from the radio, many, many years ago <-- that carp's still in there. :(
back to somewhat OT...
I think I asked about this a while back... we've got a DB here at work hosed on SQL Server Parallel Data Warehouse back end. It seems that the only way to access it is via VS's SQL Server Object Explorer - I can't get to it via SSMS.
Everything I'm doing at this point is in VBA - can I/should I be able to access this server via any sort of ADO/DAO/OBDC from VBA? The connection requires using port 17001 (IIRC), not the standard SQL Server port.
If not, I guess I'll have to (for now) query data by hand, save it to Excel/CSV then import it into my own DB to run reporting on it.
Yes, the ideal would be to learn a new language cough C## cough and write some "real" code, but that's not gonna happen in time to get this particular reporting update done.
> When a report connects to SQL Data Warehouse to retrieve report data, it connects to the control node, which manages query processing, in the SQL Data Warehouse appliance. After the connection is made, there are no differences between working with an instance of SQL Server that is and is not within a SQL Data Warehouse environment.
Extrapolating from this, it implies that it's just another SQL Server connection. :\
I've got the docs opened and I'll read through it. Specifying the port will probably be something special with the connection string so I'll muddle through that for a while.
I don't believe so, but I really don't know for zure...
and yet, they've stuck with "Access" all these years...
@this that was my implication, even if it didn't come out that way
this is pretty cool: Just got an email from UPS. I've got a package on a truck to be delivered today. They now have GPS tracking for the truck so I can get real-ish time updates on where the truck actually is. No guarantee that my house will be next, but I can see where the driver is...
That's new since the last package I had delivered last week.
Just think: a housewife somewhere is on phone, screaming at the customer service that driver made an unscheduled stop at McDonald's one block off her route
AIUI, UPS knows where every and each package are at any given moment. Everything's scanned going in and out, so it should be possible for them to figure out what to put in truck in advance.
Context
I'm currently working on a form in excel for teachers which they can use to write tests which we will create inside a learning platform. So we want to know which questions the teachers want to ask.
In order to keep everything simple we are using radio buttons for the teachers to mark th...
VBA Rubberducking
VBA Rubberducking