lazy gits

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

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

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

never think about it! )))

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

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

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

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!

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

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

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.

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

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.

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.

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
....
....

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

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

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

reminiscing???

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

Originally Posted by xladept

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

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

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.

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

Please Login or Register  to view this content.

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, 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.

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

Originally Posted by abousetta

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

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" ?

Originally Posted by royUK

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)

Originally Posted by Pepe Le Mokko

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!

Originally Posted by royUK

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

You don't say !

Originally Posted by abousetta

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.

Originally Posted by JapanDave

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

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

I certainly dropped a tear...

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.

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

Originally Posted by 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.

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

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!

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.

Originally Posted by royUK

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.

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!

Originally Posted by martindwilson

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...

Originally Posted by martindwilson

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!

Shaft City!!

hmmmmmmmmmmmmmmmm

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....}

Originally Posted by snb

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

Not just my opinion

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).

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.

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

Originally Posted by Spencer101

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".

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

@Russell Dawson

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

@Pike

Originally Posted by Pike

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

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

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

Je suis curieus...

snb
In the mail at [email protected] - it preforms many iterations calxs and takes around 5 seconds - adding any VBA slows the calxs down dramatically.

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

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.

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!

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. )

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

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

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!

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.

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?

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.

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

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

formulas are compiled code, VBA is not.

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

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

pike, do you have a link for that?

china plate

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

Originally Posted by 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)

Originally Posted by pike

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 ...)

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

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

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

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

Hope I dont cross any copy rights Attachment 173318

@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.

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

Originally Posted by Marcol

....
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.

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.

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

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

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

no-they are completely different

Manali,

Please post your question in the appropriate subforum.