Add links to cells with VBA

Sub addLinks()

‘ Converts each cell text of a selected range into a working hyperlink
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub

Advertisements

Use Find method against Cells collection to detemine the last used Row, Column in Excel

Function lastUsedRow() As Long
On Error Resume Next
Dim lLastRow As Integer
lLastRow = Cells.Find(What:=”*”, after:=Range(“A1″), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
If lLastRow = 0 Then
lLastRow = 1
End If
lastUsedRow = lLastRow

End Function

Function lastUsedCol() As Long
On Error Resume Next
Dim lLastCol As Integer
lLastCol = Cells.Find(What:=”*”, after:=Range(“A1”), SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
If lLastCol = 0 Then
lLastCol = 1
End If
lastUsedCol = lLastCol

End Function


Prompt for a file name using VBA

Function prompt4FileName(Optional sFolder As String = vbNullString, Optional sDefaultName As String = "Untitled") As String
    Dim sMacScript As String
    Dim sFileName As String

    If sFolder = vbNullString Then 'Get user document folder
        sFolder = userDocumentFolder()
    End If
    If isMac Then
        sMacScript = "set sFile to (choose file name with prompt ""Name this file"" default name """ & _
        sDefaultName & vbLf & _
        """ default location the path to documents folder)" & vbLf & _
        "return sFile"
        sFileName = MacScript(sMacScript)
    Else
        sFileName = Application.GetSaveAsFilename(sDefaultName, "Excel Files,*.xls,Excel macro enable files,*.xlsm,All Files,*.*", , "Name this file", "Save as")    End If
    prompt4FileName = sFileName
End Function

Increase VBA performance

If your macro include code to make many change in sheets, document, jumping in/out of them then you need to consider to turn off screen update to turning performance of your code. Consider following snip-set example. Comment out the red line to see the effect

Sub myMacro ()

    On Error Goto errLabel
    Application.ScreenUpdating = False

    for i =1 to 1000 

        With Sheets("Sheets1")
           .Select
           .Activate
           Cells(i,1).Value=i
        End With
        With Sheets("Sheets2")
           .Select
           .Activate
           Cells(i,1).Value=i
        End With
    next
exitLabel:

    Application.ScreenUpdating = True
    Exit Sub

errLabel:
    MsgBox "System stop processing with error: " & Err.Description, vbOKOnly + vbCritical,"HotIce Soft"
    Resume exitLabel
    Resume Next
End Sub

Quickly goto a row in Excel with VBA

Sub scroll2aRow(Optional lRow As Long = 0)
    On Error Resume Next

    Application.Goto ActiveSheet.Range("A" & lRow), True

End Sub

Use an Open File dialog in VBA with both Mac and Windows

Function myGetOpenFileName(Optional sPath As String) As String
Dim sFile As String
Dim sMacScript As String

    If isMac Then
        If sPath = vbNullString Then
            sPath = "the path to documents folder"
        Else
            sPath = " alias """ & sPath & """"
        End If
        sMacScript = "set sFile to (choose file of type ({" & _
            """com.microsoft.Excel.xls"", ""org.openxmlformats.spreadsheetml.sheet""," & _
            """public.comma-separated-values-text"", ""public.text"", ""public.csv""," & _                   """org.openxmlformats.spreadsheetml.sheet.macroenabled""}) with prompt " & _
            """Select a file to import"" default location " & sPath & ") as string" _
            & vbLf & _
            "return sFile"
         Debug.Print sMacScript
        sFile = MacScript(sMacScript)

    Else 'windows

        sFile = Application.GetOpenFilename("CSV files,*.csv,Excel 2007 files,*.xlsx", 1, _
            "Select file to import from", "&Import", False)

    End If

    myGetOpenFileName = sFileEnd Function

Writing VBA for both Windows and Macintosh

Microsoft has brought VBA back for Mac world with its latest Office. But there are many differences between two version. To write macros that run smoothly on both systems you need to began with system recognition. Follow snip-set let you do it

Function isMac() As Boolean

   If Application.OperatingSystem Like "*Mac*" Then
      isMac = True
   Else
      isMac = False
   End If

End Function

Sub myMacro ()

   if isMac() then
      'code for Mac here
   else
      'code for Win here
   end ifEnd Sub