Excel Solutions Archive

The following article shows how to mark out and interrogate a calendar with the mouse. Mark Out And Interrogate A Calendar With The Mouse

*In the real world, we’d open a form to read and write meaningful diary entries to a database. This exercise simply reveals the mechanics of right_clicking, and finds the details from the worksheet itself.

Before we begin, a few words of explanation about the spreadsheet, a working model of which can be found here.A Few Words Of Explanation About The Spreadsheet

The Process

Clicking on a cell within the grid will highlight that cell and change its value. Click-and-drag will highlight a range and change its values. If a cell is populated, it will be cleared, otherwise it will be populated, in this case by an “*”.

A right_click by contrast is a request for information from the selected cell.

There are essentially two events used, along with several modules.

  • Worksheet_SelectionChange which is called when a cell or cells are selected.
  • Worksheet_BeforeRightClick which is called by the right-hand mouse button.

The Problem

Right_clicking a cell also constitutes a selection, triggering SelectionChange. We will have to let that event run its course, clearing the cell selected before it surrenders control to the BeforeRightClick event, when we’ll repopulate the cleared cell.  But this action will trigger the SelectionChanged event again, which has to be stopped from clearing it once again.

This we’ll do with a boolean flag called blnLoading.

The Events

Enter the following in the code window behind the worksheet (i.e. not in a module).

Option Explicit

    Dim blnLoading As Boolean
    Dim sPhase As String
    Dim currCellValue As String
    Dim dDate As Date

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If ActiveCell.Row > 14 And ActiveCell.Row < 25 Then
         If ActiveCell.Column > 4 And ActiveCell.Column < 47 Then 'selection is valid
 
             On Error Resume Next
             currCellValue = Target.Value 'get the target value from (ByVal Target As Range)
 
             If blnLoading = True Then 'a value of True will force an exit from this event
                 blnLoading = False
                 Exit Sub
             End If
 
             sPhase = Cells(ActiveCell.Row, 1)
             If sPhase = "" Then Exit Sub
 
             If ActiveCell = "*" Then 'if the cell is populated, clear the selected range
                 Call ClearRange
                 Call UnblockCalendar
             Else
                 Call PopulateRange
             End If
 
             Call RedrawCells
             Range("A1").Select 'revive the SelectionChange event by changing selection.
             Exit Sub
         End If
     End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
     If currCellValue = "*" Then   'picked up by the previous event BEFORE it cleared it;
                                     'This means this is a valid diary entry with detail.
        blnLoading = True    'this will prevent the SelectionChange event (above) from running.
        Target.Select
        'currCell = Target.Address
        'Range(currCell).Select

            Target.Value = "*" 're-instate the value of the cell, since SelectionChange has cleared it
            Call PopulateRange
            dDate = Cells(13, ActiveCell.Column)
            sPhase = Cells(ActiveCell.Row, 1)
            MsgBox dDate & " - " & sPhase
        Cancel = True 'suppress Excel’s standard right_click menus
    End If
    Range("A1").Select
        blnLoading = False
End Sub

This takes care of the two Events.

Referenced Code

Append the following, populating and depopulating ranges, to the code:

Sub ClearRange()
     Selection.FormulaR1C1 = ""
     With Selection.Interior
         .Pattern = xlNone
         .TintAndShade = 0
         .PatternTintAndShade = 0
     End With
End Sub

Sub PopulateRange()
     Selection.FormulaR1C1 = "*"
     With Selection.Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .ThemeColor = xlThemeColorLight2
         .TintAndShade = 0.799981688894314
         .PatternTintAndShade = 0
     End With
End Sub

Maintenance of Gridlines

Insert a module into the application. Add the following code for maintaining the look of the grid. This was copied from the macro recorder, redundancies and all.

Option Explicit

Sub UnblockCalendar()
     Selection.FormulaR1C1 = ""
     With Selection
         Selection.Borders(xlDiagonalDown).LineStyle = xlNone
         Selection.Borders(xlDiagonalUp).LineStyle = xlNone
         Selection.Borders(xlEdgeLeft).LineStyle = xlNone
         Selection.Borders(xlEdgeTop).LineStyle = xlNone
         Selection.Borders(xlEdgeBottom).LineStyle = xlNone
         Selection.Borders(xlEdgeRight).LineStyle = xlNone
         Selection.Borders(xlInsideVertical).LineStyle = xlNone
         Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
     End With
End Sub

