VBA Rubberducking - 2019-12-12 (page 1 of 2)

« first day (2015 days earlier) ← previous day next day → last day (1165 days later) »

12:01 AM

RELOAD!

[MDoerner/AdventOfCode2019] 2 commits. 1356 additions. 2 deletions.

[Phrancis/AdventOfCode2019] 2 commits. 81 additions. 23 deletions.

[rubberduck-vba/Rubberduck] 5 opened issues. 1 closed issue. 9 issue comments.

[Zomis/minesweeper-server] 1 issue comment.

[Minesweeper] Games Played: 87, Bombs Used: 50, Moves Performed: 11824, New Users: 12

Mathieu Guindon

12:27 AM

Oops lol

@MathieuGuindon Too late - I already have and use Rubberduck :-). — Michael Wycisk 29 secs ago

@FreeMan IIUC (and IANAL) the difference occurs because in the UK, except where pedestrians are prohibited (such as motorways), the onus is always on drivers be careful to watch for and avoid pedestrians. So if I "jaywalked" and got run over, the assumption would be that the driver was not driving with due care and attention, and it would be up to them to prove otherwise. AFAIK is is never illegal to cross a road anywhere, although they try to discourage it with barriers in places.

2 hours later…

2:05 AM

Sad:

On January 26th, 2274 Mars days into the mission, NASA declared Spirit a 'stationary research station', expected to stay operational for several more months until the dust buildup on its solar panels forces a final shutdown.

4 hours later…

6:12 AM

0

Q: VBA - Sequence Generator from previous max sequence based on certain conditions

I'm new to VBA programming so the following code may look terrible which is why I want ways to understand how to better optimize it and other future codes. Sub test() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Dim arr(1 To 4...

performance beginner vba excel

1 hour later…

7:14 AM

0

Q: VBA - Sequence Generator from previous max sequence based on certain conditions

I'm new to VBA programming so the following code may look terrible which is why I want ways to understand how to better optimize it and other future codes. Sub Sequence() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Dim unique...

performance beginner vba excel

1 hour later…

8:24 AM

@theVBE-it'srightforme just learn one small thing every day and it’ll add up to a lot of knowledge.

3 hours later…

11:52 AM

@IvenBach Bright? High contrast, maybe, but not bright.

@IvenBach Wait. What??? WHERE??????

12:25 PM

user image

Sadly, it seems the dark theme must be downloaded from the store and it's blocked by corporate. Well... not blocked, just extremely limited. :/

2 hours later…

2:46 PM

well... carp!

user image

user image

