^^ dropped+created an entire schema, foreign keys, SP's, and inserted a bunch of records in a number of tables.

@Mat'sMug Are you using 2012?

same on MySQL: I'd still be waiting.

2014

^^ LOL

Hmmmm.... bad answer must have been deleted. Random +1 rep.

I get that a lot on Workplace. Heh. Especially since I downvote/delete a lot ;)

@enderland FYI set @id = ... works just as well (and I prefer set to select ...y'know, semantics ;)

@Mat'sMug set also is a lot easier to use in a method than "select"

I'm pretty sure select @var = is deprecated ;)

oh good

They do different things don't they?

It... depends

both worked

@Phrancis lol. All things do, but how so?

TSQL AFAIK is the only RDBMS that supports setting a variable using select

And I believe it's still supported only for backward compatibility

TSQL is a language technically, the RDBMS is "SQL Server" ;)

Actually, the RDBMS is Sybase ;)

under 2000 layers of MS stuff, probably still

Exciting stuff here. blog.trello.com/introducing-unlimited-labels

YAY for more labels. =)

@Ducky > For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable. For more information, see SET @local_variable.

@RubberDuck what, they've introduced a "delete board" feature?

Ugh. I wish.

I followed that feature request for over a year

then I gave up

and deleted my account

They did add a "close" board feature which effectively hides it.

that's the problem. they think users are too dumb to actually delete a board when they mean it. any "test", "test123", "testA", "testB" board remains in limbo. Things from previous jobs that I don't care about anymore, whatever. I don't want them closed, I want them deleted.

Yeah. I know. It bothers me too. Makes me think what they really want is the data.

This .End question is kind of hard.

The documentation is rather ambigous on how it should behave

What is a "region"

Good question...

Oh.. looks like I still do have that Trello account ;)

Whew, just got done setting up about 8 new computers with a couple other guys

Cool! How's the job hunt goin?

Got my application submitted on Wednesday. Now just waiting to hear back for an interview. I was actually helping that same department just now, so they know I'm good around computers evidently

I meant to say, about select @var = I think the main reason it is deprecated is because it is ambiguous to use the same keyword to do 2 completely different things.

And set just makes more sense grammatically.

@enderland Your solution worked good. I'll try the other one too

Sweet!

Hmm I kind of like his a little better in that it outputs the actual name of the distribution list instead of Not a Contact! Actually a :DistListItem

Oh cool man!

Good luck!

@Phrancis you can change that to whatever you want ;) ha

Both approaches work good... decisions decisions

Do any of you understand the intricacies of Range.End? It seems to be extremely conditional to the original region.

@cheezsteak I'd assume it uses the .UsedRange function?

Never mucked wiht it except to find the last row.

.UsedRange is nothing but a bug.

@enderland no

It gets the last cell in the direction of the CurrentRegion of the range.

@RubberDuck hahahahh you mean inconsistently getting the range of an area which might be what you are looking for isn't buggy?

@enderland Right. It's a feature.

CurrentRegion of a range is the current region of cells(1,1)

... not a bug? :)

Bugs don't exist. They're a myth. There are only features.

What is the syntax to concatenate multiple fields here?

Debug.Print Contact.FullName

What is the context?

&

@Phrancis use &. you can use + but it'll cause you headaches later as it doesn't work perfectly

Debug.Print Contact.FullName & " " & Contact.SomethingElse

Gotcha

does concatenate work in VBA? might be more clear that way

or implement a C#-like StringFormat function ;)

or a StringBuilder class if you need performance

@enderland what are the catches of the + I never heard that you could use +

@cheezsteak because VBA is "smart" and autoconverts types for you. + becomes ambiguous when you're concatenating with numbers

Compiler maybe can see + as a mathematical operator and get confused?

what mat said

not for the compiler, for the programmer ;)

the last line throws an error since it's trying to add "test" and 1 together

Debug.Print "test" & 1 is defined? wth

I would expect that to throw an error.

Wouldn't it logically just print test1 if you use & operator?

yes but I dont expect & to operate on not strings.

