# Off Topic > The Water Cooler >  >  lazy gits

## martindwilson

rant on...
i have noticed that more and more requests for vba solutions are cropping up, not just here but at other sites like mr excel
can i have code to fill this formula down.. no just drag it 
what is vba to add cells a1and b1 together,,err why
it's just lazy
now if you are one of these people
LEARN how to use functions ,pivot tables,consolidation blah blah
when you cannot find a function to do it then ask.
now what is the code to open and pour my beer?
rant off

----------


## xladept

But then you've got a sheet full of formulae!

----------


## martindwilson

and the problem with that is? it's a spread sheet not a pretty presentation tool, every thing can be hidden if you really want.
nope i think it's the same old "i know excel guvnor" but no you dont .then have to cover your backside by getting someone else to do it.

----------


## tigeravatar

```
Please Login or Register  to view this content.
```

----------


## john55

```
Please Login or Register  to view this content.
```


never think about it!  :Smilie: )))

----------


## FDibbins

lol good point MW  my son just took an exame in the UK on excel, word and PP and got 85%...he said so now i know all about excel.  i asked him if he had used index/mathc yet...his reply was...ummmmm wassat?  LOL

Edit:

TigerAvitar...can you explain to me where i put that code plz?  i tried to add it to the cooler drawer in the fridge but nuffink happened  :Frown:

----------


## john55

FDibbins, it's about ECDL?! my son took it too! I rcvd the same answer... LOL

----------


## FDibbins

john55, not too sure, im in the US, but it was a 2 part exam, the 2nd part was a choice of a, b, or c...they didnt tell him a was word, b was excel and c was PP.  he chose b and thought it applied to all 3.  got 30% lol and has to redo the 2nd part

----------


## xladept

The problem with a sheet loaded with formulas - for me - was that I then had to leave them with the client and the client's specialists - formulas, if unprotected, regularly got creamed - oh by the way, the beer is on the third shelf - that's probably the cause of the type mismatch error!

----------


## martindwilson

ah see there's the problem, the client should have been able to do them themselves and not get an outsider to do them.. its like oh i better leave an iron lung just in case you forget how to breath

----------


## xladept

Yes, it boggled the mind - even if protected, some "specialists" would go through the procedure anyway and obliterate my formulas !!!!

----------


## abousetta

Martin is right and I unfortunately fall under the category of people who jump to vba just because I can when I know there may be a possible way using formulas.

xladept, if your 'specialists' are messing with the data, I have found that moving the formulas to vba and using a change_selection sub allows me to protect the formulas without increasing the file size.

----------


## WasWodge

There is of course the outside chance (and yes I do accept that it is an outside chance) that they are asking for the fill down VBA as a part of a much larger VBA project. In this circumstance then I wouldn't treat it as a lazy question.

Now obviously in this case there might be a better way of doing it in VBA rather than autofilling a formula that can be suggested, but the bottom line is unless they have explicitly stated it then we shouldn't really assume what they are going to be using the code for.

Just out of interest I was one of the rare posters who when I was a total novice to VBA did ask the question how to fill down a formula to the last used row as part of a larger project for others to use.

I do admit that most of the time I don't believe that they are using it as part of a bigger project but then I also know from my own experience that there are a lot of people out there who automatically think a VBA version is always superior and won't hear otherwise. 

Which I do find amusing sometimes as 95% of the same people have never written or understood a code in their lives.

Anyway that is my annual standing up and making excuses for lazy posters over with, I will now revert back to my default setting of moaning and groaning about the world for the rest of the year.

Normal service restored

----------


## xladept

As you can imagine, I avoided spreadsheet formulas on my gigs.

I know there's another Windmill out there - Hey Sancho!

I did finally use a change selection event to protect formulas - but I ended up avoiding formulas wherever possible.

----------


## vlady

i personally not able to do vba... preferred formula make's me know and then study what's happening "inside a computation" in a spreadsheet.
then i am able to practice math. :Smilie: 

vba automates everything, user's don't tend to learn how did a value came out from a certain computations.

where as formula will always keep anyone in touch.

e.g.
vba - cell b1 value = 10

10????? where did this value came from........

is it A1+B1
is it A1/b1
is it A1*B1
is it A1-B1
is it A1^2.. ooopppss  :Smilie: 
....
....

*************
formuals'   

