Excel: VBA

I needed to do stuff with strings in Excel, specifically, to get the initials from a full name (first name, last name, optional middle name), to make that the basis of an (non-unique) identifier. I had not previously written VBA (sure, there are other ways), so this was new, and dare I say, quite amusing.

This is an example of the input (B2 down) and the output (C2 down):

First point is how did I get the Column B data? I grabbed it from the Adventureworks example database (look elsewhere in the blog for that), and then ran this SQL over it:

 ;with names as 
 (select p.BusinessEntityID, p.FirstName + ' ' + ISNULL(p.MiddleName,'') + ' ' + p.LastName as FullName 
 from [AdventureWorks2016].[Person].[Person] p)
 select * from names
 order by BusinessEntityID

, which returns you this, for a very useful 3,000 rows or so of test/dummy data (btw):

Here is the code for the function that gets the initials from the full name:

But when I first opened  Excel for Office 365, I could see no way of editing vba. I happened to know I needed the Developer tab to be visible, and it is not visible, by default. The steps to enable the Developer tab are:




Once you have ticked the Developer check box, the Developer tab will appear, and you can then start editing (you will be prompted to enable macros, which you must accept):


Gist for the SQL, Gist for the VBA.

So going back to the first screenshot, the way you turn [Ken J Sánchez] into [KJS] is type [GetInitials(B2)] into the cell C2, then copy down. Now obviously I could have done this in a 1-liner, but that would have been ugly and hard to test. A bonus of this way is that you get a step-in debugger.

A further trivial example. Gist.




Powershell: enumerating Excel things

Use approved verbs.

cd C:\sandbox\PowerShell
function Unlock-Reference ($reference) {
 [System.__ComObject] $reference) -gt 0)
function Show-WorkSheet 
 if (! (Test-Path -Path $spreadSheet)) {
 Write-Host "Could not find $spreadSheet. Exiting"
 $excel = New-Object -ComObject excel.application
 $excel.visible = $false
 $workbook = $excel.WorkBooks.Open($spreadSheet)
ForEach ($workSheet in $workbook.Worksheets) {
$dummy = Unlock-Reference($workSheet) 
 $dummy = Unlock-Reference($workbook)
 $dummy = Unlock-Reference($excel)