Save Valentine's Day With Excel

Forgot to get something nice for your Valentine? Don't worry - Debra shows how Excel can save you!

Excel: How to Open Files in New App Instances

I know you've cried and yelled when a certain workbook crashes your Excel - and corrupts the other open workbook(s) too.

Here's an old but still relevant tip from Virgilio Adriano on how to open Excel files in a new application instance.

Tips: add a new context menu item to make this feature available on need -basis, or make it the default behaviour!

Excel VBA: How to Find Out the Last Cell of a Worksheet

A simple and quick way of getting the real last used cell in a worksheet.

If an error occurs the LastCell range remains uninitialized and this can be confirmed from the parent subroutine or function.

Function LastCell(sSheet As String) As Range

    Dim LastRow As Long
    Dim LastRow LastCol As Long

    On Error Resume Next

    With Worksheets(sSheet)

        LastRow = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByRows).Row

        LastCol = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByColumns).Column
      
        Set LastCell = .Cells(LastRow, LastCol)

    End With

End Function

Presentations from SharePoint User Group Finland (Sep 8, 2010)

SharePoint User Group Finland published presentation materials from their September 8th meeting. Some examples how SharePoint can be utilized. Read and download the material.

Microsoft Dynamics CRM 2011 on YouTube

CRM Team has opened a YouTube channel for their video material; discussion, promos etc. Check Microsoft Dynamics CRM 2011 videos.

Excel VBA: Does the Cell or Range Have a Formula

Need to quicky check if a cell contains a formula? Use Range's HasFormula property:

Property definition

expression.HasFormula

• Expression = Range object
• Returns:
• True if all cells in range contain a formulss
• False if none contain formulas
• Null if some cells contain a formula 
• Read-only Variant

Use IsNull to check if the result is Null.

Example 1

Scenario
You have a column, where you want the user to have two options:

1. By default include a formula
2. Let the user enter a value manually

The formula is updated on Worksheet_Open or a button.

Solution 

' 

' Update only cells with formulas.

'

If ActiveSheet.Cells(Row, Column).HasFormula  Then

    ' Update or refresh your formula here.

End If

Example 2

See MSDN's HasFormula Property page for an example which recalculates the active worksheet if any cell in the currently selected range contains a formula.

Dynamics CRM 2011 Beta Is Here!

Beta for Dynamics CRM 2011 is now here, go and check it! My beta instance is already running :)