excel-vba => Mejores Prácticas VBA

Observaciones

Todos los conocemos, pero estas prácticas son mucho menos obvias para alguien que comienza a programar en VBA.

SIEMPRE use "Opción explícita"

En la ventana del editor de VBA, en el menú Herramientas, seleccione "Opciones":

Luego, en la pestaña "Editor", asegúrese de que la opción "Requerir declaración variable" esté marcada:

La selección de esta opción colocará automáticamente Option Explicit en la parte superior de cada módulo VBA.

Nota pequeña: Esto es cierto para los módulos, módulos de clase, etc. que no se han abierto hasta ahora. Por lo tanto, si ya vio el código de la Sheet1 antes de activar la opción "Requerir Declaración Variable", ¡ Option Explicit no se agregará!

Option Explicit requiere que cada variable se defina antes de su uso, por ejemplo, con una instrucción Dim . Sin la Option Explicit habilitada, el compilador VBA asumirá que cualquier palabra no reconocida es una nueva variable del tipo Variant , lo que causa errores extremadamente difíciles de detectar relacionados con errores tipográficos. Con Option Explicit habilitado, cualquier palabra no reconocida provocará que se genere un error de compilación, que indica la línea ofensiva.

Ejemplo:

Si ejecuta el siguiente código:

Sub Test()
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

Recibirás el siguiente mensaje:

Ha cometido un error al escribir myvariable lugar de my_variable , luego el cuadro de mensaje muestra una variable vacía. Si usa Option Explicit , este error no es posible porque recibirá un mensaje de error de compilación que indica el problema.

Ahora si agrega la declaración correcta:

Sub Test()
  Dim my_variable As Integer
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

Obtendrá un mensaje de error que indica precisamente el error con myvariable :

Nota sobre las opciones explícitas y las matrices ( declarar una matriz dinámica ):
Puede usar la declaración ReDim para declarar una matriz implícitamente dentro de un procedimiento.
Tenga cuidado de no escribir mal el nombre de la matriz cuando use la instrucción ReDim
Incluso si la instrucción Option Explicit se incluye en el módulo, se creará una nueva matriz
Dim arr() as Long

ReDim ar() 'creates new array "ar" - "ReDim ar()" acts like "Dim ar()"

Trabajar con matrices, no con rangos

Office Blog - Excel VBA Rendimiento de las mejores prácticas de codificación

A menudo, el mejor rendimiento se logra evitando el uso de Range tanto como sea posible. En este ejemplo, leemos en un objeto de Range completo en una matriz, cuadramos cada número en la matriz y luego devolvemos la matriz al Range . Esto accede al Range solo dos veces, mientras que un bucle lo haría 20 veces para las lecturas / escrituras.

Option Explicit
Sub WorkWithArrayExample()
    
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array

For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows.
  For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns.
    DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2
  Next Icol
Next Irow
ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once
    
End Sub

Se pueden encontrar más consejos e información con ejemplos cronometrados en UBAs VBA eficientes de escritura de Charles Williams (Parte 1) y otros artículos de la serie .

Use constantes de VB cuando estén disponibles

If MsgBox("Click OK") = vbOK Then

se puede utilizar en lugar de

If MsgBox("Click OK") = 1 Then

con el fin de mejorar la legibilidad.

Utilice el buscador de objetos para encontrar las constantes de VB disponibles. Ver → Object Browser o F2 desde VB Editor.

Entrar en clase para buscar

Ver miembros disponibles

Utilizar nombres descriptivos de variables.

Los nombres descriptivos y la estructura en su código ayudan a hacer comentarios innecesarios

Dim ductWidth  As Double
Dim ductHeight As Double
Dim ductArea   As Double

ductArea = ductWidth * ductHeight

es mejor que

Dim a, w, h

a = w * h

Esto es especialmente útil cuando está copiando datos de un lugar a otro, ya sea una celda, rango, hoja de trabajo o libro de trabajo. Ayúdate usando nombres como estos:

Dim myWB As Workbook
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim srcData As Range
Dim destData As Range

