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


Looking for a row using Find method against the Selection object

Function myFindRowPos(sText As Variant, oSheet As Worksheet, _
  Optional SearchDirection As XlSearchDirection = xlNext, _
  Optional SearchOrder As XlSearchOrder = xlByRows, _
  Optional sFindRange As String = "A1:A10000", _
  Optional LookAt As XlLookAt = xlWhole) As Long

  Dim lResult As Long, oRg As Range

  On Error GoTo errLabel
  oSheet.Activate
  oSheet.Range(sFindRange).Select

 Set oRg = Selection.Find(What:=sText, after:=ActiveCell, LookIn:=xlValues, LookAt:= _ LookAt, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False)   If Not oRg Is Nothing Then
     lResult = oRg.Row
  Else
     lResult = -1 'not found
        End IferrExit:
    myFindRowPos = lResult
    Set oRg = Nothing
    Exit Function
errLabel:
    lResult = -1
    Resume errExit
End Function

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