C1=A1/B1  ----- ahhhh, i seeee!!!!!! -(user)

----------


## npamcpp

This seems to come up regularly. One previous rant with a good follow-up discussion is here:

http://www.excelforum.com/the-water-...o-do-this.html

----------


## vlady

reminiscing??? :Wink:

----------


## royUK

Many times VBA is asked for because teh OP either has a badly designed workbook or just doesn't know or explore Excel's inbuilt tools like PivotTables, Tables or Lists, etc

----------


## romperstomper

> As you can imagine, I avoided spreadsheet formulas on my gigs.



That is a really strange (mis)use of Excel.  :Smilie:

----------


## Marcol

Go get 'em Martin

Have a look here
if cell includes string append a character to the value of another celll

Compare the questions with the VBa result,and the result the hard way!
The VBa has had 3 views so far, the hard way none, tells you something, no?

----------


## JapanDave

I have to admit I know nothing about formulas. But when a question is asked in the programming forum, I will do my best to answer that request. 

@ Marcol, sorry if I did something that is not required, as I am still learning myself.  :Frown: 

@ TigerAvatar, I am getting and error here after I tried to amend your code,




```
Please Login or Register  to view this content.
```

----------


## Marcol

@Dave, I wasn't intentionally pointing a finger at you, just trying to highlight Matrins' point.
My apologies if I have offended you ...  :Mad:

----------


## martindwilson

its not that code isn't useful and it can automate loads of boring tasks and do other quite wonderful things. it is just that obviously some people have never tried and just post "i need vba" when with a bit of thought they probably dont. my favourite is "my boss says" i cant have hidden sheets or such??? i bet that conversation never occurred.
conversely tho' you do get "I'm not allowed to use vba/code" which i read as" i don't understand it or know where to put it if i did"

----------


## Marcol

Agreed Martin, but I still encounter in real life, companies that don't allow macros, the warning message scares the sh*t out of them ...  :EEK!: 

So what does the "minion" do in these cases?
1/. Does he/she try to use the native resources in Excel?
2/. Take the file home and fix it there?
3/. Post a question in a forum, without the workbook of course, then p*ss of to the pub to await a solution, bumping from his/her blackberry as panic sets in?

What do you think?

----------


## abousetta

Marcol, this worked for me  :Smilie: 

Seriously I think most people who come to forums are roughly split into several categories, the smallest fraction being people who are here to 'learn'. Many want key-turn solutions to problems that are available in a simple Google search. Either they are too lazy to do the search and just drop the question around in a couple of forums and go watch TV until a solution magically arrives to their email AND/OR just want a solution to a problem without learning the ins and outs of the application. To be fair sometimes time is a big factor and they just don't have time to surf the learning curve, with trial and error, until perfection rears its ugly head.

Disclaimer: From time or another, I have fallen into each category, but I try to only post a new thread when I truly can't find an answer.

----------


## WasWodge

> my favourite is "my boss says" i cant have hidden sheets or such??? i bet that conversation never occurred.



I haven't had that exact conversation but I have had one with a fellow manager who insisted that he must have a VBA solution to a spreadsheet he uses "as a macro is always better" despite me explaining that it was easier just to have fixed formulas in the sheet as that was all that was needed for the task.

Eventually I caved in and wrote him the macro.

So now he has his macro that fills approx 12000 rows by 8 columns of formulas,drags in a bit of data from the main system, does a few needless loops, border fills,formatting e.t.c and screenupdating is switched on.
It takes about 8 minutes to run and amuses my mate in IT greatly every time he sees him using it (he runs it at least twice every day).

Maybe he just likes the flickering Christmas light effect it has when it runs

----------


## royUK

> Marcol, this worked for me 
> 
> Seriously I think most people who come to forums are roughly split into several categories, the smallest fraction being people who are here to 'learn'. Many want key-turn solutions to problems that are available in a simple Google search. Either they are too lazy to do the search and just drop the question around in a couple of forums and go watch TV until a solution magically arrives to their email AND/OR just want a solution to a problem without learning the ins and outs of the application. To be fair sometimes time is a big factor and they just don't have time to surf the learning curve, with trial and error, until perfection rears its ugly head.
> 
> Disclaimer: From time or another, I have fallen into each category, but I try to only post a new thread when I truly can't find an answer.