Sub RedrawCells()
     Selection.Borders(xlDiagonalDown).LineStyle = xlNone
     Selection.Borders(xlDiagonalUp).LineStyle = xlNone
     With Selection.Borders(xlEdgeLeft)
         .LineStyle = xlContinuous
         .ColorIndex = 0
         .TintAndShade = 0
         .Weight = xlThin
     End With
     With Selection.Borders(xlEdgeTop)
         .LineStyle = xlContinuous
         .ColorIndex = 0
         .TintAndShade = 0
         .Weight = xlThin
     End With
     With Selection.Borders(xlEdgeBottom)
         .LineStyle = xlContinuous
         .ColorIndex = 0
         .TintAndShade = 0
         .Weight = xlThin
     End With
     With Selection.Borders(xlEdgeRight)
         .LineStyle = xlContinuous
         .ColorIndex = 0
         .TintAndShade = 0
         .Weight = xlThin
     End With
     With Selection.Borders(xlInsideVertical)
         .LineStyle = xlContinuous
         .ColorIndex = 0
         .TintAndShade = 0
         .Weight = xlThin
     End With
     With Selection.Borders(xlInsideHorizontal)
         .LineStyle = xlContinuous
         .ColorIndex = 0
         .TintAndShade = 0
         .Weight = xlThin
     End With
End Sub

Safeguarding against catastrophe

Anyone who does a lot of Excel development will know that complex xlsm spreadsheets can crash from time to time, corrupting the opened document. In more cases than might be expected, the damaged workbook can’t be recovered by Excel’s recovery routines. If there are no backups, the work done is lost. This can be prevented with tools designed to perform Excel fix.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com

We’ve all seen spreadsheet names and addresses with a mix of formats like JAMES JONES, sARAH bERNARDT or clint eastwood, all of which look amateurish on a report. This article shows how to standardise text in the proper way in a worksheet.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

The Excel file can be found here

In this exercise we’ll build a function capable of recognising, to a large extent, the intent of the user. For instance, the Excel proper function would reduce ‘NAFTA’ to ‘Nafta’. In the rare case where someone enters ‘usa armed forces’, the function will not be able to determine the intent, since it has no dictionary of acronyms, and will quite properly change it to ‘Usa Armed Forces’. Some miscreants will thus slip through the net.

What the Function Does

The text is split into two parts, one on each side of the first space.  Therefore ‘USA Armed Forces’ becomes a pre-string consisting of ‘USA’, and a post-string of ‘Armed Forces’.

  • If there is at least one lower case character in the post-string, then it’s a reasonable assumption that the caps lock wasn’t accidently left on during capture. The post-string will thus be formatted to the proper case.
  • If the second character of the prestring is upper case, then we can reasonably assume that the entire pre-string should be capitalised.

The Data

Create a new workbook. Open the VBA code window and insert a module.

Paste the following data into the first sheets cell A1.

Organisation Proper Case
USA Armed Forces
USA armed forces
UNESCO committee
bARRY JONES
BarRy JONES
1213456
alan courtney
NAFTA
301 capital House

Rename this sheet “Main”.

The Code.

Copy and paste the following into the VBA code window of the workbook:VBA Code

Option Explicit
     Dim strText As String
     Dim preString As String
     Dim postString As String
     Dim uCount As String
     Dim lCount As String
     Dim b As Integer
     Dim i As Integer
     Dim char2 As String

Sub Main()
     Dim strText As String
     Dim cRow As Integer 'Current row
     cRow = 2
     Sheets("Main").Select
     Range("A2").Select

     Do While ActiveCell > ""
         strText = ActiveCell
         strText = fProper(strText)
         Cells(cRow, 2) = strText
         cRow = cRow + 1
         Cells(cRow, 1).Select
     Loop
 
End Sub


Function fProper(strTxt As String)
     strText = strTxt
     uCount = 0
     lCount = 0
 
     'Seek the first space.
     b = InStr(1, strText, " ")
 
     'Test if there IS a space
     If b > 0 Then
         preString = Left(strText, b - 1)
         postString = Mid(strText, b, (Len(strText) - b) + 1)
 
         'Cycle through the post-string;
         'at least 1 lower case character will imply that the caps lock wasn't on
         For i = 1 To Len(postString)
             Select Case Asc(Mid(postString, i, 1))
                 Case 65 To 90
                     uCount = uCount + 1
                 Case 97 To 122
                     lCount = lCount + 1
                 Case Else
            End Select
            If lCount > 0 Then Exit For 'Go no further if a lowercase character is found
        Next i
 
        If lCount > 0 Then
            postString = StrConv(postString, 3) '3=proper case, 2=lowercase, 1=upper case
 
            'If the 2nd character of the pre-string is uppercase, it is reasonable
            'to assume the entire pre-string should be too.
            char2 = Mid(preString, 2, 1)
            If Asc(char2) >= 65 And Asc(char2) <= 90 Then
                preString = StrConv(preString, 1) 'entire pre-string is upper
            Else
                preString = StrConv(preString, 3) 'pre-string is proper
            End If
        Else
            preString = StrConv(preString, 3) 'No lower case found, Caps Lock stuck;
            postString = StrConv(postString, 3) 'Reduce the entire string to proper
        End If
        fProper = preString & postString 'Add the two elements together
     Else
 
         'No space was found, a reasonable assumption as to case can't be made;.
         'pass the string back unaltered.
         fProper = strText
     End If
