VBA Rubberducking

4:01 PM

How about VBA is not office software, it's an ancient and universally hated and disabled scripting interface/language FOR one particular suite of office software? I was almost upset enough to dig out my reddit login for this...

Comintern

I'm not sure anything posted on reddit is worth responding to.

this

^

Inarion

:D

It's totally possible to have a sensible discussion in some subreddit... just not very likely. ;)

this

i'm sure if I looked long enough (and irreversibly mutilated myself in the process) I could find a gem even on 4chan.

but I think I'll stick to where gems are more plentiful and easy to pick out.

Comintern

@this Use your DMZ VM for that one...

Inarion

4:07 PM

@this That's the reasonable and responsible thing to do.

this

@Comintern not even that. Internet cafe or a throwaway PC

Inarion

I was just thinking that if you have a subreddit associated with a specific hobby/interest of yours, it's not uncommon to actually find stuff worth reading.

At least, if it's a small subreddit. Above a certain size, there be trolls.

this

I think the primary problem is that it's simply not curated

Comintern

@this Internet cafe may even be a little irresponsible. Maybe a data connection on a burner phone, then actually burn the phone when you're done.

4

Hosch250

LOL.

this

4:15 PM

What I'd love to do is give 4chan and its kinds the burn notice

FreeMan

@Vogel612 I saw that. I chose not to post it as I didn't want to stir everyone up early on a Friday...

Hosch250

@MathieuGuindon There was a comment on that AMA about a new VBE IDE.

You missed your chance to flaunt the duck.

this

the best thing MS could do is just to open source their VBE

Comintern

I doubt that will ever happen - it's coupled too deeply into their proprietary stacks.

this

Hush. Let me dream.

Mathieu Guindon

4:23 PM

@Hosch250 MS did it for me. they posted a link to a uservoice with my reply :)

Hosch250

Oh.

IvenBach

@M.Doerner The lack of unknown compile time stuff in the RD code base (only C# code I work on) is why I was asking the question. :+1: for mentioning that.

Comintern

@IvenBach I use a ton of reflection at work. It's particularly useful with ORMs or making dynamic adapters.

IvenBach

ORM?

Comintern

Object-relational mapping

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to construct their own ORM tools. In object-oriented programming, data-management tasks act on objects that are almost always non-scalar values. For example, an address...

this

4:35 PM

You might have seen people here mention Entity Framework or Dapper... those are ORMs.

#ProTip: Don't use EF. ;-)

FreeMan

@MathieuGuindon FWIW I upvoted that one. I'd have +1 your comment and the "rubberduckvba.com" comment, too. Alas, I saw no option to do so.

Comintern

#ProProTip Don't use a database that was generated by EF code first.

this

Note also that certain ORM will let you generate a C# classes from a database schema.

^^

It's a travesty.

Hosch250

Eh, EF code-first creates perfectly reasonable DBs--if the programmer knows how...

this

They don't.

Comintern

4:37 PM

^

Hosch250

I do.

this

and frankly the mindset is radically different

Hosch250

Basically, you need your programmer to know DBs too.

Comintern

And they also typically don't go back and set the constraints up properly on the DBMS side.

this

"those are lot of duplicates! I can make it generic!"

Hosch250

4:38 PM

@Comintern You can set those with attributes.

this

^^ "I am a programmer! I will do it myself!

Hosch250

@this Not quite :)

this

You'd be surprised. I've taken over some uhm... interesting databases and have heard similar horror stories

Hosch250

Yeah, always have a DB person create and manage your DBs.

Comintern

"Let's make all these classes inherit from a common base class. One table should work just fine."

this

4:39 PM

shudder

Hosch250

However, EF is a perfectly good tool to use if your programmer knows DB.

this

I know DB and I find EF never does what I want it to do.

Hence, dapper for me.

Comintern

@this I fight that one on a daily basis. We have an invoicing system designed by interns.

this

You have my sympathy, sir.

Comintern

Not to mention that EF is heavy AF.

this

4:41 PM

and the heaver it's, the more it leaks.

Hosch250

Yeah, be careful about linq-to-entities.

Although, you can structure your queries how you like with the Join and stuff. Once again, you have to know how the queries map back.

But, I can write as clean a query in EF as in raw SQL if I put the effort in.

this