somewhere in my fiddling with colors looking for some nice dark windoze colors, I seem to have managed to adjust the color SSMS uses to display numeric constants. :(

2

LOL.

Think of it as a good way to cure you of the habit of using magic numbers!

those aren't magic numbers... they're short term, temporary queries to figure out why the heck a report doesn't look right.

better

user image

Still, better to be on safe side.

the brighter white will help remind me that I'm using magical numbers

2:53 PM

that would certainly have an effect on Iven

BRIGHT! IT HURTS MY EYE!

which one?

IvenBach? I'm aware of only one?

36 secs ago, by this

BRIGHT! IT HURTS MY EYE!

which eye?

Oh, that's because he's a pirate.

So depending on which eye he hasn't covered at the moment

Arrrrrr... I warrrsn't awarrrre of tharrrrrrt...

2:55 PM

@this That's astronomers.

oh, didn't know pirates took up astronomy.

Pirates don't cover their eyes. Just their empty sockets.

kids are coming home, gotta polish up the dad joke skillz - it's been a couple of months

Explains a lot about the field....

pirating?

2:56 PM

@Hosch250 Mythbusters suggests otherwise.

(usually it's called a "sea", not a "field", but... whatever)

they'd need a Sea of Astronomy....

Astronomers cover one eye to keep it used to the dark when they look at star maps or whatever.

@this Astronomy Domine

which is the same reasoning that Mythbusters argued that most pirates weren't one-eyed but needed to go between bright topdeck and dark ... under the decks.

2:58 PM

Huh.

poopdeck?

I'd guess it was more of the intimidation factor.

Like, I've been through a lot of fights and have lost my eye and I'm still fighting. I'm tough.

#ItsJustAFleshWound

I'd think you would not want to fight one-eyed. Messes up your depth perception

I imagine the people fighting them knew that too.

On the other hand, most eye-witness accounts I've read haven't really mentioned any eye patches.

More things like superficial skin cuts to make themselves look tough and fierce.

Which were dangerous in their own right as a potential source of infection, but you're extremely likely to end up at least lightly wounded in a hand-to-hand combat anyway, so...

3:16 PM

That's why they douse themselves in rum, of course. To fight off the infection.

3:39 PM

@this they broke the mold when they made me. ‘Oh crap this one came out terribly flawed. Can’t have any more defective ones, smash the mold boys.’

LOL

Jokes aside there is truth to that. No siblings.

3:52 PM

Does anyone know where I can make an HTML/CSS proposal?

I want to propose a new value for display.

Or maybe visibility.

You have display: none and visibility: hidden, which are roughly the same thing.

But screenreaders also "hide" those elements

I want a new one that hides it from the screen, but displays it to screenreaders.

So we don't have to do this BS of making it a 1px by 1px square with a negative 1000px margin.

wouldn't that go to W3C? I'd hope their site has a contact button....

Yeah, I can't see a way to just file a report.

I'm pretty sure it's there. E Brown was spamming them once and almost got banned.

Probably why they took it down. LOL

He was proposing ancient languages as better alternatives to JS.

Besides, I'm not so sure they actually take in comments from anyone else outside the working group.

They seem to insist working in a vacuum.

@Hosch250 ancient languages? As in Sumerian, Hittiites, Chaledans kind of languages?

4:02 PM

I think it was stuff like VB Script and COBOL.

well VBScript was at one time supported, though.

(but only on IE5/6)

COBOL? Good luck....

That's what they told him, LOL.

I hope they actually didn't tell him to use IE6

No, they told him they weren't going to implement the languages as a standard.

I meant the "Good luck" comment.

They were basically "good luck, kthxbai."

what was his motivation in spamming them, though?

4:11 PM

Trying to get them to fix JS.

> German [...] is 30% longer, on average, than [...] English

Mmmm, Java written in German vs C# written in English :D

Java written in German has no white space.

Mathieu Guindon

#TIL Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.ApplicationIdle, new Action(() => { })).Wait(); in WPF makes it wait for the rendering to complete: we could use that to make the spinning duckies keep spinning until the UI is fully rendered (e.g. inspection results)

4:39 PM

+1

@MathieuGuindon did that come to you in the middle of the night?

Mathieu Guindon

lol no, got a WPF app at work and there's a button I click to bring up a popup where I have checkboxes for filtering a grid (a bit like... in Excel), and before I researched it you'd click the button and the wait cursor would stop waiting before the grid actually reflected the new filtered content

now you tick a checkbox and the wait cursor remains until the UI is consistent with the selection

@MathieuGuindon i have many things come to me while staring at the ceiling... dumb question... why are some avatar images large sometimes

The user posted enough messages for it to expand.

I think it's three messages.

Here's a third message...

See?

Mathieu Guindon

~~magic!~~ CSS

4:45 PM

I think three lines
in a single message
works too

coding is magic

Mathieu Guindon

@Hosch250 yes but needs 4 lines to show network rep :)

Yep.

#TIL 4 lines = rep

As far as TIL, I just realized that when I lean forward, my head's weight is supported by my lower back just above my hips...

4:55 PM

I cloned RD so I could look at how it's organized. What is the reason for the 17 projects in the solution? Is that "normal" practice?

I always figured it was mostly my shoulders, but my back is knotted up and I can feel the different groups relaxing and tensing in different positions.

for certain values of normal, yes

@SmileyFtW the idea is to allow for siloing of complexity

@SmileyFtW Roslyn has about 70 projects or something last I checked, so yeah.

That's the C# tooling for VS.

(It's also the name of the actual compiler, but that's not [fully] OSS.)

Since I am trying to learn C# and I have a "smallish" project I'm using to learn; one project for the solution would be expected? and Just organize into folders?

Probably, yes.

4:57 PM

thx

You'll know when you need 2 projects.

LOL

It'll feel like you have too many responsibilities managed together.

And instead of grouping by different features, you'll be grouping by functionality.

Like, FooApis and FooDisplays.

So, you'd move your APIs to one project and leave the display in another.

Or something like that.

lacking any other point of reference I am following what I (think I) have learned from Mug's Excel VBA examples

Hey quick question folks... hope I explain myself sufficiently.... which is better practice out of these two patterns:

Public Sub Foo()

    Bar

End Sub

Public Sub Bar()

    Baz

End Sub

Public Function Baz() As Long


End Function

Or to do:

Public Sub Foo()

Bar baz

End Sub

Public Sub Bar(ByVal Baz)

End Sub

Public Function Baz() As Long

End Function

5:02 PM

They are all public, just call Baz directly ;)