You are forgetting the cheeky ones who set themselves up as consultants then post to the forums to get the jobs completed

----------


## Pepe Le Mokko

There's a saying in Dutch ( there must be an English equivalent) that translates to :" Why do things the easy way when they can be done the hard way" ?

----------


## Pepe Le Mokko

> You are forgetting the cheeky ones who set themselves up as consultants then post to the forums to get the jobs completed



Gee, Roy, I never imagined anyone would do something like that! Are you serious ? I never, never had that feeling on forums ( and particularly this one)  :Confused:

----------


## royUK

> Gee, Roy, I never imagined anyone would do something like that! Are you serious ? I never, never had that feeling on forums ( and particularly this one)



And most of them originate from a major sub-continent!

----------


## Pepe Le Mokko

> And most of them originate from a major sub-continent!



You don't say !

----------


## JapanDave

> Marcol, this worked for me 
> 
> Seriously I think most people who come to forums are roughly split into several categories, the smallest fraction being people who are here to 'learn'. Many want key-turn solutions to problems that are available in a simple Google search. Either they are too lazy to do the search and just drop the question around in a couple of forums and go watch TV until a solution magically arrives to their email AND/OR just want a solution to a problem without learning the ins and outs of the application. To be fair sometimes time is a big factor and they just don't have time to surf the learning curve, with trial and error, until perfection rears its ugly head.
> 
> Disclaimer: From time or another, I have fallen into each category, but I try to only post a new thread when I truly can't find an answer.



This, I am here to learn and to give back what I have been taught. I could not even do a VLOOUP before a I came here and I was just looking at my first posts here and to be honest I am embarresed to to say the least, such simple questions. But, the experts here were always here to help. So if you want to see a success story (will I think I have come a long way) look  no further. 

@Marcol, no offence taken. I just don't know the true potential of excel yet and I myself am guilty of trying to re-invent the wheel when there already is one.

----------


## Pepe Le Mokko

> ...But, the experts here *were* always here to help.



 The imperfect tense is correct, now most of them have left...

----------


## Marcol

> And most of them originate from a major sub-continent!



I'm sure we have all noticed that, and could name them with some certainty.

At first this needled me, I could picture some chappie scurring through the souks and bazzars clutching his prize, and proudly presenting it to his cheapskate client or paymaster.

But then I took a pace back.

If it helps to get him and his family out of these desperate slums that the rich allow them to exist in, then good luck to him, he maybe deserves all the help he can get.

Or am I being an eternal romantic?

----------


## jeffreybrown

I certainly dropped a tear...  :Roll Eyes (Sarcastic):

----------


## xladept

Several of my contracts had the mathematical formula posted in the heading for that column and places like the Federal Reserve and Large Utilities didn't want formula laden spreadsheets.

----------


## parkingqueen2

This may sound strange but i've been so careful to post when i knew the way to do stuff through formulae coz i thought all you guys prefer and advocate VBA! I am not a coding expert but can certainly help from a formula side.

----------


## martindwilson

> Large Utilities



i just saw that! 
i worked for one for nigh on 40 years and no one ever complained about over laden spread sheets, they were only to delighted anything worked better than an abacus

----------


## royUK

> Several of my contracts had the mathematical formula posted in the heading for that column and places like the Federal Reserve and Large Utilities didn't want formula laden spreadsheets.



Excel's built in formulas & features are generally preferable & faster than VBA solutions

----------


## FortySixAndTwo

I love the title of this thread.  I'm going to have to use the word git in my vocabulary from now on!  UK English has some of the best slang and explicit words that I have ever seen or heard!  You guys rock!

----------


## xladept

The thing is that if you're going to leave your work behind, you don't want the client's specialists adapting or even destroying your work.  I've been at it, one way or another, also over 40 years.

----------


## snb

> Excel's built in formulas & features are generally preferable & faster than VBA solutions



It may be your personal preference....; there's no general preference.
It's also highly dependent on personal skills.

----------


## martindwilson

> The thing is that if you're going to leave your work behind, you don't want the client's specialists adapting or even destroying your work



 nope ,maybe they will pay you chunks of dosh to fix it after you have left!

----------


## Søren Larsen

> nope ,maybe they will pay you chunks of dosh to fix it after you have left!



I do that quite a lot; fixing models (with spreadsheet formulas) for clients after they've made a mess of it! But I'd rather they didn't mess it up and just be happy...