Set myWB = ActiveWorkbook
Set srcWS = myWB.Sheets("Sheet1")
Set destWS = myWB.Sheets("Sheet2")
Set srcData = srcWS.Range("A1:A10")
Set destData = destWS.Range("B11:B20")
destData = srcData

Si declara múltiples variables en una línea, asegúrese de especificar un tipo para cada variable como:

Dim ductWidth As Double, ductHeight As Double, ductArea As Double

Lo siguiente solo declarará la última variable y las primeras seguirán siendo Variant :

Dim ductWidth, ductHeight, ductArea As Double

Manejo de errores

El buen manejo de errores evita que los usuarios finales vean los errores de tiempo de ejecución de VBA y ayuda al desarrollador a diagnosticar y corregir errores fácilmente.

Existen tres métodos principales de manejo de errores en VBA, dos de los cuales deben evitarse para los programas distribuidos a menos que el código lo requiera específicamente.

On Error GoTo 0 'Avoid using

On Error Resume Next 'Avoid using

Prefiero usar:

On Error GoTo <line> 'Prefer using

En Error GoTo 0

Si no se establece ningún manejo de errores en su código, On Error GoTo 0 es el controlador de errores predeterminado. En este modo, cualquier error de tiempo de ejecución iniciará el mensaje de error típico de VBA, permitiéndole finalizar el código o ingresar al modo de debug , identificando la fuente. Al escribir código, este método es el más simple y útil, pero siempre debe evitarse para el código que se distribuye a los usuarios finales, ya que este método es muy desagradable y difícil de entender para los usuarios finales.

En error reanudar siguiente

On Error Resume Next hará que VBA ignore cualquier error que se lance en el tiempo de ejecución para todas las líneas que siguen a la llamada de error hasta que se haya cambiado el controlador de errores. En casos muy específicos, esta línea puede ser útil, pero debe evitarse fuera de estos casos. Por ejemplo, al iniciar un programa separado desde una macro de Excel, la llamada On Error Resume Next puede ser útil si no está seguro de si el programa ya está abierto:

'In this example, we open an instance of Powerpoint using the On Error Resume Next call
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

'Open PPT if not running, otherwise select active instance
On Error Resume Next
Set PPApp = GetObject(, "PowerPoint.Application")
On Error GoTo ErrHandler
If PPApp Is Nothing Then
    'Open PowerPoint
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Visible = True
End If

Si no hubiésemos utilizado la llamada On Error Resume Next y la aplicación de Powerpoint no estuviera abierta, el método GetObject generaría un error. Por lo tanto, On Error Resume Next fue necesario para evitar crear dos instancias de la aplicación.

Nota: también es una buena práctica restablecer inmediatamente el controlador de errores tan pronto como ya no necesite la llamada On Error Resume Next

On Error GoTo <line>

Este método de manejo de errores se recomienda para todo el código que se distribuye a otros usuarios. Esto le permite al programador controlar exactamente cómo VBA maneja un error al enviar el código a la línea especificada. La etiqueta se puede rellenar con cualquier cadena (incluidas las cadenas numéricas) y enviará el código a la cadena correspondiente que va seguida de dos puntos. Se pueden utilizar múltiples bloques de manejo de errores haciendo diferentes llamadas de On Error GoTo <line> . La subrutina a continuación muestra la sintaxis de una llamada On Error GoTo <line> .

Nota: es esencial que la línea de Exit Sub se coloque sobre el primer controlador de errores y antes de cada controlador de errores posterior para evitar que el código progrese naturalmente en el bloque sin que se llame un error. Por lo tanto, es una práctica recomendada para la función y la legibilidad colocar controladores de errores al final de un bloque de código.

Sub YourMethodName()
    On Error GoTo errorHandler
    ' Insert code here
    On Error GoTo secondErrorHandler

    Exit Sub 'The exit sub line is essential, as the code will otherwise
             'continue running into the error handling block, likely causing an error

errorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
        VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
    Exit Sub

secondErrorHandler:
    If Err.Number = 424 Then 'Object not found error (purely for illustration)
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Desctription
        Application.ScreenUpdating = True
        Application.EnableEvents = True   
        Exit Sub
    End If      
    Exit Sub

End Sub

Si sale de su método con su código de manejo de errores, asegúrese de limpiar:

Deshacer todo lo que está parcialmente completado
Cerrar archivos
Restablecer actualización de pantalla
Restablecer el modo de cálculo
Restablecer eventos
Restablecer el puntero del mouse
Llame al método de descarga en instancias de objetos, que persisten después del End Sub
Restablecer barra de estado

Documenta tu trabajo

Es una buena práctica documentar su trabajo para su uso posterior, especialmente si está codificando para una carga de trabajo dinámica. Los buenos comentarios deben explicar por qué el código está haciendo algo, no lo que está haciendo el código.

Function Bonus(EmployeeTitle as String) as Double
    If EmployeeTitle = "Sales" Then
        Bonus = 0    'Sales representatives receive commission instead of a bonus
    Else
        Bonus = .10
    End If
End Function

Si su código es tan oscuro que requiere comentarios para explicar lo que está haciendo, considere volver a escribirlo para que sea más claro en lugar de explicarlo a través de comentarios. Por ejemplo, en lugar de:

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    
    'Boolean values can be evaluated as an integer, -1 for True, 0 for False.
    'This is used here to adjust the range from 5 to 7 rows if including weekends.
    Range("A1:A" & 5 - (IncludeWeekends * 2)).Copy
    Range("B1").PasteSpecial
End Sub

Aclare el código para que sea más fácil de seguir, como por ejemplo:

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    Dim DaysinWeek as Integer
    
    If IncludeWeekends Then
        DaysinWeek = 7
    Else
        DaysinWeek = 5
    End If
    Range("A1:A" & DaysinWeek).Copy
    Range("B1").PasteSpecial
End Sub

Desactivar propiedades durante la ejecución de macro

Es la mejor práctica en cualquier lenguaje de programación para evitar una optimización prematura. Sin embargo, si las pruebas revelan que su código se está ejecutando muy lentamente, puede ganar algo de velocidad desactivando algunas de las propiedades de la aplicación mientras se ejecuta. Agregue este código a un módulo estándar:

Public Sub SpeedUp( _
    SpeedUpOn As Boolean, _
    Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
    With Application
        If SpeedUpOn Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayStatusBar = False 'in case you are not showing any messages
            ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
        Else
            .ScreenUpdating = True
            .Calculation = xlCalc
            .EnableEvents = True
            .DisplayStatusBar = True
            ActiveSheet.DisplayPageBreaks = True
        End If
    End With
End Sub

Más información en Office Blog: Excel VBA Performance Coding Besting Practices

Y solo llámelo al principio y al final de las macros:

Public Sub SomeMacro
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    SpeedUp True

    'code here ...

    'by giving the second argument the initial "calculation" state is restored
    'otherwise it is set to 'xlCalculationAutomatic'
    SpeedUp False, xlCalc
End Sub

Si bien estas pueden considerarse en gran parte "mejoras" para los procedimientos regulares de Public Sub , deshabilitar el manejo de eventos con Application.EnableEvents = False debe considerar obligatorio para las macros de eventos privados Worksheet_Change y Workbook_SheetChange que cambian los valores en una o más hojas de trabajo. Si no se deshabilitan los activadores de eventos, la macro del evento se ejecutará de forma recursiva sobre sí misma cuando un valor cambie y puede llevar a un libro "congelado". Recuerde volver a activar los eventos antes de abandonar la macro de eventos, posiblemente a través de un controlador de errores de "salida segura".

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        
        'code that may change a value on the worksheet goes here
        
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Una advertencia: aunque la desactivación de esta configuración mejorará el tiempo de ejecución, puede hacer que la depuración de la aplicación sea mucho más difícil. Si su código no funciona correctamente, comente la llamada SpeedUp True hasta que descubra el problema.

Esto es particularmente importante si está escribiendo en celdas en una hoja de trabajo y luego leyendo los resultados calculados de las funciones de la hoja de trabajo, ya que xlCalculationManual evita que se xlCalculationManual el libro de trabajo. Para evitar esto sin deshabilitar SpeedUp , es posible que desee incluir Application.Calculate para ejecutar un cálculo en puntos específicos.

NOTA: ya que estas son propiedades de la Application sí misma, debe asegurarse de que estén habilitadas nuevamente antes de que salga su macro. Esto hace que sea particularmente importante usar controladores de errores y evitar múltiples puntos de salida (es decir, End o Unload Me ).

Con manejo de errores:

Public Sub SomeMacro()
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    On Error GoTo Handler
    SpeedUp True
    
    'code here ...
    i = 1 / 0
CleanExit:
    SpeedUp False, xlCalc
    Exit Sub
Handler:
    'handle error
    Resume CleanExit
End Sub

Evite usar ActiveCell o ActiveSheet en Excel

El uso de ActiveCell o ActiveSheet puede ser fuente de errores si (por algún motivo) el código se ejecuta en el lugar equivocado.

ActiveCell.Value = "Hello" 
'will place "Hello" in the cell that is currently selected
Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the currently selected sheet

ActiveSheet.Cells(1, 1).Value = "Hello" 
'will place "Hello" in A1 of the currently selected sheet
Sheets("MySheetName").Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the sheet named "MySheetName"

El uso de Active* puede crear problemas en macros de larga ejecución si su usuario se aburre y hace clic en otra hoja de trabajo o abre otro libro.
Puede crear problemas si su código se abre o crea otro libro de trabajo.
Puede crear problemas si su código utiliza Sheets("MyOtherSheet").Select y ha olvidado en qué hoja estaba antes de comenzar a leer o escribir en él.

Nunca asuma la hoja de trabajo

Incluso cuando todo su trabajo se dirige a una sola hoja de trabajo, es una buena práctica especificar explícitamente la hoja de trabajo en su código. Este hábito hace que sea mucho más fácil expandir su código más adelante, o levantar partes (o todas) de una Sub o Function para ser reutilizadas en otro lugar. Muchos desarrolladores establecen el hábito de (re) usar el mismo nombre de variable local para una hoja de trabajo en su código, lo que hace que la reutilización de ese código sea aún más sencilla.

Como ejemplo, el siguiente código es ambiguo, ¡pero funciona! - mientras el desarrollador no active o cambie a una hoja de trabajo diferente:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Cells(1, 1).Value = Now()  ' don't refer to Cells without a sheet reference!
End Sub

Si la Sheet1 está activa, entonces la celda A1 en la Hoja1 se llenará con la fecha y hora actuales. Pero si el usuario cambia las hojas de trabajo por cualquier motivo, entonces el código actualizará cualquier cosa que la hoja de trabajo esté actualmente activa. La hoja de trabajo de destino es ambigua.

La mejor práctica es identificar siempre a qué hoja de cálculo se refiere su código:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Dim myWB As Workbook
    Set myWB = ThisWorkbook
    Dim timestampSH As Worksheet
    Set timestampSH = myWB.Sheets("Sheet1")
    timestampSH.Cells(1, 1).Value = Now()
End Sub

El código anterior es claro para identificar tanto el libro de trabajo como la hoja de trabajo. Si bien puede parecer una exageración, crear un buen hábito con respecto a las referencias de destino lo salvará de problemas futuros.

Evite utilizar SELECT o ACTIVAR

Es muy raro que alguna vez quiera usar Select o Activate en su código, pero algunos métodos de Excel requieren que se active una hoja de cálculo o libro antes de que funcionen como se espera.

Si recién está comenzando a aprender VBA, a menudo se le sugerirá que grabe sus acciones utilizando la grabadora de macros, luego observe el código. Por ejemplo, registré las acciones tomadas para ingresar un valor en la celda D3 en la Hoja2, y el código de macro se ve así:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet2").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "3.1415"   '(see **note below)
    Range("D4").Select
End Sub

Sin embargo, recuerde que la grabadora de macros crea una línea de código para CADA de sus acciones (de usuario). Esto incluye hacer clic en la pestaña de la hoja de trabajo para seleccionar Hoja2 ( Sheets("Sheet2").Select ), hacer clic en la celda D3 antes de ingresar el valor ( Range("D3").Select ), y usar la tecla Intro (que es efectivamente " seleccionando "la celda debajo de la celda seleccionada actualmente: Range("D4").Select ).

Hay varios problemas con el uso de .Select aquí:

La hoja de trabajo no siempre se especifica. Esto sucede si no cambia las hojas de trabajo durante la grabación, y significa que el código producirá resultados diferentes para diferentes hojas de trabajo activas.
.Select() es lento. Incluso si Application.ScreenUpdating se establece en False , esta es una operación innecesaria para ser procesada.
.Select() es ingobernable. Si Application.ScreenUpdating se deja en True , Excel seleccionará realmente las celdas, la hoja de trabajo, el formulario ... sea lo que sea con lo que esté trabajando. Esto es estresante para los ojos y realmente desagradable de ver.
.Select() activará oyentes. Esto ya es un poco avanzado, pero a menos que se trabaje alrededor, se activarán funciones como Worksheet_SelectionChange() .

El momento de codificar en VBA, todas las acciones de "escribir" (es decir Select estados) ya no son necesarios. Su código puede reducirse a una sola declaración para poner el valor en la celda:

'--- GOOD
ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415

'--- BETTER
Dim myWB      As Workbook
Dim myWS      As Worksheet
Dim myCell    As Range

Set myWB = ThisWorkbook             '*** see NOTE2
Set myWS = myWB.Sheets("Sheet2")
Set myCell = myWS.Range("D3")

myCell.Value = 3.1415

(El ejemplo MEJOR anterior muestra el uso de variables intermedias para separar diferentes partes de la referencia de celda. El ejemplo BUENO siempre funcionará bien, pero puede ser muy engorroso en módulos de código mucho más largos y más difícil de depurar si una de las referencias está mal escrita). )