^

Leave Foo and Bar out entirely.

So call Baz as a param in the sub call?

No, don't call Foo or Baz, just delete them.

If it's publicly accessible why have something else call it indirectly?

5:03 PM

All they are doing is passing the call through, and nothing else.

Invoke it directly.

Another benefit is you are having your do what it says.

Now, if Foo or Bar actually did other work...

Ah... so call baz at the required level of Bar which is where I will use it

Basically.

Foo is basically Main

5:04 PM

OK.

If you don't need too daisy-chain invocation don't.

Baz is a helper returning a last row calc (scalar)

So, you don't need Bar, because the only thing it does is call Baz (in your example).

Bar writes out a table but needs to know the last row to then write to a free row

Now, if bar was doing other work, for example, validating something before calling Baz, I like the second.

It's easier to test.

OK, so I'd split that up into a couple methods like this:

public Foo // Main--this shouldn't do any work
private DoWork // read and write the data here
private ReadData (args)
private WriteData (args)

5:07 PM

so would WriteData perform the last row calculation rather than call a function which does this?

It depends on how you have it currently.

The calculation would get another function.

So, it would look something like this:

private DoWork
    var data = ReadData(...)
    var result = DoCalculation(...)
    WriteData(...)  // or maybe `var success = WriteData(...)` or something

ok

So what's interesting is you have DoWork performing the calls I would put in Main

You could do that, and I actually would if it is just these 3 lines.

I normally have the outer level which is mostly just calls being Main in VBA

5:10 PM

However, most serious C# applications tend to have the main work of the program be about 3-5 lines, plus several lines of input validation, data initialization, and general config stuff (like an IoC container).

In your example where does the last row calculation fit which must be used by WriteData?

So this way, you break your Main into a few lines:

@Hosch250 Noted

public Main
    DoSetup
    DoWork
    DoTeardown

Yup

5:11 PM

And then it's super easy to test the main flow of the program without messing around with the config and all, which almost always needs a TON of mocking work.

It's also easy to see what's the main body of the program vs what's just getting it so the main body can do its thing.

So is DoWork going to house the calls to WriteData and GetLastRow?

Yeah.

@Hosch250 Equivalent to CRUD?

Ok... and so GetLastRow would have to be passed into WriteData?

hmm

@IvenBach Hmmm?

How is that similar to the 4 main data operations?

5:15 PM

Create Read Update Delete i think is the acronym.

Ok.... thanks guys.... I'm going to carry on coding this

I mean, it probably performs at least 1 of them.

But, it's not really "similar" in that one is a generalization of data operations and the other is a structure for ease of maintenance.

Honestly, it's a little more like a unit test structure.

You have setup, operations, and teardown.

Oh wow, my hacktoberfest shirt finally shipped

Act, Arrange, Assert?

Arrange, Act, Assert.

5:17 PM

:derp: #Words

how would you act if it's not arranged?

^^

You wouldn't want to be on the stage without the props in their places. ;-)

Yeah, you're basically arranging the runtime environment for it, acting on something, and then asserting that it worked and returning a code.

RD puts in the comments for me. I read and follow accordingly.

Mkay now I get the analogy.

5:18 PM

You can leave the return code out in C# (void auto-returns 0 if it makes it to the end, or it returns an error code if it hits an exception).

However, you can also do an int Main and return custom error codes if you want.

That's helpful mostly when you are running your console apps on an automated job thing.

Then you can set the job coordinator (say, AutoMate) to listen for specific codes and take other actions.

For example, 1 might be "retry in 10 minutes" while 2 might be "bad data, send an alert to the admin" and 0 would be "success".

Mathieu Guindon

@QHarr the idea is to write code at consistent abstraction levels: that's why you don't have low-abstraction Range("A27").Value = 42 calls in a procedure that invokes a high-abstraction procedures like ReadData and WriteData

or, as an image: vaguely describe at a high level how you'd make coffee, but then you start describing the process of torrefacting the grains: too low-level for the place it's in

> Torrefaction of biomass, e.g., wood or grain, is a mild form of pyrolysis at temperatures typically between 200 and 320 °C.

Doesn't look right to me.

Granted, coffee could be compared to fuel but....

huh, why does it not look right?

back sooner than I thought.....

anyone had this before....

5:33 PM

Would you torrefact coffee beans?

From the description, it sounds like something you do with wood or coals, not coffee beans.

