Tuesday, September 25, 2018

Excel's Structured References in formulae

Structured References got added to classic Excel formula expressions in Excel 2007. That was eleven (11) years ago. Never too late to learn them. They're very worthwhile in this data science world.


Friday, September 14, 2018

Show Server for Microsoft Access linked ODBC tables

The classic Microsoft Access Linked Table Manager gets a lot of use but has not been markedly enhanced for ages. The width of this pop up dialog is not wide enough to see the full text for each Table Definition and worse, once you have more than one ODBC data source with the same table/view names but different server name, you can't see the server name.

How hard is it to just patch the dialog so that it actually becomes more useful? There are no legacy baggage issues that I can see - I don't think there is an API that calls it and even if there was, it would just display the dialog, it would not care what width the dialog is or whether there is a new column in the list of entries.

Here's a hack to display tables, database and servername - I use it for an SQL Server linked ODBC datasource. I'll make the code prettier when I have another moment


' Module    : basQuickUtils


#Const InProduction = True
Option Compare Database
Option Explicit

Sub ShowServer()
' Procedure : basQuickUtils.ShowServer
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef       

#If InProduction Then
On Error GoTo ShowServer_Error
#End If

        Set db = CurrentDb
        For Each tdf In db.TableDefs
                If tdf.Connect <> "" Then
                        Debug.Print tdf.Name; _
                                    Space(64 - Len(tdf.Name)); _
                                    GetFragment(tdf.Connect, "SERVER"), _
                                    GetFragment(tdf.Connect, "DATABASE")
                End If

    Exit Sub

    Select Case Err.Number
        Case Else
        MsgBox "Errorcode " & Err.Number & " (" & Err.Description & _
                            ") in procedure ShowServer of Module basQuickUtils", _
                            vbCritical, "Unexpected problem encountered"
    End Select
    Resume ShowServer_Exit
End Sub

Private Function GetServerName(strODBCconnect As String) As String
        Const Kprefix = "SERVER="    
        Dim strTemp As String
        Dim lngEndPos As Byte
        Dim lngStartPos As Byte

        lngStartPos = InStr(strODBCconnect, Kprefix) + 7
        lngEndPos = InStr(lngStartPos, strODBCconnect, ";")
        strTemp = Mid(strODBCconnect, lngStartPos, lngEndPos - lngStartPos)
        GetServerName = strTemp
End Function

Private Function GetFragment(strODBCconnect As String, _
                 Optional strFragment As String) As String
' Procedure : basQuickUtils.GetFragment
        Dim strTemp As String
        Dim lngEndPos As Byte\
        Dim lngStartPos As Byte

#If InProduction Then
On Error GoTo GetFragment_Error
#End If

        If strFragment <> "" Then
                        lngStartPos = InStr(strODBCconnect, strFragment & "=") + _
                                      Len(strFragment) + 1
                        lngEndPos = InStr(lngStartPos, strODBCconnect, ";")
                        strTemp = Mid(strODBCconnect, lngStartPos, _
                                      lngEndPos - lngStartPos)
                        strTemp = strODBCconnect

        End If
        GetFragment = strTemp
    Exit Function

    GetFragment = ""
    Resume GetFragment_Exit
End Function

Thursday, June 7, 2018

