Friday, November 26, 2010

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, _

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

    End With

End Function

Wednesday, September 22, 2010

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 = 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

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.

' 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.

Sunday, September 12, 2010

Dynamics CRM 2011 Beta Is Here!

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

Monday, August 16, 2010

Excel 2007: Locating Data Entry Form Button

If you used the data entry form in Excel 2003, you might recall that it was easily available from the Data menu.

In Excel 2007 you won’t find a button anywhere within the ribbon. You will have to add it manually for example to the Quick Access Toolbar.

To get the Form button visible, follow these steps:
  1. Click on the down arrow on the Quick Access Toolbar
  2. Select More Commands
  3. Select All Commands
  4. Locate and select Form from the command list on the left
  5. Click Add
  6. Click OK
  7. Now you can find the Form button at the end of the QAT

Friday, August 13, 2010

Excel Error: Error 800a0011 When Trying to Add a Control to UserForm

When adding a form control to a UserForm you might get the following error message:
Excel could not complete the operation due to error 800a0011

When this occurs, your VBA code is most likely currently in break mode.

Click OK in the error dialog and reset your VBA Editor (Run->Reset). Now you can add the form control to your UserForm.

Works at least for Excel 2007.

Wednesday, August 11, 2010

SP2010: Features and Their GUIDs

A list of SharePoint 2010 features with associated GUIDs is also available. Thanks Norwegian Information Worker Team!

MOSS 2007: List of Features With GUIDs

I needed to locate a MOSS 2007 feature based on its GUID, and found Robert Bogue's (MVP) great list for the purpose. Thanks Robert!

This might just save your day too!

Tuesday, August 10, 2010

New Office 2010 Certifications Available

We've had Office 2010 available for a short while now, and for IT pros Microsoft has already released two Microsoft Office Specialist (MOS) on Microsoft Office 2010 certification exams:

Other applications' exams will follow, as well as the Expert level exams. I will reach for the Excel Expert certification and I'm looking forward for the rest of the exams to be released - just in case I'll go after the Master certification. Will keep you posted on my progress :)

A slightly funny detail of the MOS certifications is that Excel 2007 Expert and Word 2007 Expert exams seem to be still in development, and they are not required for the Microsoft Office 2007 Master Certification. I wonder what's the real status of these?

They managed to release a new full Office version meanwhile but these exams for previous version are not done yet :)

Are you planning on taking the MOS certification(s)? Are you going for the fresh 2010 version or for the 2007?

Outlook Social Connector: LinkedIn for Outlook is for 32bit Office Only

Hey LinkedIn, thanks for not mentioning your Outlook Social Connector plugin - called LinkedIn for Outlook - is for 32bit Office only.

I spent a while trying to install the plugin for my 64bit Outlook 2010. There's no indication on the LinkedIn for Outlook website that there is such a limitation.

I assume it's a too big task to add a few numbers and letters close to the Office version list.

Check the buzz around the 64bit issue from The LinkedIn Blog. As you can see, it took LinkedIn quite a while before answering the related questions.