----------


## Spencer101

> nope ,maybe they will pay you chunks of dosh to fix it after you have left!



This would work well for me.  I've littered my workplace (government office) with a plethora of workbooks laden with formulas to make MY life in the office as easy as possible.  The minute I leave that job, they're screwed with a capital WTF!  :Smilie:

----------


## xladept

Shaft City!! :Smilie:

----------


## martindwilson

hmmmmmmmmmmmmmmmm

----------


## Marcol

@ snb




> It's also highly dependent on personal skills.



... and indeed, you have more than most ...  :Roll Eyes (Sarcastic):

----------


## martindwilson

and marcol and snb went of together in a shower confetti singing the excel song

give us an e
give us an x
give us an c
give us another e
give us an array that can finish this chant
{vba vba vba....}

----------


## royUK

> It may be your personal preference....; there's no general preference.
> It's also highly dependent on personal skills.



Not just my opinion

----------


## xladept

I hadn't thought about job security - with my acute lack of business acumen, I was proud to have left my clients with virtually maintenance free routines and systems.

I will say that I find the versatility of your formulas fascinating and I'm absorbing as much as I can and as quickly as I can (not very).

----------


## pike

Always excel functions first! Vba is way to slow with any serious spreadsheet. Dont get to carried away its not brain surgery anything can be developed when your gone.

----------


## xladept

Still there are realms in which each excels(!).

----------


## Russell Dawson

> This would work well for me.  I've littered my workplace (government office) with a plethora of workbooks laden with formulas to make MY life in the office as easy as possible.  The minute I leave that job, they're screwed with a capital WTF!



Snap!  I left and left them to it, having left the odd "self destruct" button - "DO NOT PRESS". :Roll Eyes (Sarcastic): 

