VBA Rubberducking

2:55 AM

It's a null reference somewhere. Not sure where it's coming from yet.

Gotta run. bbiab

@RubberDuck change the msgbox to display exception.ToString() instead of just exception.Message ;)

Good call!

A: VBA ClickBot featuring AJAX waiting and Element searching

4:17 AM

0

I have to say that this is pretty damn impressive. Really. I'm not going to go through this line by line; my general impression is that it looks pretty readable, procedures are of decent length, it's clear what's going on. I wonder why the late binding here: Private criteria As Object Private S...

@RubberDuck there's your zombie. ^^

actually, I didn't even look at the Gator class

12:32 PM

@Mat'sMug DocableHostWindow.SubclassingWindow.WndProc is the issue somehow. I just don't understand how/why yet.

msdn.microsoft.com/en-us/library/…

A: How to securely store Connection String details in VBA

4

This is what I would do safely store connection string credentials Download and install Visual Studio Express 2012 for Windows (FREE) Open it as Administrator and create a New Project. Select Visual C# then Class Library and rename it to HiddenConnectionString In the Solution Explorer, renam...

@RubberDuck Is your repo up to date? Look for the _addin assignation in app.cs

I pulled last night. I'll check later.

WndProc is application specific, so that explains why Word works, but Excel doesn't.

Kind of.

Mornin' @vba4all

@RubberDuck morning:)

Also @Mat'sMug WndProc behaves a little differently in 64 bit, so that could be why you're not having the issue. Maybe. You think it's worthwhile to put a try catch down there to see if I can't get a better message. Debugger is broken again...

12:48 PM

Hi @vba4all/mehow!

@Mat'sMug hi Mat's Mug

retail coder?

1:38 PM

;)

Q: Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables

1:55 PM

there folks

0

The story... A bit of background info and how is the database designed... Please notice you don't really have to rebuild the tables in SQL but I shared an SQL Fiddle just in case and screenshots1 of what the database looks like. I thought it was going to be easier to explain the story and desig...

that literally took me like 2 days to prepare and write

Stack Exchange

0

Q: Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables

The story... A bit of background info and how is the database designed... Please notice you don't really have to rebuild the tables in SQL but I shared an SQL Fiddle just in case and screenshots1 of what the database looks like. I thought it was going to be easier to explain the story of what I...

oop sql database vba ado.net

Oh wow. I can see why.

ill give it a bounty as soon as i can

Q: Combinatorics in excel: Find every possible sum of every possible combination

3:36 PM

@vba4all worrying about writing long answers? check out the answers on this question and pay careful attention to the author of each

-1

Ok, I´ve found similar questions but none of them solve this problem so here I go: I´ve a list of individuals (col. "A"), and each of them has a value assigned for a determined parameter (col. "B"). I´ve some target parameter values and I want to know which combinations of individuals sum up "x"...

excel vba excel-vba math discrete-mathematics

i've already downvoted this somehow

just opened it up and there was a downvote on it from me :P

the only one hahah

i am terrible haha

> Sorry I cannot format this as code so it is readable because it takes the answer over the 30,000 character limit.

3

...4 long answers, all the same guy. some people really try hard.

3:51 PM

wow....

just wow...

@enderland + @Mat'sMug there's a better one - a question posted in their About Me

2

LOL!

lol

Epic.

2

this chatroom has been Malachi'd

2

hey @Malachi :)

hey!

3:57 PM

I wanted to ask, are you getting notifications for everything we do in the Rubberduck GitHub repo?

yeah

milestones, commits? wow

you must be getting bombarded lately!

not sure I haven't had much chance to look at them

j-walk.com/ss/jokes/vbahumor.htm

Call MeACab(Hansom)

Wow. Now this is dedication... — enderland 11 secs ago

3:59 PM

On Error GoTo Jail

2

Do not pass go. Do not collect $200.

and the CleanExit: label is PassGoAndCollect200:

2

err.raise 911

Oh wait, just clicked that link... lol

any of you use the BotDetect Libraries in .NET?

4:06 PM

Dim Everything as Object

If Everything Is Nothing Then
    MsgBox "This is a paradox of the Object-Oriented universe."
End If

3

> Walk Backwards

walk isn't a keyword is it?

No but could be a Sub

Yeah, that's what I assume it is - just wasn't sure :)

Jail:
	Application.Run "Away"

Jail:
Break

4:12 PM

Hey Mug. Can you set up VS Community to automatically launch Excel when you start debugging? Rather than attaching to the process every time.

I haven't had a chance to download it yet.

Oh damn, probably yes.. haven't even thought if it but yeah, there should be more options under the app properties /debug tab

There's also a performance profiler :D

Really??

Yeah!

Okay. Done waiting. Downloading it now.

Haha

Seen the R# code inspections screenshot I put up yesterday?

4:18 PM

@Mat'sMug Yeah. I saw that. Any indication what those potential null references in app are? I think it could have something to do with my issue.

I'm ready to post a question on SO. I'm baffled.

I might be able to figure it out if breakpoints would work...

Get VS Community first

Debugging will be easier

I hope so. I'm just hoping that I'll actually be able to step into the code.... can't debug if it won't break.

Did the stack trace point to a particular line of code? I might be able to help looking at the code in github

No. It was pointing to excel code as far as I could tell.

do you guys understand the GoSub thing?

4:21 PM

Yeah

It's from the QBasic days :)

GoSub Jail

Jail:
DoShit
Return

Use a function/sub instead

yeah but you can pass an expression

Ugh..... why haven't they purged that nonsense.

On <expressions> gosub Sub1, Sub2

OnSub sounds like the easiest way to make your code impossible to understand

2

No, it's just a GoTo thing

4:23 PM

so if your expression evaluates to an error number youre expecting you can in one line send the code execution to the correct handler

Wait you can do on error gosub???

if you replace the error with an expression that would evaluate to a number

so if you say On <expression> goSub Sub1, Sub2, Sub3 you can depending on the value of the expression send the code to the correct handler

On 91 GoSub HelloWorld
    Err.Raise 91
   'this won't execute
    debug.print "Goodbye"
 HelloWorld:
    debug.print "HelloWorld"

I mean at least that's how I am understanding it

It that the gist of it? ^^

4:28 PM

I don't think it works like that

I have never touched GoSub so I am asking you guys :)

I'd do on error goto, and perhaps do the branching in the err handler, `Select Case Err.Number / Case 6: GoSub HandleOverflow /....

oHHH I think I get it now.

I think you could do it with errors

Instead of this.

4:30 PM

gosh... i am losing my patience -> stackoverflow.com/q/26911803/2140173 !!! F*** ive deleted 5 answers and can't delete any more today

it's basically a case statement for which sub gets called

GoSub is just GoTo with a Return

Select Case expression
    Case 1: SubRoutine1
    Case 2: SubRoutine2
  End Select

@RubberDuck Yup

On expression GoSub SubRoutine1, SubRoutine2

4:31 PM

So you could do that in an error handler, I guess, if you wanted to do something like

Or you could extract actual procs instead of making the execution jumping and returning all over the place within the same proc

What I don't know is what the advantage of that EVER woudl be over a select/case statement which is more clear

It's not. It's a relic.

@vba4all Just ignore OP. Don't respond.

yeah, tomorrow morning ill delete the answer and clear any evidence i ever was there

Rubberduck addin needs a code inspection to recommend refactoring GoSubs into procedures

4:38 PM

God help the poor soul that ever sees that recommendation.

codereview.stackexchange.com/posts/69734/revisions

hmm.. tweeted?

what's that? first time i see it

does CodeReview pick that up automatically or manually

I don't know, but I've seen that banner a few times.

Code Review SE

@StackCodeReview, The Stack Exchange network

A Q&A site for peer programmer code reviews

8.6k tweets, 499 followers, following 0 users

<lunch>

5:22 PM

What's a good way to do "null" dates in VBA?

I'm doing something like:

    newMyDate= Nz(Me.Controls("update_myDate"), 0)

but then i have to do checks on the if newMyDate <> 0 then ....

Public Sub foo()
    Dim bar As Date
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
    bar = Now
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
    bar = vbEmpty
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
End Sub

IsEmpty returns False...but = vbEmpty returns True...

wtf-vba

it gets interesting when bar is a Variant

Public Sub foo()
    Dim bar
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
    bar = Now
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
    bar = vbEmpty
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
End Sub

thisworkbook.foo
Empty         True          True
Date          False         False
Long          False         True

3 mins ago, by Mat's Mug

wtf-vba

So using 0 is the same as vbEmpty I think?

Public Sub foo()
    Dim bar
    Dim nullDate

    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
    bar = Now
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
    bar = nullDate
    Debug.Print TypeName(bar), IsEmpty(bar), bar = vbEmpty
End Sub

thisworkbook.foo
Empty         True          True
Date          False         False
Empty         True          True

I hate IsEmpty seriously

^^ you can assign to an uninitialized variant

me too

5:29 PM

VBA makes "does this object exist?" so convoluted

object? Is Nothing

easy

it's value types that are a PITA

Hrmm, I think I just generally do "is not object is nothing" to get the "positive" of that. Which is backwards, I guess?

lil'bit

But I generally prefer "if object exists then" syntax

@enderland I do the double negative a lot too. I tend towards it because often that's the "happy" path.

Of course, there is that whole "fail fast" thing... so maybe it's not best after all.

5:33 PM

I guess I like thinking of

> if (positive) then
else 'negative condition
end if

make a ~~IsNotObject~~ IsValueType function?

LOL

Quick preview of my latest review.

> You're a good programmer, so I won't question that you need to use On Error Resume Next here, but you definitely should add a comment here explaining why you're doing it. As it is, I don't have to domain knowledge to understand why you're doing this (and neither will the person maintaining the code someday).

where's that?

In my browser window.

Almost done. Will share when I save.

@ptwales's question?

A: VBA ClickBot featuring AJAX waiting and Element searching

5:43 PM

Yeah. Man.... that code is pretty darn clean. I had to take the "mention everything done right" method of reviewing.

0

It seems the other answer didn't touch the Gator class, so I'll focus on that. ' Required .dlls ' `Microsoft Internet Controls` Well done right there. All too often there is no documentation about what references a piece of code needs to work. Someone will thank you for that someday withou...

Okay. Smoke, then back to work.

Set-only property is weird

5:59 PM

@RubberDuck you missed a potential bug with that On Error Resume Next

IdExists should say On Error GoTo 0 immediately after the line that expects the error, to restore error handling. Otherwise it may be hiding problems, because On Error Resume Next turns off error handling altogether, even beyond the procedure's scope (until another On Error statement is encountered). — Mat's Mug 33 secs ago

I thought it was only until the end of the procedure scope?

Public Sub foo()
    On Error Resume Next
    bar
    Debug.Print "nope"
End Sub

Public Sub bar()
    Err.Raise 999
End Sub

That's why On Error Resume Next is poison

> An On Error Resume Next statement becomes inactive when another procedure is called, so you should execute an On Error Resume Next statement in each called routine if you want inline error handling within that routine.

msdn.microsoft.com/en-us/library/office/…

aww

I thought... dammit vba

Yeah. It's practically disabled on End Sub. I think......

6:11 PM

where did I read that it bleeds out...

anyway you're right, it doesn't. this never prints "nope":

Public Sub foo()
    On Error GoTo Jail
    bar

    On Error Resume Next
    bar

    Exit Sub
Jail:
    Debug.Print "handled"
    Resume Next
End Sub

Public Sub bar()
    Err.Raise 999
    Debug.Print "nope"
End Sub

6:24 PM

@RubberDuck I added some issues under the code inspections milestone

Feel free to add more if you think of anything!

Will do.

I've been kind of focused on getting it working again. I apologize for that.

Hey no problem, blame me!

Stupid rename anyway....... =;)-

7:36 PM

