Food for thought...

[johnske]

Whenever arguments regarding the relative merits of a worksheet formula solution and employing 'native' (or inbuilt) Excel functions versus a VBA code solution arise, the speed of formula solutions and native functions is often raised by many as a factor to discount the use of a VBA solution.

There are relative merits in all these approaches i.e. formulas, inbuilt functions, and VBA code.

Formulas
The processing speed of formulas may often be quite fast, but one downside is they can very easily bulk up the workbook if there are a lot of rows where formulas are required. Another downside is that if cells containing formulas aren't locked and the worksheet protected there will (inevitably) be errors caused by users typing over or deleting formulas.

Inbuilt functions
The processing speed for inbuilt functions is usually very fast indeed, but one downside is that some manual input is usually required and (without even considering the time required to learn the how, why, and where of native functions) it takes time to find and actuate the function. Another downside is that some of these functions (e.g. AutoFilter) will not work on a worksheet that's been protected to preserve formulas, so the sheet then needs to be unprotected first and protection re-applied afterwards - all of which takes further time and slows the whole process down even further.

VBA code
VBA code is particularly handy when a formula or native function simply doesn't exist or where a series of tasks would otherwise be too slow, complex, difficult, or even impossible. The downside is that, as a general rule, the processing time for most VBA formula solutions is often (but not always) a little slower than for a similar worksheet formula solution.

However, there is a bonus upside that very often negates and may even reverse that situation - VBA can be used to enter worksheet formulas (and can even then convert the results to values where required) and can also unprotect/protect worksheets then find and actuate native functions very much faster than any human possibly can, in addition, when there are a number of controls to be actuated in a predetermined sequence, VBA can do all these actions very quickly and without any possibility of human error so, for a given task, when all things are considered VBA can often turn out to be the fastest solution after all.

Automation
In a nutshell, VBA is all about automation of routine tasks - whatever the task is, filter, copy, paste, delete, process, write worksheet formulas, use inbuilt functions, lock/unlock cells, protect/unprotect sheets, open/close workbooks, import/export, ..., etc. the task can be automated and actuated whenever it's required - usually either by manually clicking a button to call the procedure or by setting it to run whenever a pre-defined event takes place.

Last Resort?
Many would argue that VBA should only be used as the 'last resort' or as a final act of desperation. However, on the contrary, I would argue that VBA is not the last resort, it's more of the last touch to be used in polishing up your project i.e. by automating it so that users only need to input any 'manual' data and then choose whether to read, print, or perhaps email or export the results with the click of a button.


So when a poster asks for help with a problem and you consider that this would be much "easier" or "faster" with a worksheet formula or an inbuilt function, before leaping in with a formula solution or instructions on how to use a native function, consider these two points:

the name of the board is VBA Express and this implies that I, as an ordinary person seeking help with a VBA problem, could reasonably expect to be presented first and foremost with a VBA solution.
you may only be looking at a small part of a much larger problem - Viz: converting everything that the user may already have in formula form and using native functions into VBA so that they can automate it completely.So are they really looking for a formula solution or just to be told how to use a native function? Probably not (or they'd probably be posting at MrExcel or somewhere similar) - but it doesn't hurt to ask before assuming they are... We can't expect to be given all the easy ones all the time :)

[Aussiebear]

Now that is food for thought. I for one, don't mind at all if you think it could be done by formula or with VBA. I'm open to any suggestion, other than 'Fosters"

Excel Mug aka Aussiebear

[Bob Phillips]

Whenever arguments regarding the relative merits of a worksheet formula solution and employing 'native' (or inbuilt) Excel functions versus a VBA code solution arise, the speed of formula solutions and native functions is often raised by many as a factor to discount the use of a VBA solution.

There are relative merits in all these approaches i.e. formulas, inbuilt functions, and VBA code.

Formulas
The processing speed of formulas may often be quite fast, but one downside is they can very easily bulk up the workbook if there are a lot of rows where formulas are required. Another downside is that if cells containing formulas aren't locked and the worksheet protected there will (inevitably) be errors caused by users typing over or deleting formulas.
As with all things, you need to understand the product that you are using to use it properly. A whole raft of array formulae can formulae can slow the worksheet down, but this can be mitigated. Conversely, VBA can dump the user in problems that they cannot manage.


Inbuilt functions
The processing speed for inbuilt functions is usually very fast indeed, but one downside is that some manual input is usually required and (without even considering the time required to learn the how, why, and where of native functions) it takes time to find and actuate the function. Another downside is that some of these functions (e.g. AutoFilter) will not work on a worksheet that's been protected to preserve formulas, so the sheet then needs to be unprotected first and protection re-applied afterwards - all of which takes further time and slows the whole process down even further.
What about the time spent learning to code, and the object model. Not to mention the time required to be any good at it.


VBA code
VBA code is particularly handy when a formula or native function simply doesn't exist or where a series of tasks would otherwise be too slow, complex, difficult, or even impossible. The downside is that, as a general rule, the processing time for most VBA formula solutions is often (but not always) a little slower than for a similar worksheet formula solution.

However, there is a bonus upside that very often negates and may even reverse that situation - VBA can be used to enter worksheet formulas (and can even then convert the results to values where required) and can also unprotect/protect worksheets then find and actuate native functions very much faster than any human possibly can, in addition, when there are a number of controls to be actuated in a predetermined sequence, VBA can do all these actions very quickly and without any possibility of human error so, for a given task, when all things are considered VBA can often turn out to be the fastest solution after all.
Of course there is a possibility of human error, the coder is a human, so the chance is just as great. The advantage is that the code can be (but too often isn?t?) tested in structured, controlled environment.

Spreadsheets can be coded as well, adding conditional formatting, data validation, etc., etc., can mitigate bad input in many cases to far better effect.


Automation
In a nutshell, VBA is all about automation of routine tasks - whatever the task is, filter, copy, paste, delete, process, write worksheet formulas, use inbuilt functions, lock/unlock cells, protect/unprotect sheets, open/close workbooks, import/export, ..., etc. the task can be automated and actuated whenever it's required - usually either by manually clicking a button to call the procedure or by setting it to run whenever a pre-defined event takes place.
My experience is that the VBA pen is reached for far too quickly, often because the spreadsheet developer has not the skills or the wit to properly design a solution. Or even worse, because the developer is a control freak, thinks that they know best and how the user should work (a touch of the MS syndrome here).


Last Resort?
Many would argue that VBA should only be used as the 'last resort' or as a final act of desperation. However, on the contrary, I would argue that VBA is not the last resort, it's more of the last touch to be used in polishing up your project i.e. by automating it so that users only need to input any 'manual' data and then choose whether to read, print, or perhaps email or export the results with the click of a button.
I would still argue it should be last resort, but the definition of last resort should be clear. Spreadsheet solutions can be complex, and any developer should spend time developing it, which means thinking about how it will be structured, how the results will be calculated, and how it will be delivered. To my mind, that means seeing if can be done natively, seeing if automation will improve any aspects, and using one?s experience to determine whether there might be bottlenecks within this approach.


So when a poster asks for help with a problem and you consider that this would be much "easier" or "faster" with a worksheet formula or an inbuilt function, before leaping in with a formula solution or instructions on how to use a native function, consider these two points:

* the name of the board is VBA Express and this implies that I, as an ordinary person seeking help with a VBA problem, could reasonably expect to be presented first and foremost with a VBA solution.
If we are giving our time for free, it is up to us to decide how we offer our advice. If someone wants to sum 6 cells, I will NEVER offer a VBA solution to that just because this is VBA Express.

By dint of the fact that I am answering, the likelihood is that I am more skilled than the poster, so I am better placed to decide which is better.


* you may only be looking at a small part of a much larger problem - Viz: converting everything that the user may already have in formula form and using native functions into VBA so that they can automate it completely.
It is just as possible that you may not. It is up to the poster to make that clear if it is relevant.


*So are they really looking for a formula solution or just to be told how to use a native function? Probably not (or they'd probably be posting at MrExcel or somewhere similar) - but it doesn't hurt to ask before assuming they are... We can't expect to be given all the easy ones all the time
Nonsense, you cannot assume anything just because they are posting her or elsewhere. They may just be here because it is a nicer board, or because it tends to deal better with intermediate complexity problems (witness how Paul Hossler hung in with Ted the other day), not necessarily what sort of solution they want. Anyway, if someone is asking my advice, I reserve the right to say what I think is the best way.


I realise that VBA is your thing, and Excel isn?t, but this is close to silly IMO. Every argument you offer can be argued from the other side. You don't even mention one very major aspect of using VBA, namely that it means that you workbook has VBA in it. Some places will not tolerate workbooks with scripts in them.

As with all things, horses for courses is the correct approach, not exclusively or always native functionality, not exclusively or always VBA. All problems should be thought

[Simon Lloyd]

As a poster meeting - well almost meeting both ends of the scale i hav to agree with Bob on the whole


As with all things, you need to understand the product that you are using to use it properly. A whole raft of array formulae can formulae can slow the worksheet down, but this can be mitigated. Conversely, VBA can dump the user in problems that they cannot manage.

When i started posting i had very little understanding of Excel, VBA and even less understanding of the inbuilt functions. I would post my problem i the hopes that an "Expert" or someone with an ounce more knowledge than me could steer me in the right direction regardless whether it was a VBA solution or worksheet function, i like many others only ever got a VBA solution to what seems now as the most trivial of tasks where Excel's own functions would have sufficed...smarter and swifter!

In defence of VBA at this stage i must say i was in awe of its ability and so fuelled my interest!


What about the time spent learning to code, and the object model. Not to mention the time required to be any good at it.

I haven't had the pleasure (or thats the way it seems now!) of physically setting aside time to learn VBA and giving the required time to understand what i am manipulating, where and its relationships withn the rest of Excel - to me an Object was a thing i could see not a thing i worked within - Professional users may "Learn or be good at" VBA so for the would be and novice poster code is something given not learned.


My experience is that the VBA pen is reached for far too quickly, often because the spreadsheet developer has not the skills or the wit to properly design a solution. Or even worse, because the developer is a control freak, thinks that they know best and how the user should work (a touch of the MS syndrome here).I am guilty as are most intermediate posters of almost all of this statement, i started off by trying to help out at work, i stumbled across VBA......WOW!, an answer or two to my questions and i was on a quest to impress and control - control gave me power!. VBA tasks can be picked up relatively easy with real words that you can relate to, so easy to start working your way around but when you come to the worksheet functions its a whole new illegible world - try to string 3 formulae together! nightmare what will i do? turn to VBA of course, it may take me a week to write the code to do what Excel's formula wizard could have done in 5 minutes but i will have done it, i used to think what an achievement until i started "messing" around with formulae - then i really was in awe, a good 60% of anything i had ever done with VBA was possible with Formulae!

I still cant get my head round them too much but as Bob mentioned with the code this IS definately a thing that needs to be taught!.


If we are giving our time for free, it is up to us to decide how we offer our advice. If someone wants to sum 6 cells, I will NEVER offer a VBA solution to that just because this is VBA Express.

By dint of the fact that I am answering, the likelihood is that I am more skilled than the poster, so I am better placed to decide which is better.
Nothing to argue here, when i post even now (although i do stippulate sometimes i want a w/s function) i assume the person who thinks they can tackile my problem are more skilled than me and would take their advice - surely that makes life easier for me - they must know they're the experts!

you may only be looking at a small part of a much larger problem - Viz: converting everything that the user may already have in formula form and using native functions into VBA so that they can automate it completely.

It is just as possible that you may not. It is up to the poster to make that clear if it is relevant.
As is usually the case posters (i'm one of them) post the portion they are working on - no structure to follow on to everything is adhoc, no planning, no design, the fact that they haven't given you all the story doesnt mean that there is a bigger picture!


Nonsense, you cannot assume anything just because they are posting her or elsewhere. They may just be here because it is a nicer board, or because it tends to deal better with intermediate complexity problems (witness how Paul Hossler hung in with Ted the other day), not necessarily what sort of solution they want. Anyway, if someone is asking my advice, I reserve the right to say what I think is the best way.Your right i came across this board and the attraction was, great look, clean cut and easy to read and it seemed (until i got here) that it was really a forum for expert questions, you rarely see any of the "Chinz" questions experienced elsewhere, posts are answered fast and fully - yes i know Bob is a man of few words - usually! butit doesnt dilute the solutions or advice given. On the whole the members at this forum are a lot more helpful than others, more like a community!

Well thats my two penneth worth!

[Bob Phillips]

A couple of points on your specifics Simon.


I haven't had the pleasure (or thats the way it seems now!) of physically setting aside time to learn VBA and giving the required time to understand what i am manipulating, where and its relationships withn the rest of Excel - to me an Object was a thing i could see not a thing i worked within - Professional users may "Learn or be good at" VBA so for the would be and novice poster code is something given not learned.

Objects most definitely ARE things that you can see, and you should see them as such. They are things such as a car, a persson, a house, or in Excel terms, a range, a worksheet, a workbook. They are tangibles, not intangibles. this is a fundamental concept when you move up to classes.


I am guilty as are most intermediate posters of almost all of this statement, i started off by trying to help out at work, i stumbled across VBA......WOW!, an answer or two to my questions and i was on a quest to impress and control - control gave me power!. VBA tasks can be picked up relatively easy with real words that you can relate to, so easy to start working your way around but when you come to the worksheet functions its a whole new illegible world - try to string 3 formulae together! nightmare what will i do? turn to VBA of course, it may take me a week to write the code to do what Excel's formula wizard could have done in 5 minutes but i will have done it, i used to think what an achievement until i started "messing" around with formulae - then i really was in awe, a good 60% of anything i had ever done with VBA was possible with Formulae!

I consider myself adept at both VBA and native functionality. The trick is to understand when to use what.


Nothing to argue here, when i post even now (although i do stippulate sometimes i want a w/s function)

And when this is done, we have a steer, otherwise I will steer.


Your right i came across this board and the attraction was, great look, clean cut and easy to read and it seemed (until i got here) that it was really a forum for expert questions, you rarely see any of the "Chinz" questions experienced elsewhere, posts are answered fast and fully - yes i know Bob is a man of few words - usually! butit doesnt dilute the solutions or advice given. On the whole the members at this forum are a lot more helpful than others, more like a community!

Excellent point(s), and worthy to include in the brochure. VBAX is my favourite site, and it is because it is generally advert free (unlike MrExcel or OzGrid); uncluttered for a web forum; I like the people (even those Oz B*&#!~$?s :devil2:); and there is talent here.

[Aussiebear]

[quote=xld]... witness how Paul Hossler hung in with Ted the other day) /quote]

I'm a persistant little sod ain't I? :devil2:

I could see an image of what I wanted, but maybe in the haste of the moment, I didn't ask the right question/s or clearly define the issue.

Alas its an affliction that us non experienced Excel or VBA users, will as a poster suffer.

[Simon Lloyd]

And when this is done, we have a steer, otherwise I will steer.WE?

Bobs "we" is usually an "I" a soft manipulative "I" but an "I" just the same :)

[Zack Barresse]

LOL!

Good points John. I do tend to agree with Bob's points on this myself. I won't repeat everything he said, but I do agree with the points. There are times when I find myself wanting to use VBA instead of worksheet formula or built-in functionality. One perfect example of this is the IsDate() function. There is absolutely no way to tell if a number is an Excel recognized date or not - not without VBA that is. I was hoping they would have added it to 2007, but alas they did not. (Hence this (http://www.vbaexpress.com/kb/getarticle.php?kb_id=323) KB entry.)

Also, there have been times where VBA was proposed when it could have been done just as well manually. One example where this is just the case is here..

http://forums.techguy.org/business-applications/572110-excel-deleting-row-does-not.html

John, you actually posted the VBA-proposed solution. I disagree with giving that as a solution. I feel it can be done just as easy manually. Plus it is good for the OP to know their stuff and how Excel works; most importantly, how to make Excel work for them.

I don't think it is a matter of which is more preferred. Everything should be based on a case-by-case basis; everyone's project is different. Looking from requirements to availability, each situation is unique. We all have our opinions and maybe none of us are wrong - or right.

And as a matter of a "last resort" re VBA, generally and usually I think that is how it should be done. That is my opinion, period. Most things can be done better natively IMO. This is because when we learn to use the software as it was meant to we can generally do things faster and more efficiently, especially when working with more than one user. Partly this goes back to what Bob mentioned about some companies not allowing any scripting (however much I disagree with that), some, I think, goes to portability and compatibility issues.

[johnske]

:rofl: Wow! Most unusual reponse. It seems my communication skills are even poorer than I thought they were...

I thought I'd done a pretty good job of (very briefly) explaining some of the advantages of using formulas and native functions while at the same time (without intending to spark a lengthy debate) pointing out some disadvantages that may be factors for posters wanting a fully automated (or VBA) solution.

All of which led to my penultimate point - which I'll repeat - VBA is mainly about automation.

A fact of the matter is that there are quite a large number of ppl that simply don't really give a rats a*** how something is actually done or whether the 'processing speed' of one approach may be a bit faster than another - all they really care about is that routine tasks can be accomplished reasonably quickly by employees that can be trained to do a job quickly and who may have little or no training in Excel. i.e. by simply inputting data and clicking a button the employee actuates a predetermined, standardized process that eliminates errors due to an untrained employee doing things in an incorrect sequence (if there's any errors in the code the 'fault' then lays solely with the employer and/or any coder they employed).

Bob, I do not presume to dictate how helpers on this board should repond to posters questions, that's entirely up to them how (or even if)they choose to respond to a given question. Yes, VBA is my thing and I rarely use Excel formulas - but this is not the same as saying I don't ever use them or never have any use for them - and sure, if there's ever any need assistance with a formula, as that's your main thing you (or Zack) would be one of the first I'd turn to for help.

Zack, I would also not presume to take the approach that posters "need to be taught" how to use Excel. The OP in the link you gave had, despite your advice, twice (I think) stated they wanted a VBA solution - for reasons of their own, for better or for worse, whatever you, I, or anyone else may think is best, that's what they wanted so that's what I gave.

I do presume that, with the exception of VBA coding, unless they state otherwise, posters quite probably already know quite a bit about Excel (and from the depth of some of the OPs code being posted from time to time, they may not be entire novices with VBA either) - they just need a hand with a problem or two.

This is a Help forum, with VBA in it's name, so when I have time I try to help with a VBA solution... When OPs specifically state that they want a non-VBA solution (or if the VBA solution the OP wants would clearly just over-complicate things) you'll note that I usually stand back and let others handle it. :devil2:

The thing is that posters may pose a question that would clearly be better done without VBA and while we may be able to advise that there are better ways of doing things, sometimes the question may only be being posted simply as part of their 'self-learning' process and is not really intended to be used, so it is deserving of a VBA answer (if only for a 'how to' and for comparative purposes). :)

[Bob Phillips]

Yes, VBA is my thing and I rarely use Excel formulas - but this is not the same as saying I don't ever use them or never have any use for them - and sure, if there's ever any need assistance with a formula, as that's your main thing you (or Zack) would be one of the first I'd turn to for help.

Actually, it is not my main thing, VB is. If you look back at mys posts, I would venture that they are highly biased towards VB. I only came to Excel (from Lotus) in any seriousness when VBA came in as I am from a VB background, so at last I had a tool that I could identify with. And I mainly make my living from VBA, but that does not influence my arguments one jot.


I do presume that, with the exception of VBA coding, unless they state otherwise, posters quite probably already know quite a bit about Excel (and from the depth of some of the OPs code being posted from time to time, they may not be entire novices with VBA either) - they just need a hand with a problem or two.

This is probably where we diverge greatly. I think that whilst many people will readily admit that they do not know coding, they will 'claim' to know Excel, when in reality their Excel skills are not that advanced, so they think it cannot be done in Excel. And as I keep saying, far too little time is spent on thinking about the problem. When I am working, I try to take at least two half hour sessions out each day to think about what I am trying to do, and how I am/proposing to do it.


This is a Help forum, with VBA in it's name, so when I have time I try to help with a VBA solution... When OPs specifically state that they want a non-VBA solution (or if the VBA solution the OP wants would clearly just over-complicate things) you'll note that I usually stand back and let others handle it.

It is, but as far as I am concerned that is just a catchy handle. Bill Jelen calls himself Mr Excel, but he ain't, it's just a self-proclaimed title. This is a generic forum, admittedly with a VBA bent, but not exclusively so. I find it interesting that one of the founders, Zack, doesn't see it as a VBA forum, but as a help forum ... and quite rightly so IMO.

As VBA is your thing, VBA slanted solutions from you are what we would expect, but that should not be seen in any way that that is the 'correct' way, or the 'wrong' way, it is just 'your way'.


The thing is that posters may pose a question that would clearly be better done without VBA and while we may be able to advise that there are better ways of doing things, sometimes the question may only be being posted simply as part of their 'self-learning' process and is not really intended to be used, so it is deserving of a VBA answer (if only for a 'how to' and for comparative purposes).

As I said, if the OP makes any such tatements, we have a steer, if they do not, I will choose. Often, when a poster states they want it in VBA and I think it is better in native functionality, I don't join in, or I withdraw (as is my prerogative). If they state they don't want VBA but I don't think it can be done without, I will say so.

[Simon Lloyd]

It's fair to say that you are all entitled to your opinion and to have a choice on how you deal with Op's posts. No matter who or what type of poster you are you will respect any comments and advice given by the experts and accept and appreciate their solutions or "steering".

As a collaboration for Bobs statement below i pasted a link to one of my questions where he did (and has done many times) indeed express his concerns on the method i requested (Worksheet Function) and gave advice, from there on in refrained from continuing with the post - you all do this to some extent - this IS what makes VBAX a cut above the rest, no "oneupmanship" each to your own and are good at your own!


As I said, if the OP makes any such tatements, we have a steer, if they do not, I will choose. Often, when a poster states they want it in VBA and I think it is better in native functionality, I don't join in, or I withdraw (as is my prerogative). If they state they don't want VBA but I don't think it can be done without, I will say so.
http://vbaexpress.com/forum/showthread.php?t=12615

I no longer post questions at any other forum since finding the diversity of knowledge here - and when i answer Ops here i know i have a watchful expert eye making sure i don't "steer" folk wrong!

[Ken Puls]

What a great thread, guys!

Can I agree with everyone here? I thought John actually did a fairly decent job of trying to point out that there are advantages and disadvantages to using any aspect of the program, be it native funcitonaility or custom through VBA.

I also agree with Bob and others, however, that the big secret when developing a solution in Excel is to figure out what should be used and applied when, and that many of us are too quick to jump to VBA. Each project has it's own merits, of course, and what may be a resounding reason why something should use native functionality in one project may be the resounding reason why it should not in another as well. Each needs to be heavily explored and thought out before a full solution is crafted.

The realities, however, when working in a forum environment is that we don't have the big picture. So for us, it becomes a task to explore the users real needs to find out if the solution we offer is appropriate (or optimal.) I'll never get down on anyone for telling me or anyone that they should be using native over VBA or vice versa, although I always appreciate good reasoning why one would be prefereable over the other. (Many times it is immediately obvious, of course.)

Regarding the average user's ability with Excel, I also 100% agree that users know less than you think, not more. I know where I started, see users on forums, have taught classes and trained staff, and in every case I've seen about 90% or more of them who thought they knew more than they did. The general pool of Excel users are rife with over-confidence... period. (Look at yours truly who host a site with the presumptious name "ExcelGuru"!) It is for this very reason that it is so important to ask the questions about native functionality. Some will know, for sure, but many won't.

In many ways, that is actually what kept (and keeps) me coming back to VBAX. I was fascinated with VBA, but at the same time heard other (frequently better) ways to do things. Despite the name VBAX, I knew that I was able to get a good solution to an issue, rather than the solution I thought I wanted or needed. Coupled with the friendly, helpful and embracing attitudes of everyone here, VBAX became my preferred HELP forum on the net as well. I'm now proud to try and return that same level when I can, giving back the huge volume of information that I have gleaned from this very place.

While we should continue to hand out good VBA solutions, we should also make sure that the solutions handed out are appropriate use of this power. Of course, that is subject to interpretation of what we all feel is appropriate. I hope we never do all agree on that, however, as it would mean the end to great discussions like this one. :)