Heroes Happen Here

So yesterday, my fair city got a visit from Microsoft during their "HEROES happen {here}" campaign. They are celebrating/marketing the recent release (and upcoming release, in SQL Server's case) of 3 new versions of their products: Visual Studio 2008, Windows Server 2008, and MS SQL Server 2008. Back in February I received an email from my MVP Lead at MS saying that the HHH tour was going around the country, and if we wanted to attend any of them to sign up and let him know so he could notify the peeps running HHH. After a quick look at the list of cities, I realized Rochester was actually included, so how could I not go? I expected to be unemployed by this point (fyi, as of now, I'm on through June 30th), but knew that I wanted to go no matter what. So I signed up, and a week later received an email confirming my registration.

Skip forward 3 months to 3 nights ago, when I realized what the date was and that I had forgotten all about it. I had no expectations of what it was going to entail, but if I could help them out in any way, it would be a good experience. I secured the day off with my supervisor, and woke up early to make it at 7am for the registration (per my confirmation).

So I get to the convention center, see a couple registration desks and also see an MS employee just inside so I asked her what I should do. I then registered/signed in, and went off to find Gail who was running it to see what was in store for me. I eventually found her (it was still early, and she looked to be getting some fine touches done), so I introduced myself and asked what I could do to help. She walks me over to a table in the "ask an expert" area with a small sign on it saying Microsoft MVP. She said she planned on having me sit there, and answering any questions people may have. I was taken a little back, as I was just an Excel MVP and have only some experience with vs2008, certainly not enough to be an expert with it. I explained that I would do whatever she thought was best, but that I didn't really feel that I would be the best person for that job. She said she had wondered about it as well, but seemed happy nonetheless to have a real mvp there. Sure, I could tell my own "hero" story of how I went from lowly office worker to the local excel guru, and eventually an MVP. But in dealing with people who either develop full time, or work in the development field of some sort, why would a VBA programmer be anything special? So I asked her what else I could do.

At first, I was brought over to relieve one of the other people running the event who was helping out with registration. After a quick explanation of how registration works, I was there for a few hours. Once that became a little less hectic I was moved to a couple other areas there, such as a Speak Your Mind video booth where attendees could record their thoughts for Microsoft to see, as well as a few tasks here and there to make things run smoothly. I had a great time, got to meet many local people in the field as well as some more people from Microsoft (based out of the Boston area). I even stepped into a session on using vs2008 with MS Office, but quickly realized the presenter wasn't showing me anything new. Most of the people in there seemed genuinely interested in what he was doing and he certainly had a good vibe with the crowd, seemed like a big hit (which did give me a little hope that my skills could be used once my current job is over).

Overall, I had a good time and I'm happy I attended. I found it interesting (at least it felt so to me) that most of the MS employees had never come across an MVP before and seemed genuinely interested in my story and the whole MVP program. They all seemed to know about it, and had a good impression of it despite not knowing much of it. It had crossed my mind (both before, as well as after, that wonderful Summit in April) that only some groups (product teams, PR, marketing) had any interest in the MVP program and that other employees either knew nothing about it or didn't think that highly of it. I would have to say that after yesterday, I was wrong. None of them seemed to ever deal with an MVP before but all showed immediate respect for me. Perhaps that contributed to my enjoyment of the day, but I think if I had just signed up to attend and didn't help out at all (which I did for about 8 hours) I would have still thoroughly enjoyed myself. If you get a chance to attend one of these, by all means do! Good learning experience (so I heard from some attendees of the sessions I did not see), good networking opportunity, and of course good swag (pens, lunch coolers, normal attendees even got free copies of the software being introduced!)

If you get the chance to see this, or any software roll-out conference, by all means do. You won't regret it!

Splitting a worksheet based on a column's value

One question I come across a lot is when someone has a lot of data on a single worksheet, and wants to split it out to multiple worksheets based on the value of a column. For example, if someone had the following birthday data on a worksheet, and wanted to have a sheet for each month. Assume this sample data:

SHEET NAME: Birthdays
Name    Month      Day
Emmy    August     27
Bill    July       3
Andy    November   24
Bob     July       27
Dana    June       24

And after running a macro:

SHEET NAME: August
Name    Month      Day
Emmy    August     27

SHEET NAME: July
Name    Month      Day
Bill    July       3
Bob     July       27

SHEET NAME: November
Name    Month      Day
Andy    November   24

