« first day (1826 days earlier)      last day (677 days later) » 

12:00 AM
[rubberduck-vba/Rubberduck] 2 opened issues. 20 issue comments.
[Minesweeper] Games Played: 109, Bombs Used: 74, Moves Performed: 15135, New Users: 9
12:38 AM
Q: Search for used Range.NumberFormats in Excel workbook

IvenBachOn an overly abused workbook we've hit the limit and NumberFormats can no longer be added. If we do want to add more we need to consolidate current formats. My code requires a reference to Microsoft Scripting Runtime as it uses the Dictionary class. Is there anything I can do to further improve p...

1:06 AM
@puzzlepiece87 ^
6 hours later…
6:44 AM
@IvenBach Ah those times when dmg was not in millions.... :-)
4 hours later…
10:15 AM
Back from my work do in barcelona, two days in the office now :-(
2 hours later…
11:58 AM
12:32 PM
@SonGokussj4 I still play it occasionally. Nostalgia won’t let go.
thoughts anyone?
yesterday, by FreeMan
Following these instructions for setting up my ForEach Loop in SSIS. In Figure 3, he shows setting the Result Name to 0. Is there any particular reason for doing so? Is it "convention"? Will it break if I don't?
@IvenBach it's 0530 your time, whachu doing up and chatting???
@FreeMan I might be outdated but I never thought you can change the result name, which would be provided numerically, zero-based.
interesting. I don't recall the default, but it did provide some text there which he instructed to be overwritten with 0. Maybe, if there had been more than 1 parameter, the next one would have been 1.
Actually, adding in a test Execute SQL Statement step, the default text is NewResultName...
sounds like a change then. I think in 2008 (?), you couldn't rename the sets at all
ah, I think I have it. According to MS Docs it depends on the type of connection. Since I'm using OLE DB, it's 0, 1, 2...
I'm not married to OLE DB by any means - it's just the default which I didn't change. Is there any advantage to using that vs ADO, ADO.NET or ODBC? The ADO.NET connection type allows you to specify @<param name> as you're writing the query, which seems much easier than keeping track of positional numbers...
12:48 PM
yeah, I really don't like that they leak stuff like that
I've been told that while ADO.NET is much easier to program against, it's also slower and doesn't work in all situations.
In my past SSIS packages, I just used OLEDB as the default with one or two ADO.NET connections to work around some.... something.
I remember being extremely annoyed about that; being unable to just choose one or other; I had to use both. #FunTimes.
I can stick with defaults! One less thing to remember to change.
@this what's leaking?
Yes, I think OLEDB is good as a default choice.
@FreeMan among other things, the fact that you can't use named parameters unless it's ADO.NET. Why do I have to remember that ODBC or OLEDB only does positional while ADo.NET only does named? That's just a distracting plumbing detail that SSIS should have hidden away from me.
ah, got it.
1:30 PM
debugging my SSIS package and one step does not seem to be executing:
Everything in the left hand column has a green check mark, then after Expression Task - Set 'Result' To Success, it should go to the Execute SQL Task - Update DataLoadDriver to, well, update the DataLoadDriver table to mark this record as done. However, it doesn't seem to ever execute that step.
As you can see, the Data Flow Task - Load Temp Table fro CSV is now the active breakpoint step (red circle w/yellow arrow), and there's no green checkmark on Update DataLoadDriver.
What have I screwed up?
I can confirm (by querying the table) that DataLoadDriver has not been updated in anyway.
We'll get to the discussion of whether or not this is the best way to go about doing my task later. Unless that's the reason the step is being skipped.
1:58 PM
2:10 PM
posted on June 06, 2019 by CommitStrip

@Feeds at least he didn't print it out, handwrite on it, scan it then attach it 3 times.
i was just googling yesterday about iterating through Enums in VBA
and it looks like you cant really do it cept if its a linear value enum
so starts at lets say 2 and has values to whatever th end is, lets say 10, and every integer between
2:54 PM
you'd need reflection capabilities to iterate the members of an enum and their underlying values
just parsing the code isn't enough
you could store it in a table instead and have a data macro to update the VBA enum mirroring the table
good point. if you need enum names and values, you're looking at data, not code.
have a table, ditch the enum :)
i have a mirrored enum as to avoid stringifying
this isn't something that can be done in production, though because it obviously needs VBIDE API to rewrite the enum as the table is updated.
but for our "code" tables, that is a good solution to minimize code maintenance
3:12 PM
too bad i have access versions 2007-2016
@MathieuGuindon you know, i was thinking about this this morning
my goal with the enum was to make it easier to know what was available without needing to dig
2007? Shouldn't you at least be on 2010 at minimum
we are slowly updating to 2016
data macros was introduced in 2010, so once you're off 2007 for good, that shouldn't be a barrier.
yeah, by the way, do data macros work on linked tables?
3:27 PM
Q: change case the data inside a column

