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
  19.  
  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
  23.  
  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
  44.  
  45. CleanForTableFormat_Exit:
  46.   Exit Sub
  47.  
  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
  56.  
  57.    
  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.

Installing



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

Tuesday, December 5, 2017

Linked Transpose Formula

I've known about the static
Copy
Paste > Special > Transpose
forever.

Today, a class participant wanted a transpose but not static, she wanted dynamically linked formula.

Seems the =TRANSPOSE() function has been there for a long time. It's a CSE array function - so, select a range of blank cells in the shape of a transposed destination, use the expression {=TRANSPOSE(A1:B4)}

Remember you don't type the { } - they are bookended when you press Ctrl+Shift+Enter instead of Enter

See the TRANSPOSE() description

Thursday, September 1, 2016

Fonts and things in Microsoft Access

Microsoft Access designer tools have mostly been dormant and not improved over the years.

Query - SQL View font

Two things are cruel in SQL View. 

The SQL Text will not stay formatted and will automatically be wrapped - probably this is due to the two way interface between Query Design View and SQL View.

The font in SQL View isn't the best choice. You can change the font in SQL View but this also changes the Query Design View font.

File > Options > Object Designers > Query Design - Query design font

Expression Builder font

Put your cursor in the editable content text area of the Expression Builder. Ctrl+Mouse Scroll Wheel. Tip from Utter Access Forums