oooorrrrr you could just write a stored procedure, test it, and call it from dapper, done.

Hosch250

And I've had to here...

If you are allowed to...

EF is great for programmers who know DB when they aren't allowed to create new SPs without a meeting or more.

this

hasn't been an issue.

Comintern

What's really fun is when you start creating circular class mappings. I've seen EF blow a gasket with those.

Hosch250

4:43 PM

@this Is here.

this

That would be a problem for employees or cowboys.

Hosch250

@Comintern Yeah, don't do that.

this

@Hosch250 @this is this.

Comintern

^ that

Hosch250

I've actually been told to pull working SPs out and replace them with code.

this

4:44 PM

:-(

Hosch250

And if you structure your project like we do, we'd literally have at least 1k SPs to maintain.

99% of our app is CRUD stuff.

Over a couple hundred tables.

Of course, our structure is bad. But that's life.

this

FWIW, I don't really believe in CRUD SP

that's what views are for

Hosch250

We aren't allowed to set up views.

IvenBach

@Comintern So taking a List<PhoneNumbers> in OO C# an converting it to a scalar PhoneNumber1, PhoneNumber2, etc...? That's what I understood from reading.

Comintern

@Hosch250 Wut?

Hosch250

4:48 PM

Us "programmers" don't own the DB.

But we have to query it and update it.

We aren't allowed to make any changes that would show up in SSMS.

Comintern

Wow... just... wow.

this

@IvenBach my goodness if db was like that, I would bushwhack the author.

that violates normalization.

Comintern

^

IvenBach

Then I'm not understanding what it's saying.

Hosch250

@this And I'd jayhawk him just for good measure.

this

4:50 PM

Normally a "well-behaved" ORM should map a List<foo> into a # of records in the table named Foo

assuming the foo is a class (typically a POCO) with some properties.

Comintern

Example. In Dapper you can do something like connection.Query<Foo>("SELECT Name,Phone,Address FROM Foo") and it will fill up a list of Foo objects.

Or, you can use it dynamically, and it will emit a class based on your query.

Hosch250

@Comintern That's one of the reasons our newest "architect" just left.

IvenBach

@this Ah. So it'll just give you the phone numbers that belong to FooBar from the phone numbers table.

Hosch250

He wasn't the greatest programmer I've seen (some of his proposed changes weren't scaleable, IMO--we've moved RD away from them as we scaled larger).

this

assuming you filtered for FooBar sure (though it'd be unusual to have a phone number table with a person's name)

Hosch250

4:54 PM

But, he did bring in a good many changes. He pushed enough changes through to probably keep the company from sinking within 10 years.

this

@Hosch250 sorry, RD is what in that context?

Vogel612

@this you're ignoring stuff like character denormalization ...

Hosch250

Rubberduck.

We'd moved RD away from some of the changes he was proposing to implement.

this

confused You're not coding in VBA, are you?

Hosch250

What does VBA have to do with anything?

IvenBach

4:54 PM

@this I'm going for hand-wavey layman's comprehension to get started. I can refine it once I get the correct idea.

Hosch250

I'm talking architecture in RD and architecture in our website.

this

@Hosch250 rephrasing to see if I understood - so RD originally had some of his architectural design which turned out to be not so good and we moved away from that design but in your website you're still stuck with those design?

Hosch250

Some of his proposed changes are still coming down the pipeline from above.

Not quite.

In RD we moved away from it.

In our website, we are stuck with something worse.

He was pushing a cleanup to that version, and I wanted a cleanup a different way so we wouldn't be kicking the can down the road a long ways.

this

@Vogel612 hmm. IIUC, this would mean the representation would be different after normalizing. I'm shooting for simply blitting the content forth and back without any additional steps.

Gotcha, Hosch

Vogel612

@this yea, but unicode is ... an angry and dangerous beast in the wrong places?

it's like ... you can really shoot yourself in the foot if you're just comparing strings for .equals

this

5:00 PM

While that is definitely true, especially if you mean to keep it portable across different systems, in my case, I have no concerns regarding portability.

Vogel612

because that basically checks the encoded bytes against one another, but you can represent the same character / codepoint in multiple different ways in unicode..

which is ... not quite optimal to put it lightly

@this then why do you have a string in the first place?

if you want to work off a byte[] don't even use a string

Hosch250

It's nice having a window in winter.

this

because it is a string.

IvenBach

@Comintern en.wikipedia.org/wiki/Object-relational_mapping#Overview ORM allows you to write OO code (in this example) that you may be more familiar with as opposed to SQL.

Vogel612

ah... that complicates things

this

5:02 PM

basically I just want to serialize to a binary format and back.

Comintern

@this - What do you get when you shove it into a memory stream without an encoder?

Hosch250

Today started out with heavy snow (which was nice enough) and a 1:45 drive (which was not--slow, slippery...). Now it's sunny and smiling.

this

again, without any regards to portability, etc. etc.

uh. I didn't try that....

Vogel612

@IvenBach close enough. O/RMs are usually reflection powered stopgaps to help bridge the impedance mismatch between Databases and OO

Hosch250

They also help manage connecting to DBs.

You just provide a connection string, and they handle everything.

IvenBach

5:03 PM

@Vogel612 You're conversing with a native American speaker. Use smaller simpler American words. :p

Vogel612

eeehmm ...

databases work with clouds of bleh and OO works with bits of bleh

Comintern

lol

Vogel612

O/RMs try to translate clouds to bits

IvenBach

It's a way to easily aid communication between DB and OO?

Comintern

It gits yer data into yer objects.

Vogel612

5:04 PM

^

this

Ok, let's try something more concrete.

Consider an order.

Any order you see on a store, whatever.

IvenBach

The data is a "thing" of information. You only want a specific piece of that for OO.

this

Normally on an order you have the following.... a customer, date of the order, a list of items that was purchased on this order, unit price, subtotal, tax, and the total.

oh, maybe the salesperson, too.

Mathieu Guindon

think 1 record -> 1 object; 1 field/column in that record -> 1 property in that object

IvenBach

^ :click:

this

5:06 PM

The problem is that this paper form of an order is actually several things.

Customer is a table. Order Header is a table. Order Details (the line tiems) is another table. Products yet another table. Tax yet another table.

IvenBach

I'm following along.

this

sooo, how do you build a Order C# class based on at least 5 or more SQL tables?

are you gonna pull out this big ugly SQL ....

IvenBach

TBH I don't know how to do it either way.

Vogel612

SELECT * FROM order JOIN customer, products, tax, ...

IvenBach

ORM provides an abstraction so you don't need to mess with the SQL?

Vogel612

5:08 PM

at least it tries to

this

SELECT .... FROM Customers AS c INNER JOIN OrderHeaders AS o ON c.CustomerID = o.CustomerID LEFT JOIN OrderDetails AS od ON o.OrderID = od.OrderID LEFT JOIN Products AS p ON od.ProductID = p.Product.... I got bored writing

and all that for one lousy object class Order.

Now think about if you had to handle many more operations than just taking order in your application. Are you going to write all SQL all over your application?

oh, wait, they added a new table!

and we need that!

Comintern

Wow, the Wikipedia page on CRUD is really bad.

this

Have fun, pal!

Hopefully you start to see the "impedance mismatch" between the relational entities (e.g. table in SQL db) and the objects in imperative programming languages like C#.

IvenBach

I think I'm starting to.

Object From Object Oriented Relational From Relational Database Mapping From connecting them together. :click:

Mathieu Guindon

5:23 PM

with an ORM now you can do var orders = context.Orders; and call it a day. Then you can do var detailsForOrder21 = orders.SingleOrDefault(o => o.Id = 21).Details and you have them, because the ORM loaded them into Orders, because it knew it had to, because of mappings, conventions, metadata, etc.

actually if that's EF and LINQ then with deferred execution you only hit the database on .SingleOrDefault with the above

and you never needed to write any SQL to do that

Comintern

The thing I like about Dapper is the opposite. I can write the SQL and never have to write any class implementations.

Same concept, but coming at it from the opposite direction.

this

5:40 PM

and in both cases, they're using reflection to figure what's what and what to emit

FreeMan

commitstrip.com/en/2018/11/09/what-coders-really-want I love the Nerf guns & darts on the wall.

this

@Comintern just curious - do you actually write direct SQL? As a rule, I try to avoid embedding SQL in the application.

Mathieu Guindon

@this Dapper says screw this, just run the damn select statement

Comintern

@this Yep. I embed SQL all over the place. Keep in mind these are for one-shot data transformations.

I try to limit my time spent to developing the queries to get the data I need when I'm reverse engineering the database.

this

@MathieuGuindon naw. That's on the user to (ab)use it, just like EF.

Mathieu Guindon

5:43 PM

it's all over SO's code base, how bad could it be?

FreeMan

lol

this

@Comintern Gotcha, I can see why.

Though technically you're more like "scripting" with C#

Comintern

Plumbing.

this

(and I think C# makes for a better scripting language than Powershell)

Comintern

I use reflection to create generic adapters.

Speaking of DBs...

Sounds like a job for a relational database. — Comintern 18 secs ago

this

5:49 PM

I think we missed an opportunity back there at the AMA - "Can you please stop enabling those making a database out of Excel?"

Hosch250

@this It was brought up.

this

oh good.

Hosch250

They basically said it was one of the cool things to be able to enable people to work how they wanted to work, even if it wasn't the best thing available.

this

um

IvenBach

...

this

5:53 PM

I get that, but they'd do well to enable people to do the right thing.

I've had silly thoughts about merging Access and Excel. Now it doesn't look as silly.

Hosch250

My eyes are bleeding from the logic I'm reading now...

IvenBach

At least a mention of "While we enable or allow this, there is a better option..." would make sense.

Allowing people to pigeonhole themselves into a corner doesn't seem like the best practice for a company.

this

^

IvenBach

I realize that's what Excel did to me... :tear:

Mathieu Guindon

@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor. — Mathieu Guindon 1 min ago

OP: oh, but it's not working
Me: told ya

funny how every single person trying to do this seems to think they're the first one to have ever needed to do that

Comintern

6:01 PM

IKR?

My ironic side wants to implement an RDMS in MS Word now.

this

@MathieuGuindon and until MS fix that deficiency, there'll be more to come.

Mathieu Guindon

@Comintern pfft. only impressive if done in PowerPoint

Comintern

Oooooo...

Mathieu Guindon

@this unfortunately MS is being an enabler in that department

this

^

Mathieu Guindon

6:04 PM

heck they're even pushing UDFs that make https requests ffs

this

but it's web 3.0!

Comintern

Web scraping is like web -1.0

this

"Is there any sort of workaround?" .... kills me.

If you're trying to create a relational database in something that isn't a relational database, everything will be a workaround. — Comintern 4 mins ago

Mathieu Guindon

sure there is — Mathieu Guindon 5 secs ago

half-trolling, half bloody painful truth

this

The bummer is that I don't think you can install w/o admin privs.

I think FireBird is the only DBMS that can install w/o admin-privs on Windows.

Mathieu Guindon

6:11 PM

then talk to your IT dept. and get them to do their job

Hosch250

@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others! — user7735684 34 secs ago

He's beginning to get it.

this

@MathieuGuindon ideally, that would have already happened. But I've seen big corps literally tying their employees behind their back just because. Hence proliferation of Access & Excel containing business data everywhere

Mathieu Guindon

Access isn't a problem. Using Excel for this is.

@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel. — Mathieu Guindon 42 secs ago

FreeMan

6:28 PM

@this Exceless?

IvenBach

:barf: Reaching the limit of number formats in Excel is a signpost you need a dedicated program.

FreeMan

@this we have the same thing here. A couple of years ago they were building and rolling out the EDW - enterprise data warehouse. Boss sent them an email asking why we'd never heard of it and why our data wasn't in it since we were just about to build a DW of our own.

IvenBach

@MathieuGuindon LOL. That link is golden.

#TimeToLearnANewProgram

FreeMan

They went ballistic that we were going to build a "data warehouse". So now we have a "database" on our own SQL server (that IT manages for us) and they're happy because we don't call it a DW.

Mathieu Guindon

if it's just a subset of the warehoused data (e.g. current fiscal year sales), then it's a "data mart"

IvenBach

6:47 PM

2nd time around learning about IDispose and it makes so much more sense.

Hosch250

Normally, now, I have 3:30 left on the day. I still have 4:30 because of my long drive :(

Comintern

That's one of the upsides to living 5 minutes from work.

Hosch250

I"m going to finish this winter and summer, then I plan on moving to the other side of the Mississippi river.

There's a chokepoint around there that always slows things down.

Once I'm past that chokepoint, it takes me 20 minutes to get in, and there are enough trails that I could bike/walk even.

IvenBach

@Comintern That's why I'm having trouble convincing my wife into moving. 10 min "commute" for her.

Hosch250

Moving where?

In-state, or out of state to a different job?

IvenBach

6:56 PM

Into dead grandmas house to live rent free.

Hosch250

Oh, that sounds like a good option.

How much farther would it be for her?

IvenBach

Still in state. It's an hours drive without traffic, 2 hours with.

Hosch250

You might spend more on gas than on rent that way, depending on your car.

Did she leave the house to you? You could maybe sell it and buy a different place closer?

IvenBach

Rent is castratingly absurd here. That'd be a lot of gas.

It was given to my old man but he's in the 3 car garage/loft in back of the house and doesn't want to move into the house.

Hosch250

Well, keep working at it.

You might get her some time :)

IvenBach

7:00 PM

You've much to learn...

Hosch250

LOL.

IvenBach

> Heaven and Hell combined hath no chance of changing a woman's mind.

I'm pretty that's scripture somewhere.

Hosch250

Get your kid to help :)

Get them to work on her about how nice it is over there.

IvenBach

Except it's not... TLDR = yardwork and house needs to be gutted.

Once its all done it will be nice.

Hosch250

:(

Gutting will be expensive too.

IvenBach

7:06 PM

My old man can't physically do the work, and it falls to me.

Sweat equity is how it'll be done.

Her mom lived with us for several years. Once she moved out I politely (no sarcasm) expressed how nice it is for it to be just "us". She played that card on me really fast when I mentioned saving rent.

:shrug: I've gotten so used to failure I expect it.

It's become my safety blanket. It's always their reminding me it keeps me company.

Hosch250

Well, good luck.

IvenBach

:laugh: I don't need luck when failure's got my back. :p

Mathieu Guindon

@GhostCat Could it be that your Google is tuned to programming whereas the newbie's Google is tuned to, say, Kim Kardashian, American football, and the Midterm Elections 2018? Does it still work if you use duckduckgo? (I suspect it does, but I was trying to construct an excuse for the newbies.) — Andrew Morton yesterday

lol

Comintern

Hmmm... So I have service locator with a static cache of objects that hold memory mapped files. Do I have each individual instance listen for AppDomain.ProcessExit, or should the service locator do that and dispose all of the instances?

this

my vote's for keeping it close to the owner -- meaning the SL should be the one to tell everyone "git yer grubby paws off right now!"

Mathieu Guindon

7:15 PM

• Their variables have different names. — usr2564301 2 days ago

So much this

@Comintern this scares me. what is it locating?

Comintern

Makes sense. There's arguably a use case for the instances to be created outside of the SL, so they need to be disposable anyway.

@MathieuGuindon I'm using an f'd up c-tree provider that stores the next sequence number in the file header. I have to skip numbers in the PK sequence though because of business rules, so I'm memory mapping the header so I can increment it without writing and deleting a dummy record.

It's a hack on top of a hack.

The SL tracks all of the tables that I have open and serves out the current PK to the records that are going to be "skipped" items.

Sounds like a great way to get fired. — Comintern 7 secs ago

Mathieu Guindon

@Comintern epic, even

Comintern

7:30 PM

I was going to suggest printing out the ones and zeros and then scanning them back in, but I think that fails the "welcomeness" test.

Mathieu Guindon

I was going to suggest humming the Mission: Impossible theme as it's recording, for extra security.

@Comintern OP failed the welcomeness by themselves

Comintern

I like that idea. Maybe microfilm them and swallow it.

No, do you think I'm dumb? You're basically asking how to smuggle data out of a secured system. AFAIK, assisting you in doing that is a felony in my jurisdiction. — Comintern 11 secs ago

Mathieu Guindon

Walks out to go grab a coffee, picks up M:I theme on harmonica

IvenBach

7:46 PM

Reading between the lines I sense a comment was deleted at some point.

Mathieu Guindon

Yup. The one I flagged =)

FreeMan

@Comintern that one must have gotten ugly with deleted comments very quickly!

this

just for my education, I tried flagging (somethign I never do....)

and I only got 3 options, none which made sense for that question