VBA to clean up an Excel Range for creating a Table

  1. Sub CleanForTableFormat()
  2. '---------------------------------------------------------------------------------------
  3. ' Procedure : basAnanda.CleanForTableFormat
  4. ' DateTime  : 7/06/2018 13:37
  5. ' Author    : Ananda
  6. ' Purpose   :
  7. ' Notes     :
  8. ' Inputs    :
  9. ' Outputs   :
  10. '---------------------------------------------------------------------------------------
  11. '
  12. '
  13. ' CleanForTableFormat Macro
  14. ' Prerequisites: Place Cursor On a Cell in the Nominated Range
  15. '
  16. #If InProduction Then
  17. On Error GoTo CleanForTableFormat_Error
  18. #End If
  20.     If MsgBox("Is your cell cursor in the preferred area?", vbYesNo + vbQuestion + vbQuestion, "Choose Yes to Proceed"= vbNo Then
  21.         Exit Sub
  22.     End If
  24.     With Selection.CurrentRegion
  25.         .RowHeight = 15
  26.         With .Interior
  27.             .Pattern = xlNone
  28.             .TintAndShade = 0
  29.             .PatternTintAndShade = 0
  30.         End With
  31.         .Borders(xlDiagonalDown).LineStyle = xlNone
  32.         .Borders(xlDiagonalUp).LineStyle = xlNone
  33.         .Borders(xlEdgeLeft).LineStyle = xlNone
  34.         .Borders(xlEdgeTop).LineStyle = xlNone
  35.         .Borders(xlEdgeBottom).LineStyle = xlNone
  36.         .Borders(xlEdgeRight).LineStyle = xlNone
  37.         .Borders(xlInsideVertical).LineStyle = xlNone
  38.         .Borders(xlInsideHorizontal).LineStyle = xlNone
  39.         With .Font
  40.             .Bold = False
  41.             .Color = RGB(000)
  42.         End With
  43.     End With
  45. CleanForTableFormat_Exit:
  46.   Exit Sub
  48. CleanForTableFormat_Error:
  49.   Select Case Err.Number
  50.     Case Else
  51.     MsgBox "Errorcode " & Err.Number & " (" & Err.Description & _
  52.               ") in procedure CleanForTableFormat of Module basAnanda", _
  53.               vbCritical, "Unexpected problem encountered"
  54.   End Select
  55.   Resume CleanForTableFormat_Exit
  58. End Sub

Pastebin source

Saturday, April 21, 2018

Macros for G Suite Sheets

Microsoft Excel has several things going for it.

  • Extensive list of functions
  • The famous VBA programming language built-in and an extensive list of methods, properties and objects exposed in the Excel Object Model
  • Extensive and long standing use in public (it's over 30 years old)
  • Burgeoning extensions into ETL, Data Mining and Data Analysis tools
  • Every year since inception, a strong, smart development team
  • Packaged as part of the ubiquitous Microsoft Office
In the 1980s there were so many spreadsheet products, but nearly all have fallen away. The only challengers left in my mind are G Suite Sheets (a cloud only solution) and Libre Office. Neither of which had macro recording.

This year, it changes. G Suite Sheets now has Macro Recording. I'm astonished - it must be really hard to do that in cloud based solution. Here's my first drive around the block.

The demo is simple. Have a range of cells on a sheet. Record the copying of that block to another, blank sheet. 

It doesn't seem as easy as Excel. But it's pretty good.

And here's the recorded and edited macro with annotations in a G Suite Docs document

Tuesday, February 27, 2018

Talking about Backup and Sync for Google Drive

I've been meaning to write notes on Google Drive and using Backup & Sync - that will be for another day, in the meantime +Lee Sapara has made the following instructional videos.


Choosing Which Folders to Sync

Handling Deletion of Files

Letting Backup and Sync work with removable USB storage / devices

Friday, February 23, 2018

Move on to Excel Tables as soon as you can

I was just reading +Bill Jelen 's prolific blog on Excel - the article I came across was the VLOOKUP bug - seems if you add a new column in the middle of your reference range, the first cell using VLOOKUP re-calculated properly but subsequent cells are "lazy" and need to be woken up, with Ctrl+Alt+Shift+F9.

I tested out Bill's Bug and yes, it is a bug. I then put the whole reference range (including headers) into an Excel Table, changed the VLOOKUP to reference that Table and it just works without extra steps.

Another reason why I keep pushing the idea that Excel Tables are not mandatory but many mundane Excel features now work properly with Tables but not with old timey ranges. 

Monday, February 5, 2018

Getting frustrated with the Microsoft Word keyboard cursor?

Microsoft Word has progressively been modified over time to make text editing easier for people. Unfortunately, every so often what the Word developers think is a good feature doesn't quite jive with the end users. One case in point is when you start selecting part of a paragraph and the keyboard cursor jumps to include the invisible paragraph mark or worse, the whole paragraph. Do you want to turn it off?

See Smart Paragraph Selection and Smart Cursoring

Like what you read?

Buy Me A Coffee

Get VBA out of maintenance mode

Microsoft planned and delivered the .NET framework in 2000 and VB.NET in 2002. It's 2018, and that's a long time ago. .NET is now the main framework that Microsoft uses for the cloud apps and even for desktop.

Poor old Visual Basic for Applications (VBA), the "internal" and bundled programming language for Microsoft Office - Access, Word, Excel, Powerpoint, Project, Visio etc... was put on maintenance status - it would be updated to cater for operating system changes here and there but it has no new enhancements. Yet, VBA for Microsoft Office desktop has not died and passed on - as long as Microsoft Office desktop lives and runs, VBA will provide a programming facility to people.

VB.NET (or offshoot) shows no signs of replacing VBA Classic. And the two platforms do not compete in the same arena at all, they are complementary instead of being antagonistic. Why not enhance VBA. With features like Linq , String Interpolation, constructs to manage large chunks of text and so on.

Tell Microsoft you want it this way