** NOTA: la grabadora de macros hace muchas suposiciones sobre el tipo de datos que está ingresando, en este caso ingresando un valor de cadena como una fórmula para crear el valor. Su código no tiene que hacer esto y simplemente puede asignar un valor numérico directamente a la celda como se muestra arriba.

** NOTA2: la práctica recomendada es establecer la variable del libro de trabajo local en ThisWorkbook lugar de ActiveWorkbook (a menos que lo necesite explícitamente). La razón es que su macro generalmente necesitará / usará recursos en el libro de trabajo en que se origina el código VBA y NO mirará fuera de ese libro, de nuevo, a menos que usted indique explícitamente que su código funcione con otro libro. Cuando tiene varios libros abiertos en Excel, ActiveWorkbook es el que tiene un enfoque diferente al que se está viendo en su Editor de VBA . Entonces, crees que estás ejecutando en un libro de trabajo cuando realmente estás haciendo referencia a otro. ThisWorkbook refiere al libro de trabajo que contiene el código que se está ejecutando.

Siempre defina y establezca referencias a todos los libros de trabajo y hojas

Cuando trabaje con varios libros de trabajo abiertos, cada uno de los cuales puede tener múltiples hojas, es más seguro definir y establecer referencias a todos los libros de trabajo y hojas.

No confíe en ActiveWorkbook o ActiveSheet ya que pueden ser modificados por el usuario.

El ejemplo de código siguiente muestra cómo copiar un rango de hoja “Raw_Data” en el libro “Data.xlsx” a la hoja de “Refined_Data” en el libro “Results.xlsx”.

El procedimiento también se muestra cómo copiar y pegar sin utilizar la Select método.

Option Explicit

Sub CopyRanges_BetweenShts()

    
    Dim wbSrc                           As Workbook
    Dim wbDest                          As Workbook
    Dim shtCopy                         As Worksheet
    Dim shtPaste                        As Worksheet
    
    ' set reference to all workbooks by name, don't rely on ActiveWorkbook
    Set wbSrc = Workbooks("Data.xlsx")
    Set wbDest = Workbooks("Results.xlsx")
    
    ' set reference to all sheets by name, don't rely on ActiveSheet
    Set shtCopy = wbSrc.Sheet1 '// "Raw_Data" sheet
    Set shtPaste = wbDest.Sheet2 '// "Refined_Data") sheet
    
    ' copy range from "Data" workbook to "Results" workbook without using Select
    shtCopy.Range("A1:C10").Copy _
    Destination:=shtPaste.Range("A1")

End Sub