`? 1 & 2 ' 12

Christ so & converts everything to string?

& is the string concatenation operator. + can act as one, but it's really the addition operator.

@cheezsteak yes

VBA is "smart" (do the double quotes with your fingers as you say it)

Why doesn't this work?

      Debug.Print Contact.FullName & "\t" & Contact.Email1Address

I tried without the quotes and got compile error

escape sequences are .net world

string literals require the double quotes

So, can you not concat a tabulation?

try & vbTab &

That worked!

vbNewLine makes a new line

there's a couple of language constants like that

vbCrLf makes a new line also, but IIRC vbNewLine is more portable

escape sequences are Unix world

right.

Does VBA allow to use line breaks if you have a long string concat to do and want to have a little vertical white space?

Of course not. Bleh

use the underscore _ (requires a whitespace before) for a line continuation

Ah ok

expect many ..." & _

Or I could just do this instead add another Debug.Print

For i = 1 To ContactsFolder.Items.Count
    If TypeOf ContactsFolder.Items(i) Is DistListItem Then
      Set distList = ContactsFolder.Items(i)
      Debug.Print distList.DLName
    ElseIf TypeOf ContactsFolder.Items(i) Is ContactItem Then
      Set Contact = ContactsFolder.Items(i)
      ' Change these to get a different information field
      Debug.Print Contact.FullName
      Debug.Print Contact.Email1Address
    Else
      Debug.Print "Item is something else"
    End If
Next i

no, line continuation is to continue an instruction across 2 or more code lines

concatenate with vbNewLine instead

Cool

line cont' is useful for, say, hard-coding SQL strings

Ah I can see that. Which of course is something that should probably not be done ;)

depends

SNAP is plagued with this shit, it's everywhere

lol

That's probably VB6 right?

the plague is really about concatenating the parameter values into the string

yeah

I've heard from a DB Dev friend (who no longer works here) that it is definitely vulnerable to SQL injection :D

Not surprising

They're not using my SqlCommand class ;)

I would just like to remind everyone that nothing VBA does when it comes to concatenation and the & and + operators is nearly as surprising as the crap javascript pulls.

Haha

Mini code review please? (see my comment line)

For i = 1 To ContactsFolder.Items.Count
    If Contact.CompanyName = "Example Systems" Then ' Those this If condition make my ass look fat?
        If TypeOf ContactsFolder.Items(i) Is DistListItem Then
            Set distList = ContactsFolder.Items(i)
            Debug.Print distList.DLName
        ElseIf TypeOf ContactsFolder.Items(i) Is ContactItem Then
            Set Contact = ContactsFolder.Items(i)
            ' Change these to get a different information field
            Debug.Print Contact.FullName

Reverse that condition to reduce nesting

Oh wait

That would mean to introduce a goto

Nah. The if looks fine.

The alternative is

Yay I introduced a new bug!

If Not Contact.CompanyName = "Foo" Then
    Exit For
End If

Which you can do, but.... idk. 6 of one half a bakers dozen of the other.

@Phrancis You mean a feature, right?

Not exit sub

Goto continue

Wih a continue label just above the next

I bet it's getting hung up because of that mailing list

@Mat'sMug What?! Dude.... come on man. msdn.microsoft.com/en-us/library/t2at9t47.aspx

NOooooooo. I apologize. You're right.

Object variable or With block variable not set

don't search SO for that error message

Use a For Each instead of For i

meeting. later!

Why is the first If statement in this block?

@cheezsteak he can't. The items are all differnt types. Thanks MS!

@cheezsteak Because the programmer is very bad, I'm wagering.

Contact is not initialized

@Phrancis You have to actually get the contact from the contactsfolder.

you are comparing before you set it in your

Something like Set Contact = ContactFolder(i)

Or ContactFolder.Items(i)

Shamelessly plugging my latest blog post. christopherjmcclellan.wordpress.com/2014/11/21/…

This works:

For i = 1 To ContactsFolder.Items.Count
    If TypeOf ContactsFolder.Items(i) Is DistListItem Then
        Set distList = ContactsFolder.Items(i)
        Debug.Print distList.DLName
    ElseIf TypeOf ContactsFolder.Items(i) Is ContactItem Then
        Set Contact = ContactsFolder.Items(i)
            If Contact.CompanyName = "Example Systems" Then
                ' Change these to get a different information field
                Debug.Print Contact.FullName
                Debug.Print Contact.Email1Address & vbNewLine

Is that more standard-ish looking?

Dim item As ContactsItem
For Each item In ContactsFolder.Items
    If TypeOf item Is DistListItem Then
        Debug.Print item.DLName
    ElseIf TypeOf item Is ContactItem Then
        If item.CompanyName = "Example Systems" Then
            Debug.Print item.FullName
            Debug.Print item.Email1Address & vbNewLine
        End If
    Else
      Debug.Print "Item is something else"
    End If
Next i

For Each whenever you can is my motto

Iterating over indexes is for when you have multiple sequences or are mutating the sequence

OK. I'll try that. What is the reasoning for using For Each whenever you can? Faster?

simpler IMO

also handles Arrays and collections the same way

If you want to iterate over a sequence by index you need to know it's bounds.

And it sounds better. "I am considering each item individually." We don't care about the rest of the sequence.

Ideally almost everything should be

For Each element In sequence
    FunctionCall(element)
Next Element

@Phrancis what type is ContactsFolder?

A... Folder?

Rem This subroutine queries one or more fields from Outlook contacts, including mailing lists
Sub ContactName()
    On Error GoTo ErrHandler

    Dim ContactsFolder As Folder
    Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
    MsgBox ("Contacts found: " & ContactsFolder.Items.Count)

    Dim Contact As ContactItem
    Dim distList As DistListItem

    For Each Item In ContactsFolder.Items
        If TypeOf Item Is DistListItem Then
            ' Debug.Print Item.DLName
        ElseIf TypeOf Item Is ContactItem Then

^^ Look better?

Yes. Much.

Don't know if you found this yet, but here is the outlook dev reference msdn.microsoft.com/en-us/library/office/…

Oh nice, I was just working off of a beginner tutorial on MSDN

@ducky IE8 doesn't like your blog :(

Really?

My Office suite is 2007 will most of that reference still apply?

Thanks Wordpress......

@Phrancis More or less. Shouldn't be very different.

I'm surprised nobody said anything about Rem ;-)

You can get to the 2010 reference

nope.

it redirects you to 2013....

Can you use a wildcard for strings in VBA? Analogous to SQL LIKE "Example Sys%"

There is the Like operator which is mild regex

# for numbers [A-z] ? and *

I don't think there is anything else though

msdn.microsoft.com/en-us/library/swf8kaxw.aspx

For folders and directories there is only "?" and "*" and only in the basename

If fileName Like "*.txt"

Yeah, I like the Like operator, but if performance if a concern, go for Instr.

Or Left$() and Right$().

What approach works for you guys to debug a Type Mismatch?

@Phrancis ?typename(myVaraible)

is there a good way to stop an access form from calculating any calculated controls, ala the calculationmode=xlManual from excel?

I don't get type mismatches ;p

@enderland esc?

Or ctrl+break perhaps

I'm looking for something programmatic

Oh

I have some requery's in my form events I init every form that trigger the entire form to recalculate and I can't easily intercept this :(

No idea

Me & Access aren't very good friends

I hate it

I wish I had just done C#

Hmmmmm I've never really used calculated controls.

If I made a custom Dict class, how would you do inline initialization?

@enderland Where in the script does ?typename() go? ErrHandler?

I have from a n by 2 array and from a jagged array.

@Phrancis The immediate window.

set a breakpoint before the typemismatch and type that into the immediate window and press enter.

View>>Immediate Window

Or Ctl+G

Ctrl+G FTW!

heh my profiler is totally helping me right now...

OK now I need to learn how to set a break point. Debugging is fun!

it is

I'm currently trying to optimize a really, really slow process which is constnatly requerying my form :(

this is annoying though

@Phrancis F9 or click the sidebar

Hello @Silverfish.

Thank you @RubberDuck

How are ya?

So to find where I should put it, should I go back a step from Stop and put the break point there?

Yeah, but it has to be an executable line of code.

@RubberDuck not sure I'll be able to get anything done this weekend though, I might be able to do some work offline on a laptop... if my wife lets me bring it without biting my head off :)

Wait, how do you step back?

@Phrancis right click and set next statement on the line you want to run next. Or just drag the yellow arrow

Hi @Silverfish! You write VBA? If you have some code you'd like reviewed, feel free to post on the main site - we need more VBA questions on CR!

For the first time in 7 years I now have a computer with MS Office on it... I used to write a lot of VB code for Excel, simulations and so on. Once I shake off the rust, is it going to feel like I've never been away, or has a lot changed in that time? It's far too vague a question to ask on the main site and thought I would ask in chat!

It hasn't changed much since, uh, what, 1999?

There are new data types for x64, like LongLong

I first started writing code in MS Office back in the 90s and I remember the crushing disappointment when all my WordBASIC stuff was rendered useless, I was hoping nothing so drastic has transpired :)

Nope, you should feel right at home. As if you left 10 years ago and came back to find everything right where it was :)

I am told that Excel worksheets can now be much larger, which could well come in useful

Yup.

@Silverfish Practically nothing has changed in VBA for a very long time.

Oh and the Ribbon is going to take some gettin' used to in Office in general, but the toolbars in the VBE remained untouched.

Office itself looks very alien, because of the ribbon

@Mat'sMug Dude, go enjoy the weekend. It won't kill you to step away from Rubberduck for a day or two.

aha yes

Besides, I'll be sneaking in coding sessions where I can.

I still hate that Ribbon....

I'll still try to bring my laptop

I <3 the Ribbon!

Hated it at first though

my main gripe with teh ribbon is it makes all shortcuts really long

To be fair, I don't hate the ribbon as much as I hate Metro Apps.

"shortcuts"

IT trainers tell me that it has been very useful getting less experienced users to try out different features (for some reason, they didn't dare messing around with anything you had to reach through a menu, but give them a pretty button to press and "I wonder what this does?" comes to the fore)

@Silverfish that's the rubberduck in question, in casr you wondered:github.com/retailcoder/Rubberduck

*case

OK so I found the source of my issue, I think. item variable is getting changed to Nothing then it breaks...

@RubberDuck is VBAUNIT dead in favor of RetailCoder's RubberDuck?

I probably won't mind the ribbon so much once I have worked out where on earth everything has gone

@cheezsteak VBEX is not dead, but I think VBAUnit is.

@Silverfish Well everyone in this chat room is keenly experienced with all things Office so ask away if you're unsure :)

I actually have commits I need to push to VBEX....

This is a very friendly chat! I mostly hang out on the stats stack exchange, but the chat there is very quiet

I'm out of stars, but thank you very much @Silverfish.

CR's main chat is busier: say hi to the folks in The 2nd Monitor anytime!

OK so I'm lost on break points

@RubberDuck how do you run out of stars?

@enderland Because I'm always here.

@RubberDuck I am deleting my VBAUNIT fork. I haven't done anything with it.

Okay. I think I'll try to remember to delete that repo tonight and update VBEX.

But I might drink beer instead.

^^ That.

(trying to run out of stars)

I'm reassured that the readoption process doesn't sound too traumatic... I have mostly been using OpenOffice/LibreOffice but I found getting any code written on there would take me twenty times as long. In contrast I'm sure 80% of the world's worst code arose from the MS macro recorder, but whoever made that was a genius.

@Mat'sMug You have to try?

I tried OpenOffice. Then bought a MS Office license.

I liked Open Office itself, but trying to code anything was a nightmare.

I tried getting into scripting LibreOffice to avoid the UI but the UI was in the way....

No intellisense. Nope, not gonna do it without intellisense.

But I'm out @all. May or may not bbl. It's nice to meet you @Silverfish. Come back anytime.

I think you are mostly expected to use an external editor for scripting Libre/Open office.

The Office aspect is pretty nice, though pivot tables and so on were pretty clunky. The coding is not fun at all.

I never even realized you could run out of stars

thanks @rubberduck, thanks for the great welcome

@enderland you get 20 per day, per chatroom :)

@Silverfish can't wait to review some VBA code! VBA is like a micro-community here on CR.. rest of the world doesn't quite get it :D

I've never hit that

I guess room owners/mods can probably do as many as they want

@enderland I wish

(I own this chatroom and The 2nd Monitor, and run out ...pretty often)

@Mat'sMug you can, pin it and then unpin ;)

I think?

Looks like it goes into the error right after it looks at this record:

Mary | Example Systems
[email protected]

Any clue?

one more F8

actually, F8 till after the Resume

dude, your screen has too much green, it's not normal

green == comments in my IDE

I probably ought to take up that CodeReview offer. I think my programming style has been permanently crippled by my learning to code in ZX BASIC on a Sinclair Spectrum. It's a great way of (a) learning exactly why "go to considered harmful", while (b) never really escaping the mindset.

mine has been "polluted" with C# and .net

so I go and implement things like ?StringFomat("Sales$: {0:C2}", salesAmount)

and IRepository and IUnitOfWork... over ADODB

I consider it a minor blessing I never had to take "first course in programming" in Java

How does code review etc work when you're looking at code which is generally heavily integrated into into a spreadsheet (or other) document?

usually the code and enough context suffice, but it doesn't hurt to provide a screenshot too

It definitely helps to have example data, but we've all been around long enough to find ways to clean up code without it.

especially if the spreadsheet layout is... funky

On the stats site, there is a similar issue making sure there is enough data to be reproducible. There's a useful command that R users are told to apply to their data object (or often preferably, a minimal but useful subset of it), called dput. It outputs the command you'd use to create that object - which they can then copy and paste into the top of their code sample so that the rest of us can reproduce it.

@Mat'sMug This is where it went after Resume

make Item an Object instead of a ContactItem

Boom, that fixed it thank you @Mat'sMug

Now my question is, Why?

VBA was trying to coerce the object into a ContactItem for every object in the collection, but the collection contains Object items, where most are ContactItem objects, but some aren't. That's when it blows up.

that's one of the most annoying things about VBA: it's not type safe, and tries to be "smart" and makes TONS of implicit type conversions for you. So you can tell it to concatenate an apple with an orange, and it will turn an orange into an apple for you.

Ahh. So if I expect the collection may have multiple types, setting it to Object is a way to make it generic then?

I try to write code so as to make type conversions as explicit as possible

or Variant if the collection can contain value types

Variant is absolutely anything, including COM stuff like IUnknown - a-ny-thing.

What are value types?

(short version)

Object is a variable that contains a reference to an object - say, a class instance; a value type is just a plain value.

like Integer

I see

So if I was iterating through a column in an Excel file that contained both integers, decimals and text, I would probably want to use a Variant then?

you'd actually be iterating Range objects ;)

That's exactly why Spolsky had the VBA team add Variant @Phrancis.

So, when would I expect to encounter Value types, for example?

Range.Value contains a value of type Variant that can be a String, or a Long, or a Date

Ahh I see

ideally you never want to use a Variant.. unless you really know what you're doing :)

It's hard to get out of SQL mentality lol.

see the type issues you're having? that's because of Variant

> You have fully used your vote allowance for today

out-of-stars!

Ammo well spent!

@Phrancis I need to come up with a way of populating a dwd.FiscalCalendar table... is there a magical way to do it?

_Id, _DateInserted, _DateUpdated, CalendarDate, CalendarDayOfWeek, CalendarDayOfMonth, CalendarDayOfYear, CalendarWeekOfMonth, CalendarWeekOfYear, CalendarMonthOfYear, CalendarYear, FiscalDayOfWeek, FiscalDayOfMonth, FiscalDayOfQuarter, FiscalDayOfYear, FiscalWeekOfMonth, FiscalWeekOfQuarter, FiscalWeekOfYear, FiscalMonthOfQuarter, FiscalMonthOfYear, FiscalQuarterOfYear, FiscalYear, Holiday

Like, make a "calendar" in a table?

You could make a loop that adds one day and populate each column using the appropriate date function.... Other than that, not sure

Yes. There's a magical way to do it.

Google a bit Mug. You'll find a prebuilt script.

I already shut down my work machine or I'd just send it to you.

experts-exchange.com/Database/MS-SQL-Server/…

@Phrancis here is a link for the same thing in Postgres: junctionbox.ca/2013/04/09/…

Oh wow, it's drastically easier in Postgres...

INSERT INTO calendar (day_id, year, month, day, quarter, day_of_week, day_of_year, week_of_year)
(SELECT ts,
  EXTRACT(YEAR FROM ts),
  EXTRACT(MONTH FROM ts),
  EXTRACT(DAY FROM ts),
  EXTRACT(QUARTER FROM ts),
  EXTRACT(DOW FROM ts),
  EXTRACT(DOY FROM ts),
  EXTRACT(WEEK FROM ts)
  FROM generate_series('2012-01-01'::timestamp, '2038-01-01', '1day'::interval) AS t(ts));

Gotta love generate_series

Yeah, except the fiscal fields

Ugh

You could normalize those into a separate table and just insert/update with a join

create table FiscalYearDayReference
(
	DayOfYear int,
	FiscalQuarter int,
	-- etc.
);

Then just do an update to your main table after you've inserted the basic dates, join on DayOfYear

Alternatively, if there is a specific offset that you are certain of, or that can be calculated programmatically, you could update it using DATEADD() type of functions me thinks

Thanks everyone for informative and friendly discussion - will hopefully have some code for inspection sometime soon :)

WEEKEND!

@Phrancis I know! I'll write some VBA code to populate it, and then I'll put it up on CR! :D

and I'm outta here, It's way TTGH

later folks!

@Mat'sMug That's the spirit!