Creating a personal MACRO that references another workbook

Question

Objective: Open any workbook and run a macro, that will switch out all names and replace them with the IDs as referenced in the workbook "NameToID".

The workbook has a table with two columns, one that is a name and the other that is their associated ID. The workbook, sheet, and table have the same label of "NameToID".

The Macro is stored in my personal folder and so it is accessible to each document I open. Right now, I open up my NameToID workbook, then I open up the workbook where I want to run the macro - but when I run it I get a runtime error 9 "Subscript out of range" error. I can't figure out how to fix it. Does the NameToID workbook need to be stored somewhere in a personal folder so it is always accessible? Any help would great thanks.

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire 
 workbook from a table
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to your table
  Set tbl = Workbooks("NameToID").Worksheets("NameToID").ListObjects("NameToID")

'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)

'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
      For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> tbl.Parent.Name Then

      sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

        End If
      Next sht
  Next x

End Sub

Yes... which line. Very important. Also ActiveWorkbook.Worksheets Is the activeworkbook the workbook you think it is? Perhaps through a debug print activeworkbook.name just above this line and make sure it's what you think it is. — JNevill

Tigregalis Tigregalis · Accepted Answer · 2017-11-03T17:52:55

Your problem is:

A subscript out of range error usually means that the member (workbook "NameToID") of the collection (Workbooks), doesn't exist. You need to refer to workbook "NameToID" by its full filename; otherwise it gives you a subscript out of range error, i.e. it should be Workbooks("NameToID.xlsm") not Workbooks("NameToID"). Note: Obviously I'm assuming *.xlsm but change it to *.xlsx or other if relevant.

In this context, "subscript" refers to "index", "reference" or "key"; and "range" refers to the acceptable range (values) for these references. "subscript out of range" therefore means that the target reference doesn't exist within the acceptable range of references.

So this line becomes:

Set tbl = Workbooks("NameToID.xlsm").Worksheets("NameToID").ListObjects("NameToID")

However, the answer to your question is:

Before you do this, you need to check if NameToID is open;

a. if it is, proceed;

b. if it isn't, open it;

c. alternatively, you can always assume that it will be open, so there is no need to check, but you have to refer to it correctly; and this is what you are doing now

d. alternatively, you can always assume that it will not be open, so there is no need to check, but you will have to open it

We need a function to check if a workbook is open; this function loops through all open workbooks and checks to see if the requested member exists. If it does, it returns true. If it doesn't, it returns false.

Function IsWorkbookOpen(ByVal wbName As String) As Boolean

    Dim wb As Workbook
    For Each wb In Application.Workbooks ' loop through open workbooks
        If wb.Name = wbName Then ' if we find a match, set return value to True and exit the function
            IsWorkbookOpen = True
            Exit Function
        End If
    Next wb
    ' if we didn't find a match, set return value to False
    IsWorkbookOpen = False

End Function

With this function to assist, we open the workbook if it isn't yet open.

If Not IsWorkbookOpen("NameToID.xlsm") Then Application.Workbooks.Open "C:\...\NameToID.xlsm"

You can then proceed as you were, from Set tbl =...

Creating a personal MACRO that references another workbook

1 Answers