Friday, 3 July 2015

A Fishy Tale of Report Support

Not, strictly speaking, an Excel post at all: but I thought I'd share this with you anyway.

From time to time, I have to cover the support work on a large reporting system which serves Excel files out through a web portal. We're running something between five hundred and a thousand distinct reports.

Here's an edited - heavily redacted! - transcript of a typical support call, with the details changed to protect commercial confidentiality, and the fundamental concepts entirely unaltered:

  Fish:    "Hi, Aquarium Support Desk?"   Support: "Hello, this is the Aquarium Support Desk, can I help you?"   Fish:    "It's the water"   Support: "There's something wrong with your water?"   Fish:    "Yes, there's something wrong with the water: can you help?"
  Support: * Thinks * I could ask what's wrong with the water, again...   Support: ...But it's going to be quicker to get the testing kit and go there myself
  Support: "Which tank are you in?"   Fish:    "Yes, I have a problem with the water"   Support: "I need to run some tests, can you tell me which tank has a problem with the water"   Fish:    "Thanks, how long will it take you to fix this?"   Support: "I don't know which one of the 450 tanks of water in this building has a problem, and I need to know which tank to test"   Fish:    "It's a problem with the water, can you tell me how long it'll be?"   Support: "Are there any identifying marks on your tank?"

There's no 'punchline'... So lets stop right there, and nail the mistake before the repetition gets tedious.

The Fish has no concept for 'Tank' and the support technician doesn't realise that nothing AT ALL is communicated in any question or statement centred on 'tank' and 'location'.

Maybe if Support used the word 'Aquarium', they'd get a response; and maybe not - some people have a distinct vocabulary for their work, and the concepts embedded in their work can only be communicated in that exact language (Oracle administrators are the most extreme example) - but it's entirely possible that the fish has NO vocabulary for, and no concept of, a distinct body of water with a boundary container.

That's not to say the fish is stupid - I'd like to see how long *you* would last on a tropical reef, and your attempts at breathing when immersed in water are, quite frankly, embarrassing - and anyway, the problem was never going to be solved by being 'smarter' than the fish.

The problem is going to be solved by being smarter than a support technician who doesn't see that that a gap in communication isn't going to be fixed by throwing more questions down the the same hole: asking for more details about the tank won't work, no matter how many questions you ask, if 'Tank' is a concept you can't communicate at all.

So here's the 'fix': ask a question centred on a concept the user will understand, with answers that will 'leak' location information:

  Support: * Mutters, almost as if he or she doesn't want to be heard *   Support: "It's the 4%@#ing octopus again"   Support: * Asks, clearly and politely: *   Support: "Is there an octopus in the tank adjacent to yours?"   Fish:    "There's a Pacific blue-ringed female so sunward..."   Fish:    "And a tuberculate pelagic octopus next to the source of vibration I complained about last week"
  Support: "Tank 224. I'll be right over with the testing kit"

Something else for you to ponder: the 'user experience' here is that Support asked no end of irrelevant questions that elucidated nothing except their own cluelessness, and they took their own sweet time to get to work on the problem.

And the first thing the user will see is support faffing around with a testing kit, when they've already been told, repeatedly, that there's something wrong with the water.

Support isn't a technical skill, it's about communication and manipulation. And if you are wondering how this fishy tale is relevant: more than half the time expended by support technicians in speaking to the users consists of failed attempts to identify the specific report that the user needed fixing..

So anyone who builds reports - or Excel applications - and releases them for widespread use needs to be labelling-up *every* sheet with the app or report name, and filling in a 'Settings' or 'about this report' or 'documentation' sheet that the user can be guided to by first-line support. Or by you.

And the moral is:

The most important piece of information that your application, worksheet, or report can communicate to a support technician is an unambiguous identifying name; and, better still, a location.

Meanwhile, I will buy you beer if you can bring a toxic blue-ringed octopus into today's technical explanations to the users.