all right, there's like 20 code inspections down the pipeline. back to work!

9:20 PM

this SO ticker is a good source of inspiration for Rubberduck code inspections!]

9:34 PM

@ticker......

On Error Resume Next is shoving all errors under the carpet - bascically this code cannot possibly be crashing. — retailcoder 27 mins ago

@RubberDuck technically you can still get hard crashes in some cases even with on error resume next, I've had ActiveX objects throw non-debuggable errors (and not otherwise catchable, for that matter) with that set in futile attempts to actually find the line of code the error happens on...

wtf

On the second Cells.Find I get the runtime error 91 — Tony 12 mins ago

@enderland True, but I'm pretty sure that's not the case here.

ummmmmmmmmmm

wtf

lol

@RubberDuck aww man I was hoping it wasn't an "on error resume next is causing me to not undesrtand why my probgrmam is falign!"

ptwales

9:43 PM

@RubberDuck what am i looking at?

@ptwales Query plan where somehow multiple tasks both take up 100% of the processing.

I think SQL Server is a bit confused. It's also estimating about 5000 rows when in reality the query returns close to 300,000

I'd wager your stats are off.

is this an estimated execution plan, or the actual one?

I refreshed them myself today.

Actual.

wow

I'm... flabbergasted

To be fair, it's about 50 lines of SQL, and one of the "tables" is a view with just as many underneath it.

I've found views tend to mess with query plans.

9:47 PM

yeah well, there's "to mess" and then there's "to mess up"

Computer's only as smart as the guy telling it what to do.

I mean, I could inline the view logic and triple the LoC. It would probably straighten the query plan out.

But no one else would ever be able to touch the query. Ever.

I haven't used views very much, but might you be better off trying to put that data into a temp table?

Hey @Phrancis!

@RubberDuck never underestimate what a consultant can touch (and mess up further).

@Mat'sMug You mean what a consultant can fail to understand .... that ^

9:50 PM

Hey @ducky - Mug posted your execution plan next door I figured I'd stop by

I wish I could get a snapshot of the overview. It's a dousy.

Now I thought steps in execution plans were sequential, rather than simultaneous?

whistles changing production code..... whistles

I thought so too.

So it would make sense if it used 100%, then 100% again later

Well.... yeah. Maybe I guess.

I honestly don't know much about how it goes about generating plans.

I just know a little bit about optimizing them.

9:54 PM

I know there are ways to force SQL Server to use a certain execution method, useful for circumventing shit like nested loops

I was a little surprised that this was more efficient than this though.

    AND NOT EXISTS(
            SELECT
             FROM A
            UNION
            SELECT FROM B
)

Does a view store its own execution plan like a stored proc/func would? Does it index itself?

AND NOT EXISTS(
       SELECT
       FROM A
)

AND NOT EXISTS(
      SELECT
       FROM B
)

@Phrancis Yes and no.

A view can be indexed, but you have to explicitly do so.

This one is not. Mostly because it's then a PITA to refresh the tables from the external source.

It does keep it's own query plan though. Which is why it's less efficient than it could be.

But I'm not into maintaining that logic all over the place. Let it be inefficient.

@RubberDuck indexed views must be schema-bound, it's a PITA period; views normally leverage indexes from underlying table

@Mat'sMug Right. And schema-binding is all well and good if you're running a transactional db. Forget it with an ETL db though.

10:01 PM

@RubberDuck I think the former NOT EXISTS is better because it only has to scan your data set once (for more conditions) rather than twice (with fewer conditions)

Yeah. You would think so, right?

Took twice as long to run.

TTGH (and play with VS Community)

Then continue to curse at my broken a$$ code......

@RubberDuck added 2 new labels to identify code inspection types, and upated all issues under Code Inspections - there's "Code Quality Issues" and "Maintainability/Readability Issues" for now.

@RubberDuck I guess it would also be affected by the way it is joined in your subquery, it may be throwing one of those nasty nested loops.

Well, BTW.Work

10:27 PM

> Hello. We're preparing for first use.