Añadir los valores de la hoja de trabajo anterior o siguiente con VBA en Microsoft Excel
En este artículo, crearemos una función personalizada para calcular la suma de valores en el rango de diferentes hojas.
Crearemos tres funciones personalizadas diferentes que calcularán la suma de valores en el rango de otras hojas, según el nombre de la hoja, el número de hoja y la referencia de hoja de la hoja activa.
Los datos brutos de este ejemplo constan de cuatro hojas. Cada hoja tiene detalles del número de artículos vendidos por cada miembro del equipo en el rango B7 a B16 para un día en particular. Hemos tomado datos para el 1 ^ st ^ día, 2 ^ nd ^, 3 ^ rd ^ y el último día del mes.
Explicación lógica
En este ejemplo, hemos creado tres funciones personalizadas que son: -. SumBySheetName. SumBySheetNumber. SumByOffsetSheetNumber. SumBySheetName: – Esta función personalizada toma el rango y el nombre de la hoja como parámetros de entrada. Devuelve la suma de valores en el rango definido del nombre de hoja definido.
-
SumBySheetNumber: – Esta función personalizada toma el rango y el número de hoja como parámetros de entrada. Devuelve la suma de los valores en el rango definido para el número de hoja definido.
-
SumByOffsetSheetNumber: – Esta función toma el rango y el desplazamiento de la hoja activa como parámetros de entrada. Devuelve la suma de los valores en el rango definido de la hoja definida por el índice de compensación.
Cada hoja del libro muestra el total de artículos vendidos el primer día, el día anterior, el día actual, el día siguiente y el último día.
Si observa de cerca la función personalizada, encontrará que las cuatro hojas utilizan la misma función personalizada con los mismos parámetros.
La función personalizada “SumBySheetNumber” se utiliza para calcular el total de artículos vendidos el primer día, ya que la primera hoja siempre llevará los datos del primer día del mes.
La función personalizada “SumByOffsetSheetNumber” se utiliza para calcular el total de artículos vendidos en el día anterior, actual y siguiente, ya que las hojas del libro de trabajo están ordenadas en secuencia de fechas.
La función personalizada «SumBySheetName» se utiliza para calcular el total de artículos vendidos el último día del mes, ya que el nombre de la hoja del último día siempre será «LastDayOfMonth».
Explicación del código
WorksheetFunction.Sum El objeto WorksheetFunction se utiliza para acceder a funciones de hoja de Excel desde Visual Basic. Hemos utilizado la función SUM de la hoja de Excel para agregar los valores en el rango.
InputRange.Address El código anterior se utiliza para devolver la dirección del rango especificado por el objeto de rango «InputRange».
Siga a continuación el código
Option Explicit Function SumBySheetName(InputRange As Range, Optional SheetName As Variant) 'Declaring variable Dim SheetIn As Worksheet 'Checking whether value is assigned to optional parameter 'IF optional parameter is missing then generate sum for range in the active sheet If IsMissing(SheetName) Then SumBySheetName = WorksheetFunction.Sum(InputRange) Exit Function End If 'Looping through sheets in the worksheets collection For Each SheetIn In Worksheets 'Checking whether specified sheet exist in the worksheets collection If SheetName = SheetIn.Name Then 'Calculating the sum for specified range of the specified sheet SumBySheetName = WorksheetFunction.Sum(Worksheets(SheetName).Range(InputRange.Address)) Exit Function End If Next SumBySheetName = "Specified Sheet doesn't exist in the workbook" End Function Function SumBySheetNumber(InputRange As Range, Optional SheetIndex As Integer = 0) 'Checking whether optional parameter is missing or sheet index is assigned zero value If SheetIndex = 0 Then SumBySheetNumber = WorksheetFunction.Sum(InputRange) 'Checking whether sheet index is greater than number of sheets in the workbook ElseIf SheetIndex > Sheets.Count Then SumBySheetNumber = "Sheet Index is greater than number of sheets in the workbook" Else SumBySheetNumber = WorksheetFunction.Sum(Worksheets(SheetIndex).Range(InputRange.Address)) End If End Function Function SumByOffsetSheetNumber(InputRange As Range, Optional SheetOffset As Integer = 0) 'Error Handling On Error GoTo Last 'Calculating sum SumByOffsetSheetNumber = WorksheetFunction.Sum(Worksheets(InputRange.Worksheet.Index + _ SheetOffset).Range(InputRange.Address)) Exit Function Last: SumByOffsetSheetNumber = "Referred Sheet not exists" End Function
Si te gustó este blog, compártelo con tus amigos en Facebook y Facebook.
Nos encantaría saber de usted, háganos saber cómo podemos mejorar nuestro trabajo y hacerlo mejor para usted. Escríbanos a [email protected]