Sunday, 25 January 2015

Trusted Locations: a source of misleading error messages

When's the last time you ran some VBA that opened a spreadsheet over the network? Or even on your own local temp folder?

If you're working in Office 2010 or 2013, you might just have seen this error message:

Office has detected a problem with this file. To help protect your computer this file cannot be opened.

...But you open the file yourself, manually, and it's fine. Readable, no error messages on open, maybe contains VBA macros and COM controls and you get a warning message, but not corrupted or obviously malicious.

You *might* see a message about content disabled, or the 'Trust Centre'.

What's actually happened is that the 'Trust Centre' ('Trust Center' in American English) needs a manual intervention to add the folder containing your file, and to label it as a safe location.

Microsoft have published instructions on doing this here: Add, remove, or change a trusted location

That's great if it's just you, but unhelpful if you write applications that open Excel files. But, before I show you the VBA to automate away that annoyance, here's a short code snippet for opening Excel files safely in a separate 'sanitised' instance of Excel.exe:

Application.ShowStartupDialog = False With New Excel.Application     On Error Resume Next     .ShowStartupDialog = False     .Visible = False     .EnableCancelKey = xlDisabled     .UserControl = False     .Interactive = False     .EnableEvents = False     'If .Calculation <> xlCalculationManual Then     '    .Calculation = xlCalculationManual     'End If     '.CalculateBeforeSave = False     .DisplayAlerts = False     .AutomationSecurity = msoAutomationSecurityForceDisable          On Error Resume Next     For i = 1 To .AddIns.Count         If .AddIns(i).IsOpen Then             .AddIns(i).Installed = False         End If     Next i          For i = 1 To .COMAddIns.Count         .COMAddIns(i).Connect = False         If Not .COMAddIns(i).Object Is Nothing Then             .COMAddIns(i).Object.Close             .COMAddIns(i).Object.Quit         End If     Next i                On Error GoTo ErrAppExit          .Workbooks.Open FileName = strFile, _                      UpdateLinks:=False,  _                      ReadOnly:=True,  _                      Password:=vbNullString,  _                      Notify:=False,  _                      AddToMRU:=False        ' **** Your code to work on the file goes here ****     ' **** Set all objects to Nothing before exiting ****        For i = .Workbooks.Count To 1 Step -1          .Workbooks(i).Close      Next i ErrAppExit:     On Error Resume Next     If Err.Number > 0 Then         StatusMessage = "#ERROR " & Err.Number & ": " & Err.Description & sError     End If          .Quit      End With  'New Excel.Application 

The Trust Centre is annoying, but you might want to take *some* precautions when opening files from a bad neighborhood. Especially if others will use your VBA code to open files from places you never heard of.

Whatever. Here's the code to set a trusted location which I posted in StackOverflow a couple of days ago:

TrustThisFolder(): Excel VBA Code to Add a Folder to the 'Trusted Folders' Registry List.