El objeto WorksheetFunction se ejecuta más rápido que un equivalente UDF

VBA se compila en tiempo de ejecución, lo que tiene un gran impacto negativo en su rendimiento, todo lo que se incorpore será más rápido, intente usarlos.

Como ejemplo, estoy comparando las funciones SUM y COUNTIF, pero puede usarlo para cualquier cosa que pueda resolver con WorkSheetFunctions.

Un primer intento para esos sería recorrer el rango y procesarlo celda por celda (usando un rango):

Sub UseRange()
    Dim rng as Range
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    Total = 0
    CountLessThan01 = 0
    For Each rng in Sheets(1).Range("A1:A100")
        Total = Total + rng.Value2
        If rng.Value < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next rng
    Debug.Print Total & ", " & CountLessThan01
End Sub

Una mejora puede ser almacenar los valores de rango en una matriz y procesar que:

Sub UseArray()
    Dim DataToSummarize As Variant
    Dim i As Long
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
    Total = 0
    CountLessThan01 = 0
    For i = 1 To 100
        Total = Total + DataToSummarize(i, 1)
        If DataToSummarize(i, 1) < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next i
    Debug.Print Total & ", " & CountLessThan01
End Sub

Pero en lugar de escribir cualquier bucle, puede usar la función Application.Worksheetfunction que es muy útil para ejecutar fórmulas simples:

Sub UseWorksheetFunction()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application.WorksheetFunction
        Total = .Sum(Sheets(1).Range("A1:A100"))
        CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

O, para cálculos más complejos, incluso puedes usar Application.Evaluate :

Sub UseEvaluate()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application
        Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
            external:=True) & ")")
        CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

Y finalmente, corriendo por encima de los Subs de 25,000 veces cada uno, aquí está el tiempo promedio (5 pruebas) en milisegundos (por supuesto, será diferente en cada PC, pero comparados entre sí, se comportarán de manera similar):

UseWorksheetFunction: 2156 ms
UseArray: 2219 ms (+ 3%)
UsoEvaluar: 4693 ms (+ 118%)
Rango de utilización: 6530 ms (+ 203%)

Evite volver a proponer los nombres de Propiedades o Métodos como sus variables

En general, no se considera una "mejor práctica" reutilizar los nombres reservados de Propiedades o Métodos como el nombre de sus propios procedimientos y variables.

Forma incorrecta : si bien lo siguiente es (estrictamente hablando) legal, el código de trabajo, la reutilización del método de Búsqueda , así como las propiedades de Fila , Columna y Dirección , pueden causar problemas / conflictos con la ambigüedad del nombre y es simplemente confuso en general.

Option Explicit

Sub find()
    Dim row As Long, column As Long
    Dim find As String, address As Range
    
    find = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set address = .SpecialCells(xlCellTypeLastCell)
        row = .find(what:=find, after:=address).row        '< note .row not capitalized
        column = .find(what:=find, after:=address).column  '< note .column not capitalized
        
        Debug.Print "The first 'something' is in " & .Cells(row, column).address(0, 0)
    End With
End Sub

Buena forma : con todas las palabras reservadas renombradas en aproximaciones cercanas pero únicas de los originales, se han evitado todos los posibles conflictos de nombres.

Option Explicit

Sub myFind()
    Dim rw As Long, col As Long
    Dim wht As String, lastCell As Range
    
    wht = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set lastCell = .SpecialCells(xlCellTypeLastCell)
        rw = .Find(What:=wht, After:=lastCell).Row         '◄ note .Find and .Row
        col = .Find(What:=wht, After:=lastCell).Column     '◄ .Find and .Column
        
        Debug.Print "The first 'something' is in " & .Cells(rw, col).Address(0, 0)
    End With
End Sub

Si bien puede llegar un momento en el que desee reescribir intencionalmente un método o una propiedad estándar según sus propias especificaciones, esas situaciones son pocas y distantes entre sí. En su mayor parte, evite reutilizar nombres reservados para sus propias construcciones.

Modified text is an extract of the original Stack Overflow Documentation

Licenciado bajo CC BY-SA 3.0

No afiliado a Stack Overflow

excel-vba
Mejores Prácticas VBA

Buscar..