End Function

Add a button to Sheet “Main” and assign it to sub procedure “Main”Add A Button To Sheet Main

The Results

Press the button, then examine column B of the sheet for the results.

Recovering damaged workbooks

From time to time Excel, like most software, will crash. The normal recovery routines that Excel would use to recover the workbook will fail more often than not. At such times it would be useful to have a tool to do Excel recovery for xlsx or xlsm files.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com

Data on a server can be modified by examining the records ‘client-side’ in Excel VBA, changing them as required, and saving them back to the server.
A more efficient way of doing this, particularly if the database is at a remote location and there’s a lot of traffic involved, is to do the work ‘server-side’. This exercise calls a stored procedure from Excel to categorise employees into age-ranges according to their dates of birth (i.e. 18-25 years, 26-35 years, etc.), without a copious exchange of data between the server and Excel.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

There are three elements to the exercise:

  • A data table tblStaff within a database TestDB;
  • A stored procedure spAgeRange;
  • An Excel xlsm, which we’ll call xlsm. A sample Excel file can be found here

Data Table

Create a database in SQL Server called DBTest.

Set up the following columns for a table tblStaff.

Set Up The Columns For A Table tblStaff

Copy the following into the table:

2017/05/25 1 Brown J 1946/12/02 M
2017/05/25 2 Smart A 1976/03/26 F
2017/05/25 3 Cruise T 1962/07/03 M
2017/05/25 4 Lohan L 1986/07/02 F
2017/05/25 5 Fredricksen F 1964/03/15 M
2017/05/25 6 Snyder L 1968/07/05 F
2017/05/25 7 Lipnicki J 1983/11/25 M
2017/05/25 8 Hoover S 2002/12/08 F
2017/05/25 9 Watson E 1990/04/15 F

Stored Procedure.

Run this script against TestDB to create the stored procedure:

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spAgeRange] Script Date: 2017/05/10 12:16:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spAgeRange]

    @PayrollDate varchar(50)
 
AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) 
    WHERE tblStaff.PayrollDate = @PayrollDate
 
    Update tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate

    Update tblStaff set AgeRange = '46 to 55' where Age >= 46 and Age < 56 and PayrollDate = PayrollDate 
 
    Update tblStaff set AgeRange = '39 to 45' where Age >= 39 and Age < 46 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '31 to 38' where Age >= 30 and Age < 39 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '25 to 30' where Age >= 25 and Age < 30 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '18 to 24' where Age >= 18 and Age < 25 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '<18' where Age < 18 and PayrollDate = @PayrollDate

END

The stored procedure will saved under “Programmability” in the database.

Excel VBA

All that remains is to call the stored procedure from Excel, providing the PayrollDate parameter of “2017/05/25”. You will note I have simply data-typed  PayrollDate as a string rather than wrestle with varying date formats. It is simple enough to convert a string to a date using the Convert function if PayrollDate is to be used for arithmetic purposes.

Create a new workbook. Open the VBA code window and insert a module.

From the code window’s Tools menu, reference the appropriate Active X 2.nn library to facilitate use of data objects.Reference The Appropriate Active X 2.nn Library To Facilitate Use Of Data Objects

Paste the following code into the Code window. This, once activated, will connect to SQL Server, as per the ConnectDatabase sub procedure

'All "public" in case the code is spread over several modules.
Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strConnectionstring As String
Public strServer As String
Public strDBase As String
Public strUser As String
Public strPwd As String
Public PayrollDate As String

Sub WriteStoredProcedure()
     PayrollDate = "2017/05/25"
     Call ConnectDatabase
     On Error GoTo errSP
     strSQL = "EXEC spAgeRange '" & PayrollDate & "'"
     connDB.Execute (strSQL)
     Exit Sub
errSP:
MsgBox Err.Description
End Sub

Sub ConnectDatabase()
     If connDB.State = 1 Then connDB.Close
     On Error GoTo ErrConnect
     strServer = "SERVERNAME" ‘The name or IP Address of the SQL Server
     strDBase = "TestDB"
     strUser = "" 'leave this blank for Windows authentication
     strPwd = ""

     If strPwd > "" Then
         strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Connection Timeout=30;"
     Else
         strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" &    strDBase 'Windows authentication
     End If
     connDB.ConnectionTimeout = 30
     connDB.Open strConnectionstring
Exit Sub
ErrConnect:
     MsgBox Err.Description
End Sub

Add a button to Sheet1 and assign it to sub procedure “WriteStoredProcedure

The Results

Press the button, then  examine tblStaff, which should be updated with ages and age ranges. The processing has taken place server-side.

Recovering corrupted workbooks

Should Excel crash it might well take your only copy of the workbook down with it. A good percentage of the time Excel is often unable to recover damaged workbooks; in such a case, all  the work done since the creation of the workbook might be irrevocably lost, unless you have a tool to repair Excel xlsx or xlsm files.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com