VBA Rubberducking - 2018-04-25 (page 1 of 2)

« first day (1419 days earlier) ← previous day next day → last day (1761 days later) »

12:00 AM

RELOAD!

[banane-io/PDB] 1 commit. 7 additions. 7 deletions.

[rubberduck-vba/Rubberduck] 6 opened issues. 23 issue comments.

[rubberduck-vba/Rubberduck] mansellan created pull request #3965: Add menu and toolbar icons for VB6 to merge mansellan/Rubberduck/next into rubberduck-vba/Rubberduck/next

> Still need to hook the clipboard, to ensure that icons work in all locales and to avoid dumping the user clipboard on startup. PR'ing now to check that the approach is reasonable.

Also, I created a utility to capture icons edited in the VB6 toolbar icon editor and save them out to .dat files, I'll make this available in a seperate repo.

[rubberduck-vba/Rubberduck] mansellan added label PR-Status: Review Requested to pull request #3965: Add menu and toolbar icons for VB6

[rubberduck-vba/Rubberduck] mansellan added label PR-Status: WIP to pull request #3965: Add menu and toolbar icons for VB6

TTGTB

Mathieu Guindon

@mansellan night!

12:14 AM

Private this As TSomething https://rubberduckvba.wordpress.com/2018/04/25/private-this-as-tsomething/

Tweeted by rubberduckvba on April 25, 2018 at 12:08 AM