C.EdoI created a code that import the data regularly into a selected workbook. The code run well, it takes less than 20 seconds before the data imported successfully. how does it work ? I get a monthly data with excel format, I want the data to be imported into my workbook to be analysed further. ...

> Code Inspections exports these columns:
`Type Project Component Issue Line Column`
Could we add proc name?

Also, in the lines
`Warning Excel Excel Expression '[a5]' cannot be validated at compile-time. `
Should first 'Excel' be project name like all the rest?
> I may have submitted this before but can't find it in the issue search box:

Assignment to 'Cancel' implicitly assigns default member of class 'ReturnBoolean'.

Private Sub txtQuestion_Exit(ByVal Cancel As msforms.ReturnBoolean)
Cancel = True

End Sub
> Procedure name should really be there, yes.

Not sure what's up with "Excel Excel" here...


A quick look at [DeclarationInspectionResult.cs](https://github.com/rubberduck-vba/Rubberduck/blob/13ae992d83516d1d6e14196d53d72572381a1ab6/Rubberduck.CodeAnalysis/Inspections/Results/DeclarationInspectionResult.cs) points to "Excel Excel"
> RD 4
Constant 'ANSICHARACTERS' is not used.
but it is
Function UnAccent(ByVal inputString As String) As String
Dim Index As Long, Position As Long
Const ACCENTED_CHARS As String = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿøØŸœŒ"
Const ANSICHARACTERS As String = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyyoOYoO"
For Index = 1 To Len(inputString)
Position = InStr(ACCENTED_CHARS, Mid$(inputString, Index, 1))
> Is ACCENTED_CHARS issuing a result too?
> RD 2.4

Member 'Enabled' was not found on the compile-time interface for type 'Control'.
Dim ctl As MSForms.control
ctl.Enabled = gdbcCCSADB.Connected

Member 'Name' was not found on the compile-time interface for type 'TextBox'.
Dim ctl As control, oTxt As MSForms.TextBox, OptionName as string
OptionName = oTxt.Name

Member 'Row' was not found on the compile-time interface for type 'Range'.
Dim rgCategoryTable As Range
Do While rgCategoryTable.Row < Active
3:43 PM
@FreeMan Brain's been doing that to me for a while. He keeps making me think of exercise first thing every morning too :shudder:. With that kind of stuff he's asking for a brutal Q-tipping.
> RD 2.4
I don't know what that means.

Member 'XLApp' has a 'VB_VarHelpID' attribute with value(s) '-1', but no corresponding annotation.

`Private WithEvents XLApp As Excel.Application ' set in UserForm_Initialize()
> The MSForms stuff is a known issue, the MSForms interfaces are messing with us.

`Range.Row` is new though. Interestingly as far as I can tell is *does* resolve correctly, so... that would be a bug with the inspection itself.
> RD 2.4

Object variable 'ForeColor' is assigned without the 'Set' keyword.
`Me.lblFolderExistence.ForeColor = vbRed`
but it's Long

Object variable 'Visible' is assigned without the 'Set' keyword.
`.ChartArea.Format.Fill.Visible = False `
but it's Boolean
> That's RD letting you know that the VBE is hiding a member attribute for `XLApp` - if you export the module you'll find a `Attribute VB_VarHepID = 1` hidden instruction.

The inspection is working as intended: it means to warn about hidden attributes that don't have a corresponding in-code `@annotation`, and offers one quick-fix to automatically add the annotation comment, and another to remove the hidden attribute.
> Still there
> RD 2.4
Looks unproblematic to me. This is in an addin referring to an active workbook.

`sSurveyID = ActiveWorkbook.Worksheets("Surveys").Range("A2").Value
> The type of `ForeColor` is (correctly) resolving to `OLE_COLOR`:


...a type that I can't find in the Object Browser somehow, even with hidden members shown... if RD can't find it either, then it's assuming it's dealing with an object type, which usually isn't a wrong assumption to make. Tough call.

`Fill.Visible` is actually `MsoTriState` - an enum that resolves as such, and no
> Note: the inspection is currently not evaluating the data type of the RHS expression. This is mainly what's making it so frail - however it *should* handle the simple case where the RHS expression is a `SimpleNameExpression`:

> Note: the inspection is currently not evaluating the data type of the RHS expression. This is mainly what's making it so frail - however it *should* handle the simple case where the RHS expression is a `SimpleNameExpression`:

4:09 PM
@Duga wrong snippet, that was newExpr stuff
4:28 PM
> That's possibly a bug indeed - the inspection means to flag `ThisWorkbook.Worksheets("sheetname")` and `ThisWorkbook.Sheets("sheetname")`, where `"sheetname"` is a worksheet that exists at compile-time in `ThisWorkbook`.

The inspection found that there's a worksheet named `Surveys` in the host document -- or in any case, it did find a sheet named "Surveys" in one of the opened VBA projects...

Is the sheet in the add-in workbook? If not, I think the inspection is making a bad assumption,
@Duga submit a patch then?
> I'm not quite following: could you spell out what you consider the issue for contributors like me who don't write VBA every day?
> That is working exactly as intended... The implicit code is Cancel.Value = True; Value being the default member of the ReturnBoolean class.
4:44 PM
@Vogel612 IKR?
okay so it's not only me that's getting cranky at that particular comment.
yea sure, and I may be somewhat more "baseline irritable" because my back has been an annoyance the last few days and the performance of Code Inspections is bugging me a lot
5:12 PM
could be a new tag for easy single line fixes.
The duck-themed stuff just comes to me.
> In fact, in other cases such one-line declarations affect other parsing tools I use.
if your tool has a problem with Option Explicit, ditch the tool, not Option Explicit
Bwahahahah wut?
If Option Explicit is giving you problems then your problems are worse than you realize.
5:18 PM
Just breathe Mug.
Notes that some people just don't like using a template :/
5:49 PM
> > AFAIK only if the code is edited, Virgin sheet modules stay empty.

That is not true. The IDE setting adds `Option Explicit` to any & every new module, including `ThisWorkbook` and `Sheet1` in a brand new workbook / VBA project. The only thing it doesn't do, is add `Option Explicit` to *existing* modules.

> In fact, in other cases such one-line declarations affect other parsing tools I use.

If `Option Explicit` is causing problems with a tool, that tool has a bug. Vanilla-VBE adds `O
6:05 PM
i effing hate image fields
6:31 PM
wasted most of my day...
^ Was about to comment on meetings wasting your time.
thoughts on this:
5 hours ago, by FreeMan
user image
May 31 at 12:10, by Vogel612
ahh meetings... the most accepted way of wasting humungous amounts of time and productivity
what I don't get is why people keep doing meetings anyway if they already know it's a waste of time?
Higher pay grades consider them useful?
One major perk where I'm at is there's very few meetings. Even fewer I have to attend.
The expectation is "Get your work done" and trust you to do it.
There is value in getting all the "stakeholders" together to hash it all out. Unfortunately, I've been in meetings (at a previous employer, not current) where 30 people, many very high ranking (i.e. $$$$/hr), were sitting around for 4-5 hours (yes, they used to schedule 4-5 hour meetings) waiting for ONE person to make ONE decision. He was a doctor and they walked on water - nobody dared push him or challenge him...
...or just say "I've got work to do, let me know what you decide" and walk out.
glad I don't work there anymore!
@this any tips on the SSIS issue? I had a brain waive and tried changing the arrows leading to that last task to completion instead of success, but that didn't make any difference
6:43 PM
sorry not today
found it!
well, I think. Haven't tested yet
Yup, that did it!
7:06 PM
lol. it was set to and?
@FreeMan :+1:
    If Not IsEmpty(Cell.Offset(2, 0)) Then
        Sheets(Array("L12", "L13-24", "L25-36" _
        , "L12 (2)", "L13-24 (2)", "L25-36 (2)" _
        , "L12 (3)", "L13-24 (3)", "L25-36 (3)")).Select
    If Not IsEmpty(Cell.Offset(2, 0)) Then
        Sheets(Array("L12", "L13-24", "L25-36" _
                                     , "L12 (2)", "L13-24 (2)", "L25-36 (2)" _
                                                               , "L12 (3)", "L13-24 (3)", "L25-36 (3)")).Select
"Align continuations" needs to be rebranded "mess up continuations". #FixedIt
@KySoto yup. that's the default. Never even though of that being a possibility. This is my first SSIS package, after all...
@MathieuGuindon I read that as "post-incident". Guess both are appropriate...
7:30 PM
May 29 at 18:57, by this
Basically, create a SP that kicks off the SSIS package, then call the SP from the Access code. Loop until some known condition indicates completion. right?
is there any config necessary in SSIS or on the server to allow a SP to kick off an SSIS package?
oh, guess I have to publish the package, at the least
yeah, pretty much. You need permissions to do this. Best done with either EXECUTE AS OWNER or signing procedure to avoid giving direct access to the msdb database
(or to SSIS DB catalog)
if you can, use SSIS catalog. Much better than old way of msdb + jobs
well, I'm sysadmin at the moment, so I can do anything bwahahahahahaha including totally FUBAR myself.
of course, I'm also assuming that my package will run once I don't have sysadmin rights... (it should, I'm not doing anything fancy)
if the user can invoke the sprocs, they won't have the right....
hence the need to have OWNER execution or signing to grant those server-scoped permissions without actually giving them those permissions directly.
this syntax: exec as owner msdb.dbo.sp_start_job is obviously not correct as SSMS gives me a red squiggly under msdb. by Owner you mean the actual owner of the sproc, not the literal string "owner", eh?
Note that OWNER may not work if the ownership chaining isn't right. In which case, you need the signature.
@FreeMan most of my SSIS stuff has been much much simpler than what you had going on there, so i dont blame you for not knowing.
@FreeMan since you are sysadmin you can always check yo permissions and ensure that you are good that way
@this this means that when it executes it executes with the privs of the one who made it, not the one who is running it?
@this Wall of Doom Text
cool. Will give that wall of doom at least a cursory read.
I say that if you don't read it, you are doomed to wander around aimlessly in the byzantine labyrinth that is SQL Server permission system.
8:00 PM
I do have access to our local DBA, mebbe he'll be willing/able to help out with the correct rights for this, especially if it means he can revoke my sysadmin.
is there anything special I need to do in terms of rights when it comes to publishing the SSIS package?
somewhere in my list of tabs, I've got a page on publishing it. I got started and it created the SSISDB db on our server, so I've got that going for me.
sorry can't recall ATM; need to research
no worries! You always seem to know this stuff off the top of your head, and I tend to vampire off that. I can find that tab and read through it myself.
8:18 PM
> That would be fine, i would think, since doing something like that as part of the Type declaration would need special attention by the coder anyhow. But all valid declarations would still be a nice convenience.
> I agree, however I'd rather avoid generating code we know is invalid in the first place :)
WTF, it takes 3 minutes to delete in a transaction and now it's taking 10 mins and still running to delete without a transaction and isn't blocked?
missing index?
cascading FKs?
no, if it ran successfully in a transaction for 3 minutes, it should have took 1/2 the time without transaction for same command.
I think the non-blocking state is a lie
8:43 PM
im sad
i have a stupid table that was set up stupidly by the ERP vender
it uses an image field to store text
oh nice, a ByteBag field!
so to look at the data outside of the software, you have to convert, but you cant index the view (and the view returns a million or so records easy)
@IvenBach Busy day at work so I can't type up an answer, but two things:
so i figured... OH i can just go and use a trigger to auto convert
except... i cant
Msg 311, Level 16, State 1, Procedure CUST_LINE_BINARY_insert, Line 14
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
#1: trivial:
formatUsedOnWorksheet.Name = "UsedNumberFormats"
formatUsedOnWorksheet.Range("A1").Value2 = "NumberFormat"
formatUsedOnWorksheet.Range("B1").Value2 = "Worksheet"
formatUsedOnWorksheet.Range("C1").Value2 = "Cells"
8:45 PM
You can With those @IvenBach
doesn't get any stupider than the ERP that stored doors & window schematicss in XML fields. 2 years of sales history and the DB was ~450GB. can't imagine what it looks like now, 6 years later. the reports routinely took >45 minutes to run (had to query the XML)
#2: substantial - you read/write to the spreadsheet a lot. Anything you could do to use arrays would help, but I don't blame you for read/writing because I don't feel confident manipulating formats within arrays.
'Originally Application.WorksheetFunction.Transpose() was used.
'Under certain conditions it would not error out with no resolution
@IvenBach Transpose has an old Excel limit - 65336 rows or whatever that byte number is.
That's why it was failing silently.
@KySoto tell them to update. image is long ago deprecated
they probably have
its not cost effective to get back on maint
they want us to pay maint fees covering the time that we were off maint
or totally re buy it
which is effing expensive
its possibly more expensive for the bs repaying maint we didnt and couldnt use than to rebuy
but to rebuy it would be several hundred grand
i dont know for sure, but including the need for updated SQL server software (which im pushing to get anyway) its somewhere in the 500k area
and of course we are cash strapped because of the massive growth we are experiencing
~35% growth over the last year and a half
i found a workaround
idk how fast it will be, but its better than nothing
8:55 PM
ERP is autocorrect pulling a prank. the real acronym is SCAM.
but yeah the whole reason im doing what im doing is so that i can have a version of the table that has the image field already converted to nvarchar
you know, that does sound like a good use-case for a trigger
 alter table dbo.Foo add SaneVersionOfStupidImageField nvarchar(max) null
and populate the column on insert/update
actually, you don't want to do that
ERP vendors tend to dislike clients messing with their db
9:30 PM
i created a second table and am using that
i ended up making the following trigger


	-- =============================================
	-- Author:		<Author,,Name>
	-- Create date: <Create Date,,>
	-- Description:	<Description,,>
	-- =============================================
	   ON  [dbo].[CUST_LINE_BINARY]
	   after INSERT
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		insert into dbo.cust_line_bits(Cust_order_id,Cust_order_Line_no,bits) SELECT CUST_LINE_BINARY.CUST_ORDER_ID, CUST_LINE
that allowed me to get around the inserted table's limitation
@MathieuGuindon for us, if we ever upgrade to a new version of our ERP altering the table schema.... would be bad.
we can get away with adding a new table to our replica
since the data never actually was in our ERP database
@puzzlepiece87 When you do find make the time a full review will help.
RE: reading from the workbook. I'm not aware of another way to get the formats using an array. That's what I was hoping would come from a full review by a knowledge endowed Mug we all know.
9:58 PM
@KySoto I made a SageExt db at work where I store everything I need for Sage - data, views, SPs, etc.
ive only ever heard negative things about sage
it's... tedious.
im sorry
working with my ERP's tables isnt bad
the only ones that suck are the binary tables
the security is...
based off of what you can do in sqlbase
im guessing
it makes me sad
10:11 PM
@puzzlepiece87 Didn't intend to appear unappreciative in my last reply, if I did. Working through some "interesting" formulas. Thanks for looking into it.
^ :derp:
@MathieuGuindon The star of shame, LOL.
That is some mighty trigger inducing advertising right there.
10:55 PM
> We're intrigued! Care to elaborate, Pat?
Proper response would have been "take that, @msaccess!"
or something
like, "uhm, nope"
they're always very p.c. and avoid telling anyone "yeah... Excel shouldn't be used for this though"
Cue "how do I ensure a unique ID doesn't get reused" on SO
11:10 PM
The latest in the NoSQL trend: Excel!
11:25 PM
Excel does have a schema, though...
IDK - Excel falls into the semistructured data bin w/ XML, JSON, and various NoSQL variants.
why only semi?
semi means there is no schema defined up front
e.g. those can have extra columns or not, where in a fully structured data, that would be an error
ah. the type-safety aspect
Pat Leahy
‏ @patleahy
3s3 seconds ago

Sheets are tables. A column with unique values can be a primary key. You can have other key columns. Create join tables using VLOOKUP to join on values in one table with a keys in another. CONCAT allows you to create composite keys.
11:58 PM
I once thought along that way... until I decided to find out exactly what using an actual database would mean. ..yeah that was the end of it.

« first day (1826 days earlier)      last day (677 days later) »