user image

Bit tiny but I have added reference to Microsoft HTML Object Library

@this it's sometimes called roasting, is it not?

Ah, torrefacto

But the shown members are not for that class

not sure why one would want to drink "liquefied coal" or "diluted asphalt", though

Mathieu Guindon

5:35 PM

#language - yeah, you call it "roasting" in English

Exactly why I was baffled when I first came across torrefaction. I figured it was a particular coffee lingo that I didn't know about but when it talks about coals & fuel....

Hummm... it is only listing a subset

But apparently "torrefacto" is a lingo word, referring to a particular kind of roasting. #TIL.

But hey, it's not that far away. I mean, coffee's basically tar, right?

Officially confused

I beg to differ

5:37 PM

@MathieuGuindon ok

@Vogel612 Interesting insults for coffee drinkers.

hey, that's only the torrefacto roasting method, okay :)

Anyone got any ideas what on earth is going on with these member calls please?

well not calls...apologies....listings

Probably Intellisense borked.

I have closed the file ; removed and added the reference but I think you are right

Yeah

Need a new workbook it seems!

@Hosch250 Thanks. That's a first for me.

5:40 PM

Restart Excel maybe?

Opening a new workbook has solved it.

One of the funnier reddits is GTA Or Russia.

I like Military Stories, Abrupt Chaos, What Could Go Wrong, and My People Need Me.

Oh fabled Avalon. The dream of tearable tabs allowing for multiple Module panes on different monitors can't come soon enough. AvalonMention++

#TIL Torrefaction

Mathieu Guindon

5:58 PM

La torréfaction est l'action d'exposer un aliment à un feu direct ou à une source de chaleur adaptée. Elle donne un arôme qui rappelle l'odeur des aliments un peu grillés, calcinés (empyreumatique). La torréfaction est utilisée aussi bien dans la fabrication du café ou du cacao que de la bière (maltage notamment de l'orge). On peut aussi torréfier des amandes, des noisettes, etc. == Processus de torréfaction == === Vue d'ensemble === Sous l'effet d'une température relativement élevée, les sucres et l'eau donnent des caramels. Quand il n'y a plus d'eau, les sucres et les acides aminés développent…

6:16 PM

i feel like mat might actually kill someone if he saw this many globals...

'approvals
Private approvalArr As Variant, approvalList As String, approvalEmail As String
Private approvalCEO As Boolean, approvalCOO As Boolean, approvalCFO As Boolean, approvalBUPres As Boolean, approvalVPGM As Boolean, approvalVPFin As Boolean, approvalVPBD As Boolean, approvalSiteGM As Boolean, approvalSiteFD As Boolean, approvalCont As Boolean, approvalTax As Boolean, approvalRisk As Boolean, approvalLegal As Boolean, approvalHR As Boolean, approvalProcure As Boolean

Mathieu Guindon

sees a total of zero globals

wait no, I see two

Should integration tests check details? I have janky-wannabe unit/integration tests that check if a range has an array formula and others non-array formula.

Mathieu Guindon

tests should test what needs to be tested :)

if a test makes changing the code (without changing its purpose/spec) easier, go for it

@Cyril Another reason for not having a #WallOfDeclarations

FWIW, this header begs to be made into a UDT....

then you can this. whatever

6:18 PM

@MathieuGuindon btw, what's the appropriate term for a module-private variable? i have just called anythin gotuside of a subroutine* as a global

^ Congratulations. It's time to increase your pedantry.

i usually refer to them as module level variables -- being at module level has nothing to do with accessibility, which can be either private or public.

'Module1
Option Explicit

Public Foo As String
Private Bar As String

Therefore, either private module level variable or public module level variable or collectively module level variables

Mathieu Guindon

it's only global if it's Public (implicitly or not) in a standard module. Anywhere else, it's just another module variable - aka "private field"

6:20 PM

copy

@Cyril The variable Foo is public. The variable Bar is not. Can you see if from outside where it's declared. IE Looking from the outside in.

Mathieu Guindon

(or "public field", if public in a class module)

Note though that in a class module, the public module level variable isn't really the same thing.

@MathieuGuindon do you see the "field" parlance used in any VBA docs?

Mathieu Guindon

public fields show up as Property Get + Property Let/Set on the class' interface

^ not recommended, tho.

Mathieu Guindon

6:21 PM

@this no and I wonder why

it's like they use baby words for a baby language

IME, it's from C-family. IDK why they couldn't have for VB family.

Mathieu Guindon

