74606482-CODIGOS-VBA

CODIGOS VBA

IMPRIMIR CELDAS SELECIONADAS, HOJAS Y

LIBROS

A veces vale la pena incluir una función para que el usuario del libro pueda imprimir

cómodamente (y seguro), por ejemplo desde un botón. De esta manera mantenemos el

control sobre lo que se va a imprimir, y de los parámetros que se mandarán a la impresora.

Abajo encontrarás sencillos ejemplos de la aplicación de cómo imprimir desde Excel VBA:

Selección

Hojas seleccionadas

Todas las hojas

Primero ajustamos los parámetros de la impresión, el apartado PageSetup. Luego se

imprime con el método PrintOut.

Marcado VBA para imprimir las celdas seleccionadas

Sub Imprimir_seleccion()

'preparar la hoja para la impresión

With ActiveSheet.PageSetup

.PrintArea = ""

.Orientation = xlPortrait 'xlLandscape

.PaperSize = xlPaperA4 'formato A4

.BlackAndWhite = False 'incluir colores o no

.FitToPagesWide = 1 'reduce el tamaño de la hoja (ancho)

.FitToPagesTall = 1 'reduce el tamaño de la hoja (alto)

.CenterHorizontally = False 'centrar horizontalmente

.CenterVertically = False 'centrar verticalmente

End With

'imprimir las celdas seleccionadas (1 copia)

ActiveWindow.Selection.PrintOut copies:=1, collate:=True

End Sub

Código VBA para imprimir las hojas seleccionadas




.PrintArea = ""








End With


ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True

End Sub

Código VBA para imprimir todas las hojas del libro



'bucle que repasa todas las hojas

For Each Worksheet In ActiveWorkbook.Sheets


.PrintArea = ""








End With

Next Worksheet 'fin del bucle


ActiveWorkbook.PrintOut From:=1, To:=1, copies:=1, collate:=True

End Sub

Alineación izquierda/derecha

Sub Ajustar_izq_der()

If Selection.HorizontalAlignment = xlRight Then

Selection.HorizontalAlignment = xlLeft

Else

Selection.HorizontalAlignment = xlRight

End If

End Sub

Convertir pesetas a euro

Sub Convertir()

Set Area = Selection

For Each Cell In Area

z = Round(Cell / 166.386, 2)

Cell.Value = z

Cell.NumberFormat = "#,##0.00"

Next Cell

End Sub

Pegar formato

Sub PegarFormato()

Selection.PasteSpecial Paste:=xlFormats

Application.CutCopyMode = False

End Sub

Pegar valor

Sub PegarValor()

Selection.PasteSpecial Paste:=xlValues


End Sub

Dos decimales

Sub DosDec()

Dim Area As Range

Set Area = Selection

For Each Cell In Area

z = Round(Cell, 2)

Cell.Value = z

Cell.NumberFormat = "#,##0.00"

Next Cell

End Sub

Separador de miles

Sub SeparadorMil()

Dim Area As Range

Set Area = SelectionIf Area.NumberFormat = "#,##0" Then

Area.NumberFormat = "#,##0.00"

Else

Selection.NumberFormat = "#,##0"

End If

End Sub

Suprimir filas vacías

Sub SuprimirFilasVacias()

LastRow = ActiveSheet.UsedRange.Row - 1 + _

ActiveSheet.UsedRange.Rows.Count

For r = LastRow To 1 Step -1

If Application.CountA(Rows(r)) = 0 Then

Rows(r).Delete

End If

Next r

End Sub

Autofilter

Sub FilterExcel()

Selection.AutoFilter

End Sub

Grids (Líneas de división)

Sub Grids()

If ActiveWindow.DisplayGridlines = True Then

ActiveWindow.DisplayGridlines = False

Else

ActiveWindow.DisplayGridlines = True

End If

End Sub

Cambiar A1 a RC (columnas tiene números en vez de

letras)

Sub Rc()

If Application.ReferenceStyle = xlR1C1 Then

Application.ReferenceStyle = xlA1

Else

Application.ReferenceStyle = xlR1C1

End If

End Sub

Modificar paleta de colores

Sub ModificarPaleta()

ActiveWindow.Zoom = 75

ActiveWorkbook.Colors(44) = RGB(236, 235, 194)



End Sub

Mostrar todas las hojas

Sub MostrarHojas()

Set wsHoja = Worksheets

For Each wsHoja In ActiveWorkbook.Worksheets

If wsHoja.Visible = False Then

wsHoja.Visible = True

End If

Next wsHoja

End Sub

WAIT – mostrar un formulario VBA durante un

tiempo predeterminado

Con el método WAIT puedes hacer que un formulario se cierre después de un tiempo