SHEET NAME: June
Name    Month      Day
Dana    June       24

That is a simplistic example, but I see something like that frequently. The following code will do that for you, just set the column to use for the splitting-data and run.

Option Explicit
Sub SplitIntoMultipleSheetsBasedOnColumn()
 Dim TheColumn As Range, ValRG As Range
 Dim UniqVals() As Variant, AllVals() As Variant
 Dim FirstDataRow As Long, i As Long, Cnt As Long
 
 'Unique values in the column specified by TheColumn are given their own worksheet,
 ' and their entire row is copied to that worksheet
 Set TheColumn = Columns("G") 'must be a single column
 FirstDataRow = 2 'so that the header row(s) aren't turned into a sheet
 
 Set ValRG = Intersect(TheColumn, TheColumn.Worksheet.UsedRange, _
  TheColumn.Worksheet.Rows(FirstDataRow & ":" & TheColumn.Worksheet.Rows.Count))
 If ValRG Is Nothing Then
  MsgBox "No data found. Exiting."
  Exit Sub
 End If
 ReDim UniqVals(0)
 Cnt = 0
 AllVals = ValRG.Value
 For i = 1 To UBound(AllVals, 1)
  If Not InArray(UniqVals, AllVals(i, 1)) Then
   ReDim Preserve UniqVals(Cnt)
   UniqVals(Cnt) = AllVals(i, 1)
   Cnt = Cnt + 1
  End If
 Next 'i
 
 Application.ScreenUpdating = False
 For i = LBound(UniqVals) To UBound(UniqVals)
  Set ValRG = FoundRange(TheColumn, UniqVals(i))
  With Sheets.Add(After:=Sheets(Sheets.Count))
   On Error Resume Next
   .Name = ValidSheetName(UniqVals(i))
   On Error GoTo 0
   If FirstDataRow > 1 Then TheColumn.Worksheet.Range(TheColumn.Cells(1), _
    TheColumn.Cells(FirstDataRow - 1)).EntireRow.Copy .Range("A1")
   ValRG.EntireRow.Copy .Range("A" & FirstDataRow)
  End With
 Next 'i
 Application.ScreenUpdating = True
End Sub
Private Function ValidSheetName(ByVal DesiredSheetName As String) As String
 On Error Resume Next
 ValidSheetName = Left(Replace(Replace(Replace(Replace(Replace(Replace(Replace( _
  DesiredSheetName, ":", ""), "\", ""), "/", ""), "?", ""), "*", ""), "[", ""), _
  "]", ""), 31)
End Function
Public Function InArray(ByRef vArray(), ByVal vValue) As Boolean
 Dim i As Long
 For i = LBound(vArray) To UBound(vArray)
  If vArray(i) = vValue Then
   InArray = True
   Exit Function
  End If
 Next 'i
 InArray = False
End Function
Function FoundRange(ByVal vRG As Range, ByVal vVal) As Range
 Dim FND As Range, FND1 As Range
 Set FND = vRG.Find(vVal, LookIn:=xlValues, LookAt:=xlWhole)
 If Not FND Is Nothing Then
  Set FoundRange = FND
  Set FND1 = FND
  Set FND = vRG.FindNext(FND)
  Do Until FND.Address = FND1.Address
   Set FoundRange = Union(FoundRange, FND)
   Set FND = vRG.FindNext(FND)
  Loop
 End If
End Function

Note that you don't need to sort your sheet first, though it will make things run a bit smoother. I originally had the code go through an array containing the sheet data, but a lot of the format was being lost. This way takes slightly longer but everything stays to the new sheets. I use this for my job, and I'm sure other people can use it for theirs as well.

VBA will be back for Mac Office!

Ok, those of you that know me know that I'm not a huge Mac/Apple fan. I'm not really much against them, though I don't like the 'anti-pc' tone of their commercials versus a traditional 'pro-mac' commercial. Regardless, being a huge Excel and VBA fan, I felt bad for the many mac users that bought Office 2008 only to find out VBA was gone. Now I know the reasons behind it, so I'm not going to whinge about it, but it still sucked. That move alone would have prompted me to leave my mac in the dust (if I had one), despite it being a decision on Microsoft's part rather than Apple's.

So I was very happy to read today that VBA will be back in the next version of Mac Office! The press release is really about the release of SP1, but said MS will be bringing VBA back at a yet-unspecified future date. Score one for the good guys!

Go VBA!