Suposem que tenim un informe dissenyat per executar-se diàriament i genera una sèrie de fitxers de resultats amb diferents dades automàticament. Aquests fitxers es guarden a directoris específics que estan definits dins la macro d’exportació.
La realitat ens diu que al final aquest informe s’executa més d’un cop al dia i el que es vol és no tornar a generar els fitxers de resultats sinó actualitzar-los. De fet, a la primera execució es poden haver generat fitxers o no. Com sabem si un fitxer existeix per obrir-lo i actualitzar-lo?
Per fer-ho farem servir un objecte que anirem veient sovint quan volem accedir a les funcions de sistema, el FileSystemObject. Aquest objecte ens permet operar amb Windows fent servir escrits de sistema.

Exemple:
Sub obrir_fitxer_si_existeix()
Dim Arxiu As String
Arxiu = "C:\test.xls"
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(Arxiu) Then
    Workbooks.Open FileName:=Arxiu
Else
    Workbooks.Add
End If

End Sub
Aquest exemple obre l’arxiu test.xls si existeix o en crea un de nou.

No, no parlarem de seleccions catalanes. Un cop introduït el concepte de llenguatge orientat a objectes parlarem d’un objecte molt important, sobre tot per les primeres macros. Normalment voldrem seleccionar alguna cosa del llibre d’Excel per treballar-hi, avui parlarem de com fer seleccions.

A Excel podem selecionar diversos objectes: fulls, rangs, gràfics, elements dels gràfics o formes. Com que és un mètode, sempre el posarem al final de la línia de codi.

Seleccionar un full:
Sheets("[Nom del full]").Select
Seleccionar un rang:
Range("[Rang]").select
Seleccionar una serie d’un gràfic:
ActiveChart.SeriesCollection([Num Serie]).Select

Quan treballem amb fulls podem fer referència als fulls de dues maneres: pel nom o per l’índex. Si ens referim pel nom el posarem entre cometes: Sheets("Nom"); si ens referim per l’índex posarem la posició que ocupa al llibre d’Excel: Sheets (1). Compte al canviar d’ordre els fulls si es fa servir amb aquest mètode.

Si Treballem amb cel·les ho farem amb l’objecte Range o amb l’objecte Cells.
Amb Cells.Select seleccionem les cel·les de tota la fulla, amb Cells(2,3) celeccionem la cel·la C2 (en format R1C1).
Amb Range podem selecionar una cel·la ("A1") o un rang ("A1:B23") tal com ho faríem dins una fòrmula. També podem referir-nos a grups de cel·les ("A1","B3","H4:H5"). un objecte Range pot estar compost per altres objectes range, aquest mètode és útil quan volem seleccionar un rang de cel·les que canvia o que agafa una referència segons una altra cel·la.

Range("A1", Range("A1").End(xlDown)).Select
Aquí selecciona un rang des de la cel·la A1 fins avall sigui quina sigui el nombre de cel·les. Aquí fem servir la propietat End que ens permet recòrrer (cap avall en aquest cas) totes les cel·les amb valors situades contiguament (equival a CTRL o Fi + fletxa avall).

Range("C3").End(xlToRight).Offset(0, 1).Select
Aquesta ordre selecciona la cel·la de la fila 3 que hi ha a la primera columna lliure a la dreta de C3. Fem servir la propietat Offset(X,Y) que agafant una cel·la de referència ens permet desplaçar-nos X files i Y columnes.

VBA és un Visual Basic (en versió Microsoft) adaptat a fer-se servir amb les aplicacions office. El VBA és un llenguatge orientat a objecte, és a dir, hi ha uns objectes que representen algo i nosaltres modifiquem aquests objectes. Exemples d’objectes a Excel són l’aplicació (Application), un altre el llibre amb el que treballem (Workbook),un rang de cel·les (Range("A1:C30")) o una selecció (Selection).

