VBA Rubberducking - 2018-10-29 (page 1 of 3)

« first day (1606 days earlier) ← previous day next day → last day (1574 days later) »

12:00 AM

RELOAD!

[rubberduck-vba/Rubberduck] 1 closed issue. 10 issue comments.

4 hours later…

3:58 AM

Octobers almost over. Have you qualified for your 2018 Hacktoberfest shirt?

3 hours later…

6:41 AM

Yep. I think I had already qualified at the 6th.

4 hours later…

10:28 AM

0

Q: Code to convert survey output to usefull input runs slow

I wrote this code to convert survey output to usefull input used in another system. This sub is used to identify the individual events that are lumped into one cell, which can't be split using the text to columns function due to the use of an improper delimeter I can't control. Added is one samp...

11:13 AM

0

Q: Searching multiple fields by split string items

I'm working in VB6 with an MS-Access database and currently the project has a stock search function. There's one textbox that a search term can be typed into with 4 checkboxes to select which of the fields to search - the user can currently select either one of multiple of the checkboxes to allow...

sql search iteration ms-access vb6

11:53 AM

[rubberduck-vba/Rubberduck] daFreeMan opened issue #4468: More SCP silliness - adding an extra "("

> RD .4058

Starting with:

DateSerial(Year|)

Type a `(`, end up with:

DateSerial(Year((|))

Or, for the most _M_ CVE:

(|)

Typing a `(` yields:

(((|))

[rubberduck-vba/Rubberduck] daFreeMan edited issue #4468: More SCP silliness - adding an extra "("

[rubberduck-vba/Rubberduck] retailcoder added label bug to issue #4468: More SCP silliness - adding an extra "("

[rubberduck-vba/Rubberduck] retailcoder added label feature-autocomplete to issue #4468: More SCP silliness - adding an extra "("

[rubberduck-vba/Rubberduck] retailcoder added label feature-self-closing-pairs to issue #4468: More SCP silliness - adding an extra "("

[rubberduck-vba/Rubberduck] daFreeMan created comment on issue #4468: More SCP silliness - adding an extra "("

> As an additional note, starting with the borked:

(((|))

hitting <kbd>Backspace</kbd> yields

((|)

which ain't right neither...

Good morning, @MathieuGuindon, I'm glad I can start your week off on a positive note! ;)

[rubberduck-vba/Rubberduck] retailcoder created comment on issue #4468: More SCP silliness - adding an extra "("

> I noticed as well, thanks for making an issue for it. Was introduced with the previous prettifier fix. Unfortunately that's the part that can't really be unit-tested, making it vulnerable to such regression issues.

[rubberduck-vba/Rubberduck] daFreeMan edited comment on issue #4468: More SCP silliness - adding an extra "("

> As an additional note, starting with the borked:

(((|))

hitting <kbd>Backspace</kbd> yields

((|)

which ain't right neither, but seems reasonable for the situation.

Mathieu Guindon

@Duga the backspacing is correct though

as noted in my updated comment.

at least the backspace isn't equally broken. Or, maybe, it would be better if it was?

;)

for some values of better

Mathieu Guindon

I'd actually take a SCP service bug over a prettifier bug, yeah ;-)

SCP service can be tested =)

But the prettifier is at the mercy of the VBE, basically

12:03 PM

to some extent, we're all at the mercy of the VBE...

Mathieu Guindon

aren't we haha

user image

user image

^^ noice!

not much pumpkin left there, well done

Mathieu Guindon

=)

12:24 PM

Is that Jack Skullington?

[rubberduck-vba/Rubberduck] MDoerner created comment on issue #4468: More SCP silliness - adding an extra "("

> I observe this behaviour with double quotes in parentheses, as well.

Either way, nicely done!

12:36 PM

[rubberduck-vba/Rubberduck] bclothier created comment on issue #3979: Inspection: Member Range implicitly references ActiveSheet - false positive with fully qualified arguments

> Coming from a POV of an Access developer, I'm surprised that nobody has mentioned using `Me` in the cases where one intends to write code that should operate on the sheet's code-behind.

In Access, it is legal to do either:

```
MyControl.Value = "foo"
```

or:

```
Me.MyControl.Value = "foo"
```

However, I think it's safe to say that majority of Access developers would prefer the latter form over the former because it's now explicit that this is a member access to a property of

[rubberduck-vba/Rubberduck] bclothier edited comment on issue #3979: Inspection: Member Range implicitly references ActiveSheet - false positive with fully qualified arguments

> Coming from a POV of an Access developer, I'm surprised that nobody has mentioned using `Me` in the cases where one intends to write code that should operate on the sheet's code-behind.

In Access, it is legal to do either:

```
MyControl.Value = "foo"
```

or:

```
Me.MyControl.Value = "foo"
```

However, I think it's safe to say that majority of Access developers would prefer the latter form over the former because it's now explicit that this is a member access to a property of

[rubberduck-vba/Rubberduck] FastExcel created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> We seem to be talking at cross-purposes.

To clarify: I think the following statements are true

· Excel does not have a native currency data type so cannot cast values to currency.

· All Excel numbers are internally floating point doubles in the value layer regardless of the way the Excel rendering engine interprets the cell format codes for the portion of the grid that is visible in the active window whenever the screen is updated.

· Excel formulas and the

12:55 PM

@Duga @MathieuGuindon Why can't you unit test the prettier?

And why is there no separate CodePrettifier class?

[rubberduck-vba/Rubberduck] comintern created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> First, to be clear, I do not consider all of the statements to be true. In fact, the example code shows at very least the statement "Excel does not have a native currency data type so cannot cast values to currency" to be demonstratively false. That said...

> A) Which method should VBA programmers generally use to retrieve values from Excel? The choices are default method, explicit .Value or explicit .Value2

They should use `.Value` or `.Value2` as the situation warrants.

> B) Should

[rubberduck-vba/Rubberduck] comintern created comment on issue #3979: Inspection: Member Range implicitly references ActiveSheet - false positive with fully qualified arguments

> @bclothier I'd agree that Me is also preferable - that was the reference to #3569.

1:17 PM

[rubberduck-vba/Rubberduck] SteveLaycock opened issue #4469: Request for time project save

> If there are no immediate plans to reintroduce version control to RubberDuck would it be possible to introduce exporting the project file every X minutes.

[rubberduck-vba/Rubberduck] bclothier created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> @FastExcel

I agree (for little what I know about Excel) except for this point:
`The .Value2 method does not do this silent casting`

We need to have a MCVE that demonstrates this. So I figure we'd use the famous "0.1 + 0.1 + 0.1 = 0.3". With Excel's equality check, they are obviously doing more than just a straight equality. See the screenshot:

![image](https://user-images.githubusercontent.com/2367644/47651195-8892f880-db50-11e8-8021-9f638c20749a.png)

In VBA (recall that by defa

[rubberduck-vba/Rubberduck] bclothier edited comment on issue #3320: Inspection for Range.Value2 over Range.Value

> @FastExcel

I agree (for little what I know about Excel) except for this point:
`The .Value2 method does not do this silent casting`

We need to have a MCVE that demonstrates this. So I figure we'd use the famous "0.1 + 0.1 + 0.1 = 0.3". With Excel's equality check, they are obviously doing more than just a straight equality. See the screenshot:

![image](https://user-images.githubusercontent.com/2367644/47651195-8892f880-db50-11e8-8021-9f638c20749a.png)

In VBA (recall that by defa

1:33 PM

@Duga What was the issue that we pulled auto-save over?

@Comintern that was before me joining but I seem to recall a passing mention that it made VBE lag too much

[rubberduck-vba/Rubberduck] wqweto created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> > So, let's format them as currency:

. . . and now we start getting
```
? typename(Sheet1.Range("C1").Value)
Currency
```
. . . instead of `Double` the way `Value2` data-type (still) remains.

Mathieu Guindon

@M.Doerner because it's literally the VBE behavior. It can be mocked reasonably well, but the selection adjustments it does depend on the VBE itself; mocking those would be like mocking bytes being written to a mock file. Also there was a class dedicated to this, but it was moved to the class responsible for the module writes.

[rubberduck-vba/Rubberduck] bclothier created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> Correct, but the point is that if you write VBA formula, you are going to be doing it without Excel's extra work to make a "little unequal" equal; and the casting wouldn't help you avoid that fundamental problem because it doesn't exist in VBA. That's why I said it's not just a casting problem.

1:51 PM

@Duga Let's just make a suggestion for both .Value and .Value2 that says something like "Are you sure that's what you want to do?". We can add one for ADODB too "Precision and scale of VBA calculations on field values may not match the equivalent SQL for some values of NUMERIC(P, S)!" </sarcasm>

@Comintern I think you mispelled clippy for sarcasm.

lol

Mathieu Guindon

actually there are a few prettifier tests, but their usefulness is rather limited, compared to stepping-through with the actual VBE mangling the code

@MathieuGuindon why can't you just document how VBE mangles and add it to the test?

That way, any changes can only be an improvement.

Mathieu Guindon

because if I get that mangling wrong (and I will), I'll still be looking at passing tests testing broken behavior

2:01 PM

@this you want the VBE mangling documented? See #3320. ;)

yes, he understands the difference...

Mathieu Guindon

also it makes very stateful mocks, which means very frail tests anyway

@MathieuGuindon maybe i'm being a Captain Oblivious -- but why can't you type input in NP++ (or whatever), then repeat the same in VBE, then copy the paste the output? VBE shoudln't be changing the output based on moon cycle.... right?

@FreeMan Who's that jerk sprouting on about 0.1 + 0.1 + 0.1 = 0.3 anyway?

Part of what makes it difficult is that it can be both context sensitive and dependent on the input order.

@this I wasn't gonna say anything...

Ah yes. for that we'd need about 1000 unit tests (and hope that they all are accurate)

2:05 PM

You'd probably have to feed it a list of character codes and index changes to test it properly. Ugly stuff.

Ugly, yes. but is that really worse than cowboying it?

Ugly in the sense that the unit tests would need unit tests.

Mathieu Guindon

no, it's not random. yes, it's documentable and yes, it can be mocked... it means spending effort on getting a fake ICodeModule to not only spit out the test-specified test code, now you need to compute what the new code looks like after you've removed an arbitrary line, then what it looks like again after you've inserted that feature-modified line, and then get the caret position exactly correct, else all these green tests are bogus

And when you get to that point, how do you isolate what's under test anymore?

Mathieu Guindon

^

2:07 PM

Heh. Like that comic Hosch showed sometime ago -- "how do you unit test a unit testing framework?"

Mathieu Guindon

mock module has so much state & logic is needs its own tests

Stored Procedure design question: I need to check a configuration item (stored in the DB) to determine if a SP should run on a particular location's data. I'm thinking I should check it in the SP (bail if the SP doesn't apply to the current location) because that ensures every call is properly protected. Thinking correctly?

:starts working on the mock mock module:

[rubberduck-vba/Rubberduck] SteveLaycock edited issue #4469: Request for timed project save

@FreeMan Why wouldn't that check be in the SP?

Mathieu Guindon

2:09 PM

* get the caret position exactly correct at each step of the process

:starts working on the mock mock mock module: ... pauses to mock the mock mock mock module

@Comintern just checkin'

It's mocks all the way down!

Mathieu Guindon

maybe it's just me, but I'd rather step-through & get a solid implementation that's known to work as it should, and then it never has a reason to change

But you're writing an unit test that includes the result of the step-through.

Meh, as long that is what is being done, it's almost the same thing.

(for some values of "almost")

Mathieu Guindon

as long as the prettifier returns the caret position it puts the caret at, AC tests will be valid

2:15 PM

@Comintern Just for @FreeMan's consideration - one reason to do this in a separate SP is if the validation logic is quite complex and your application involves a number of steps (for illustration, let's say it's a wizard-style interface), then it makes sense to have a SP that is dedicated to validating the values. That way, the application can call it before processing on to the next form and if fails, update UI with flagged problems.

Mathieu Guindon

to test the prettifier, I'd need to suddenly start caring about where the VBE puts the caret after a code change - and no AC feature needs to care about that, because of the prettifier

You could have the actual SP call the validation SP internally as a extra precaution, which means that the validation SP will get called twice. That might be unacceptable. Either way, this requires more careful programming on the application's part.

Mathieu Guindon

the prettifier / ICodeModule-writing is beyond the "unit", basically.

If the check is quick to do, no sense in breaking it up.

@MathieuGuindon Now I gotcha.

@this Agreed, but if for example the final insert depends on the validation passing, call the validation SP from inside the insert SP before committing. That's where I was going with that.

2:17 PM

Yes, in one complex application we developed, we did basically that -- all actual SP always calls the validation SP which can be independently called.

In this case, the validation SP were quick to execute so it wasn't a big deal to run it twice

Come to think of it, I remember now that validation SP normally don't run in a transaction, too. However the actual SP will wrap the validation SP inside its transaction.

That way, the actual SP is guaranteed to succeed which was critical to the business. They did not want mangled half-data.

@this & @Comintern thanks for the extra thoughts. This particular instance is a simple check of a single value in the config table for the location, so it's basically a 1-liner. I will definitely keep in mind a stand-alone validation procedure should things become more complex in the future

But the system also would lead to lot of contentions. For more users there are, there'll be lot of fighting over precious resources.

YW!

[rubberduck-vba/Rubberduck] retailcoder closed issue #3038: 'Find All References' returns a factor of actual references

[rubberduck-vba/Rubberduck] retailcoder is checking what fun stuff @Duga can say about project cards

[rubberduck-vba/Rubberduck] retailcoder merged pull request #4466: Fix references count from bclothier/Rubberduck/FixReferences into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] retailcoder is bored so why not move a project card

[rubberduck-vba/Rubberduck] bclothier pushed commit 991e6500 to next: Change from ConcurrentBag to ConcurrentDictionary and get rid of old reference when adding references.

[rubberduck-vba/Rubberduck] web-flow pushed commit a177cc53 to next

Merge pull request #4466 from bclothier/FixReferences

Fix references count

[rubberduck-vba/Rubberduck] retailcoder merged pull request #4456: Clean up COM leaks in Rubberduck from bclothier/Rubberduck/ComSafeCleanupLeaks into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] bclothier synchronized pull request #4458: Host application document

[rubberduck-vba/Rubberduck] retailcoder merged pull request #4467: More quickfix bug fixes from comintern/Rubberduck/quickfixes into rubberduck-vba/Rubberduck/next

[rubberduck-vba/Rubberduck] comintern pushed commit d122208a to next: Comment out With blocks with TODO instead of removing them.

[rubberduck-vba/Rubberduck] comintern pushed commit 44006877 to next: Correct test.

[rubberduck-vba/Rubberduck] comintern pushed commit 7ae665ab to next: Correct QualifiedMemberName selection for results. Closes #3636

[rubberduck-vba/Rubberduck] comintern pushed commit 36737a4e to next: Do not report variable not used if it has assignments. Closes #2427

[rubberduck-vba/Rubberduck] comintern pushed commit 68855f6b to next: Ignore "passing" test with concurrency issues.

[rubberduck-vba/Rubberduck] web-flow pushed commit 0aaf7c1d to next

Merge pull request #4467 from comintern/quickfixes

More quickfix bug fixes

[rubberduck-vba/Rubberduck] retailcoder closed issue #3636: Variable is used but not assigned inspection fires multiple times with With block

[rubberduck-vba/Rubberduck] retailcoder project card. Enough said.

[rubberduck-vba/Rubberduck] retailcoder closed issue #2427: "Remove unused declaration" quickfix should remove assignment references too

[rubberduck-vba/Rubberduck] retailcoder project card. Enough said.

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot created tag Rubberduck-v2.2.0.4067

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot published prerelease Rubberduck-v2.2.0.4067

[rubberduck-vba/Rubberduck] build for commit a177cc53 on next: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] bclothier is checking what fun stuff @Duga can say about project cards

[rubberduck-vba/Rubberduck] bclothier is bored so why not move a project card

[rubberduck-vba/Rubberduck] jmarkman starred us

3

2:51 PM

just want to put this out there... nearly getting into a major car accident sucks

@KySoto Tell me about it.

jackass stopped on the freeway onramp

Not as bad as getting into a major car accident sucks though.

Church-acquaintance's friend lost their son on Saturday.

They'd already lost their daughter at 17.

Apparently they lost both kids now.

if my reflexes were crappier, id have hit hte dude and it would have been a major pile up

2:52 PM

@KySoto And someone could've been killed, which sucks even more than a wild accident that everyone walks away from.

yeah, it likely would have been me since i probably would have spun off

and there is no reinforced beam on my truck between the rear cab door and the front seat door

:(

granted i didnt choose the vehical, it was an inheritance fro my grandfather

But then, if you have a truck, it's heavier and higher than the car.

But, congrats on surviving :)

its basically a pickup truck

2:54 PM

^ Makes rollovers more likely when swerving.

well i didnt actually get into an accident

@Comintern True.

Even with weight and height taken into account, I'd take my chances in my car way before my truck.

@Hosch250 Ugh! As a parent, I can only imagine, but certainly don't want to...

@FreeMan Agreed. That's horrible to have had actually lost someone like that and not once.

2:58 PM

:barf: DMV is so much fun.

For accidents & near-accidents, I will have to defer to the wisdom of Calvin and Hobbes:

user image

@this way to make my day with that comic

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot published prerelease Rubberduck-v2.2.0.4068

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot created tag Rubberduck-v2.2.0.4068

[rubberduck-vba/Rubberduck] build for commit 46cdca5f on next: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] chentao807 starred us

3

@Duga @this - is this progress on the Access ghost process issue?

Today is YEAH!

New toys at work!

(New, big, powerful computer.)

3:05 PM

Congrats, Hosch!

w00t!

toyz is fun!

for all values of fun!

@FreeMan No, that's 4458.

I managed to conflict myself: chat.stackexchange.com/transcript/message/47391760#47391760

oh. :(

easily resolved, though. It's still building, though

well, I grabbed 4068 already, so I've got whatever's fixord there

3:08 PM

I doubt you'll see much difference w/ 4068. That was from 4456, which is mostly invisible to the user, I think.

but i laso think you get Comintern's quickfixes, too

Nope, not in yet. The other fix was the reference count being incorrect.

His PR is queued, too. AV be extra slow

[rubberduck-vba/Rubberduck] build for commit f156ec7c on unknown branch: AppVeyor build failed

BUILD FAILURE!

Mathieu Guindon

@Hosch250 merry x-mas!

@Duga gosh, that build went all sorts of wrong.

Mathieu Guindon

about #4427 / underscores in public members of a class module (@Hosch250's PR) - I think we'd need to leverage @this' work on IHostApplication first, no? I mean, the annoying false positives for event handlers are what's holding me back from merging.

3:24 PM

@this meh, unless there's something specific I'm after, I usually grab one update per day.

maybe more if work's really boring.

[rubberduck-vba/Rubberduck] retailcoder commented on Rubberduck.Parsing/Rewriter/AttributesRewriteSession.cs of pull request #4465: Reorganizing Rewriting

@MathieuGuindon you don't need that. You get that from typelib api

Mathieu Guindon

oh

and that includes any ActiveX controls on a sheet or Access report?

I already suggested that to Hosch.

anything

Mathieu Guindon

then #4427 should handle it

...and "procedure not used" should follow

3:29 PM

yes, those have project cards in the typelib api project

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot published prerelease Rubberduck-v2.2.0.4070

[rubberduck-vba/Rubberduck] rubberduck-vba-releasebot created tag Rubberduck-v2.2.0.4070

[rubberduck-vba/Rubberduck] build for commit 0aaf7c1d on next: AppVeyor build succeeded

[rubberduck-vba/Rubberduck] FastExcel created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> “In fact, the example code shows at very least the statement "Excel does not have a native currency data type so cannot cast values to currency" to be demonstratively false.”

Could you explain how it shows that?
Sounds like your understanding of Excel internals is radically different to mine.

(All I see is various VBA methods transforming Excel values to VBA values and datatypes: it does not show any native Excel data types at all)

[rubberduck-vba/Rubberduck] comintern created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> > Could you explain how it shows that?
> Sounds like your understanding of Excel internals is radically different to mine.

Yes.

> ```
> varValue = [a1].Value
> varValue2 = [a1].Value2
>
> Debug.Print TypeName(varValue) 'Currency
> ```

^^^ Who is casting that if Excel isn't? `varValue ` is declared as `Variant`.

@Duga this is getting ugly...

"Sounds like your understanding of Excel internals is radically different to mine" is apparently a true statement though...

Mathieu Guindon

can't wait for Charles Williams to chew my head off over this at Summit 2019...

Sorry 'bout that.

Mathieu Guindon

3:44 PM

:)

@MathieuGuindon more SCP silliness involving (). It's a different situation - you want it in the same issue (#4468) I opened earlier today or make a new one for the new situation?

[rubberduck-vba/Rubberduck] FastExcel created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> Excel does some dirty bit-twiddling tricks in calculation (to try to keep end-users happy ) that deviate from strict IEEE floating point. The VBA run-time does not do these tricks.
So your test is highlighting differences in calculation algorithms rather than differences in data types.

From: bclothier <notifications@github.com>
Sent: 29 October 2018 13:43
To: rubberduck-vba/Rubberduck <Rubberduck@noreply.github.com>
Cc: Charles Williams <Charles@DecisionModels.com>; Mention <mention@nor

Mathieu Guindon

IMO that inspection should be, at best, a hint-level "FYI" reminder about Currency and Date data types being carried into VBA for Value, and/or brought in as Double (with possible rounding errors) for Value2. Not sure what what's any more complicated than that?

@FreeMan what's the repro code & input?

That argument cuts both ways though. We could also issue a hint for Value2 with an FYI reminder that the value is a Double without Excel's "schema" applied to it.

Either way, we would be issuing inspection results on data (and without any knowledge of what the data is), not code.

private function foo(| --> private function foo()()... No Repo

never mind...

happened once, can't make it happen again.

3:52 PM

@FreeMan Try it with some extraneous white-space between Function and Foo.

@Comintern winner winner chicken dinner!

Private Function foo(| note double space --> Private Function foo(|)()

@MathieuGuindon ^

makes me think that AvalonEdit code panes will be easier...

For some values of "easier".

@Comintern just to check the assumption - if we knew that the input was from a cell that's formatted X, we can then infer the suggested choice?

4:08 PM

@this Maybe - it depends on what you're going to do with it though. If you're going to display it in UI, then all bets are off. If you're going to perform some mathematical calculation, it would still be just a guess in that we can't know what the business rule is. If there's an implicit cast happening on the VBA side (i.e., .Value formatted as Currency assigned to a Double or visa-versa), then yes.

However, we would be inspecting data in that case - the cell format is part of the data as much as the field definition is in a database table.

Mathieu Guindon

IMO that's overboard

^

That's why my instinct is to err on the side of "don't suggest something that could cause the user to break their own code".

Design question: I need to return a count of things. In one situation, there are no things to count, so I need to just short-circuit & return 0, in another I need to count via method A, and in the 3rd I need to count via method B. Would one stored procedure that understands all the logic be the "right" design or should I have that decision made in code which then assigns 0 or calls the proper SP?

Mathieu Guindon

I've yet to encounter an instance of .Value screwing me over

thinking about separating "business logic" from "data". None of it is in a form/code-behind.

Mathieu Guindon

4:12 PM

separating logic from data is a Good Thing(tm), regardless of whether you have a form or not

@FreeMan one SP is fine... until performance hurts. If a roundtrip is expensive, it might be OK to repeat the logic in the application (implemented as an independent and testable function, of course!)

so I should make the decision of how to calculate in code and ask the server one of 2 different questions.

Confused. Method A nad method B are thing you do in the database?

Mathieu Guindon

@FreeMan IMO it's rather hard to tell what's best given such foggy hypothethical

@Comintern just to see how it works in different contexts. Were I to suggest an Access specific inspection to guard against unexpected implicit cast using Me.MyControl.Value, which is a variant. For a bound control, we can reliably infer that the .Value can only be a Variant of a specific subtype OR Null, never anything else. So if we see a suspicious cast, should we flag that?

@MathieuGuindon as evidenced by my confusion. ;-)

4:15 PM

calculating survey response rates. In some places we email the surveys & I calculate rate as Responses / Emails sent. In other places, we don't email the surveys (use on-site iPad) and I calculate Responses / Appointments complete. Footnote to indicate the difference

Mathieu Guindon

do "method A" and "method B" involve different WHERE criteria?

@MathieuGuindon Just a bit - they come from different tables!

@FreeMan Do the different counting methods have an independent purpose when looking only at the database side? Put them in the place where they minimize effort and maximize reuse.

^

shoulda been more specific up front...

Mathieu Guindon

4:17 PM

I'd probably make the SP return the two counts, and let the code decide which one it wants. SP would also return whatever the code needs to make the decision.

and if that decision-making is trivial, then the SP would just return the one figure that's needed

#ItDepends

^ Minimizing the round trips is a good thing, so it's usually worthwhile sending extra column that never get read than doing 2 round trips.

interesting... return Count A, Count B & flag?

as opposed to flag as input returns count?

Mathieu Guindon

@FreeMan treat every db request as a dial-up connection over a 14.4K modem.

2

Although that also depends on the record count. It's better to make 2 round trips that return 1000 results than one that returns 1M results.

^^

you and your fancy-pants 14.4 modem!

Mathieu Guindon

4:20 PM

note that this approach makes nested recordsets particularly annoying

@MathieuGuindon Honestly, though, that doesn't help. If Flag then StoredProcedureA Else StoredProcedureB vs StoredProcedureEverything Flag is still one contact to the DB.

and one round trip

FWIW, I don't think nested recordsets are worthwhile. Granted they're cool for hierarchical data, but for purpose of data-binding, it's usually best sent as a flat list for simplicity. Let the engine figure what's connectd to what.

@FreeMan and is the same 1 round-trip as StoredProcedureEverything Flag, CountA, CountB

@FreeMan though in all situations, it's one roundtrip, i'd prefer "everything" because that means there's less objects that the application depends on.

IOW, if the application doesn't need to execute the A/B in isolation, then it need to not even know about it.

That would give you the freedom to modify A/B without breaking the application

@this that makes sense. Plus, it just hit me that I've added one round trip earlier in the process to retrieve the flag from the DB. Everything can get the flag while it's there and saves the extra round trip

Mathieu Guindon

4:26 PM

^

Question: What's a build that fails with 0 errors?

Seems like a decent way to go, though I'll need to rename the variable that holds the data because it's no longer just emailCount...

Mathieu Guindon

trick question. a failed build isn't a build ;-)

@this Irritable Build Syndrome - Really S**y...

@this <guru-on-mountaintop>The Empty Source.</guru-on-mountaintop>

Mathieu Guindon

4:28 PM

haha

groans

@Comintern Yeah... what happened to that comic that used to pop up in the feed 1-2x/week

I think you're referringt o commitstrip

and that was added by Hosch, not Comintern

The annoying thing is that I get plenty of warnings but 0 errors.

no, that one's been here every now and then. This was the all about zen/monks/doing your time

Not even in the debug output

oooh, codezen something?

4:30 PM

yeah, that one!

that isn't a comic, though.

it's just stories

been a year or more

ok, it had pictures, that's "comic" in my head.

and I must remind you that there is a central flaw in their narrative....

.... they use Java.

ah. maybe that's why it was killed off... :)

hmm, occurred to me just now that Java is already ancient. Seemed like yesterday when it was all the rage and "The Language to Learn™"

but that was 2 decades ago

dang, i'm old.

(for some values of "old")

4:34 PM

Yeah, 2+ decades is old vis: VBA

notes difference between old and bad

Granted, but VBA came from BASIC so it's part of a much older family

since 60s.

Java, OTOH, seems to be a new offshoot that was loosely C-like but not really.

(unless there's a predecessor that I don't know about)

[rubberduck-vba/Rubberduck] FastExcel created comment on issue #3320: Inspection for Range.Value2 over Range.Value

> The .Value method (which is part of the object model interface between Excel and VBA) is doing the casting from a native Excel value to a VBA currency data type which then gets assigned to the variant. The .Value method does not change the value in the Excel cell. Formatting the cell as currency using either VBA or the UI does not change the value in the cell.

If Excel cast numbers formatted as currency to the same currency datatype as VBA (limited to 4 digits after the decimal) then any s

@this someone posted this a while back. My extensive (minimum 14 seconds) searching couldn't find it in chat history, so I turned to the general googles.

user image

My new toys are all set up.

Except for one more install.

4:50 PM

@FreeMan yeah, looks like it's basically borrowing a bit from C/C++ and Scheme. Never really seen scheme so can't say how much borrowing.

> The .Value method (which is part of the object model interface between Excel and VBA) is doing the casting

^ I don't know what that even means.

@this Codeless Code.

And it seems they stopped with the moon one.

@Hosch250 yeah, that's the ticket!

@Comintern wasn't it shown that for a cell formatted as a currency, Value will return a Variant with Currency subtype whereas Value2 returns a Double? It has to be done within Excel's part before it's marshaled to VBA, no?

Twice.

4:57 PM

?

The assertion that the Object Model is some weird third layer and not part of Excel is the new part.

oooh now I grok

I read that as just Excel

e.g. Excel's library

« first day (1606 days earlier) ← previous day next day → last day (1574 days later) »

Transcript for

Oct '1829

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