Ejemplos macros vba Excel

Una pausa en el avance como
para reforzar un poquito lo avanzado.

Sub nombre_padre

'obtener el nombre del padre
'de activesheet

MsgBox ActiveSheet.Parent.FullName

End sub

'recordando el tema del modelo de objetos de Excel
'aqui un claro ejemplo de la jerarquia de objetos de
que les comente:

fijense primero nombramos al objeto "ActiveSheet" y colocamos el punto luego la palabra "Parent"(Padre)
(entiendase entonces que al decir "Activesheet.Parent"
estamos hablando del objeto inmediato superior a a la
hoja activa que seria logicamente"El libro"(ActiveWorkbook)
en este caso entonces veran que es lo mismo
colocar "ActiveSheet.Parent" o "ActiveWorkbook"
y de esa forma tambien podremos referenciar objetos haciendo referencia al objeto padre para el objeto correspondiente del modelo de objetos de Excel.

y finalmente colocamos la, propiedad "FullName"

esto nos daria exactamente el mismo resultado

sub nombre_libro

MsgBox ActiveWorkbook.FullName

end sub

-----------

obtener nombre del libro
************************

' ojo :...pero por ejemplo si el libro activo no se guardo
' ninguna vez todavia nos devolvera "Book1" o "Libro"'
dependiendo del idioma de tu Excel .

'y si tu libro ya lo habias guardado anteriormente
' devolvera ademas del nombre el "path" o ruta de
tu libro activo .

de esta manera : D:\Documents and Settings\Administrador\Escritorio\WorkPlan_jes1.xls
end sub


'Redondeando activecell
'---------------------------

Sub redondear_cells()

s = Round(ActiveCell, 2)

ActiveCell.Value = s

ActiveCell.NumberFormat = "#,##0.00"

End Sub


'Formateando(fuente)
'------------------------


Sub cambiar_formato()

Cells.Select 'seleccionamos todas las celdas
Selection.Font.Name = "Arial Black" 'asignamos la fuente
Selection.Font.Size = 13 'el tamaño de la fuente

'en el ejemplo seleccionamos todas las celdas
'pero puedes comentar la linea -->'Cells.Select'
y seleccionar cualquier rango de celdas'incluso
celdas discontinuas

End Sub


Cambiar entre estilos de referencia

de A1 a RC y viceversa

Application.ReferenceStyle = xlA1Application.ReferenceStyle = xlR1C1
Application.ReferenceStyle = xlA1

'Pegar los formatos
'--------------
'primero copia alguna celda que tenga el formato
'que deseas copiar'luego colocate sobre la celda o celdas
'ahora corre la macro


Sub Paste_formats()

Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False

End Sub

'Copiar y Pegar valores
'------------------------


Sub PegarValor()

'de igual manera colocate
'sobre una celda con formula

Selection.Copy

'ahora sobre el rango donde deseas
'pegar solo como valor

Selection.PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

End Sub

Se me paso mencionar algunas de las combinaciones de teclas para el editor de codigo pero aqui hay unas cuantas que falto mencionar.

Combinaciones de teclas para manejar el editor
-------------------------------------------------------
de codigo para vba (por ahora las más necesarias)
----------------------------------------------------------

para Abrir el editor simplemente presiona ----> Alt + F11

Estando ya sobre ele entorno del editor vba

PRESIONA ---> F2 (Examinador de objetos )

ir a cuadro de dialogo buscar ---> F3

ir a ventana de propiedades ----> F4

Para Ejecutar una macro -------> F5

Correr el codigo linea por linea (las instrucciones)----> F8

Respuesta(Insertar en un datatable un rango de casillas del excel)

Hoy he recibido un correo con la sgte consulta:

- Programa .net que lea un excel y actualice la BD
- ¿Como insertar en un datatable un rango de casillas de Excel?

*Doctores , no es la intención si es que este ejemplo sale un poco del orden del avance de temas del blog
Pero creo que es importante igualmente
compartir la mayor cantidad de conocimientos posibles.

No lo he hecho antes, pero creo que si procedes de esta forma, funcionará:

1.-Convierte tu rango de celdas a un archivo xml físico.
2.-Luego, el datatable acepta como origen de datos un archivo XML, entonces cargas el datatable con el xml que creaste...algo así:DataSet ds = new DataSet();ds.ReadXml("myXmlFile.xml");return ds.Tables(0);

Procedimientos y Funciones 0.1

Ahora ya seguarmente con una mejor referencia
de como trabajar en Excel con el modelo de objetos.

Volviendo a la definicion inicial del Vba(Visual Basic para Aplicaciones)

"Es el lenguaje con el que podras manipular los objetos de Excel y crear tus propias aplicaciones(claro mas adelante) por ahora macros utiles."

*Por eso era muy importante primero verificar el modelo de objetos de Excel.

Los procedimientos
---------------------
Conjunto de instrucciones que realiza una accion.
*esta encerrado pot las palabras reservadas
Sub y End Sub

ejemplo
----------
Sub crear_libro

'instruccion1
'instruccion2
'instruccion3

End Sub

Las funciones
---------------
A diferencia de un procedimiento
una función devolvera valores
*esta encerrado po las palabras reservadas
"Function" "End Function"

ejemplo
--------

Function MostrarTriple(numero)

'devolvera el triple de un número

MostrarTriple=numero*3

Msgbox MostrarTriple

End Function

ejemplo
--------
'el primer pasos sera crear un procedimiento(mostrar_triple)
'que llame a la [funcion "convertirTriple"]
'luego le pasamos el numero que deseamos que convierta
'debes colocarte ahora sobre el procedimiento mostrar_triple'
y presionar f8 hasta que obtengas recuperar el valor de la funcion

'pega el procedimiento y la funcion en un modulo

Sub mostrar_triple()

'aqui le pasamso el numero 55 a la funcion
'y simplemente mostramos en un mensaje
' el valor que nos devuelve la funcion

MsgBox convertirTriple(55)

End Sub


Function convertirTriple(numero)

'devolvera el triple de un número
'en este caso el numero="55"'y
'luego lo multiplica por 3

convertirTriple = numero * 3

End Function

El Modelo de objetos de Excel

Antes de continuar con los temas debemos tenera algunas consideraciones para continuar trabajando con objetos en el Excel (Excel vba)

Importante no olvidar lo sgte :

- Los objetos cuentan con "propiedades" y "métodos".

-Una propiedad es un atributo del objeto que define una característica del mismo

Un Ejemplo, la propiedad "Row" del objeto "ActiveCell" nos indica en qué fila de la hoja está la celda activa.

Macro sobre la propiedad Row de la celda

Sub celdax()

MsgBox ActiveCell.Row 'devuelve la fila de la celda activa
MsgBox ActiveCell.Column 'devuelve la columna de la celda activa

End Sub

Para poder acceder a una propiedad de un objeto se utiliza el nombre del objeto, seguido de un punto y el nombre de la propiedad.

Como se vio justamente en ejemplo de la propiedad Row para la celda

ActiveCell.Row

donde el objeto es la celda activa : ActiveCell

la propiedad es : Row

MsgBox ActiveCell.Row

Los "métodos" son las acciones que podemos llevar a cabo sobre el objeto.

Para llamar a un método se utiliza el nombre del objeto, un punto y el nombre del método

Ejemplo, el método "ActiveCell.Clear" borra el contenido de la celda activa

como te habras dado cuenta en la mayoria ejemplos hemos empleado instrucciones
muy similares a esta.

Hemos colocado Msgbox para mostrar una característica de un objeto

y de esa manera obtener el valor de una propiedad determinada.

MsgBox ActiveCell.Row

Va ser importante entonces notar la diferencia entre propiedades y metodos de l0s objetos en el Excel

Hasta aqui las propiedades nos permiten:(obtener características de los objetos )
y los metodos (llevar acciones sobre un objeto tal y como se vio en el ejemplo la accion ejecutada fue borrar el contenido de la celda "ActiveCell.Clear").

*La totalidad de acciones que puede realizar un usuario sobre un objeto(Metodos del objeto)
*Valores que se podran obtener o asignar(propiedades del objeto)


*Tampoco debes olvidar que para trabajar sobre un objeto en Excel no es necesario seleccionarlo

'En esta macro de ejmplo podras comprobarlo

Sub celda_conseleccion()

'seleccionamos el rango A1

Range("A1").Select

'asignamos a la seleccion el color'con el index=5

ActiveCell.Interior.ColorIndex = 5

'pero las lineas iniciales no seran necesarias
'bastara con trabajar directamente sobre el'rango "A1"


Range("A1").Interior.ColorIndex = 8


End Sub

Metodos del Objeto Range

A) Activate:
--------
Activar el Rango seleccionado.

