Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

WorksheetFunction funciones de Excel en VBA


(adsbygoogle = window.adsbygoogle || []).push({});

WorksheetFunction tiene por fin realizar casi todas las funciones que trae incorporadas Excel, como se dijo ejecuta casi todas no todas, pero que funciones de Excel se pueden ejecutar desde VBA, esto es aclarado en el listado que se incorporó en otro post, el mencionado listado se muestran todas las funciones que trae incorporada Excel y que pueden ser ejecutadas desde VBA con WorksheetFunction; en Funciones de Excel, descripción y traducción al inglés y otros idiomas, encontrarás listadas y con una breve explicación sobre que hace cada función, este post tiene como fin contestar preguntas como: que es WorksheetFunction, para que sirve WorksheetFunction, como se utiliza o usa WorksheetFunction.
Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

Si quieres aprender o profundizar  sobre el manejo de hojas de Excel, te recomiendo que leas un excelente libro sobre Excel el que te ayudará manejar las planillas de cálculo, debes hacer click acá, si quieres un libro sobre Excel, en inglés, entonces debes hacer click acá. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores curso que he visto en internet.
Como se dijo en Visual Basic o VBA las funciones de hoja de cálculo de Microsoft Excel, pueden ejecutarse mediante el objeto WorksheetFunction, es decir se usa como contenedor de las funciones de Excel, permitiendo WorksheetFunction poder llamar las funciones desde Visual Basic. 

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

A continuación voy a mostrar algunos ejemplos sobre como se usa WorksheetFunction, previo se hace una pequeña explicación y se muestra como se usa el código.

El siguiente código suma un rango de datos y luego asigna la variable a la celda C1:

sum1 = Application.WorksheetFunction.Sum(Range(«C» & pf & «:C» & uf)) 
Cells(1, 3) = sum1

Para determinar el promedio  de un rango se utiliza «Average»:

prom = Application.WorksheetFunction.Average(Range(«D» & pf & «:D» & uf))
Cells(1, 4) = prom

Si se requiere contar datos se debe aplicar la WorksheetFunction de la siguiente manera:

cta = Application.WorksheetFunction.Count(Range(«D» & pf & «:D» & uf))
Cells(1, 5) = cta

Con el código que se muestra a continuación se hace un reemplazo de caracteres:

rep = Application.WorksheetFunction.Replace(rep, 1, 3, «778»)
Cells(3, 1) = rep

Para obtener el máximo valor de una columna o rango se debe usar la función «Max»:

Max = Application.WorksheetFunction.Max(Range(«D» & pf & «:D» & uf))
Cells(1, 5) = Max

Quizás unas de las funciones más usadas es BuscarV, se debe usar de la siguiente forma:

Bus = Application.WorksheetFunction.VLookup(Cells(1, «E»), Range(«D» & pf & «:F» & uf), 3)
Cells(1, 5) = Bus

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


(adsbygoogle = window.adsbygoogle || []).push({});

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Entradas relacionadas

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (33)

oooqpkomvtogzixcxyyzlshcarfore

Responder

I’m the business owner of JustCBD Store company (justcbdstore.com) and I’m presently planning to expand my wholesale side of business. I really hope that someone at targetdomain share some guidance 🙂 I thought that the best way to accomplish this would be to reach out to vape shops and cbd stores. I was really hoping if anybody could recommend a dependable web-site where I can get CBD Shops B2B Leads I am currently checking out creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not exactly sure which one would be the most ideal selection and would appreciate any support on this. Or would it be easier for me to scrape my own leads? Suggestions?

Responder

права для обращения в суд с заявлением и это процедура банкротство юридических лиц тема знакома каждому

Responder

Very nice post. I absolutely appreciate this website. Thanks!

Responder

Oh my goodness! Incredible article dude! Thank you, However I am encountering troubles with your RSS. I don’t know the reason why I can’t join it. Is there anybody else having identical RSS issues? Anybody who knows the solution will you kindly respond? Thanx!!

