« first day (1735 days earlier)      last day (1445 days later) » 

4:00 AM
I had defined the symbol in the VBEditor project's conditional compiliation constants
Right
 
Did the new project format mess up the symbols?
// on the precompiler directives works too. :-D
 
IDK; if I type in the DEBUG in the textbox and save, it automatically removes it and ticks the define DEBUG symbol
LOL now i'm a moron, thanks
i'll just // it
ah right - same problem with #define which also works but htey 're spread among files
seems to be just 2 files, though
 
Weird. Did the project show up in the diff when you set the constant?
 
yeah
even rebuilt
when i do use the #define, the dimmed lines lights up.
it should be like that if the conditional compilation constants is defined, too, no?
 
Yep.
I haven't tried it since the project format switched over though.
 
4:10 AM
ugh. is 2K lines an annoyingly long stored procedure?
 
2K lines is annoyingly long in general, but for SQL you generally have to overlook things like that.
 
fml I'm about halfway done
I hate Sage and its stupid redundant 90-column denormalized tables
 
SQL like dem vertical paragraphs.
of course, denormalization makes it worse
you could just use SQL to write SQL
 
thing is, once I'm done I need to either duplicate everything for each Sage db I need to write to, or make that huge thing dynamic sql and deal with the complications of running dynamic sql in a transaction
 
haha that sounds painful. I doubt I've ever written a single stored proc quite that long. Thus far it has always made design sense to split it logically or move some component to the C# side
 
4:16 AM
haha no way I'm polluting my awesome C# WPF app with all that sql junk =)
 
@MathieuGuindon can you queue them up?
 
meaning?
 
do the transaction entirely inside a ginormous dynamic sql batch
As an alternative, you might not need to use dynamic sql runtime but only for designtime
 
not sure I follow
wait you mean have dynamic-sql script the create procedure stuff?
 
create a batch script that takes parameters and alters a stored procedure with the varying changes you need. That way, your changes are easily propagated to all different databases of similar schema.
yeah
 
4:20 AM
that's... not crazy at all..
 
more like alter but yes
actually if your SS is recent enough, CREATE OR ALTER
do you use SQLCMD?
 
it's so recent it's half-written
 
recent = the SS version
 
@this did once, through powershell
 
I think CREATE OR ALTER was added in 2016? 2017?
 
4:21 AM
I'm on 2014 here
 
SQLCMD can be used via SSMS. Works wonder for parameters that are not amenable to parameterizing via dynamic SQL
But do have a guard clause just in case.
 
like db name for from clauses?
 
yeah
 
something like this:
 
4:23 AM
was thinking dynsql to replace $db$ tokens with quotename(@sageDb)
 
--Requires SQLCMD mode
:SETVAR SqlCmdIsOn "SqlCmdIsOn"
GO

IF ('$(SqlCmdIsOn)' = '$' + '(SqlCmdIsOn)')
BEGIN
    RAISERROR ('This script must be run in SQLCMD mode.', 20, 1) WITH LOG
	SET NOEXEC ON;
END;

GO

:ON ERROR EXIT

--Set the name of database to be configured.
:SETVAR dbName foo
<rest of script>
The added bonus of a SQLCMD-enabled script is that we get ON ERROR EXIT which negates the incredibly stupid error handling behavior of T-SQL
no more "ooo, a little error. no worries. let's just abort the transaction and keep going."
 
o__O
@this BTW I see you still haven't updated your MVP activities... clock's ticking ;-)
 
blargh
it's tedious
 
it absolutely is
 
i'll get to it shortly.
 
4:30 AM
less so when you update it say, quarterly
 
yeah. that's what I said to myself last year. -_-
(and the year before that...)
 
I'm not stalking your profile, just curious how much other MVPs put up there... first renewal is ...nerve-wrecking =)
 
in first few years, it wasn't as bothersome - basically write a page or so of what you've been doing, done. Only in recent years they start doing all this kind of metrics.
I agree it is nerve-wrecking.
I still have no clue whether I'll be renewed just as I didn't my first renewal.
 
at least you're in a "standard" category.. I feel like I'm kind of not really fitting the Office Apps & Services category. Kind of like somewhere between the defunct VB category and "development tools" but for outdated tech... makes me wonder, maybe I should blog more about the development and intricacies of RD & involved tech.. IDK
OTOH I do put up a crap ton of content on SO's Excel tag
eh, whatever happens, happens.
 
actually we're in the same boat
after all, I primarily work building business solutions
so yeah, Office Apps & Services doesn't do us justice
it was fine when it was just "Access" since it's what we use the most to build the solution (usually in conjunction with other technologies)
but now it's "Office Apps & Services" which technically encompasses the whole Office but? I don't know if lumping those who develop using Office as platform with those who use Office to do their business is a good idea.
(for some values of "platform")
 
4:44 AM
"host" =)
 
we'll find out July 1st, I suppose.
 
I said platform primarily because we don't just use Access exclusively. It ends up being the Office's octopus.
 
and I'm sure many excel applications out there are just like that.
I WANT ME A ONE BUTTON TO SEND A EMAIL!
 
4:46 AM
don't forget web scraping
 
of course.
lol i see what you did there
alright, time to hit sack. night!
 
hehe... all right, I'm off to bed. got more tsql to write tomorrow!
'night!
 
 
3 hours later…
8:16 AM
> **Rubberduck version information**
Version 2.4.0.4586
OS: Microsoft Windows NT 10.0.17763.0, x64
Host Product: Microsoft Office x64
Host Version: 16.0.11328.20146
Host Executable: WINWORD.EXE

**Description**
I've just had one of those Doh moments where despite being aware that any function calls in an iif (Immediate If) are always evaluated I still managed to shoot myself in the foot. Would it be possible to add a code inspection for IIf statements that contain function calls.

Pro
 
 
1 hour later…
 
2 hours later…
11:48 AM
> Interestingly, we wouldn't be the first to do something like this.... The (ancient) Office Developer editions added a standard module called `VSSODE` when a non-Access project was added to VSS (Access used custom DB properties).

```
Attribute VB_Name = "VSSODE"
Option Explicit
' SCC = This is a Source Code Control file
' Designed for the Microsoft Office 2000 Developer VSS Add-in
' Do Not Remove!

' SCC_Project_Name =$/VBAProject
' SCC_Aux_Path=C:\Program Files (x86)\Microsoft Visua
 
12:05 PM
> I couldn't find any other issues referencing this, but I am sure other people refer to this by a different name. Sorry if this already exists.

Anyway, it would be useful if Rubberduck could somehow identify orphaned subs, functions, classes, enums, types, etc.. I.e. code that isn't referenced by any other parts of the code and can theoretically be deleted. I do come across these types of functions from time to time in my project, and it would be great to have a way to find them. Rubberduck
 
12:38 PM
> The basic functionality for this is already there in the Form of the `DeclarationNotUsedInspection`. To deal with circular references, it would have to smarten up considerably, though.

I am not sure whether we already have an empty method inspection. If not, it might be a good addition.

Regarding empty if blocks, there is already an inspection. However, I think it is currently deactivated because of some issues.
 
1:05 PM
> There is already a `ProcedureNotUsed` Inspection. Am I missing something?

I just created this little `Sub`:

```VBA
Private Sub test()
MsgBox ("I'm not used!")
End Sub
```
Parsed the project and got this inspection result:
![2019-03-07 08_03_50-code inspections](https://user-images.githubusercontent.com/11889733/53958683-b31d3100-40af-11e9-9432-57f1a9083f94.png)
 
What was the reason for commenting out the supertype resolution for documents here? What that a performance concern or something else?
 
1:16 PM
> It seems as you already have this covered, I just found it really hard to find. Your code inspection window looks quite different from mine, but that might be some features for the upcoming release? I am on the latest 2.4.0 release, but not on any of the dev releases. In the view you present it is a lot easier to find those warnings specifically, and that would solve it 😄
> **Rubberduck version information**
Version 2.4.0.4586
OS: Microsoft Windows NT 10.0.15063.0, x64
Host Product: Microsoft Office 2016 x64
Host Version: 16.0.4810.1000
Host Executable: MSACCESS.EXE

**Description**
`Find All References` seems to be missing some references when they're on the LHS of an assignment

**To Reproduce**
Steps to reproduce the behavior:
1. Create a class with members and appropriate Getters/Letters
2. Assign a class member a value (put it on the LHS)
3. Us
 
@Comintern performance and stability, yes
 
> If you look at the identifier at the top of the tab, you'll see that the declaration you're finding references for is the property `Get`. The one in the green square is the `Let`. This appears to be working as designed.

As to whether it should (optionally?) aggregate the `Let`, `Get`, and `Set` in this dialog would be up for discussion - I could see the usefulness of adding that functionality.
 
@MathieuGuindon That's kind of what I thought. This is rapidly veering toward wiring up the TypeLibs API...
 
> I'm running dev build .4586.

I'm not sure when @comintern updated the Code Inspections window, but it was reasonably recent. This is the first time I've seen the new grouping icons and options - last I looked at CI, the only options were "Inspection Type" and "Location" and they were a drop-down, not buttons. This is a pleasant surprise! (But I've noticed a couple of bugs that I'll be reporting shortly. 😢)

Unless you have to jump through major hoops to get updates installed on a corpora
> Ah, yes, that does make sense. I had seen that, but it hadn't fully registered.

For the naive user (waives hand), having it default to finding `Get`, `Set/Let`, with an option to have it find only one side or the other would be quite useful. Grouping them by `Get` and `Set/Let` would help ejumacate said naive users while reducing the confusion for less naive users.
> Thanks for the tip, I'll start running the latest builds. I am not particularly afraid of things breaking; I run Arch Linux on my personal computers, so I am kinda used to things breaking.

Feel free to close the issue unless you want to keep it for e.g. the possibility of adding empty blocks / empty function checks if it isn't there already.
> Thanks for the tip, I'll start running the latest builds. I am not particularly afraid of things breaking; I run Arch Linux on my personal computers, so I am kinda used to it.

Feel free to close the issue unless you want to keep it for e.g. the possibility of adding empty blocks / empty function checks if it isn't there already.
> **Rubberduck version information**
The info below can be copy-paste-completed from the first lines of Rubberduck's log or the About box:
Version 2.4.0.4586
OS: Microsoft Windows NT 10.0.15063.0, x64
Host Product: Microsoft Office 2016 x64
Host Version: 16.0.4810.1000
Host Executable: MSACCESS.EXE

**Description**
Expanding one inspection grouping causes all to expand.

**To Reproduce**
Steps to reproduce the behavior:
1. Open CI window
2. Click on `Collapse all` button
3. Click
 
1:43 PM
@Duga Ooops. Missed a use of the grouping grid there.
 
@Comintern blame Irubataru - if he hadn't complained about not being able to find the feature he was after, I wouldn't have noticed. ;)
 
NP - the collapse and expand functions of the grouping grid do that as a side effect if there isn't a behavior attached to persist the collapsed state.
3 LoC fix.
 
my bad, I'll try to find a more difficult one for you next time.
 
> FWIW I disagree that overloading the built-in error numbers is bad. To me raising error 5 with a custom message is similar to throwing an `ArgumentException` with said custom message, and making a new custom exception type for it makes no sense. If I see error 5, I know it has to do with an invalid argument. If I see "invalid 'foo' argument" with a weird error number, then I immediately think "but why?".

I don't think this can be inspected.... unless I give up throwing error 5. Convince me
 
2:14 PM
> For the record, I am guilty of having the same, overloading the built-in error number which is why I noted in the OP that the part about inspection the built-in error range can be debatable.

The primary justification I can offer is this - keep in the mind that .NET gives us the ability to create and derive from exception classes, so we can easily create custom exceptions for situations where a custom message isn't enough. That doesn't exist in VBA where we get is a dumb ol' number & 2 stri
 
2:54 PM
@Duga @FreeMan I'm probably missing something. Why does the title say Find All References but screenshots are about CI?
 
@Duga Just turn the inspection off.
 
> Agreed, VBA errors aren't exception types. *But* IMO the best practice recommendation to "use the best applicable built-in exception type whenever possible" is solid, and correctly transposes to VBA errors, at least error 5, which I mentally translate into ""invalid operation" or "invalid argument", which I believe are the most common built-in exceptions to throw.

OTOH I can totally see custom errors in the built-in range as being an inspection-worthy problem... maybe have a setting to excl
 
@Hosch250 that would work if it's 2 inspections, rather than 1. The other range (0, >=65535 and < vbObjectError) should be inspected.
 
@Hosch250 I wouldn't want the inspection to fire for error 5, but an overloaded/customized error 6 or 13 makes no sense and I would want the inspection to warn about those =)
@this IDK about making it two inspections though
 
If we do make it configurable, though, that means we need to address the other long-standing issue about inspection-level setting
we already have one and it's kind of a wart
@MathieuGuindon given the behavior for the numbers that aren't 1-65535 or vbOjectError-vbObjectError+65535, I think that must be inspected at all times because that's going to trip users up.
 
@this It does not!!!
or, maybe, I was cornfused
 
I could see raising a "Subscript out of range" for custom enumerables.
 
@FreeMan @Duga begs to differ.
 
lol
@Comintern true
 
^
 
3:04 PM
@this we all know that @Duga can be grumpy and tempermental...
 
That's about the only one other than an error 5 though.
70 Permission denied
63 Bad record number
 
I think I may have overloaded the file error (51? 53?) once
 
51 Internal error
 
isn't 53 "file not found"?
 
Yes
 
3:06 PM
@this True\False\File not found?
 
just checked. :)
LOL, no primarily to substitute the file parameter in
 
I override 91 all the time
2
 
e.g. 'C:\foo\bar\baz.txt' not found
but that was only one time.
 
Like a re-throw?
 
As a matter of fact, yes.
 
3:07 PM
so, "exclusion list" isn't a good idea
 
I'd probably want the inspection to detect any re-raising.
 
Err.Raise Err.Number?
 
It was for an internal API, and at that point, I decided custom error numbers worked better than overloading built-in error numbers.
 
3:08 PM
rethrowing is fine, depending on where/why it's done
 
so I wouldn't have to wonder whether it's my custom error or some other unexpected error that happened to be the same "error numer"
 
If Err.Number = 51 Then
    Err.Raise 51, , "Expected True or False"
End If
More like that
 
^ I'd have just re-translated it with a custom error number.
 
I would have too.
 
3:09 PM
so that's why you added the code path analysis?
 
and TypeName(Me) for the source arg
 
I assumed because of a variable assignment
 
@this No. You can raise an arbitrary expression.
 
oh, like Err.Raise 1 + 1?
 
Err.Raise Err.Number / 0 'bwahahaha
 
3:10 PM
that'd be uh, totally moronic
 
Err.Raise SomeFunction()
 
facepalm
 
TBH, I'd never thunk to do it.
 
Probably because I'm going ot be juggling 5 parameters
 
3:11 PM
Seen it in the wild.
 
facepalm x2
 
There are lots of clever custom error handling patterns out there.
 
@Comintern Makes sense to me...
 
@Comintern I think you mispelt "dumb"
 
""clever""
 
3:13 PM
Examine the state, and return whichever number it determines if it finds errors in a preferred sequence to fix?
 
I think I mispelt "cults" as "patterns" too.
 
Like, A has to be fixed for us to go farther, and it's an error X. B comes next, and is error Y.
 
I've read that if you see code written by a genius, you should run.
 
@this Not quite.
 
@Hosch250 except, the parameter is just a number.
Not a type
 
3:14 PM
What if SomeFunction() returns a number?
Wouldn't it raise the number returned?
 
You don't even have to pass the parameter. Err is a global static.
 
and keep in mind you have 4 other parameters to set, too
 
Public Function SomeFunction()
    SomeFunction = Err.Number + vbObjectError
End Function
Fixed!
 
this would be OK:
MyErr = SomeFunction()
Err.Raise MyErr.Number, MyErr.Source, MyErr.Description, MyErr.HelpFile, MyErr.HelpContext
 
Ohhhh.
 
3:16 PM
Should be SomeSub().
 
So when you do Err.Raise SomeFunction(), it's really just setting global error state in the function?
 
I'd like to see you return something from that.
 
only the number, yes.
 
The only way that returns is if you catch the error you just raised.
 
Which is also especially insidious when you consider how re-throwing can behave.
 
3:17 PM
Public Sub SetErrorState(number As Integer, description As String)
    On Error Resume Next
    Err.Raise number, , description
End Sub
 
Public Sub x()
On Error GoTo ErrHandler
    y

    Exit Sub

ErrHandler:
    Err.Raise 5
End Sub

Public Sub y()
    Err.Raise vbObjectError, , "foo"
End Sub
 
@this missing Set keyword?
 
you end up with the 5 - Foo
@MathieuGuindon assumed a UDT
 
The inspection that would catch the most bone-headed error handlers would be an empty label inspection.
 
3:20 PM
@Comintern we need the code-path analysis to have a string flag for the ErrorHandler of a given instruction :)
 
^
Public Sub Foo()
    On Error GoTo CargoCultMustHaveHandler
    '...
CargoCultMustHaveHandler:
End Sub
 
^ I positively despise those half-assed "error handling" like that
 
On Error Bail Out
On Error Corrupt State
 
and then any given instruction needs a bool CanRunInErrorState flag too
 
^^ that should be a ThunderCodeâ„¢ inspection
@MathieuGuindon let's not forget the fun-loving flag DebugMode!
 
3:23 PM
if (hasOnErrorStmt && !instructions.Any(i => i.CanRunInErrorState)) { /* cargo-cult handler */ }
 
I've seen this too:
Public Sub Foo()
    On Error GoTo CargoCultMustHaveHandler
    '...
CargoCultMustHaveHandler:
    Resume Next
End Sub
 
uh, isn't that going to faceplant?
 
facepalm nuclear facepalm
 
or is the Exit Sub implied?
 
@this it's On Error Resume Next with lipstick
 
3:25 PM
^
 
@this has to be, otherwise that's error 20 on Resume Next
 
Oh I know, I'm saying that without Exit Sub in the body, it'll fall through.
^
 
@MathieuGuindon Nope.
Public Sub Foo()
    Dim x As Long
    On Error GoTo CargoCultMustHaveHandler
    x = 1 / 0
CargoCultMustHaveHandler:
    Resume Next
End Sub
 
eeew
 
3:26 PM
The error state never gets cleared.
 
that's surprising as hell, too
 
So Resume Next "resumes" to itself.
 
yeah but 3 times?
 
and doesn't throw error 20??
 
3:26 PM
IKR?
 
now gotcha
11, then 0, then 20, then bye
 
Public Sub Foo()
    Dim x As Long
    On Error GoTo CargoCultMustHaveHandler
    x = 1 / 0
CargoCultMustHaveHandler:
    Debug.Print "Resuming"
    Resume Next
End Sub
Output:
 
Resuming
Resuming
Resuming
 
should add the err.number
it'll print out the 11,0,20
 
3:28 PM
Resuming 11
Resuming 0
Resuming 20
 
the confusing part is that the 1/0 is just right before the Resume Next
 
so it eats error 20... wow
 
so the first time it's actually a jump. then the 2nd time it's resuming, then 3rd is another jump
IKR?
 
never knew that was even possible
 
that's just awful awful
 
3:29 PM
It catches error 20.
 
and I bet you that the fools who wrote that travesty walks off serenely thinking that his error handler has done the job
 
Then resumes to after the Resume.
 
it leaves the sub, right?!?
 
@Comintern normally that error can't be trapped though
lol
Sorry, I missed the left part "Module 1". Now it works ! — lalebarde 12 mins ago
 
Yeah, I'm not sure exactly how that "works".
Or doesn't work as the case may be.
 
3:31 PM
"I missed the 5 times someone mentioned "module, not worksheet" too"
 
The left part?
 
@Comintern Answer: VBA isn't a language. It's a bug.
 
@Comintern of the screenshot!
"wth are they all babbling about, my worksheet module is a module"
 
A: Sorry, the code can be fixed. Coder not so much.
 
You have been searching wrong. Search this "freelance developer for VBA". — cyboashu 6 mins ago
 
3:33 PM
lol
OK wait. It's not resuming after the resume. Otherwise the Debug.Print wouldn't output 3 times.
 
And now we not only get incomplete and duplicate answers on a question, we get welcoming answers.
0
A: Excel VBA: Create new workbooks from template based on sheet with data

DarXydeHi and welcome to StackOverflow. Unfortunately you will have to give it a go yourself, and ask for help where you get stuck. I suggest to start with something more simple (though your plan is not very complicated), have a play around on how VBA works, and then try do your plan. I think the foll...

 
already flagged
 
Flags Overflow?
 
"FO" is not welcoming?
 
totally read "FO" as "fk off", then second-guessed
 
3:39 PM
@MathieuGuindon Same.
 
That's where I was going with that. It's an unfortunate acronym.
 
Yeah, that's by-design not welcoming :D
 
hard to welcome users while conveying the fact that shopping-list of requirements aren't welcome though
 
A: $250/hr, estimating 10 hrs.
 
*without a possibility of escalation to "if you can't be helpful, don't comment"
 
4:02 PM
@MathieuGuindon That's why I prefer the old way. {close question} "Here are the rules. TTYL."
 
@IvenBach Sent you an email.
 
4:13 PM
got an email from MS Office 365, my "1-year MVP trial" is ending next week; I'll migrate the emails over to the GoDaddy email forwarding service (yeah, I let it renew :/). Let me know if you don't want me to.
(no way I'm paying for O365 Enterprise!)
 
hmm that doesn't sound right
you should check w/ your lead about that.
 
it shouldn't be used for production anyway
 
that's the case for MSDN, but AIUI, O365 is ok?
I actually haven't used my O365 benefit, though so I can be way off the base on this.
 
4:45 PM
just wondering - would you consider this a code smell?
For Each ctl In .Controls
 Select Case foo
   Case bar
     ctl.Enabled = True
     ctl.Locked = False
   Case foo
     ctl.Enabled = False
  End Select
Next
specifically, the ctl.Locked = True is missing.
 
Do they default to True? If anything I'd think that just warrants a comment.
 
in this particular case, they are Access controls so you can set the property at design time
a brand new control would default to true/false for enabled/locked respectively
 
Is Select Case foo the same "foo" as Case foo?
That smells if it is.
 
Oops, sorry
I wasn't considering the conditional branching as much as the effect any given branch had
 
ctl.Enabled = (foo = bar)
If foo = bar Then ctl.Locked = True
 
4:49 PM
Without knowing more about the code, repeated invocation of the code with different branch would lead to strange state for controls because the properties aren't consistently managed, right?
 
For Each ctl In .Controls
    ctl.Enabled = Foo = bar
    ctl.Locked = Foo <> bar
Next
 
#itdepends
 
From my POV, the code looks dangerous because we don't know what the intention is when we don't set all the properties explicitly in each branch, or at least have a comment that the property doesn't apply.
 
agreed
 
If the states are tied to foo and bar, then yes - I would personally always set both.
 
4:51 PM
^
 
can we inspect for that?
 
Or pass it to an IControlConfigurator or something.
 
^ would be better
I was going to say that this would be much more cleaner:
 Select Case foo
   Case bar
     result = True
   Case bad
      result = False
  End Select

  For Each ctl In .Controls
    ctl.Locked = Not Result
    ctl.Enabled = Result
  Next
but that's also more simplistic. It might be more complicated than just that.
 
If it's much more complicated than that, then I'd extract a function or interface.
 
  For Each ctl In .Controls
    controlStateManager.Apply(ctl, foo, bar)
  Next
 
4:53 PM
RD will help you with one of those...
 
Yep. Was thinking it'd be nice to get that inspected and thus suggested to convert the code
I think that kind of pattern is common. Lot of repeated steps in the branching.
 
I'd think there would be more false positives than hits.
 
yeah, possibly.
 
Stuff like this would trip it up hard:
 Select Case TypeName(ctrl)
   Case "bar"
     ctrl.Name = "Foo"
   Case "baz"
     ctrl.Caption = "Foo"
  End Select
 
4:57 PM
but in that case, we are setting different properties of same object
what if we said, if a property of an object is assigned in 2 or more branches, that's a smell?
 

« first day (1735 days earlier)      last day (1445 days later) »