B)Clear
-----
Borrar los valores y formatos del Rango.

C)ClearContents
--------------
Solo borra los valores del rango manteniendo el formato intacto.

D)Delete
-------
Eliminar el Rango indicado.

E)Calculate
---------
Recalcular las formulas en un rango indicado.

'Ahora una macro de ejmplo de los metodos del objeto range

'Excel VBA Macro de ejemplo aplicacion de metodos del objeto range

Sub ejemplo_range_met()

Range("A1").Activate 'Activa el Rango
Range("A1").Delete 'eliminar el rango
Range("A1:A100").Calculate 'recalculo en el range
Range("A2").Clear 'borra contenido y formatos
Range("A2").ClearContents 'solo contenidos

End Sub

Objeto Range del Excel- Excel VBA MAcros Ejemplo propiedades Objeto Range

Un concepto adeacuado y util del obejor range del Excel por ahora seria pensar en un objeto Range como una celda, una fila, una columna o una selección de celdas

*Un objeto range estara contenido dentro de un objeto woorsheet

Propiedades del Objeto Range
-----------------------------

a)Address
---------
Indica la dirección del rango seleccionado.

ejemplo
--------
MsgBox ActiveCell.Address 'devolvera la direccion dela celda activa

b)Count
------:
Cuenta el número de objetos de un conjunto.