Responder

The very next time I read a blog, Hopefully it does not disappoint me as much as this particular one. I mean, I know it was my choice to read, but I genuinely believed you would have something interesting to say. All I hear is a bunch of whining about something you could possibly fix if you weren’t too busy seeking attention.

Responder

An impressive share! I have just forwarded this onto a friend who has been conducting a little homework on this. And he in fact ordered me lunch because I stumbled upon it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanx for spending some time to discuss this issue here on your site.

Responder

After I initially left a comment I appear to have clicked on the -Notify me when new comments are added- checkbox and now each time a comment is added I recieve four emails with the exact same comment. Is there an easy method you are able to remove me from that service? Thanks!

Responder

Very good post. I am going through some of these issues as well..

Responder

Howdy! I just would like to offer you a huge thumbs up for the great info you’ve got here on this post. I am coming back to your website for more soon.

Responder

I need to to thank you for this great read!! I certainly loved every little bit of it. I have got you saved as a favorite to check out new things you post…

Responder

Excellent article. I’m facing many of these issues as well..

Responder

I was very pleased to find this web site. I want to to thank you for ones time due to this fantastic read!! I definitely really liked every little bit of it and i also have you bookmarked to check out new things in your site.

Responder

paetlbfndmcpilvoyxwvuexqrnlejk

Responder

I couldn’t refrain from commenting. Very well written!

Responder

This is a topic which is near to my heart… Take care! Where are your contact details though?

Responder

I’m extremely pleased to uncover this web site. I wanted to thank you for ones time just for this fantastic read!! I definitely savored every part of it and i also have you book marked to check out new stuff in your web site.

Responder

There’s definately a great deal to learn about this topic. I like all the points you have made.

Responder

Wonderful article! We will be linking to this great content on our site. Keep up the great writing.

Responder

I was able to find good info from your articles.

Responder

Howdy, I do believe your website may be having web browser compatibility problems. Whenever I take a look at your web site in Safari, it looks fine however, when opening in I.E., it has some overlapping issues. I just wanted to provide you with a quick heads up! Other than that, excellent site!

Responder

I’m amazed, I must say. Seldom do I come across a blog that’s equally educative and interesting, and without a doubt, you’ve hit the nail on the head. The issue is something too few people are speaking intelligently about. I’m very happy that I found this in my search for something relating to this.

Responder

Good info. Lucky me I found your website by chance (stumbleupon). I’ve saved as a favorite for later!

Responder

Good blog you have here.. It’s difficult to find high quality writing like yours these days. I truly appreciate people like you! Take care!!

Responder

Pretty! This was an incredibly wonderful article. Many thanks for supplying this information.

Responder

An outstanding share! I’ve just forwarded this onto a friend who had been doing a little research on this. And he actually ordered me lunch because I found it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanks for spending time to discuss this matter here on your web site.

Responder

Can I simply just say what a comfort to discover someone who truly knows what they’re talking about over the internet. You actually understand how to bring a problem to light and make it important. More people ought to look at this and understand this side of the story. It’s surprising you aren’t more popular since you most certainly have the gift.

Responder

This is a very good tip particularly to those new to the blogosphere. Simple but very precise information… Many thanks for sharing this one. A must read article!

Responder

I want to to thank you for this very good read!! I certainly loved every little bit of it. I have you saved as a favorite to check out new stuff you post…

Responder

Having read this I believed it was really informative. I appreciate you spending some time and energy to put this informative article together. I once again find myself personally spending way too much time both reading and commenting. But so what, it was still worth it!

Responder

I’m extremely pleased to uncover this site. I need to to thank you for your time due to this fantastic read!! I definitely savored every little bit of it and i also have you bookmarked to look at new things on your web site.

Responder

This site was… how do I say it? Relevant!! Finally I have found something that helped me. Thanks!

Responder

This is a great tip especially to those new to the blogosphere. Short but very accurate info… Thanks for sharing this one. A must read article!

Responder