Tot i que VBA ofereix un assortiment decent de funcions integrades, és possible que no sempre trobeu exactament el que necessiteu. Afortunadament, també podeu utilitzar la majoria de les funcions del full de treball d'Excel als vostres procediments VBA. Les úniques funcions del full de treball que no podeu utilitzar són les que tenen una funció VBA equivalent. Per exemple, no podeu utilitzar la funció RAND d'Excel (que genera un nombre aleatori) perquè VBA té una funció equivalent: Rnd.
VBA fa que les funcions del full de treball d'Excel estiguin disponibles a través de l'objecte WorksheetFunction, que es troba a l'objecte Aplicació. Aquí teniu un exemple de com podeu utilitzar la funció SUMA d'Excel en una instrucció VBA:
Total = Application.WorksheetFunction.SUM(Range(“A1:A12”))
Podeu ometre la part Aplicació o la part WorksheetFunction de l'expressió. En qualsevol cas, VBA esbrina què estàs fent. En altres paraules, aquestes tres expressions funcionen totes exactament igual:
Total = Application.WorksheetFunction.SUM(Range(“A1:A12”))
Total = WorksheetFunction.SUM(Rang(“A1:A12”))
Total = Aplicació.SUMA(Rang(“A1:A12”))
La meva preferència personal és utilitzar la part WorksheetFunction només per deixar perfectament clar que el codi utilitza una funció d'Excel.
Exemples de funcions de full de treball
Aquí, descobrireu com utilitzar les funcions del full de treball a les vostres expressions VBA.
Trobar el valor màxim en un interval
Aquí teniu un exemple que mostra com utilitzar la funció de full de treball MAX d'Excel en un procediment VBA. Aquest procediment mostra el valor màxim a la columna A del full de treball actiu:
Utilitzant una funció de full de treball al vostre codi VBA.
Sub ShowMax()
Atenua TheMax com a doble
TheMax = WorksheetFunction.MAX(Range ("A:A"))
MsgBox TheMax
End Sub
Podeu utilitzar la funció MIN per obtenir el valor més petit d'un interval. I com és d'esperar, podeu utilitzar altres funcions del full de treball d'una manera similar. Per exemple, podeu utilitzar la funció GRAN per determinar el k -èsimo valor més gran d'un interval. La següent expressió ho demostra:
Segon més alt = WorksheetFunction.LARGE(Range(“A:A”),2)
Observeu que la funció GRAN utilitza dos arguments. El segon argument representa la k- èsima part — 2, en aquest cas (el segon valor més gran).
Càlcul del pagament d'una hipoteca
L'exemple següent utilitza la funció de full de treball PMT per calcular el pagament d'una hipoteca. S'utilitzen tres variables per emmagatzemar les dades que es passen a la funció Pmt com a arguments. Un quadre de missatge mostra el pagament calculat.
Sub PmtCalc()
Dim IntRate com a doble
Dim LoanAmt com a doble
Períodes tènues tan llargs
IntRate = 0,0625/12
Períodes = 30 * 12
LoanAmt = 150000
MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)
End Sub
Com mostra la declaració següent, també podeu inserir els valors directament com a arguments de funció:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Tanmateix, l'ús de variables per emmagatzemar els paràmetres fa que el codi sigui més fàcil de llegir i modificar, si cal.
Utilitzant una funció de cerca
L'exemple següent utilitza les funcions InputBox i MsgBox de VBA, a més de la funció VLOOKUP d'Excel. Demana un número de peça i després obté el preu d'una taula de cerca. A continuació, l'interval A1:B13 s'anomena PriceList.
La gamma, anomenada PriceList, conté preus de peces.
Sub GetPrice()
Dim PartNum com a variant
Preu reduït com a doble
PartNum = InputBox("Introduïu el número de peça")
Fulls ("Preus").Activar
Preu = WorksheetFunction.VLOOKUP(PartNum, Range(“PriceList”), 2, False)
MsgBox PartNum & " costos " & Preu
End Sub
A continuació es mostra com funciona el procediment GetPrice:
-
La funció InputBox de VBA demana a l'usuari un número de peça.
-
El número de part que introdueix l'usuari s'assigna a la variable PartNum.
-
La següent declaració activa el full de treball Preus, per si encara no és el full actiu.
-
El codi utilitza la funció VLOOKUP per trobar el número de peça a la taula.
-
Tingueu en compte que els arguments que feu servir en aquesta declaració són els mateixos que els que utilitzaríeu amb la funció en una fórmula de full de treball. Aquesta instrucció assigna el resultat de la funció a la variable Preu.
-
El codi mostra el preu de la peça mitjançant la funció MsgBox.
Aquest procediment no té cap tractament d'errors i falla estrepitosamente si introduïu un número de peça inexistent. (Proveu-ho.) Si aquesta fos una aplicació real que s'utilitza en un negoci real, voldríeu afegir algunes declaracions que tracten els errors amb més gràcia.
Introducció de funcions del full de treball
No podeu utilitzar el quadre de diàleg Funció d'enganxar d'Excel per inserir una funció de full de treball en un mòdul VBA. En lloc d'això, introduïu aquestes funcions de la manera antiga: a mà. Tanmateix, podeu utilitzar el quadre de diàleg Enganxa la funció per identificar la funció que voleu utilitzar i conèixer els seus arguments.
També podeu aprofitar l'opció de membres de la llista automàtica de VBE, que mostra una llista desplegable de totes les funcions del full de treball. Només heu d'escriure Application.WorksheetFunction , seguit d'un punt. A continuació, veuràs una llista de les funcions que pots utilitzar. Si aquesta funció no funciona, trieu l'ordre Eines → Opcions de VBE, feu clic a la pestanya Editor i marqueu l'opció Membres de la llista automàtica.
Obtenció d'una llista de funcions del full de treball que podeu utilitzar al vostre codi VBA.
Més informació sobre l'ús de les funcions del full de treball
Els nouvinguts a VBA sovint confonen les funcions integrades de VBA i les funcions del llibre de treball d'Excel. Una bona regla per recordar és que VBA no intenta reinventar la roda. En la seva majoria, VBA no duplica les funcions del full de treball d'Excel.
Per a la majoria de les funcions del full de treball que no estan disponibles com a mètodes de l'objecte WorksheetFunction, podeu utilitzar un operador o funció integrat de VBA equivalent. Per exemple, la funció de full de treball MOD no està disponible a l'objecte WorksheetFunction perquè VBA té un equivalent: el seu operador Mod integrat.
Linia inferior? Si necessiteu utilitzar una funció, primer determineu si VBA té alguna cosa que s'adapti a les vostres necessitats. Si no, consulteu les funcions del full de treball. Si tot falla, és possible que pugueu escriure una funció personalitzada mitjançant VBA.