ejemplo
--------

MsgBox Selection.Cells.Count 'devolvera la cantidad de celdas seleccionadas

c)Name
------:
Nos servira pra asignar o recuperar el nombre de un rango

Selection.Name = "prueba_vba"

d)Value
------
Devuelve el valor de un rango

MsgBox Range("A1").Value 'el valor del rango de la celda A1

e)formula
--------
MsgBox Range("A1").Formula

Objeto Worksheet de Excel

El objeto Worksheets representa a las Hojas de un Libro de Excel

*La colección de WorkSheets es formada por las hojas de un libro.

* Cada miembro de la colección Sheets es un objeto Worksheet(hoja)


Metodos del objeto Worksheets
--------------------------------

Delete
-------
Permite Eliminar una Hoja


Por ejemplo Eliminar la hoja Abril

Sub delete_hoja()

Application.DisplayAlerts = False 'asignamos a falso para evitar
' las alertas de Excel al eliminar hojas

Sheets("Abril").Delete 'eliminando hoja="Abril"

Application.DisplayAlerts = True 'devolvemos el valor por defecto "verdadero"


End Sub



Add
------
Crear un nueva hoja



Sub new_sht()


Sheets.Add 'agregar una hoja

Sheets.Add after:=Sheets(2) 'agregar un hoja al libro y colocarla despues de la hoja2

End Sub



Move
------
Permite Mover una Hoja

Sub new_sht()

'movemos la hoja enero despues de report

Worksheets("enero").Move After:=Sheets("report")

End Sub




Select
------
Permite Seleccionar una Hoja



sub selex

Sheets(2).Select 'seleccionamos la hoja 2 del libro

end sub



Copy
------
Permite Copiar una Hoja



Sub new_sht_copy()

'creamos auna copia de la hoja enero
'y la colcamos despues de la segunda hoja del libro


Worksheets("enero").Copy After:=Sheets(2)

End Sub


'ahora un ejemplo de 2 propiedades del objeto worksheet

Sub ejemplo_worksheet_prop()

'macro de ejemplo 2 propiedades
'muy comunes del objeto worksheet
'te devuelve el nombre de la hoja
'por ahora innecesario aparentemente
'pera notaras la utilidad de esta
'propiedad a la hora de hacer compraciones
'o busquedas entre las hojas del libro

MsgBox ActiveSheet.Name


'esto te devolvera el indice de
'la hoja activa(Índice de la Hoja)
'es decir si es la hoja 1 o 2 o 3,etc del libro

MsgBox ActiveSheet.Index


End Sub