Private Sub TrustThisFolder(Optional FolderPath As String, _                             Optional TrustSubfolders As Boolean = True, _                             Optional TrustNetworkFolders As Boolean = False, _                             Optional sDescription As String) ' Add a folder to the 'Trusted Locations' list so that your project's VBA can ' open Excel files without raising errors like "Office has detected a problem ' with this file. To help protect your computer this file cannot be opened." ' Ths function has been implemented to fail silently on error: if you suspect ' that users don't have permission to assign 'Trusted Location' status in all ' locations, reformulate this as a function returning True or False ' Nigel Heffernan January 2015 http:\\ ' ' Based on code published by Daniel Pineault in on June 23, 2010: '\2010\06\23\vbscript-createset-trusted-location-using-vbscript\ ' **** **** **** ****  THIS CODE IS IN THE PUBLIC DOMAIN  **** **** **** **** ' UNIT TESTING: ' ' 1:    Reinstate the commented-out line 'Debug.Print sSubKey & vbTab & sPath ' 2:    Open the Immediate Window and run this command: '           TrustThisFolder "Z:\", True, True, "The user's home directory" ' 3:    If  "Z:\"  is already in the list, choose another folder ' 4:    Repeat step 2 or 3: the folder should be listed in the debug output ' 5:    If it isn't listed, disable the error-handler and record any errors ' On Error GoTo ErrSub Dim sKeyPath    As String Dim oRegistry   As Object Dim sSubKey     As String Dim oSubKeys    ' type not specified. After it's populated, it can be iterated Dim oSubKey     ' type not specified. Dim bSubFolders         As Boolean Dim bNetworkLocation    As Boolean Dim iTrustNetwork       As Long Dim sPath   As String Dim sDate   As String Dim sDesc   As String Dim i       As Long Const HKEY_CURRENT_USER = &H80000001 bSubFolders = True bNetworkLocation = False If FolderPath = "" Then     FolderPath = FSO.GetSpecialFolder(2).Path     If sDescription = "" Then         sDescription = "The user's local temp folder"     End If End If If Right(FolderPath, 1) <> "\" Then     FolderPath = FolderPath & "\" End If sKeyPath = "" sKeyPath = sKeyPath & "SOFTWARE\Microsoft\Office\" sKeyPath = sKeyPath & Application.Version sKeyPath = sKeyPath & "\Excel\Security\Trusted Locations\"       Set oRegistry = GetObject("winmgmts:\\.\root\default:StdRegProv") '   Note: not the usual \root\cimv2  for WMI scripting: the StdRegProv isn't in that folder   oRegistry.EnumKey HKEY_CURRENT_USER, sKeyPath, oSubKeys For Each oSubKey In oSubKeys     sSubKey = CStr(oSubKey)     oRegistry.GetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Path", sPath          'Debug.Print sSubKey & vbTab & sPath              If sPath = FolderPath Then         Exit For     End If           Next oSubKey If sPath <> FolderPath Then     If IsNumeric(Replace(sSubKey, "Location", "")) Then         i = CLng(Replace(sSubKey, "Location", "")) + 1     Else         i = UBound(oSubKeys) + 1     End If          sSubKey = "Location" & CStr(i)          If TrustNetworkFolders Then         iTrustNetwork = 1         oRegistry.GetDWORDValue HKEY_CURRENT_USER, sKeyPath, "AllowNetworkLocations", iTrustNetwork         If iTrustNetwork = 0 Then             oRegistry.SetDWORDValue HKEY_CURRENT_USER, sKeyPath, "AllowNetworkLocations", 1         End If     End If          oRegistry.CreateKey HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey     oRegistry.SetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Path", FolderPath     oRegistry.SetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Description", sDescription     oRegistry.SetDWORDValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "AllowSubFolders", 1      End If ExitSub:     Set oRegistry = Nothing     Exit Sub ErrSub:          Resume ExitSub End Sub

You will note that I acknowledge the original author, Daniel Pineault, who posted this code on in 2010: VB Script to set a Trusted Location

This code has been widely-reposted without attribution: please don't do that, it's discourteous - if you, too, aspire to being recognised as an 'expert', this can only happen if you're working in a culture that acknowledges expertise; and you, and your courtesy to others, *are* that culture.

Sunday, 18 January 2015

Asterisk the Galling: Using The VBA InputBox() For Passwords

Using the VBA InputBox for passwords and hiding the user's keyboard input with asterisks.

This is another horrible hack, born from a requirement to stop storing Excel sheet and workbook passwords in the worksheets themselves, in the interests of security.

Experienced Excel developers, power users, IT security experts, and preserved rat brains floating in jars of formaldehyde might *just* be capable of reasoning-out one or two minor inconsistencies lurking in the logic of that statement.