[rubberduck-vba/Rubberduck] build for commit 7a6c861c on unknown branch: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] [codecov[bot]](github.com/apps/codecov) commented on pull request #3965: Add menu and toolbar icons for VB6

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/3965?src=pr&el=h1) Report
> Merging [#3965](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/3965?src=pr&el=desc) into [next](https://codecov.io/gh/rubberduck-vba/Rubberduck/commit/bcf72dd4c455ac67fa8772c650a7a497135262df?src=pr&el=desc) will **decrease** coverage by `0.08%`.
> The diff coverage is `0%`.

```diff
@@ Coverage Diff @@
## next #3965 +/- ##
===========================

[rubberduck-vba/Rubberduck] build for commit 7a6c861c on unknown branch: 57.59% (target 0%)

Mathieu Guindon

@TweetingDuck hey wtf

I thought I had cancelled that schedule

dammit

rushes edits

Rubberduck News: Private this As TSomething

posted on April 25, 2018 by Rubberduck VBA

A post on Code Review recently caught my attention (emphasis mine): If you are setting up a class, don’t encapsulate a Type inside of it – you are only repeating what a class does! I am not sure where this anti-pattern comes from. The author of these words didn’t use the term “anti-pattern” in the… Continue reading Private this As TSomething →

Mathieu Guindon

cool, updated, and 0 link clicks from the tweet

@MathieuGuindon if you're going to talk about me, I'd appreciate a heads up....

Mathieu Guindon

12:25 AM

LOL

Took me a while to get that one.

@PeterMTaylor My experience is pretty limited; I've worked with it for RD's Extract Method and for one more..... escape me ATM. I could borrow large amount of code from others easily but then there were few aspects that didn't exist and that took more time than I felt should have; even had Hosch help me out.

;-) I'm so relevant. :p

> many VBA programmers have never dared implementing a class module “because it’s too confusing” and hard to follow.

That was very true when I first saw it. But so does the private this as tperson...

That's where the hand-holding laymans wording explanations of Iven will come to the rescue, eventually.

Mathieu Guindon

@IvenBach imo mThing = pThing and then Thing = mThing isn't as straightforward as this.Thing = value and then Thing = this.Thing, but YMMV

TBH I always hated that.

even though I did use HN everywhere but almost never in that way.

and it really gets my goat when I see Public Sub Foo(strBar As Long)

OTOH, I do find myself doing things like Public Sub DoSomething(LongValue As Variant) which is sort of HN-esque but necessary because I must use Variant to support null as a valid input while indicating that I expect a Long subtype.

Mathieu Guindon

12:41 AM

> What’s an easy way to know how much space the instance fields of a class take up?

LenB it.

Mathieu Guindon

Without the UDT, that's manual

@this the class?

don't think that'll work

you'd have to LenB each field individually

Mathieu Guindon

bingo

So there's concrete advantages, and I'm still struggling to see a real downside

well, remember I accidentally SO'd myself?

Mathieu Guindon

12:43 AM

I... wut?

Public Property Get Name(Value As String)
  Name = Value
End Property

@MathieuGuindon I agree with you. I just had a really hard time understanding what was going on with a private field in vba.

something like that.

this compiles, and will run(!)

I had to pause and think and realized I forgot to add the this.

Mathieu Guindon

but once the pattern is a habit, it hits you like a 2x4 doesn't it?

the HN fan could say "see that's why you should mName = Value

Oh, yes, I don't disagree. I had to "adjust my google" so to speak

Mathieu Guindon

12:45 AM

the HN fan didn't read Spolsky's blog, obviously :)

so to remember that Name = Value in a property accessor must be wrong.

Mathieu Guindon

hmm, we can inspect for that

@this except that should be Property Let, but yeah ;-)

#FunFact this is totally legal:

Public Property Let Class1(Class1 As Class1)
    Set Class1 = Class1
End Property

but won't SO, even though it looks like it ought to.

Let, Get, what's the difference they be all same. :p

@Hosch250 4gb for ASP.NET and web development

@IvenBach get 4 slices toaster.

12:52 AM

Now the option to dl that so I can build hoschs solution isn't coming up.

Success! Re-extracting gives the message again. #NotTheRightWayButItWorks

Why is leaving your comfort zone so hard!

Because by definition, the comfort zone is comfortable

Mathieu Guindon

@IvenBach would it be a comfort zone if leaving it was easy?

^

For that matter leaving the bed in the mornings isn't easy...

Mathieu Guindon

point

1:00 AM

Sure. Just arrange for your mattress to be set on fire when it's time to get up.

Mathieu Guindon

lol

simple

Just get a jack to lift up your mattress until it's vertical and dumps you out of bed?

Mathieu Guindon

IME that just turn the floor into a bed

:Morning: ":groan: But I dont :jack-enjages: OOF I'm out of bed"

reminds me of that episode of Donald Duck

Mathieu Guindon

1:02 AM

Run to the Hills intro as an alarm ringtone does the job TBH

the one where the dripping faucet was driving him crazy; turned into a bomb

@Hosch250 The Database project?

1:19 AM

[rubberduck-vba/Rubberduck] bclothier commented on Rubberduck.Core/UI/Command/IMenuItem.cs of pull request #3965: Add menu and toolbar icons for VB6

[rubberduck-vba/Rubberduck] bclothier commented on Rubberduck.VBEEditor/Utility/LowColorImage.cs of pull request #3965: Add menu and toolbar icons for VB6

[rubberduck-vba/Rubberduck] bclothier commented on Rubberduck.VBEEditor/Utility/LowColorImage.cs of pull request #3965: Add menu and toolbar icons for VB6

[rubberduck-vba/Rubberduck] bclothier commented on Rubberduck.VBEEditor/Utility/LowColorImage.cs of pull request #3965: Add menu and toolbar icons for VB6

[rubberduck-vba/Rubberduck] bclothier commented on Rubberduck.VBEditor.VB6/SafeComWrappers/Office/CommandBarButton.cs of pull request #3965: Add menu and toolbar icons for VB6

[rubberduck-vba/Rubberduck] retailcoder commented on Rubberduck.VBEEditor/Utility/LowColorImage.cs of pull request #3965: Add menu and toolbar icons for VB6

@Duga educate me, why wouldn't it be good, given that it's basically continuing reading from the same file at each offset, @MathieuGuindon?

Mathieu Guindon

I didn't look at the code, just the review comments... but in general once you've consumed a stream the last thing you want is to reuse it

that'll teach me to speak while foot is in mouth

Ok, just wondering if I didn't know something - I do agree in general reusing stream on different files or at random points => bad.

but in this case, it's sequential read so....

Mathieu Guindon

yeah kinda makes sense then

Thanks, it totally solved it!! — MaxF 39 secs ago

I know! I wouldn't have written that answer if it didn't!

should have dupe-closed, but for some reason my home browser is missing my whole "Stack Overflow" bookmark folder

1:50 AM

@Hosch250 I took a look at anything with .cs. I got scouts right now. Tonight I need to figure out how to upload to my repo so I can submit a PR.

This #OutOfMyComfortZone stuff is hard.

1 hour later…

3:06 AM

@IvenBach PR to where?

@IvenBach No, they'll be in the other project.

@PeterMTaylor Interesting read.

I'm of the opinion that if you have a backlog, something's wrong.

Well, that's not quite what I mean.

I mean, you have one set: stuff that needs to get out in the next release, and another set: stuff that would be nice to have at some point.

If something isn't critical enough to make the cut one way, then it is the other way.

That won't always work of course, but nothing always works.

But, this tends to keep clients really happy. They ask for it, they get it in a release or two.

And the faster the release cycle, the better.

Also, as far as controlling access to the ticket system, that just doesn't work in some orgs.

We are SOC 2/PCI compliant, which means we have to have a ticket for every single PR.

That means I need a ticket if I want to write unit tests, if I need to make a tweak to a DB migration script, or any dev-only issue.

I can see maybe keeping two ticket queues if you need to, or using roles to limit users to creating tickets with specific labels, but it gets messy if done wrong, and it's hard to do right.

Anyway, TTGTB.

gets off soapbox, turns soapbox over, climbs in

4:10 AM

@Hosch250 I probably did it wrong then.

I looked through it but nothing I was looking through seemed wrong. Only minor suggestion which I think were from R#.

1 hour later…

5:18 AM

[rubberduck-vba/Rubberduck] bruglesco starred us

2

6:00 AM

[rubberduck-vba/Rubberduck] WaynePhillipsEA commented on pull request #3965: Add menu and toolbar icons for VB6

> Good work. Unfortunately, I still don't get many icons. I get 1, sometimes 2 icons, and the rest are blank. No exceptions in the log anymore. Just FYI, my locale is en-gb. I suspect the clipboard API hook will eventually sort this out.

6:20 AM

@this Apologies for butting in. I am playing catch-up on dialogue here. What is HN? Was just reading the Private this article and then switched back to that remark and am lost.

3 hours later…

9:01 AM

0

Q: With New MyTestableMacro .Run - What does it do?

I am following this tutorial by RubberduckVBA and have come across this piece of code, but have no idea what it really does: With New MyTestableMacro .Run dataServiceStub, wsServiceStub End With I have tried to search for "New keyword in With statement" as well as a few other things, but d...

soo ... With New is apparently something that's hard to grasp

9:37 AM

@QHarr HN = hungarian notation

@Vogel612 it's certainly not idiomatic in VBA. I think it's more common for people to have seen the more verbose version I posted and treat it as one "instruction" per line. There is something to be said about simplicity of having only one logical instruction per a line, especially if they don't have a programming background. VBA isn't Perl.

9:53 AM

@this Doh! Thank you

10:17 AM

[rubberduck-vba/Rubberduck] mirogta starred us

10:48 AM

[rubberduck-vba/Rubberduck] mansellan commented on Rubberduck.VBEEditor/Utility/LowColorImage.cs of pull request #3965: Add menu and toolbar icons for VB6

11:10 AM

[rubberduck-vba/Rubberduck] mistaven forked us into mistaven/Rubberduck

Hello Pond... am I right you can't debug Excel with VS? Or can you?

@QHarr rephrasing your question --- no, you cannot debug your VBA code with VS.

@this Thank you. Sorry for poor phrasing.

well, at least not easily as you would debug VBA - you might get to see the dissembly if you attach the native debugger, but that's way too low-level anyhow.

but you definitely can attach VS to Excel and debug any COM add-ins (e.g. RD) that way.

Yes dissembly being shown. I am currently posting a SO question about Excel crashing and VS showing anunhandled win32 exception occurred in Excel.exe

But I don't know if that is just an umbrella term

I think probably is

Mathieu Guindon

11:20 AM

@this meh.. I'd argue that it's a better use of the With keyword than using it merely to spare keystrokes with the member accesses. There's a reason the With keyword was created with the ability to own an object reference ;-)

2

pretty low level stuff. I admit I don't fully understand what it's saying myself.

Typically I end up looking at the call stack to diagnose the probable cause.

[rubberduck-vba/Rubberduck] mansellan commented on Rubberduck.VBEEditor/Utility/LowColorImage.cs of pull request #3965: Add menu and toolbar icons for VB6

@MathieuGuindon not saying it's "bad". It's just not commonly used that way AFAICS. Who know, maybe with some re-education, it'll become common.

@this I know where it is coming from I just don't understand why. So will finish off polishing the question and then post. Pretty sure you guys will know immediately as it is your area. I think a COM Object may be to blame. But I could be wrong.

user image

TIL something about VBA Intellisense

11:28 AM

Now posted stackoverflow.com/questions/50021133/…

@SlowLearner Intellisense≠ compiler

Make that 2 things...

Mathieu Guindon

Yeah we need to put the "intelli" back in "IntelliSense"

^ so that.

I was just surprised that I was getting options for returning something from a Sub. If that happens... well I wonder what crazy stuff I've let intellisense add to my code over the years...

11:32 AM

by far the most thing that bugs me the most is the inability to match uppercases like in C#.... we'd be rid of HNs much sooner had we that.

@SlowLearner I think it's more that the intellisense is "dumber" than a compiler for sake of optimization. AFAICT, the "compile" doesn't happen until you leave the line

so it won't try to predict whether the As Type clause is legal because you wrote Sub earlier.

and when I say "for sake of optimization", I refer to the state of hardware in 90s.

@this same, although I'm working with XML at the moment and XSD and just wouldn't have thought they'd not find some way of validating the intellisense prediction

... although appreciate that the VBA IDE is an old product with minimal upkeep as well (yay for Rubberduck)

would be cool to find a way to intercept the intellisense, I'm sure that's not trivial :-/

Yeah, I do agree w/ Mat that crappy IDE makes crappy code more widespread, and VBE is just a bit..... long in the tooth.

Oh, there is a way. The way is "AvalonEdit".

just need someone to hunker down and commit ... what? 3-6 man-months?

ah... well that, at least, looks like a good head start

12:16 PM

@this pretty tempted to get involved in Avalon. Have a uni assignment to get done first though.

12:27 PM

FWIW it doesn't have to be done solo. I think it's big enough to be split among people. It would definitely be great to see this make it to RD eventually.

12:50 PM

@Vogel612 so write a review!

I'd need to dig up my terribly rusty VBA

@IvenBach Did you just look at the Database project? That one is pretty good. Look at the other project.

1:07 PM

-1

Q: How to unit test VBA code? - Two different pointers

I am working on this excellent tutorial, but in the end my first test is not passing, due to the fact that I can clearly see I am creating two different arrays (and pointers), and trying to compare them with one another. Now the tutorial from what I can see leaves out a few lines of code that I...

vba unit-testing rubberduck

1:47 PM

1

Q: How to unit test VBA code? - Two different pointers

I am working on this excellent tutorial, but in the end my first test is not passing, due to the fact that I can clearly see I am creating two different arrays (and pointers), and trying to compare them with one another. Now the tutorial from what I can see leaves out a few lines of code that I ...

vba unit-testing rubberduck

Mathieu Guindon

2:01 PM

@StackDuck you're likely passing the variant parameters by value

@MathieuGuindon prolly should update that post to use LongPtr and also highlight the ByRef

Mathieu Guindon

yeah

@MathieuGuindon Hi, on your example you already had it as Private Sub IWorksheetService_WriteAllData(ByRef data As Variant), and that is the way I had it from the beginning, but it shows two different pointers, and I have your code verbatim, that is also why I have the AreEqual and not SequenceEquals — Alfa Bravo 48 secs ago

@this I'm missing something, shouldn't that work?

(it definitely did on my 32-bit Excel when I wrote that)

Hmm, that whole VarPtr stuff might have been a stretch / me trying to figure out something to test for in a short example that involves dependencies and stubs. I'll update the article to a simpler test that asserts the two arrays contain the same values, with SequenceEquals. I can assure you that the code did work on my 32-bit Excel when I wrote it.. I shouldn't have involved pointers there, it needlessly complicates things. That said, how to test whether a method is working with a specific array is a very valid question nonetheless. — Mathieu Guindon 7 secs ago

hmm, can SequenceEquals work with a 2D array?

2:24 PM

fwiw, i never got any Assert to work w/ array/collection

I had to write something for one of either

oh it was dictionary

#If LateBind Then
Private Sub AssertDictionariesAreEqual( _
    Expected As Object, _
    Actual As Object _
)
#Else
Private Sub AssertDictionariesAreEqual( _
    Expected As Scripting.Dictionary, _
    Actual As Scripting.Dictionary _
)
#End If
    Dim Key As Variant

    Assert.AreEqual Expected.Count, Actual.Count

    For Each Key In Expected.Keys
        Assert.IsTrue Actual.Exists(Key)

        Assert.AreEqual Expected(Key), Actual(Key)
    Next
End Sub

Mathieu Guindon

Assert.SequenceEquals(Expected.Keys, Actual.Keys) should have worked

let me verify - i think i did try it before but let's see...

oh wait, i think that's the issue - i didn't use Keys

I thought, Assert.SequenceEquals(Expected, Actual)

prolly asking too much

Mathieu Guindon

yeah. it is documented as solely working with arrays ;-)

@MathieuGuindon That's where you loop through it.

Mathieu Guindon

looking at the implementation, it looks like a n-dimensional array should work

2:32 PM

confirmed - `Assert.SequenceEquals Expected.Keys, Actual.Keys
Assert.SequenceEquals Expected.Items, Actual.Items` was what I should have done

as for the original question -- I need to copy the code and set up - will do it when i have a bit more time

but fwiw - if one uses VBA.Collection, #SOL

don't think it exposes a method to provide an array?

Mathieu Guindon

Okay, I just repro'd the failing WorksheetServiceWorksOffDataFromDataService test on 32-bit Office 2010. Gosh, wtf... — Mathieu Guindon 11 secs ago

@this nope

and so i'm clear - there's no method that eats the COM enumerator?

would be useful for those types of objects.

Mathieu Guindon

@this not that I know of

might be useful to provide that method.

@mansellan university? Or do I have a fellow unicycler at the pond?

3:31 PM

Is it expected that double clicking a folder in the CE will expand the folder?

If not, that would be a nifty feature to add...

@Hosch250 I looked at both. The only thing I spotted were a couple using that could be removed and one of your Derived : Base calls was redundant.

@FreeMan Sometimes you have to double-double click IE very fast and repeatedly.

Mathieu Guindon

@FreeMan is it?

Seriously?

OK, I'm kind of stuck on my SVG stuff, so I might as well start cleaning it up.

I'll write a self-review as I do and send it to you.

I'm not sure what a peer review is. I couldn't run the code, it did build however. I'd need a bit of instruction on what an actual peer review is, aside from using VS or R# hints.

https://goo.gl/k4zVWJ - SQL Injection, Little Bobby Tables, Mrs. Null, Mr. O'Neil - is it "boy that's a lot of code for so little benefit"? #VBA #Excel #Access #SQLinjection #MSAccess #SQL #Adodb - in the awesome article from my first guest @retailcoder @rubberduckvba 😎👨‍💻

Tweeted by vitoshacademy on April 25, 2018 at 2:57 PM

3:37 PM

@MathieuGuindon No amount of spastic, caffeine fueled multi-clicking causes the folder to expand, only hitting the little triangle next to it.

@MathieuGuindon Link to the post? I don't follow shortened URL's.

vitoshacademy.com/automagic-secure-adodb from the ever so slightly less paranoid among us

bookmarked, btw

Mathieu Guindon

@FreeMan it's essentially this:

25

Q: Creating ADODB Parameters on the fly

I have put together a small wrapper class to simplify creating parameterized ADODB queries with VB6/VBA. At this point I'm keeping things simple, so it's only supporting input parameters and from what I've tested it seems to work exactly as intended. The main reason for writing this, is because ...

sql-server vba sql-injection vb6 adodb

and this:

9

Q: Materializing any ADODB Query

Following-up on Creating ADODB Parameters on the fly and pushing the "wrapping" of ADODB a step further, I have written two more classes that allows me to expose methods that don't require a Connection object, without returning an ADODB.Recordset. Taking this method as a reference: Public Func...

sql vba vb6 adodb

@MathieuGuindon just curious about two things --- 1) why not simply generate the procedures a la EF?

Mathieu Guindon

?

3:46 PM

I have an utility that basically analyzes a SQL Server database metadata and generates all the procedures. That way a stored procedure uspSomething(@foo int @bar varchar(255) gets a VBA representation of ADO.DB.uspSomething(foo_Long As Variant, bar_String As Variant)

Mathieu Guindon

oh you mean leverage the implicit command stuff e.g. myConnection.SomeProcedure p1, p2

no no

Mathieu Guindon

oh

what then?

it's literally a VBA procedure that runs a ADODB.Command, builds specific ADODB.Parameter with all the properties based on the metadata.

then pass in the VBA parameters into those defined ADODB.Parameter, and execute the command.

Mathieu Guindon

hmm.. well, see, that "generator" code could use that SqlCommand instead of wiring up the parameters "manually"

solves a different problem, basically

3:49 PM

That way, there's no need to wrap or guess what a parameter ought be - the metadata is all there from SQL Server, so as much of the generated code can be made compile-time

Mathieu Guindon

I see

so you basically turn the database into an API

Yeah. For me it's more important to have visiblity into VBA procedures calls what stored procedure.

Yeah

that approach let me get all that static code analysis for free (using MZ-Tools's See All Callers)

Mathieu Guindon

but what if you want to run one stupid simple SELECT statement against a database with 2497 tables?

(ok, it's not really a real code analysis but close enuff)

NO

no naked sql

Mathieu Guindon

I don't care much for "naked sql", as long as it's properly parameterized

3:51 PM

access should be always via the sprocs or views, IMO.

Sure but if you embed the naked sql, you've complicated the lifecycle management

Mathieu Guindon

okay, so, what if you only need to select one single view against a database with 2497 tables?

there's a bug in SQL. well.... guess we gonna issue a new front-end version.

do it in a sproc.....

Mathieu Guindon

so you generate a TON of VBA code that lets you query just about everything, only to call 1 procedure?

ok, then I didn't understand

Mathieu Guindon

maybe I didn't ;-)

3:53 PM

oh you think i'm creating views?

no i'm not

I'm just harvesting what is aleady there in the database

That hour went by fast.

@TweetingDuck @MathieuGuindon I like the use of CallByName

Mathieu Guindon

@this yes - my PoV is that that's potentially overkill ;-)

so if I wanna a view, I must create it in DB by hand first then the code generator will pick that view.

Yeah, I don't actually do CRUD stuff that EF would create

Mathieu Guindon

it's great if you're connecting to a db that's dedicated to your app though

3:54 PM

Yeah.

the code generator has some simple filter to restrict to a certain schema if it's necessary.

But yes, my typical scenario is that the database is purpose built, so I basically do "DB-first", then generate the wrapping procedures to provide end-to-end traceability.

Mathieu Guindon

again, that works for a specific use case. if you just need to get a value from a table in an ERP database, it's overkill to generate anything based on the db schema

Yeah, I see what you mean - I probably wouldn't want all 2500 tables.

But if I were working with such databsae, I'd be creating a new schema or even a dedicated database to encapuslate the access.

then have the generator work off that portion.

Mathieu Guindon

probably solved by adding an "overload" that takes a ParamArray of object names to generate code for

or that

yeah, i usually use schema or database as filter. Array of names is too error-prone, IMO.

"oh drats I forgot to add that one thingy...."

now the 2nd question was - why encapuslate SqlResult? What's wrong with a disconnected recordset?

Mathieu Guindon

3:58 PM

schema doesn't help if everything is in dbo, and if you only have SELECT and EXECUTE permissions on the db server you can't just go and create your own db

@this it's an ADODB type; the idea was to eliminate ADODB-anything from the calling code

In which case I bludgeon their DBA or IT guy for poor management.

#DoItRightDammit

Mathieu Guindon

lol

Oh ok, the article left me w/ the impression that it was a List something class that basically read the values off the recordset.

Makes more sense now.

Mathieu Guindon

huh, but SqlResult is only in the CR post.. I purposely removed it from the article!

sorry - I was actually reading and referring to the CR post

Mathieu Guindon

4:01 PM

k

sorry should have said "post", not "article".

#IvenWords

Mathieu Guindon

FWIW SqlResult is essentially a collection and QuickExecuteXxxxx is consuming the recordset and pulling the results into a SqlResult instance

works nicely for small recordsets

for larger ones you probably don't want to iterate it twice (once to populate, once to consume)

That's the part I don't get. If you already got an ADODB.Recordset, you've already paid the cost. So changing to a collection seems like a busy work even for a small collection.

After all it comes with lot of stuff like filtering, sorting, and wahtever that would be hard to implement in a custom collection class.

and disconnected recordsets means there is no further network traffic, so it becomes a blob in memory with all those extra methods for "free"

Mathieu Guindon

that would have worked, but the rs is connected so shutting the connection means caller can't iterate it

I get the idea of wrapping recordset with a facade but to copy values to a collection?

You can disconnect and iterate it.

It just needs to be a client-side recordset.

Mathieu Guindon

4:07 PM

IOW iterate the cmd.Execute rs only to populate another rs?

nope.

Mathieu Guindon

I'm missing something

cant' find the original kb article - this'll have to do: 4guysfromrolla.com/webtech/080101-1.shtml

note in particular the .ClientLocation = adUseClient, then later Set .ActiveConnection = Nothing

that's now disconnected and you can still iterate, sort, filter, clone, whatever with that rs.

Mathieu Guindon

hmm

that would work

and it could be stubbed too, so doesn't interfere with testability

Yeah, so that's why I don't see point in copying the data to a collection.

Right. I can see using a facade for the recordset if you need to avoid using ADO in the calling code but not not using the recordset in the implementation.

Mathieu Guindon

4:13 PM

14 mins ago, by Mathieu Guindon

@this it's an ADODB type; the idea was to eliminate ADODB-anything from the calling code

that way you could have SqlCommand in an add-in, reference the add-in, and do parameterized SQL stuff without needing to worry about ADODB.

(and hey SqlCommand could "swap" ADODB for DAO and calling code wouldn't care less)

That I get. But it's all implementation details; a facade over the recordset means it's still easy to expose niceties like sort, filter, clone without them knowing that they have a ADODB.Recordset under the hood.

Mathieu Guindon

yeah

truth is, that code was written back when I was working as a VB6 dev in a code base where folks would open a recordset, iterate it, and never close it, often leaving connections dangling and whatnot

the goal was to provide a simple API that dealt with all of that

copying to the collection/array, you've basically bought a big honking, expensive truck only to insist on driving on the old dinky car that you wouldn't trade in and pay a hefty payment on the said truck.

Mathieu Guindon

@this you'll probably want to post that as an answer to the CR post.

Oh yeah, I totally get that. VB6 and VBA codebases tend to be quite promiscuous with the connection details. :(

Mathieu Guindon

4:18 PM

> SqlResult should wrap a disconnected recordset, instead of reinventing a square wheel

Hmm. Yeah. you're right. I just wanted to be sure I understood the motivation and that I wasn't missing something. I'll post to that.

Mathieu Guindon

:+1:

@Vityata ^^ this conversation would probably interest you ^^

@this Don't let my bad word habits rub off on you.

4:42 PM

@MathieuGuindon Came across exceldevelopmentplatform.blogspot.com/2018/03/… that appears to let you use Linq and Lambdas in VBA. Thought you might be interested.

Mathieu Guindon

stringly-typed. defeats the whole purpose...

interesting nonetheless though

@IvenBach University - doing an Open University BSc.

4:59 PM

Duck check: msdn.microsoft.com/en-us/vba/language-reference-vba/articles/… tells some (IE Win16, VBA7, ...) of the variables are predefined. I thought the coder had to define all the conditional compilation arguments.

Are these for convenience?

those are built-in constants

you can additionally define your own constants as well

:+1: Now I get it.

consider this. If VBA6 and VBA7 weren't built-in how do you guarantee that VBA7 code won't run on a VB6 host?

That makes sense why so many examples have If VBA7 then ...

#If VBA7 Then but yes

5:03 PM

I feel really dense sometimes.

FWIW in a VBA6 host, the VBA7 and Win64 does not exist at all

@MathieuGuindon - thanks, interesting. @this - feel like becoming a guest author? :D

They'd then default to false, or just not compile?

@Vityata of what?

@IvenBach put it to test. do something like #If ThatDoesNotExist Then

(and don't define the ThatDoesNotExist --- try to run the code. What happens to the code inside that branch?

I typed without thinking... I knew the answer, just had to process my question first.

The code inside the branch doesn't exist in p-code.

5:12 PM

Ok, good.

now, what about this.... Assume the host is VBA7:

#If VBA6 Then
  ....
#ElseIf VBA7 Then
  ...
#End If

which branch gets executed?

I want to say that the #ElseIf VBA7 Then branch will be executed. But I'm going to go with #If VBA6 Then is what gets executed. this is because if it's VBA7 compatible it'd be VBA6 compatible also.

did you try and run the code?

Doing that. I prefer to solidify my answer before testing.

Sub CheckingCompilerConstants()
    #If VBA6 Then
      Debug.Print "VBA6 compatible"
    #ElseIf VBA7 Then
      Debug.Print "VBA7 compatible"
    #End If
End Sub

Gave me VBA6 compatible

so you can see how ordering matters since VBA6 and VBA7 are both true for a VBA7 host

If you wanted to have a VBA6 exclusive #If VBA6 And Not VBA7 Then would be needed.

5:19 PM

you could but that's overkill. I don't have a good example where it would be needed, to be honset.

I just #If VBA7 Then .... #Else ... #End most of time

Nor do I.

#If VBA7 Then .... #Else VBA6 Then .... #Else .... #End If would be a way if you need to do something for VBA6 only.

I've been bitten before by my own poor ordering before.

the only time the #Else would be actually executed would be if the host is VBA5 host.... which is what?

a bajillion years ago?

@this of vitoshacademy.com :D Writing an article about anything related with VBA, Access, Excel :D

You may put your ideas about ADODB :)

also

:D

5:48 PM

@Vityata vitoshacademy.com/vba-avoid-nested-loops-with-recursion-part-2 You can also order 7 Mixed Fruit.

Mathieu Guindon

You're comparing apples and oranges, and likely making R do something it isn't optimal at (I know nothing of R, so I wouldn't know what exactly). R is apparently interpreted, and VBA is compiled to p-code, which is interpreted by the VB6 runtime. VBA is all-purpose (that's what the "A" in "BASIC" stands for), R seems much more specialized. You're racing a Corolla with a dirtbike: who wins depends what the track looks like. — Mathieu Guindon 14 secs ago

figured VBA makes a nice little Corolla

« first day (1419 days earlier) ← previous day next day → last day (1761 days later) »

Transcript for

Apr '1825

VBA Rubberducking

This chat has moved to Discord: discord.gg/MYX9RECenJ

antlr com-interop parsing rubberduck vb6 vba whiskey-tango-foxtrot

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile