VBA Rubberducking

I am writing a vb script on excel below is my problem. I have more than 20 sheets in excel and one main sheet (All Programs with 200 names). Each sheet has column with Names and 24months(Jan18 to Dec18, Jan19 to Dec20). Each sheet names is subset of main sheet Main sheet(All Programs) has 200 N...

KySoto

-_-

i was hoping that i could create a foreign key connecting my application table to the changelog table

but you cant use a composite key in a foreign key situation

M.Doerner

@QuackExchange The nested four loops might hint at the problem.

KySoto

@IvenBach Thanks dude.

Mathieu Guindon

10:01 PM

i changed the title for easy filtering . Thank you @m — Rohith Gowda 39 mins ago

edits title to be exactly the opposite of what I said

Hosch250

Looks like he's not a native speaker, or New Jersey schools failed epicly (again).

Mathieu Guindon

10:24 PM

hmm.. ok so to clarify then - Cmd is an object reference to the button you're trying to "click"? Is that object owned by another instance of Access.Application? I believe SendKeys used unqualified like this, goes against Application, which would be your "current project" instance - if you can get ahold of the second Access.Application instance, and try invoking SendKeys off that Access.Application object.... — Mathieu Guindon 4 mins ago

@this can an Access VBA project reference another Access VBA project?

AFAIK you can't have two VBA projects opened in Access, so... I'm a bit lost about how that might work

this

yes, they are commonly known as "library database"

and you'd be wrong RE: not having multiple VBA projects open in Access - that IS possible.

Mathieu Guindon

see, I keep talking out of my mug

go help this poor chap!

IvenBach

Duck polling: what would y’all consider is familiarity with DB vs proficiency?

I tend to underestimate my knowledge and want to apply to a job. The DB requirement makes me hesistate in doing so.

Comintern

10:39 PM

@IvenBach Depends? I consider familiarity to be something like "Oh yeah, Oracle is a type of DBMS". Proficiency would be knowing the quirks that make Oracle different from other DBMS's. Mastery would be the ability to code golf in that DBMS's SQL dialect.

How is the job posting worded?

IvenBach

Familiarity would be able to get work done, looking up how to do it in documentation

While proficient wouldn’t need the documentation, except in odd occasions?

this

No way. I consider myself proficient with SQL but I always read documentation all the time. I can't commit to the memory every bits and odds about SQL quirks. I would measure the proficiency based on how fast you can find the answer using the documentation.

So if they have a page on say.... ALTER TABLE and go blank.... they are not proficient.

Comintern

Same here. I consider myself proficient in SQL, but I'm always looking stuff up, especially things I don't use a lot.

IvenBach

That'd put me at pre-proficient.

Comintern

preficient?

this

10:44 PM

OTOH, one who can glance at it, quickly jump to the section that's relevant, then look up ALTER COLUMN page and now has a statement composed --- that's much more proficient.

Typing it entirely from memory is arguably impressive but unrealistic in general.

Comintern

^

IvenBach

> There's 3 levels of vert prevert, pervert, and provert.

My old man long since mastered being a provert. :p

One job description has: 'Knowledge of Access or other data-entry software.'

Comintern

LOL "or other data-entry software".

IvenBach

Another job posting has: 'Experience with database entry and design (Access or equivalent)'

Several of these positions revolve around the idea of getting information consolidated.\

Comintern

I don't consider myself proficient in Access, but I'm certainly familiar enough to get around.

Might take me 3 times as long to create a form or a report, but I could pull it off if I had to.

I think that may be the fundamental difference between the 2 terms.

this

10:49 PM

opens Tools->References

:(

closes References dialog, reopens from CE

3

Mathieu Guindon

@IvenBach I lookup stuff all the time. Delete/Update from join isn't something you "just try and see how it goes" on a production db =)

this

oh gosh such brave soul you are!

I never could edit data on a production database....

Being a sissy I am, I always use beta....

Mathieu Guindon

sometimes you just have to

ok sometimes you script against the test db see it works fine then run it against the prod db

#CowboyWithSafetyNet

this

After one deletes a table without a backup and lock up a whole department, losing productivity and some substantial amount of money, one becomes very gun-shy.

#TotallyNotSpeakingFromExperience

#HappenedToAFriendIKnow

IvenBach

@this Ouch

KySoto

10:58 PM

backups are king

Comintern

I've done stuff like that before.

KySoto

audit tables are boss

this

@KySoto more importantly --- working backup

KySoto

yeah, true

IvenBach

If I do get interviewed and they ask about experience.

this

10:58 PM

(I actually did take a backup but I botched that up.... soo.....)

KySoto

we have been recovering from SQL server backups for a while now

Comintern

Last instance was a stupid drag and drop error in a share I wasn't supposed to have RW access in. I accidentally move a folder into another folder when I tried to double click on it and didn't even notice.

this

That was years ago. Still haunts me.

IvenBach

> I've experienced them before. I found out the hard way to not try anything on a prod first time around.

KySoto

O_O

this

10:59 PM

Ooo that's scary. "I thought I done made the backup!" and not even know about it.

KySoto

generally speaking, the only databases i dont write data directly to are our production databases

since the stuff i normally work with is tracability stuff

this

@KySoto unless you're a consultant called in to put out the fire for a company that has no IT department and everything is going to hell in a handbasket.

IvenBach

Knowing that even the #Experts can get it wrong gives me hope.

this

oooh, a funny anecdote for you!

That was relayed to me from one of my old employer.

He was called on site to help out a customer with designing a report for a some thing to do with RPG (no, not a generic of game, a programming language. Predates SQL)

Comintern

The ones that are the most fun are rebuilding .NET source code from a decompiled executable after you forgot to push it and rolled back two or three weeks worth of work.

KySoto

11:02 PM

@this not going to lie, thats why ive never consulted for anyone

this

so the customer explained what they wanted and were quite very apprehensive -- after all they had this expensive mainframe working like clockwork and there were lot of processes that revolved around this, ok?

He went, "Don't worry! I'm an expert at this!"

Starts working on the report.

KySoto

didnt backup the report...

this

Does something (he didn't recall what) and everything went down

KySoto

everything explodes...

FFFFFFUUUUU!!

this

Since then, he has never called himself an expert - preferring to say "I'll do my best".

KySoto

11:04 PM

this

I think that's a great advice. Call yourself "expert", you gonna be like that ^

KySoto

yeah... people call me that, and im like... really, im secret trash

but im better then all of yall

IvenBach

I'm not an expert. Really the only difference that sets me apart from you is I've failed a lot more at it than you have.

KySoto

lol

i have a reasonable amount of experience sighting traps in code...

IvenBach

Co-workers think I'm an Excel-Guru. Nope. Just willing to learn enough to automate my work.

KySoto

11:07 PM

but then again, im equivalent to a level 3 rogue in the art

lol @IvenBach , the secret is... you are lazy

you dont wanna manually do crap you dont have to do

Mathieu Guindon

ttgh

IvenBach

Be safe mug.

KySoto

well.. thats how it is for me anyway

he out? later man

IvenBach

I tell everyone openly I'm lazy, just not slothgful.

KySoto

ive spent many months doing work that reduces how much time it takes to build apps

really ive probably saved a good 60% due to that work

or even more, since im just spitballin it

instead of spending 10% of my time on research, finding out exactly how i need to build stuff and get everything they need, i spend closer to 80% of my time on research

well those are also spit balled

but everyone is happy, and im not overworking myself

so i dont feel burnt from my job

this

11:12 PM

:+1:

KySoto

i guess to summarize it, i follow the scotty rule, but to allow myself to follow that rule, it took a lotta hard work building up an infrastructure. that infrastructure isnt as good as what you guys could do, but i also am trying to keep it comprehensible to the other people who use it.

IvenBach

Work for your customer. If they're not happy you may not be employed.

KySoto

Because ive had some of the functions that i make set up.. not quite all the way how you guys would do it, but closer, and it totally befuddled some of my fellow programmers

it took a minute to get them to use it

but they really didnt get it

it was a... well i trust that you know what you are doing... so i guess i'll use it, kinda deal

Vogel612

so I spent most of my day trying to troubleshoot native library binding issues...

KySoto

@IvenBach Thats a good way to do it, I've always tried to make it programmer complex, user easy.

so i validate the living piss out of hte data before it goes anywhere

Vogel612

11:17 PM

turns out the folks I sent the PR to were happy with me just skipping that part of the code entirely, sooo ...

KySoto

reference other data etc

oooooofff

Vogel612

obtw @this I might go and rework deployment sometime soon-ish

KySoto

and they just scan a label, and they are 90% good to go. all they have to do is usually put in some quantity or whatever

IvenBach

@Vogel612 There there vogel. We've all been there.

Vogel612

Yea it's kinda fun to contribute to a library that you plan to extensively use for a sideproject to ease management on a sideproject (which is RD)

this

11:18 PM

@Vogel612 Cool - what did you have in mind?

Vogel612

Rewrite using Cake

this

hmm never heard of Cake.

what does that buy us?

Vogel612

Buys us build configuration in C# and native tasks

KySoto

one day i will get the competence and confidence to contribute to a library

Vogel612

should allow us to completely separate Deployment from all the other projects

this

11:20 PM

Nice. Will the build task be redone?

Vogel612

AIUI it's compatible with MSBuild Tasks, so... not necessarily

this

As long it works for both local building and AV building, I'm happy.

the deployment would have been helluva much easier if I only care about AV building but we need to make sure contributors can build locally with the same settings....

can't wait to see what you'll do with it! :)

Vogel612

Check out this file that's where the magic happens

not even 100 lines that tie together the whole build process

And I'm pretty sure we won't need remotely as much because we're not doing multi-targeting or multiple packages or version overrides or linking native binaries

sooo ...

Tweeting Duck

6-Question Quiz on Dynamic Arrays https://youtu.be/YNZktG-IeTw

Tweeted by OzExcel on January 21, 2019 at 6:51 PM

Duga

[rubberduck-vba/Rubberduck] MDoerner milestoned issue #4733: Introduce the ISelection service

[rubberduck-vba/Rubberduck] MDoerner created pull request #4733: Introduce the ISelection service to merge MDoerner/Rubberduck/IntroduceSelectionService into rubberduck-vba/Rubberduck/next

> This service is a provides access to the active selection and selection of individual modules without involving COM wrappers directly; the interface only uses QMNs and selections.

It is currently not used anywhere. Future PRs might change that.

[rubberduck-vba/Rubberduck] MDoerner edited pull request #4733: Introduce the ISelection service

[rubberduck-vba/Rubberduck] MDoerner milestoned issue #4717: Inspection Result window performance overhaul (Round 1)

[rubberduck-vba/Rubberduck] MDoerner milestoned issue #4727: Code Explorer hotfix 3

[rubberduck-vba/Rubberduck] MDoerner milestoned issue #4699: Clean up the persistence services

[rubberduck-vba/Rubberduck] MDoerner removed label PR-Status: Conflicting from pull request #4686: Recovering member attributes

[rubberduck-vba/Rubberduck] MDoerner synchronized pull request #4686: Recovering member attributes

this

11:42 PM

> buildSystem.AppVeyor.IsRunningOnAppVeyor;

I like that!

Vogel612

yea, they're running on appveyor and travis

this

yeah I saw.

Vogel612

they determine versions automatically by parsing git tags

it's just... lots of fancy stuff

this

but how does it work w/ the local build, exactly?

it doesn't require one to have an extension in VS or something like that, right?

Vogel612

nope

it's basically a standalone app that handles the build process

this

11:44 PM

so we still can do Project -> Build like we normally can?

Vogel612

yep

it just calls MSBuild under the hood

For example here

this

I figured but I was more curious about how VS knows that it should call out ot Cake rather than MSBuild

Vogel612

now that's something that I would need to find out, actually...

cakebuild.net/docs/editors/visualstudio

this

not clear whether the extension is needed for the 2nd part of the page.

it sounds like build.cake would be all is needed to bind the VS' command to those tasks but ?

Vogel612

yea, that's a bit confusing...

Duga

11:53 PM

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

Transcript for