RyanDev.com

Many people use Excel to analyze data and to “watch” data.  Did you know that you can have Excel play a sound or even a song when a cell changes if you want to alert someone?  In fact, you could trigger any kind of workflow you wanted.  This example shows how to call into the Win32 API using Excel to play a sound.

We first need to declare to Excel how to call into the Win32 API.  We also want to declare a public variable to keep track of the value in cell A1 so we can know when it changes.

First, add a module to the workbook that you will be using and add the following 2 lines of code:

Declare Function sndPlaySound32 Lib “winmm.dll” Alias “sndPlaySoundA” (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
 
Public CellValue As Variant ’store the cell value here

The first line is how you make Excel aware of the Win32 API call to play a sound.  The second line of code is declaring the global variable.

Second, we want to read in the value of cell A1 when the workbook first opens so we can keep track of when it changes.  Use the following code:
Private Sub Workbook_Open()
CellValue = Sheets(1).Range(”a1″)
End Sub

Now that we are storing what the value is and we have told Excel how to play the sound we now need to check the cell each time something on that sheet changes.  Use the following code:

Private Sub Worksheet_Calculate()
If Range(”a1″) > 1 And CellValue <> Range(”a1″) Then
    sndPlaySound32 “C:\Error.WAV”, 0
    CellValue = Range(”a1″)
End If
End Sub

‘if you want it to play the sound every time anything on the sheet changes, delete out the
‘parts about CellValue

Obviously you can add in any logic you would like.  You could choose to send an email, print the document to the printer or even using Unified Communications (UC) services (from companies such as http://www.engage2day.com) you could have the system IM someone or even have the system call them and let them know a threshold has been reached.  This may be overkill for an Excel spreadsheet but it certainly opens up the possibilities of what you can do with a simple Excel sheet.

The Page Setup window of Excel does not offer many choices for adding dynamic information into the header and footer.

To add the time the file was last saved use the following code:

Sub LastSavedTimeInFooter()
ActiveSheet.PageSetup.CenterFooter = ActiveWorkbook.BuiltinDocumentProperties(”Last Save Time”)
End Sub

Note, the above code will have to be manually run.  To have this happen automatically you could do the following:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterFooter = ActiveWorkbook.BuiltinDocumentProperties(”Last Save Time”)
End Sub

With this code it would automatically run every time you had to print and would always be updated on your hard-copies. 

When you create new documents in Microsoft Word, for example, when you click save Word will often suggest a name of Document1.  If you create a new document and click save Word will suggest Document2 and so on until you restart Word and then it will be Document1 again.  Since Microsoft InfoPath is more web-based it always suggests the same name when saving a document to a SharePoint repository. 

Sometimes you want to auto-increment the number attached to the file name.  Maybe you use InfoPath forms to fill out PTO requests in your organization and instead of everyone saving one with the default name you want to control that name.  Below is some easy to use code to increment the number that is stored on the end of the file-name regardless of who in your organization is saving the form to the library.  You will want to modify the OnSubmitRequest of the Form in InfoPath and add in the custom code.

---

function XDocument::OnSubmitRequest(eventObj)
{
    // If the submit operation is successful, set
    // eventObj.ReturnStatus = true
    var fSuccessful = false;
 
 
 //start of custom code
 var fso,d, f,ts;
 fso = new ActiveXObject(”Scripting.FileSystemObject”);
 //Change the following path to a file that everyone has acccess to or have some fun with AJAX and make a db call.
 //Note: In Java Script to do a “\” you actually need 2, “\\”
 //NextNumber.txt needs to be a text file with only a # in it.  Start it at 0.
 f = fso.GetFile(”\\\\servershare\\InfoPathForms\\NextNumber.txt”);

 ts = f.OpenAsTextStream(1,-2);
 s = ts.ReadLine( );
// Read in the value from the file that will be used as the next document number
        s++;
        ts.Close( );
// Now open the file to write back the next number after incrementing it
        ts = f.OpenAsTextStream(2,-2);
        ts.Write( s );
        ts.Close( );
 //end custom code
    // Set the URL of the file that you want to submit here.
    var strUrl = “http://servername/InfoPathPTOForms/PTORequest” + s + “.xml”;
 
    try
    {
        // Create an xmlhttp object.
        var oXmlHttp = new ActiveXObject(”MSXML2.XMLHTTP”);
 
        // Check whether the document with the same name already exists on SharePoint Team Services (STS).
        oXmlHttp.Open(”HEAD”, strUrl, false);
        oXmlHttp.Send();
 
        //  No document with the URL has been found. Continue to submit.
        // If you must replace the original file, you must call
        // oXmlHttp.Open(”DELETE”, strUrl, false) to delete the document
        // on STS first.
        if (oXmlHttp.Status == 404)
        {
            // Put the document on STS.
            oXmlHttp.Open(”PUT”, strUrl, false);
            oXmlHttp.Send(XDocument.DOM.xml);
 
            //  A 200 status code or a 201 status code indicates that the form has been submitted successfully.
            if (oXmlHttp.Status == 200 || oXmlHttp.Status == 201)
            {
                fSuccessful = true;
            }
        }
    }
    catch (ex){}
   
    if (fSuccessful)
    {
        XDocument.UI.Alert(”Document submitted successfully!”);
        eventObj.ReturnStatus = true;
    }
    else
    {
        eventObj.ReturnStatus = false;
    }
}

Sometimes when writing a complex formula you may not know which sheet of data you want the value to come from.  Or, maybe the sheet will change based on some conditions.  This is easy to accomplish.

For example, the following formula will return the value in cell A1 of whichever sheet is typed into cell B1. 

=INDIRECT(ADDRESS(1,1,,,B1))

The INDIRECT function can be used for many useful dynamically determined values.  In this case, the Address function will return the full address of the cell you are looking for based on the current workbook.  So, if ”Sheet2″ is typed into cell B1, ADDRESS(1,1,,,B1) = Sheet2!$A$1.  If the value in cell B1 were changed to “JanuarySales” the value returned by the Address function would be JanuarySales!$A$1.

The power is in the Indirect function.  Indirect will return the value of the cell that you are passing into the function.  So, Indirect(”Sheet2!$A$1″) will return the value that is in cell A1 on Sheet2.

The wonderful power of Excel is that any of the parameters can be specified with other functions.  Maybe you need to use an If statement to properly determine which sheet to pull from.  =INDIRECT(ADDRESS(1,1,,,If(Month(Now())=1,”JanuarySales”,”FebruarySales”)))

Many people use Microsoft Excel to aggregate data from many sources or to analyze the data using Excel’s many great functions.  Wouldn’t it be great to be able to trigger work-flow or notifications if certain cells changed or maybe if they changed above a certain threshold?  This is easy to do.

First, open the workbook that contains the cells you would like to monitor.  Then press Ctrl+F11 to open the Visual Basic Code Editor.  You first need to tell Excel that you would like to be notified when cells on a particular sheet change.  You can do that with the following code.

---

Sub auto_open()
‘if you make any changes to the code, you will need to save and reopen the workbook
ThisWorkbook.Worksheets(”Sheet1″).OnEntry = “DidCellsChange”
End Sub

---

You have now wired the OnEntry event on the Sheet object to your code.  This method is called each time a cell changes.Now, all you have to do is have a Sub routine called “DidCellsChange” to catch the event.  The below example shows how to check to make sure that the cell being changed is within a range of cells which you care about.

---

Sub DidCellsChange() ‘checks to see if the cells in the Range have changed
If Not Application.Intersect(ActiveCell, Range(”b5:b65″)) Is Nothing Then
  ‘ do your code here
  ‘ call out to a webservice or change the cell color or send an email, etc.
End If
End Sub

---

Pretty simple.  Note, your workbook will have to be open for this to work because it is dependent on Excel’s formulas and features.

Here are some quick and easy to use tutorials if you are interested in learning VBA.  Please let me know if there are other topics you would like me to cover.  There are 6 modules and each has a series of attachments to download the full documentation and code samples.  Below are summaries of what each module covers.

---

Module 1

Introduction

The purpose of this module is to familiarize you with the Visual Basic Editor environment and to introduce some coding techniques. By the end of this module you should be able to accomplish the following tasks:

• Arrange and navigate the Visual Basic Editor
• Write and edit macros
• Write code to respond to events
• Recognize and have a basic understanding of the Object Model

Attachments: Module 1.doc

---

Module 2

Introduction

The purpose of this module is to introduce automation of Microsoft Office products. By the end of this module you should be able to accomplish the following tasks:

• Automate one Office product from within a different Office product
• Understand how to reference the Object Model of different Office products
• Use debugging techniques
• Implement error handling

Attachments: Module 2.doc; DataFile.xls

---

Module 3

Introduction

The purpose of this module is to allow you to put in practice lessons from Modules 1 and 2. Each of the examples listed here come from real calls that I have had. By the end of this module you should be able to accomplish the following tasks:

• Write custom functions in Microsoft Excel
• Play a sound in Excel when a cell changes
• Format cells in a Word table

Attachments: Module 3.doc; Count Criteria.xls

---

Module 4

Introduction

The purpose of this module is to introduce user forms that can be used in VBA. By the end of this module you should be able to accomplish the following tasks:

• Create a user form in Excel.
• Create a user from in Word to pull data from Access.

Attachments: Module 4.doc; Count Formulas.xls; Addresses.mdb

---

Module 5

Introduction

The purpose of this module is to introduce VBA programming with Microsoft Access. By the end of this module you should be able to accomplish the following tasks:

• Create a new Microsoft Access database from within Excel.

Attachments: Module 5.doc; Addresses.mdb

---

Module 6

Introduction

The purpose of this module is to introduce VBA in Microsoft Outlook and Microsoft PowerPoint. By the end of this module you should be able to accomplish the following tasks:

• Create a user form in Excel.
• Create a user from in Word to pull data from Access.

Attachments: Module 6.doc; Addresses.mdb

---

Download the full code.

---

Attribute VB_Name = “FindReplace”
Public Sub GlobalFindandReplace()
Dim oDoc As Word.Document
Dim oFile As File
Dim oFolder As Folder
Dim fs As FileSystemObject

Set fs = New FileSystemObject
Set oFolder = fs.GetFolder(”c:\ptr”) ‘the folder with all of the files

Application.DisplayAlerts = wdAlertsNone ‘don’t put up any prompts
For Each oFile In oFolder.Files ‘loop through all files in the folder
  If oFile.Type = “Microsoft Word Document” Then ‘only touch Word files
  Set oDoc = Application.Documents.Open(oFile.Path) ‘open the file
  ‘***********************
  ‘beginning of first find
  Selection.Find.ClearFormatting
  Selection.Find.Replacement.ClearFormatting
  With Selection.Find
  .text = “administrator”
  .Replacement.text = “Administrator”
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchCase = True
  .MatchWholeWord = False
  .MatchByte = False
  .MatchAllWordForms = False
  .MatchSoundsLike = False
  .MatchWildcards = False
  .MatchFuzzy = False
  .Execute Replace:=wdReplaceAll
  End With
  ‘end of first find
  ‘************************
  ‘if a second find is needed copy the above code here

‘end of second find

End If
  ’save and close the document
  oDoc.Save
  oDoc.Close
Next oFile ‘move on to the next file
Application.DisplayAlerts = wdAlertsAll ‘turn alerts back on
End Sub

---

Download the full code.

Download the full solution.

---

The code for this is below:Dim FirstSheet As IntegerPrivate Sub cmdCancel_Click()
Unload Me
Sheets(FirstSheet).Select
End
End Sub

Private Sub cmdOk_Click()
Dim Total As Double, SubTotal As Double
Dim i As Integer
Dim StartTime As Variant
StartTime = Now()
Me.MousePointer = fmMousePointerHourGlass
Total = 0

If optThis.Value = True Then
    On Error Resume Next
    Total = Selection.SpecialCells(xlCellTypeFormulas, 23).Count
End If
If optEntire.Value = True Then
    For i = 1 To ActiveWorkbook.Sheets.Count
        SubTotal = 0
        Sheets(i).Activate
        On Error Resume Next
        SubTotal = Selection.SpecialCells(xlCellTypeFormulas, 23).Count
        Total = Total + SubTotal
    Next i
End If
If optSelection.Value = True Then
    Dim SelRange As Range
    Dim address As String
    address = RefEdit1.Value
    Set SelRange = Range(address)
    On Error Resume Next
    Total = SelRange.SpecialCells(xlCellTypeFormulas, 23).Count
End If
Me.MousePointer = fmMousePointerDefault
If Total = 0 Then
    MsgBox “There were no formulas counted.” & vbCrLf & “Total time: ” & Format(Now() - StartTime, “h:m:ss”), , “Formula Count”
Else
    MsgBox “There were ” & Format(Total, “###,###,###”) & ” formulas counted.” & vbCrLf & “Total time: ” & Format(Now() - StartTime, “h:m:ss”), , “Formula Count”
End If
Sheets(FirstSheet).Select
End Sub

Private Sub optEntire_Click()
Frame2.Visible = False
End Sub

Private Sub optSelection_Click()
Frame2.Visible = True
Frame2.SetFocus
End Sub

Private Sub optThis_Click()
Frame2.Visible = False
End Sub

Private Sub UserForm_Initialize()
optThis.Value = True
FirstSheet = ActiveWorkbook.ActiveSheet.Index
End Sub

---

Download the full solution.

Here is some sample code that will document the formulas you have in your worksheet.  It will create a separate sheet named Formulas that will list all of the formulas being used.

---

Public Sub DocumentFormulas()
‘——————————————————————————————
‘Created by Ryan Brown
‘This is a sample that will document the formulas from the sheet that it is run on
‘It will create a sheet called Formulas and you will get an error if a sheet named formulas
‘already exists.
‘It will put both the cell address and the cell formula into 2 separate columns
‘——————————————————————————————
Dim SelRange As Range
Dim oCell As Object
Dim strSheet As String
Dim i As Integer

strSheet = ActiveSheet.Name

’store all of the formulas on the given sheet into a range variable
Set SelRange = Selection.SpecialCells(xlCellTypeFormulas, 23)

ActiveWorkbook.Sheets.Add
ActiveSheet.Name = “Formulas”

i = 1   ’start at cell A1

‘loop through each cell in the range of formulas and print the address and formula
For Each oCell In SelRange.Cells
    Sheets(”Formulas”).Cells(i, 1) = oCell.address
    Sheets(”Formulas”).Cells(i, 2).NumberFormat = “@”   ‘change cell format first to text so formula shows
    Sheets(”Formulas”).Cells(i, 2) = oCell.Formula
    i = i + 1
Next oCell
End Sub

---

Below is some sample code that I have used in the past that has some useful date and time functions.  The code was written in Visual Basic 6.0 but can easily be converted to .Net.

---

Attribute VB_Name = “TimeDateFunctions”
Public TheYear As Integer, TheMonth As Integer, TheDay As Integer

Public Function LastDayOfMonth(ByVal ADate As Date, PreviousMonth As Integer) As Integer ‘Previous month is an offset of the passed in date to return the last day in a different month

‘For example, if ADate is in February and PreviousMonth is 3, the last day of November will be given

‘This will not go back more than a year, previous month max is 12

Dim CurrentMonth As Integer, i As Integer

CurrentMonth = Month(ADate) - PreviousMonth

TheYear = Year(ADate)

If CurrentMonth <= 0 Then

CurrentMonth = 12 + CurrentMonth

TheYear = TheYear - 1

End If

TheMonth = CurrentMonth

Select Case CurrentMonth

Case 1, 3, 5, 7, 8, 10, 12

LastDayOfMonth = 31

Case 4, 6, 9, 11

LastDayOfMonth = 30

Case 2

LastDayOfMonth = 28

End Select

End Function

---

Public Function WeekDayName(ByVal WeekDayNum As Integer) As String

Select Case WeekDayNum

Case 1

WeekDayName = “Sunday”

Case 2

WeekDayName = “Monday”

Case 3

WeekDayName = “Tuesday”

Case 4

WeekDayName = “Wednesday”

Case 5

WeekDayName = “Thursday”

Case 6

WeekDayName = “Friday”

Case 7

WeekDayName = “Saturday”

Case Else

MsgBox “This function returns the name of the weekday that was passed in.” & _

“Valid parameter values are 1-7 corresponding to weekdays Sunday through Saturday.”

End Select

End Function

---

Download the code.