Attached example had a mixed reception.  (Its safe - only takes you to another sheet, but they didn't know that)   :Smilie:

----------


## Spencer101

@Russell Dawson

"request reset to connection between chair and keyboard"  Hahahaha!  Excellent stuff  :Wink:

----------


## snb

@Pike





> Always excel functions first! Vba is way to slow with any serious spreadsheet.



Please post your sample workbook to illustrate your assertion.... :Wink:  :Wink:  (see the forum rules !)
BTW I prefer funny spreadsheets, not serious ones.

----------


## pike

Hi snb 
no problems I'll PM the excel workbook tomorrow .. by the way the roman bronzes  were amazing

----------


## snb

Je suis curieus...

----------


## pike

snb 
In the mail at suggestie@snb-vba.eu - it preforms many iterations calxs and takes around 5 seconds - adding any VBA slows the calxs down dramatically.

----------


## pike

John Walkenback compares the three ways

The trade off between speed and file size

Basic Formula calx time 10.8 seconds filesize 24.4 mb
MegaFormula calx time 6.2 seconds file size 8.9mb
VBA calx time 106.8 second filesize 8.6 mb

----------


## ChemistB

I don't understand the assertion that formulas are easier for the client to screw up?  If the spreadsheet is properly locked down, how are the formulas any easier to get to than the VBA?  It would be nice if Data Validation and Conditional formatting could be locked down so easily.  Maybe in some future version.

----------


## xladept

Logic is nice but not always relevant in human situations.  I've actually watched specialists go through the entire procedure to unlock and destroy formulas entered in cells!

Incredible, to be sure!

----------


## romperstomper

To me that would imply that either they were not in fact specialists or the formulas in question were incorrect/inefficient. (I've done that myself to workbooks, and other applications, created by consultants.  :Wink: )

----------


## xladept

I've found that "specialist" is actually an euphemism.

----------


## romperstomper

I've never actually heard the term used in connection with Excel, but have often thought the same thing about consultants and system analysts...

----------


## xladept

Yeah - I remember, years ago when I was writing COBOL and I used what is now called the immediate if - the systems analyst couldn't get it and was actually amazed that it worked!

----------


## FortySixAndTwo

I specialize in specialties.  That makes me a specialist!  I prefer VBA over any other solution and don't see why it would be poo-pood on?!?  I know that the people I write procedures for couldn't care either way, as long as the workbook is working right in the end.  

@Tigeravatar, I tried your code for beer dispensing but it poured week-water tasting beer at run-time.  I think it needs to be amended by demography so that the proper beers/ales are poured.

----------


## vandan_tanna

I bet if MS made beer function a built-in function for excel, it would pour beer faster and of right strength!

I agree with ChemistB...if properly designed, a solution using built-in function will be more robust, faster and easier to maintain. I love VBA....but if built-in functions can do the job...why go through all the pain of coding and error handling etc etc?

----------


## xladept

What's the real advantage that the built-ins have over the VBA - isn't it that they avoid the I/O??  If you got it all into core the VBA would run just as fast if not faster; I find myself using a lot of UDT's for that very reason.

----------


## martindwilson

some users will ask for code to do a simple function,what is the point of that? reinventing the wheel?

----------


## shg

Formulas run in process; VBA runs out of process, and every read or write requires a function call.

----------


## JosephP

formulas are compiled code, VBA is not.

----------


## pike

to reiterate vba is slow 

John Walkenback compares the three ways

The trade off between speed and file size

Basic Formula calx time 10.8 seconds filesize 24.4 mb
MegaFormula calx time 6.2 seconds file size 8.9mb
VBA calx time 106.8 second filesize 8.6 mb

----------


## WasWodge

Prime example to go with this threads title. 

Why do I think the OP isn't going to make any attempt to tidy it themselves and is going to post the whole recorded code scrolls and all?

http://www.excelforum.com/excel-prog...ng-in-vba.html

----------


## npamcpp

pike, do you have a link for that?

china plate

----------


## pike

No, its in a passage from Excel Power Programmming by John Walken Back

----------


## WasWodge

> Prime example to go with this threads title. 
> 
> Why do I think the OP isn't going to make any attempt to tidy it themselves and is going to post the whole recorded code scrolls and all?
> 
> http://www.excelforum.com/excel-prog...ng-in-vba.html



I take it back the OP came back with a solution themselves (although I am not sure they actually did it themselves)

----------


## snb

> No, its in a passage from Excel Power Programmming by John Walken Back



Now that is hardly a convincing argument (to put it mildly).
Please show the files, please post the code, (see the forum rules ... :Smilie:  :Smilie: )

----------


## royUK

Which forum rules states you have to prove the point?

Charles Williams is an expert in Excel "speed" - see his reply here

---------- Post added at 01:08 PM ---------- Previous post was at 01:07 PM ----------

Which forum rules states you have to prove the point?

Charles Williams is an expert in Excel "speed" - see his reply here

----------


## Marcol

Nice to see you're using the built in "double post" facility on this forum to stress your point Roy ...  :Wink: 

How many times now have the lazy gits told us that this was cured?

*[EDIT]*
Now where did that second post go ...  :Confused:  Looks like it's been merged!

----------


## pike

Hope I dont cross any copy rights Attachment 173318

----------


## snb

@Pike

That doesn't add anything to 'proving' in my view.
JW is by the way talking about a VBA Function (I suppose a UDF).
That doesn't bear upon using VBA in general and the calculations you can perform without writing each result to a worksheet.

----------


## xladept

And, VBA is more versatile than the canned functions and formulas.

----------


## Marcol

> And, VBA is more versatile than the canned functions and formulas.



 ....  :Confused: 
Especially so if you can't be bothered learning "the canned functions and formulas."

See the thread title ...  :EEK!:

----------


## Mordred

> .... 
> Especially so if you can't be bothered learning "the canned functions and formulas."
> 
> See the thread title ...



I sort of disagree with that!  I've spent all my time learning VBA that I haven't had the time to learn canned functions and formulas.  I don't consider myself lazy because of it...there's just not enough time in a day to learn it all.

----------


## pike

Hi snb,

Its no big deal as I've stated before there is little difference in small projects

The e-mailed workbook is the proof that when you do major engineering calx's there is no comparison between the faster Excel Functions over VBA.

----------


## xladept

Nothing seems more esoteric to me than seeing VBA coded to envelope the canned whatever.

----------


## JapanDave

Test post.......................

----------


## D_Rennie

Where is this code for pouring beer, i am now interested and want to know more about the syntax for this.

----------


## manali

Guy's are the excell formula and sql quarry same?I want to know about that...pls help.

----------


## JosephP

no-they are completely different

----------


## arlu1201

Manali,

Please post your question in the appropriate subforum.

----------

