Cómo manipular datos en Excel usando VBA

Microsoft Excel es una herramienta poderosa que puede usarse para la manipulación de datos. Para aprovechar al máximo el software, necesita utilizar VBA. Visual Basic para aplicaciones, o VBA, brinda a los usuarios de Excel la capacidad de crear macros, que son funciones personalizadas que ahorran tiempo para la manipulación y el análisis de datos.

Las macros procesan el código VBA para administrar grandes conjuntos de datos que, de lo contrario, requerirían mucho tiempo para modificar. Por ejemplo, con VBA puede crear una macro para dar formato automáticamente a ciertos campos que cumplen con sus criterios determinados.

A continuación se muestra un ejemplo de un script VBA utilizado en Excel:


Sub ConfigureLogic ()
Dim qstEntries
Dim dqstEntries
Dim qstCnt, dqstCnt
qstEntries = Range ("QualifiedEntry"). Count
qst = qstEntries - WorksheetFunction.CountIf (Range ("QualifiedEntry"), "")
ReDim QualifiedEntryText (qst)
'MsgBox (qst)
dqstEntries = Range ("DisQualifiedEntry"). Count
dqst = dqstEntries - WorksheetFunction.CountIf (Range ("DisQualifiedEntry"), "")
ReDim DescalifiedEntryText (dqst)
'MsgBox (dqst)
Para qstCnt = 1 a qst
QualifiedEntryText (qstCnt) = ThisWorkbook.Worksheets ("Qualifiers"). Range ("J" & 8 + qstCnt) .value
'MsgBox (QualifiedEntryText (qstCnt))
registro ("Entrada de entrada calificada configurada #" & qstCnt & "como {" & QualifiedEntryText (qstCnt) & "}")
Siguiente
Para dqstCnt = 1 a dqst
DisqualifiedEntryText (dqstCnt) = ThisWorkbook.Worksheets ("Qualifiers"). Range ("M" & 8 + dqstCnt) .value
'MsgBox (DisqualifiedEntryText (dqstCnt))
registro ("Entrada de entrada descualificada configurada #" & qstCnt & "como {" & DisqualifiedEntryText (dqstCnt) & "}")
Siguiente
includeEntry = ThisWorkbook.Worksheets ("Qualifiers"). Range ("IncludeSibling"). value
'MsgBox (includeEntry)
registro ("Entradas incluidas en la búsqueda -" y includeEntry)
End Sub

Cómo analizar y manipular entradas en una hoja de cálculo

Para poder utilizar VBA para el análisis de datos, deberá verificar la configuración en Excel para la herramienta Developer. Para encontrarlo, ubique la Cinta de Excel y busque la pestaña Desarrollador . Si no se muestra, deberá activarlo en el menú Configuración de Excel.

A continuación, cree una nueva hoja de cálculo y llámela "Calificadores". Usaremos esta hoja para verificar todas las cosas que califican para las selecciones.

A continuación, configure los calificadores en la hoja de acuerdo con el código. Se debe introducir manualmente; cortar y pegar no funcionará.


ThisWorkbook.Worksheets ("Qualifiers"). Range ("J" & 8 + qstCnt) .value

Cómo localizar el rango y construir una matriz

El rango en la función anterior es la celda J9. La función de rango nota un 8; sin embargo, el rango real es 9 porque:

 Para qstCnt = 1 a qst

La declaración anterior comienza en 1, no en 0. Por lo tanto, la lista comienza en 9. En este caso, note (qstCnt = 1).

Para construir una matriz de entradas en la hoja de trabajo de Calificadores, coloque palabras aleatorias en las celdas J9-J13. Una vez que se completan las filas, podemos avanzar en la búsqueda y manipulación de datos en Excel.

 Hojas de cálculo secundarias privadas ()
Dim sheetcount
Dim WS como hoja de trabajo
sheetcount = 0
registro ("***** Starting Scrub *********")
Para cada WS en este libro de trabajo. Hojas de trabajo
sheetcount = sheetcount + 1
Si WS.Name = "Seleccionado" entonces
'Necesito registrar la fecha y la hora en la hoja llamada "Registro"
ActionCnt = ActionCnt + 1
registro ("Hoja de llamada:" & WS.Name)
hoja de cálculo (hoja)
Más
ActionCnt = ActionCnt + 1
registro ("Se saltó sobre la hoja:" & WS.Name)
Terminara si
Siguiente WS
'MsgBox ("terminando")
ActionCnt = ActionCnt + 1
registro ("**** Scrub DONE!")
Application.ScreenUpdating = True
End Sub