Aquest objectes els podem manipular mitjançant mètodes com un Copy o propietats com Name. Un mètode ens permet transformar l’objecte o treballar-hi mentre que una propietat normalment modificarà algun aspecte del propi objecte. L’editor de VBA ens ajudarà en trobar quins mètodes o propietats es poden aplicar a un objecte en concret, quan posem un punt redera d’un objecte l’editor ens mostrarà la llista de possibilitats que podem escriure. Si trobem la opció que cerquem la podem seleccionar amb un [Tab].

Exemple:

ThisWorkbook.Sheets(1).Range("A1:C3").Select
Selection.Copy
Sheets(2).Range("A1").PasteSpecial xlValues

El que fa: Selecciona el rang “A1:C3″ del primer full del llibre on està escrita la macro. Copia la selecció. I finalment l’enganxa en valors a la cel·la A1 del segon full del llibre actiu. Fixeu-vos que a la primera línia es crea l’objecte “selecció” que a la segona se li aplica el mètode de copiar.

Per veure quins objectes hi ha disponibles i la seva ajuda ho podeu fer mitjançant l’explorador d’objectes.Botó de l'examinador d'objectes

La llista de objectes, mètodes i propietats disponibles depenen de les referències carregades a l’editor. Les referències carregades a l’Excel normalment són les del VBA, les comunes d’Office, les especiífiques d’Excel i les d’automatització OLE. Si, per exemple volem manipular un Arxiu PowerPoint des d’Excel hauriem de fer referència a la llibreria de PowerPoint per poder tenir els objectes, mètodes i propietats disponibles. D’això en parlarem més endavant.

Segur que més d’un cop heu escrit una d’aquelles fòrmules espectacularment grans a la barra de fòrmules de l’Excel. Al cap d’uns quants caràcters i parèntesis no sabeu on sou i per veure què fa la fòrmula o on falla us podeu estar més de deu minuts perduts entre cinc o sis parèntesis. Doncs bé, hi ha una forma molt senzilla de fer les fòrmules més llegibles.

Quan escriviu una fòrmula podeu fer-ho en diverses línies per a que sigui és fàcil de llegir. Premeu [Alt] + [Enter] i fareu una nova línia dins la barra de fòrmules que no afectarà al comportament de la fòrmula.

Fòrmula més llegible

Si feu això escrivint text et text resultant a la cel·la si que tindrà diverses línies.

Després de les vacances d’estiu tractem una de les funcionalitats d’Excel més potents i segurament la més desconeguda: les fòrmules matricials.
La potència de les fòrmules matricials és que ens permeten analitzar matrius de dades aplicant filtres complexos per columnes. La fòrmula més simple i propera és el “sumar.si” que actua com a fòrmula matricial amb una sola condició. Una típica fòrmula matricial es aplicar una serie de condicions per obtenir una suma final, però es pot fer amb altres funcions com “promedio”, “media”, “max” o “min”.

Fòrmula matricial amb dues condicions:

{=SUMA(SI(B7:B23="A4";SI(D7:D23="negre";E7:E23)))}

Una fòrmula matricial es marca posant-la dins de claudàtors. Aquesta es llegeix de la següent manera: suma els valors del rang E7:E23 que, a la mateixa fila, tinguin “A4″ a la columna B i “negre” a la columna D. S’escriu posant la fòrmula que es vol avaluar i dins hi posem condicionals simples (“SI”). Els condicionals i operacions lògiques es poden anar aniuant unes a dins de les altres fins fer fòrmules extremadament complicades.

Atenció! Un cop escrita la fòrmula no s’introdueix amb un simple “Enter” ja que no s’avaluaria, hem de fer “Maj.”+”Ctrl”+”Enter” i veurem els claudàtors emmarcant la fòrmula.

Aquest tipus de fòrmula s’ha de fer servir amb moderació, ja que com més compliquem la fòrmula, analitzem matrius molt grans o posem moltes fòrmules matricials al mateix llibre d’Excel veurem com el rendiment del programa disminueix molt.

Arxiu d’exemple i exercicis (9 kb)

No, les macros no són la solució als problemes que ens podem trobar amb l’Excel, només són una altra eina. Molts cops podem solucionar els problemes puntuals que tinguem amb una bona fòrmula o conjunt de fòrmules.

Les fòrmules ténen diversos avantatges respecte les macros. Són visibles, fàcilment auditables, es calculen automàticament i molts cops són més senzilles. També hem de pensar que l’Excel està optimitzat per-lo servir amb les fòrmules i procediments que té incorporats, si ens n’inventem de nous per fer el mateix probablement siguin més lents.

Tot i així les fòrmules ténen un problema molt evident: costen de llegir. Quan tenim fòrmules bastant complexes amb funcions aniuades dins altres funcions molts cops ens perdem degut a la quantitat d’opcions disponibles. Més endavant donarem consells per solucionar aquest petit inconvenient.

Molts de vosaltres m’heu demanat algun cop que us ensenyi a fer macros, d’altres no voleu que faci macros en alguns fulls d’Excel perquè no veieu què fan i no ho podeu controlar. En aquesta serie d’articles intentaré que aprengueu a dominar les macros i us hi sentiu a gust.

Hi ha diverses raons per decidir-nos a fer macros. Una feina repetitiva en el temps, una feina puntual però amb un alt grau de repeticions o una feina que necessiti obligatòriament una macro per a poder-la fer.

Per aprendre a fer macros el que normalment es fa és gravar una serie d’accions. Això ho fem mitjançant el menú Herramientas > Macros > Gravar nueva macro… Això ens dona una base, però no sempre és suficient, molts cops haurem de passar per l’editor de VBA botó Botó VBA (Alt + F11). Una macro és una rutina de codi de Visual Basic for Applications (és un Visual Basic simplificat), per això quan haguem de modificar una macro ho farem amb Visual Basic.

La finestra d’edició de VBA és aquesta:

Finestra VBA

Aquí podreu veure el codi que s’ha escrit al gravar la macro. A l’esquerra es mostren els projectes (llibres Excel) i a la dreta hi ha el codi que pot estar als mòduls de codi, a les fulles o al llibre.

Si observeu detingudament el codi veureu que és bastant senzill d’entendre. Tot i així hi ha dos problemes que apareixen sovint. En gravar una macro totes les ordres intermitjes que fem amb el cursor (com desplaçar la pàgina) també hi queden gravades i botenim un excés de codi inútil. Això fa que el codi “brut” sembli liós. L’altre problema és que veurem que algunes coses no queden gravades i llavors haurem d’escriure el codi nosaltres mateixos.

En resum, gairebé sempre haurem de passar per l’editor de VBA per tenir unes macros potents, flexibles i ben escrites; l’ideal es que es puguin reutilitzar i siguin entenedores.

Potser algun cop voldrem una macro que es pugui executar només un sol cop. O potser voldrem que sota unes determinades condicions s’elimini una macro. Doncs bé, aquest codi us pot ajudar.

Sub EliminaMacro(macro as string, mòdul as string)

Dim liDeb, NbLi

With ThisWorkbook.VBProject.VBComponents(mòdul).CodeModule
liDeb = .ProcStartLine(macro, 0)
NbLi = .ProcCountLines(macro, 0)
.DeleteLines liDeb, NbLi
End With

End Sub

La crida de la macro es fa des d’una altra macro:
EliminaMacro "[nom de la macro]" , "[nom del mòdul]"

Aquesta macro elimina només la macro especificada dins el mòdul especificat, deixa totes les altres línies de codi intactes. Com és una subrutina amb variables la macro queda oculta i no es pot executar des del menú macro>macros… (Alt+F8). També podem eliminar les variables i posar el nom del mòdul i de la macro manualment als llocs especificats, d’aquesta manera la macro es faria visible.

Follow

Get every new post delivered to your Inbox.