call it like it is - a field

The trouble, however, is if you search the documentation, you might get bad result. :-\

OTOH, I haven't really searched documentation for anything like "module variable"

Mathieu Guindon

writes browser extension to change all Hungarian m_ variables to f_

@MathieuGuindon use "l_"

6:22 PM

and on that subject the whole "module" concept annoys me to no end.

What's a module?

Mathieu Guindon

some logical container

I presume

ah, but it behaves differently if it's a class or a standard or document

*any of a number of distinct but interrelated units from which a program may be built up or into which a complex activity may be analyzed.

so now you're saying "standard module", "class module" or "document module"

Mathieu Guindon

yeah

6:24 PM

(more realistically, just "standard module", "class" or "document" but stilll........)

it's annoying AF

Mathieu Guindon

so, a Module then is the container for the source code

yes

if it was a file, then it would have worked.

I guess that is kind of true in a VB6 project

and modules are inside of a larger module

Mathieu Guindon

it conceptually kind of is :)

6:24 PM

which is why naming is important

module-ception

~~but see, the problem here is that a module has no namespacing at all.~~

vba-ide = module
class module = module
standard module = module

The problem is that anything in a standard module pollutes the global namespace.

vba-ide isn't a module. ;-)

it technically is!

eh? No. It's an IDE.

6:25 PM

the interrelated units just happen to also be modules

semantic point, but applies to your "what is a module?" question in ridiculousness

Oops, my chat didn't update.

oh shit, i just did f that up, meant to say vbaproject*

@Hosch250 Crank the crank faster, then.

yeah, sorry

@this whiiirrrrrrr

6:27 PM

LOL, but still, a project can't be used like a module could be.

and at least, the project represents the global.... kinda.

@this that's the point.

Anyway, the whole organization is messed up

because the default behavior is to make a new standard module, then another, then another, until you have 1000000000 procedures listed in the intellisense

standard modules, class modules, etc., are within the module that VB uses (the vbaproject)... purely based off of definition that a module is the housing for the interrelated units.

yes, i concur in the messed-up'ed-ness

@this if only intellisense was intelligence

Mathieu Guindon

food for thought: is an Enum a data type?

sets fire, leaves

This is fine.

Mathieu Guindon

6:31 PM

=)

so, module != data type then. and yet a class defines a type.

the question "what is a module" probably needs its own blog post

#basics

in VB*, enums are basically fancy-pant constants that has its namespace

and pollutes the global namespace anyway

@this Basically the same in most .NET langs.

ah, but you can do reflection on it as if it was an object

Mathieu Guindon

in C# you must qualify the member though

@this well that's because it is an object :)

with VB*, you basically have to either write routines to translate "Foo" into 42 or use VBIDE API to parse it.

so VB*'s enum isn't an object.

just like how module isn't an object.

Mathieu Guindon

6:33 PM

if VB* excludes VB.NET, then yeah

Dim foo As Module : Set foo = Foo 'illegal

@MathieuGuindon sorry, didn't read that. Did you meant Fred.NET?

@MathieuGuindon Yeah, it's basically a struct with a bunch of int constants.

Mathieu Guindon

where struct : ValueType : Object

Not really.

Enum is a class.

Mathieu Guindon

wait wut

6:35 PM

And all enums implement Enum.

Yeah.

Mathieu Guindon

:brainsplode:

Java strikes back!

That's why you need to do : struct, enum when you want an enum generic.

Because you need to exclude System.Enum itself.

wait wait

i can make an enum based on struct?

Mathieu Guindon

@Hosch250 that's C#8 though, right?

6:36 PM

7, I think.

Yeah, 7.3.

Mathieu Guindon

@this you can make an enum with an Int64 underlying type if you want

That I knew

but struct?

that's not an integer type.

Mathieu Guindon

well, Int64 is a struct

as is Int32

struct isn't really a type, it's more like a keyword, like class and interface

docs.microsoft.com/en-us/dotnet/csharp/programming-guide/…

Mathieu Guindon

every value type is a struct

6:39 PM

#TIL

I need to remind myself that in the end, everything's a one or a zero.

Mathieu Guindon

6:59 PM

#TIL Boolean isn't type safe at all

@BigBen the part that's baffling, is the part where the integer value is somehow still present under the hood of the Boolean variable. This basically means Boolean is not type-safe in VBA. — Mathieu Guindon 1 min ago

« first day (2015 days earlier) ← previous day next day → last day (1165 days later) »

Transcript for

Dec '1912

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