Loading an ARRAY with formula text, not formula values

[polarisking]

I'm attempting to speed things up when searching a multi-sheet workbook, cell by cell, for links within a cell.

My approach is to use INSTR to find the value ".xl" within the actual formula text.

When I do this cell by cell, I have to use artifacts like cell.formula and cell.hasformula.

I'd like to load the worksheet's entire used range into an ARRAY and search that instead. However, when I load the range, the values of the cells are loaded as opposed to the formula text.

Any ideas as to how I capture the actual formula text in the array with the standard load statement: Array = Range(Cells(1, 1), Cells(#Rows, #Cols))

Thanks in advance.

 

[Fluff13]

How about like
ary = Range("E2:G4").Formula

 

[polarisking]

Great response; perfect answer. Thank you so much.

 

[Fluff13]

My pleasure & thanks for the feedback

 

[polarisking]

Happy to share this with the community

Sub ListLinks()
    
    Dim ws              As Worksheet
    Dim arrForm         As Variant
    
    Dim LastRow         As Long
    Dim LastCol         As Long
    
    Dim ctrRow          As Long
    Dim ctrCol          As Long
    Dim CellsWithForm   As Long
    
    CellsWithForm = 0
    For Each ws In Worksheets
        ws.Activate
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

        arrForm = Range(Cells(1, 1), Cells(LastRow, LastCol)).Formula
        
        For ctrRow = 1 To LastRow
            For ctrCol = 1 To LastCol
                If IsError(arrForm(ctrRow, ctrCol)) Then
                    'Ignore
                ElseIf Len(arrForm(ctrRow, ctrCol)) > 0 And _
                    InStr(arrForm(ctrRow, ctrCol), ".xl") <> 0 Then
                    CellsWithForm = CellsWithForm + 1
                    'Debug.Print ws.Name & ": Row " & ctrRow & " Col " & ctrCol & " Formula: " & arrForm(ctrRow, ctrCol)
                End If
            Next ctrCol
        Next ctrRow
    Next ws
    
    MsgBox CellsWithForm & " cells with formulas found."
    
End Sub

 

[polarisking]

My pleasure & thanks for the feedback

Not rocket science, but I've posted my macro. Useful for adding to one's Personal workbook, or utility collection. You can either uncomment the Debug.Print line, or write out to another tab.

Thanks again.