determinado. Esto puede ser útil para presentar información al usuario („La importación ha

terminado con éxito‟, „El archivo está guardado‟ etc). El truco está en utilizar el método

WAIT de VBA.

Procedimiento

Crea un formulario „frmMensaje„ con el mensaje que quieres que aparezca. Añade este

código al formulario. El ejemplo nos dice que la rutina se va a esperar („Wait„) hasta la

hora

Now + TimeValue("00:00:04")

Es decir la hora actual más 4 segundos.

Sub MostrarFormulario()

Private Sub UserForm_Activate()

Application.Wait Now + TimeValue("00:00:04")

frmMensaje.Hide

End Sub

Luego, para mostrar el formulario en cualquier parte de tu programa, aplicas este código.

frmMensaje.Show

En el ejemplo de abajo, el formulario se mostrará al abrir el libro (ponemos el código en el

contenedor de código VBA „EsteLibro„.

Private Sub Workbook_Open()

frmMensaje.Show

End Sub

Scrollrow – Imagen fija a un costado de la

pantalla

Si queremos que el usuario vea una imagen en el costado superior izquierdo de la pantalla,

esté donde esté en la hoja, podemos aplicar el siguiente código.

Trata de utilizar la propiedad SCROLLROW, que nos da la celda superior izquierdo de la

pantalla visible. Y SCROLLCOLUMN de la columna por supuesto. Luego insertamos un

comentario, en la cual ponemos una imagen y un poco de texto.

El resultado será algo como

El código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim strRuta As String

Dim intRowActual as Double, intColumnActual As Double

Dim intRow as Double, intColumn As Double

'ruta a imagen

strRuta = "C:\imagen.jpg"

'fila/columna actual

intRowActual = ActiveCell.Row

intColumnActual = ActiveCell.Column

'fila/columna de scrollRow

intRow = ActiveWindow.ScrollRow + 1

intColumn = ActiveWindow.ScrollColumn

'insertamos comentario

Cells.ClearComments

With Cells(intRow, intColumn)

.AddComment

.Comment.Text Text:="Hola"

.Comment.Visible = True

End With

'añadimos imagen al comentario

Cells(intRow, intColumn).Comment.Shape.Select True

Selection.ShapeRange.Fill.UserPicture strRuta

'aparcamos en celda actual

Cells(intRowActual, intColumnActual).Select

End Sub

Sumar rangos variables con VBA Excel

Sumar un rango en Excel es fácil. Sumar un rango expresado por una variable en VBA es

un poco más complicado (pero sigue siendo fácil).

No siempre se sabe de antemano que celdas formarán parte del rango a sumar. Entonces

tenemos que expresar el rango de forma variable.

En Excel es fácil sumar este rango mediante una sencilla fórmula. Pero VBA no contiene

ninguna función igual. Entonces hay que hacer que VBA utilice las funciones de Excel.

Utilizar funciones Excel en VBA

Tenemos un rango varSuma, el rango a sumar. Para sumar las celdas de este rango tenemos

que llamar a la función SUM de Excel.

Application.WorksheetFunction.Sum(varSuma)

De esta manera puedes aplicar cualquier fórmula de Excel en VBA, con tal de que

empieces la línea de código con

Application.WorksheetFunction...

Nuestro ejemplo

En este ejemplo el rango que nos interesa sumar son los valores correspondientes a “BB”,

es decir C8:C13.

Escribir la suma (en celda)

'el rango a sumar

varSuma = Range(Cells(8, 3), Cells(13, 3))

'sumar el rango

Cells(1, 1) = Application.WorksheetFunction.Sum(varSuma)

<h2>Escribir la suma (variable)</h2>

<div class="cb">

<pre>

'el rango a sumar

varSuma = Range(Cells(8, 3), Cells(13, 3))

'sumar el rango

SUMA = Application.WorksheetFunction.Sum(varSuma)

Introducción a los formatos personalizados

¿Qué son los formatos personlizados?

A veces los formatos predefinidos de Excel no dan – hace falta aplicar un formato

personalizado a tus celdas.

Con bastante frecuencia quiero que celdas que contienen fechas tengan el formato AAAA-

MM-DD (año-mes-día, 2004-01-17) como algunos sistemas informáticos trabajan con este

formato.

Pero hay un problema; este formato no siempre está presente en todos los ordenadores.

Entonces se crea su propio formato AAAA-MM-DD.

Crear un formato personlizado

El diálogo de Formatos se abre tecleando Ctrl+1 o desde el menú contextual (click derecho

en celda, Formato de celdas, Número, Personalizada. A la derecha, en la caja de texto Tipo

se entra el formato.

Unos ejemplos sencillos

Empezamos con unos ejemplos para que veas el efecto del formato.

Formato Valor celda Output celda

AAAA-MM-DD 17-ene-04 2004-01-17

MMMM 17-ene-04 Enero

DDD 17-ene-04 Sab

[Azul][>0,00]0%;[Rojo]-[<0,00]0%;- -0,45 -45%

El ejemplo arriba: Azul/formato porcentaje sin decimales si mayor que 0,00. Rojo si menor que

0,00.

‘-’ si equivale a 0,00.

#.##0,00 1125100012,2587 1.125.100.012,26

#.##0,00 52,21 52,21

0000,00 52,21 0052,21

#” “??/?? =7/2 3 1/2

##0,0E+0 100000 100,0E+3

Números

Aquí decides la presentación de números (valores). Intenta encontrar formatos que sean

“fáciles” y eficaces. Un formato extendido en contabilidad es:

Número: Decimales = 2, Separador de miles = Sí

En vez de presentar un dinero así: 125050000,7 debes aplicar un formato como:

125.050.000,69

Como puedes ver en el primer ejemplo, la celda contiene un valor que termina en ,69.

Luego el formato dice cuántos decimales se van a mostrar.

Hay unos atajos rápidos para formatear celdas.

CTRL + SHIFT + ! = 2 decimales/separador miles

CTRL + SHIFT + % = porcentaje

CTRL + SHIFT + # = formato fecha

Algunos códigos útiles de Macros de Excel + Libro Macros

Estoy armando un listadito de códigos muy útiles para usar cuando estás desarrollando una macro. Los voy compartiendo y actualizando a medida que voy avanzando. A fines del año pasado empecé a hacer varias macros en el laburo, y la verdad que ahora quiero hacer todo con macros. Hace mucho tiempo que uso Excel, y la verdad que las macros le dan un giro muy groso, y eso que apenas estoy aprendiendo!!! Además subo y comparto un libro en .pdf que está muy bueno, con el cual estoy aprendiendo "formalmente", y no solo por prueba-error. Lamentablemente el libro está en inglés, así que no va a ser útil para todos. Pero como para escribir macros hay que usar comandos en inglés supongo que muchos lo van a aprovechar. Una aclaración que no está de más, soy un simple usuario de Excel, del palo de la contabilidad y los números, que sabe algo de inglés, y que tiene una curiosidad enorme.Si alguien anda por un camino parecido al mio, ánimo que muchas veces parece que no damos pie con bola, pero al final sale! El Grabador de macros que trae Excel... A ver, alguien que estudio o que ya tiene un manejo avanzado de macros va a decir que es una porquería, y algo de razón tiene. Pero a favor tengo que decir que me ha resultado muy útil para empezar, y que me sigue siendo útil cuando quiero hacer algo que no sé con qué código será. Una vez que aprendes a leerlo y corregirlo el grabador de macros es una herramienta más. Bueno, aquí van los primeros códigos

Codigo

No mostrar el trabajo de la Macro

-No se ve lo que va ejecutando-

Application.ScreenUpdating = False

No calcular automaticamente

-Es igual a configurar en las opciones de Excel "Calcular=Manual"-

Application.Calculation = xlManual

Pegar formulas

-Es lo mismo a "Pegado especial -> Fórmulas"-

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks_

:=False, Transpose:=False


Pegar valores

-Es lo mismo a "Pegado especial -> Valores"-

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks_

:=False, Transpose:=False


Ocultar hoja

-En este caso oculta Hoja1-

Worksheets("Hoja1" ).Hidden True

Mostrar el trabajo de la Macro

-Deshace lo que hicimos con ocultar-

Application.ScreenUpdating = True

Guardar como y cerrar

-Aquí va a abrir dos cuadros pidiendo al usuario que ingrese una nombre primero y después una

carpeta, guarda y cierra-

Dim NombreLibro As String

Dim RutaGuardado As String

NombreLibro = InputBox(""Guardar como:"" ) & "".xlsm""

RutaGuardado = InputBox(""Guardar en la carpeta:"", , ""C:Mis documentos"" )

Dim NombreyRuta As String

NombreyRuta = RutaGuardado & NombreLibro

ActiveWorkbook.SaveAs Filename:=NombreyRuta

ActiveWorkbook.Close False

Ocultar varias hojas

-Lo mismo que ocultar una, pero para varias usando un Array-

Sheets(Array(""Hoja1"", ""Hoja2"", ""Hoja3"" ).Select

ActiveWindow.SelectedSheets.Visible = False

Encontrar Ultima fila

-Este código es muy bueno porque encuentra la última fila, cosa que puede variar entre un reporte

y otro-

Dim ULTIMAFILA as String

ULTIMAFILA = Cells(Rows.Count, 1).End(xlUp).Row

Filtrar y copiar registros unicos

-En este caso filtra el rango A1:A4 y copia valores únicos a G1

Range(""A1:A4"" ).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(""G1"" _

), Unique:=True

Programar una función de redondeo

Redondear un número es una tarea común que podemos hacer con la fórmula de Excel

REDONDEAR. En éste post nos proponemos programar una función en VBA, mediante una macro, que produzca el redondeo.

Redondear a cero decimales

Comencemos redondeando a cero decimales. Así el número 3,14159 (¡me suena este número!) se puede redondear a cero decimales como 3. Y el número 2,7182818 (y éste ¿me debería sonar?) se puede redondear a cero decimales como 3.

http://trucosexcel.blogspot.com/2011/07/programar-una-funcion-de-redondeo.html

Código:

Function Redondealo(n As Double) As Double

If n >= Int(n) + 0.5 Then

Redondealo = Int(n) + 1

Else

Redondealo = Int(n)

End If

End Function

El primer número visto es pi que se obtiene con la función de Excel =PI()

El segundo número visto es e, que es la base de los logaritmos neperianos. También podemos decir que es la exponencial de 1. En Excel se puede calcular con la siguiente expresión: =EXP(1) Si aplicamos la función definida por el usuario Redondealo obtendremos en ambos casos el valor de 3. =Redondealo(PI()) =Redondealo(EXP(1))

Redondear invocando la función de Excel

Todas las funciones disponibles en Excel se pueden invocar mediante una macro. El código requiere que la función a la que llamemos esté escrita en inglés. Para obtener un listado de la funciones en inglés puede consultar el siguiente enlace. http://trucosexcel.blogspot.com/2008/10/glosario-traduccin-de-funciones.html La función REDONDEAR en español equivale a la función ROUND en inglés. La expresión que hemos de utilizar en VBA para invocar a esta función es la siguiente: Application.WorksheetFunction.round(número, precisión)

http://trucosexcel.blogspot.com/2008/10/glosario-traduccin-de-funciones.html

http://2.bp.blogspot.com/-ayiswUmUhPA/TiXcOmwpQaI/AAAAAAAADK4/AySatBEYxWY/s1600/SNAG-0012.png

http://3.bp.blogspot.com/-27STYR4aZdA/TibbXIMCeTI/AAAAAAAADLk/DUqPVGh-Mgs/s1600/SNAG-0016.png

http://2.bp.blogspot.com/-ayiswUmUhPA/TiXcOmwpQaI/AAAAAAAADK4/AySatBEYxWY/s1600/SNAG-0012.png

http://3.bp.blogspot.com/-27STYR4aZdA/TibbXIMCeTI/AAAAAAAADLk/DUqPVGh-Mgs/s1600/SNAG-0016.png

Código:

Function SuRedondeo(numero, precision)

SuRedondeo = Application.WorksheetFunction.Round(numero, precision)

End Function

Programemos la función

Con la función Redondealo fuimos capaces de redondear a cero decimales. En esta ocasión vamos a introducir la variable p que recoge la precisión del redondeo. La función se llama MiRedondeo y el código es el siguiente.

Código:

Function MiRedondeo(n As Double, p As Single) As Double

If n * 10 ^ p >= Int(n * 10 ^ p) + 0.5 Then

MiRedondeo = Int(n * 10 ^ p + 1) / (10 ^ p)

Else

MiRedondeo = Int(n * 10 ^ p) / (10 ^ p)

End If

End Function

Podemos ver el resultado aplicado a Pi y a e.

http://3.bp.blogspot.com/-xEJYoCNJr9g/TiXuwKjUPKI/AAAAAAAADLE/eJFjMJV4Fys/s1600/SNAG-0014.png

Public Function redn(nnum As Double, nxx As Single) As Double

cal0 = Int(nnum)

cal1 = dec(nnum)

cal2 = cal1 * 10 ^ nxx

cal3 = Int(cal2)

cal4 = cal2 * 10 ^ (nxx - 1)

cal5 = Int(cal4)

cal6 = dec(cal5 / (10 ^ (nxx - 1)))

cal7 = cal6 / (10 ^ nxx)

comp = 5 / (10 ^ (nxx + 1))

If cal7 >= comp Then

cal8 = cal3 + 1

Else

http://2.bp.blogspot.com/-G3Kfp090Ut4/TiXx0ulcJkI/AAAAAAAADLI/Ho8o8EW5xWg/s1600/SNAG-0015.png

cal8 = cal3

End If

redn = cal0 + (cal8 / (10 ^ nxx))

End Function

74606482-CODIGOS-VBA

Documents

Transcript of 74606482-CODIGOS-VBA