Hay un ejemplo de un contador de pestañas de trabajo.

Dim sheetcount

Dim WS como hoja de trabajo

sheetcount = 0

registro ("***** Starting Scrub *********")

Para cada WS en este libro de trabajo. Hojas de trabajo

sheetcount = sheetcount + 1

Después de inicializar el recuento de hojas, configúrelo en 0 para reiniciar el contador.

Logging () es otra subrutina que realiza un seguimiento de todas las acciones para auditar las selecciones.

El siguiente bucle For configura el libro de trabajo activo para el conteo. WS es el inicializado y ThisWorkbook. Las hojas de trabajo es la pestaña activa en el libro. Como no hemos nombrado el libro, este módulo se ejecutará en cualquier libro activo. Si está trabajando en varios libros y tiene activado el incorrecto, intentará ejecutarse en él. Para evitar errores, tome precauciones para nombrar su libro de trabajo específico o solo trabaje en uno a la vez.

Cada vez que se dispara el bucle, se agrega una variable al conteo de hojas para realizar un seguimiento del número de pestañas. Luego nos movemos a:

 Si WS.Name = "Seleccionado" entonces
'Necesito registrar la fecha y la hora en la hoja llamada "Registro"
ActionCnt = ActionCnt + 1
registro ("Hoja de llamada:" & WS.Name)
hoja de cálculo (hoja)
Más
ActionCnt = ActionCnt + 1
registro ("Se saltó sobre la hoja:" & WS.Name)
Terminara si

Aquí, buscamos la pestaña Seleccionado.

Si la variable WS es igual a Seleccionado, la registramos y activamos la subrutina Scrub Sheet. Si la variable WS no es igual a Seleccionado, se registra que esa hoja se omitió y se cuenta la acción. El código anterior es un ejemplo de cómo contar el número de y localizar una pestaña en particular.

La siguiente lista es todos los diferentes métodos que pueden usarse para manipular datos.

¡Que te diviertas!

Cómo contar el número de hojas en un libro de trabajo

TAB oscuro

Para cada ficha en este libro de trabajo. Hojas de trabajo

'alguna rutina aqui

 Tenue celda
cellcount = Cells (ThisWorkbook.Worksheets ("worksheet"). Rows.Count, 1) .End (xlUp) .Row

Filtrar utilizando criterios avanzados

 Rango ("A2: Z99"). Tecla de clasificación1: = Rango ("A5"), orden1: = xlAscendiente, encabezado: = xlNo

Aplicar propiedad autoajustable a una columna

 Columnas ("A: A") EntireColumn.AutoFit

Obtención de valores de otra hoja de trabajo

 nuevo valor oscuro
newvalue = ThisWorkbook.Worksheets ("worksheet"). Range ("F1"). value

Insertando una columna en una hoja de trabajo

 Hilera, columna
Celdas (fila, columna) .EntireColumn.Select
Selección.inserte

Insertar múltiples columnas en una hoja de trabajo

 Dim insertCnt
Hilera, columna
Para insertCnt = 1 a N
ThisWorkbook.Worksheets ("hoja de trabajo"). Seleccione
Celdas (fila, columna) .EntireColumn.Select
Selección.inserte
Siguiente

Agregar un rango con nombre a una hoja en particular

 ThisWorkbook.Worksheets ("worksheet"). Names.Add Name: = "Status", RefersToR1C1: = "= worksheet! C2"

Insertar una fila completa en una hoja de trabajo

 Hilera, columna
Celdas (fila, columna) .EntireRow.Select
Selección.inserte

Copiar una fila entera para pegar

 ActiveSheet.Range ("A1"). EntireRow.Select
Selección.copia

Eliminar una fila completa

 ActiveSheet.Range ("A1"). EntireRow.Select
Selección.Eliminar

Seleccione una hoja particular

 ThisWorkbook.Worksheets ("hoja de trabajo"). Seleccione

Comparar valores de un rango

 Tenue primer rango
Dim Logictest
Logictest = "alguna palabra o valor"
Si (Rango (primer rango) .value = Logictest) entonces
'alguna rutina aqui
Terminara si