However, I've still got to do it. And I can either create a VBA form and hope that the 'Password Chars' method exposed by some (but not all) textbox controls is reliable and secure, and can't be switched off by clever but misguided users; or just type the password into a standard VBA.Interaction InputBox() function.

Unfortunately, the InputBox function doesn't have a 'PasswordChars' option. So here's the simple and straightforward VBA code to do that, with the necessary API functions declared for both 64- and 32-bit environments:

Option Explicit #If VBA7 And Win64 Then    ' 64 bit Excel under 64-bit windows   ' Use LongLong and LongPtr     Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _                                     (ByVal hWnd1 As LongPtr, _                                      ByVal hWnd2 As LongPtr, _                                      ByVal lpsz1 As String, _                                      ByVal lpsz2 As String _                                      ) As LongPtr     Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _                                     (ByVal lpClassName As String, _                                      ByVal lpWindowName As String) As LongPtr     Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _                                     (ByVal hwnd As LongPtr, _                                      ByVal wMsg As Long, _                                      ByVal wParam As Long, _                                      ByRef lParam As Any _                                      ) As LongPtr     Private Declare PtrSafe Function SetTimer Lib "user32" _                                     (ByVal hwnd As LongPtr, _                                      ByVal nIDEvent As LongPtr, _                                      ByVal uElapse As Long, _                                      ByVal lpTimerFunc As LongPtr _                                      ) As Long      Public Declare PtrSafe Function KillTimer Lib "user32" _                                     (ByVal hwnd As LongPtr, _                                      ByVal nIDEvent As LongPtr _                                      ) As Long      #ElseIf VBA7 Then     ' 64 bit Excel in all environments  ' Use LongPtr only, LongLong is not available     Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _                                     (ByVal hWnd1 As LongPtr, _                                      ByVal hWnd2 As LongPtr, _                                      ByVal lpsz1 As String, _                                      ByVal lpsz2 As String _                                      ) As LongPtr     Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _                                     (ByVal lpClassName As String, _                                      ByVal lpWindowName As String) As LongPtr     Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _                                     (ByVal hwnd As LongPtr, _                                      ByVal wMsg As Long, _                                      ByVal wParam As Long, _                                      ByRef lParam As Any _                                      ) As LongPtr     Private Declare PtrSafe Function SetTimer Lib "user32" _                                     (ByVal hwnd As LongPtr, _                                      ByVal nIDEvent As Long, _                                      ByVal uElapse As Long, _                                      ByVal lpTimerFunc As LongPtr) As LongPtr     Private Declare PtrSafe Function KillTimer Lib "user32" _                                     (ByVal hwnd As LongPtr, _                                      ByVal nIDEvent As Long) As Long #Else    ' 32 bit Excel     Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _                             (ByVal hWnd1 As Long, _                              ByVal hWnd2 As Long, _                              ByVal lpsz1 As String, _                              ByVal lpsz2 As String _                              ) As Long     Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _                             (ByVal lpClassName As String, _                              ByVal lpWindowName As String) As Long     Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _                             (ByVal hwnd As Long, _                              ByVal wMsg As Long, _                              ByVal wParam As Long, _                              ByRef lParam As Any _                              ) As Long     Private Declare Function SetTimer Lib "user32" _                             (ByVal hwnd As Long, _                              ByVal nIDEvent As Long, _                              ByVal uElapse As Long, _                              ByVal lpTimerFunc As Long) As Long     Public Declare Function KillTimer Lib "user32" _                             (ByVal hwnd As Long, _                              ByVal nIDEvent As Long) As Long #End If Private Const PASSBOX_INPUT_CAPTION As String = "Password Required" Private Const EM_SETPASSWORDCHAR    As Long = &HCC Private Const NV_INPUTBOX           As Long = &H5000& Public Function InputBoxPassword(Prompt As String, _                                  Optional Default As String = vbNullString, _                                  Optional XPos, Optional YPos, _                                  Optional HelpFile, Optional HelpContext _                                  ) As String On Error Resume Next ' Replicates the functionality of a VBA InputBox function, with the user's ' typed input displayed as asterisks. The 'Title' parameter for the dialog ' caption is hardcoded as "Password Required" in this implementation. ' REQUIRED function: TimerProcInputBox ' REQUIRED API declarations: FindWindow, FindWindowEx, SetTimer, KillTimer ' Nigel Heffernan, January 2015, HTTP:// ' **** **** **** *** THIS CODE IS IN THE PUBLIC DOMAIN **** **** **** **** ' Based on code posted by user 'manish1239' in Xtreme Visual Basic Talk in ' October 2003 ' Coding notes: we send the 'Set PasswordChar' message to the textbox edit ' window in the VBA 'InputBox' dialog.  This isn't a straightforward task: ' InputBox is synchronous, a 'Modal Dialog' which leaves our application's ' VBA code in a waiting state at the exact moment we need to call the Send ' Message API function. So it runs by a delayed callback from an API Timer ' Warning: many of the 64-bit API declarations posted online are incorrect ' and *none* of them are correct for the pointer-safe Timer API Functions. On Error Resume Next SetTimer 0&, 0&, 10&, AddressOf TimerProcInputBox InputBoxPassword = InputBox(Prompt, _                             PASSBOX_INPUT_CAPTION, _                             Default, _                             XPos, YPos, _                             HelpFile, HelpContext) End Function #If VBA7 And Win64 Then     ' 64 bit Excel under 64-bit windows  ' Use LongLong and LongPtr                             ' Note that wMsg is always the WM_TIMER message, which fits in a Long     Public Sub TimerProcInputBox(ByVal hwnd As LongPtr, _                                  ByVal wMsg As Long, _                                  ByVal idEvent As LongPtr, _                                  ByVal dwTime As LongLong)     On Error Resume Next                               ' REQUIRED for Function InputBoxPassword     '          KillTimer hWndIbox, idEvent          Dim hWndIbox As LongPtr   ' Handle to VBA InputBox            hWndIbox = FindWindowEx(FindWindow("#32770", PASSBOX_INPUT_CAPTION), 0, "Edit", "")          If hWndIbox <> 0 Then         SendMessage hWndIbox, EM_SETPASSWORDCHAR, Asc("*"), 0&     End If              End Sub      #ElseIf VBA7 Then          ' 64 bit Excel in all environments   ' Use LongPtr only     Public Sub TimerProcInputBox(ByVal hwnd As LongPtr, _                                  ByVal wMsg As Long, _                                  ByVal idEvent As LongPtr, _                                  ByVal dwTime As Long)     On Error Resume Next                               ' REQUIRED for Function InputBoxPassword     '          Dim hWndIbox As LongPtr    ' Handle to VBA InputBox          KillTimer hwnd, idEvent          hWndIbox = FindWindowEx(FindWindow("#32770", PASSBOX_INPUT_CAPTION), 0, "Edit", "")                If hWndIbox <> 0 Then         SendMessage hWndIbox, EM_SETPASSWORDCHAR, Asc("*"), 0&     End If                       End Sub      #Else    ' 32 bit Excel     Public Sub TimerProcInputBox(ByVal hwnd As Long, _                                  ByVal wMsg As Long, _                                  ByVal idEvent As Long, _                                  ByVal dwTime As Long)     On Error Resume Next          ' REQUIRED for Function InputBoxPassword     '          Dim hWndIbox As Long    ' Handle to VBA InputBox          KillTimer hwnd, idEvent            hWndIbox = FindWindowEx(FindWindow("#32770", PASSBOX_INPUT_CAPTION), 0&, "Edit", "")          If hWndIbox <> 0 Then         SendMessage hWndIbox, EM_SETPASSWORDCHAR, Asc("*"), 0&     End If                       End Sub #End If

Share and enjoy.