Tim tiene un libro de trabajo que es bastante grande y se pregunta si hay una manera de hacer un «inventario» de las funciones que se usan en las diversas fórmulas del libro, junto con las celdas en las que se usan esas funciones.

Esta tarea es un poco más difícil de lo que parece a primera vista. Puede establecer algunas reglas sobre lo que constituye una función de Excel, pero esas reglas pueden volverse un poco complicadas. Por ejemplo, podría pensar que una función está definida por una palabra en mayúscula seguida de un paréntesis de apertura. Sin embargo, esto no ha sido así durante un tiempo, ya que los nombres de las funciones ahora pueden contener puntos.

La forma más fácil que encontré para obtener un inventario de funciones es desarrollar una macro que recorra cada fórmula en cada hoja de trabajo en el libro de trabajo y comparar esa fórmula con todas las funciones posibles de la hoja de trabajo. La siguiente macro lo hará bien:

Sub FormulaInventory()

Dim EFunc(500) As String     Dim iEFCnt As Integer     Dim sFile As String     Dim sTemp As String     Dim SourceBook As Workbook     Dim TargetBook As Workbook     Dim TargetSheet As Worksheet     Dim w As Worksheet     Dim c As Range     Dim iRow As Integer     Dim J As Integer     Dim K As Integer     Dim L As Integer

' Read functions from text file     sFile = ActiveWorkbook.Path & "\ExcelFunctions.txt"

iEFCnt = 0     Open sFile For Input As #1     While Not EOF(1)

Line Input #1, sTemp         sTemp = Trim(sTemp)

If sTemp > "" Then             iEFCnt = iEFCnt + 1             EFunc(iEFCnt) = sTemp & "("

End If     Wend     Close #1

' Sort functions; longest to shortest     For J = 1 To iEFCnt - 1         L = J         For K = J + 1 To iEFCnt             If Len(EFunc(L)) < Len(EFunc(K)) Then L = K         Next K         If L <> J Then             sTemp = EFunc(J)

EFunc(J) = EFunc(L)

EFunc(L) = sTemp         End If     Next J

' Create and setup new workbook     Set SourceBook = ActiveWorkbook     Set TargetBook = Workbooks.Add     Set TargetSheet = TargetBook.Worksheets.Add     TargetSheet.Name = "Inventory"

TargetSheet.Cells(1, 1) = "Function Inventory for " & SourceBook.Name     TargetSheet.Cells(3, 1) = "Function"

TargetSheet.Cells(3, 2) = "Worksheet"

TargetSheet.Cells(3, 3) = "Cell"

TargetSheet.Range("A1").Font.Bold = True     TargetSheet.Range("A3:C3").Font.Bold = True     With TargetSheet.Range("A3:C3").Cells.Borders(xlEdgeBottom)

.LineStyle = xlContinuous         .Weight = xlThin     End With

' Perform actual inventory     iRow = 4     For Each w In SourceBook.Worksheets         For Each c In w.Cells.SpecialCells(xlCellTypeFormulas)

sTemp = c.Formula             For J = 1 To iEFCnt                 If InStr(sTemp, EFunc(J)) Then                     TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1)

TargetSheet.Cells(iRow, 2) = w.Name                     TargetSheet.Cells(iRow, 3) = Replace(c.Address, "$", "")

iRow = iRow + 1                     sTemp = Replace(sTemp, EFunc(J), "")

End If             Next J         Next c     Next w End Sub

Un examen cuidadoso de la macro indica que se lee en un archivo de texto (ExcelFunctions.txt) que incluye una lista de todas las funciones de la hoja de cálculo de Excel. Este archivo puede ser link: /excelribbon-ExcelFunctions.txt [descargado usando este enlace], y debe almacenarse en la misma carpeta que el libro de trabajo que está analizando. (Esto significa que el libro de trabajo que está analizando ya debe estar guardado en una carpeta).

Cuando los nombres de las funciones de la hoja de trabajo se cargan desde el archivo de texto, la macro agrega un paréntesis de apertura a cada nombre. Estos nombres se almacenan en una matriz y luego esta matriz se ordena según la longitud del nombre de la función, con los nombres de función más largos al principio de la matriz. Esto se hace debido a cierta ambigüedad en los nombres de las funciones. Por ejemplo, cuando se lee la matriz de nombre de función, tendrá una función llamada LEFT (y otra llamada T (. Al mirar una fórmula, si se encuentra el nombre LEFT (, entonces también se encontrará el nombre T (). Al mirar primero los nombres de función más largos y luego eliminar esos nombres de la cadena de fórmulas, elimina la posibilidad de «falsos positivos».

Debe tenerse en cuenta que esta función solo funcionará con las funciones de la hoja de trabajo enumeradas en el archivo ExcelFunctions.txt. Puede modificar el archivo, según desee, para adaptar lo que se encuentra. Actualmente incluye todas las funciones de hoja de trabajo estándar para Excel 2016, pero es posible que desee modificarlo para incluir las funciones disponibles por sus complementos, o puede que desee eliminar funciones que le parezcan demasiado esotéricas. (Sugerencia:

También puede simplemente agregar un apóstrofo delante del nombre de una función en el archivo de texto y luego nunca se incluirá en un inventario.) Hay poco menos de 500 nombres de funciones en th e archivo ahora; si agrega muchos más, deberá cambiar los elementos numéricos declarados en la macro para la matriz EFunc.

Cuando se complete la macro, encontrará su inventario en el nuevo libro de trabajo que crea la macro. Debe tener en cuenta que el tiempo que tarda la macro en completarse puede variar de bastante rápido a increíblemente lento. Cuanto más grande sea su libro de trabajo, más hojas de trabajo contiene y más fórmulas en esas hojas de trabajo, más tiempo llevará terminar su trabajo.

Recuerde que debe comparar todas las fórmulas del libro con casi 500 nombres de funciones de la hoja de trabajo.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (5136) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.