Silly Question about variables

Aside from the fact that unused variables are confusing when you or someone else revisits your code a year later, the only consequence is memory usage.

Hi xlBunny,

Are you using VBA and declaring variables or are you using the Names Manager and declaring them?

Named ranges are a great way to communicate worksheet structure to sub procedures; not so much for user-defined functions, since UDFs should be passed everything they need to calculate a result without sniffing around the worksheet.

I was willing to brush it off if there was no cost to performance.

You could declare scalar (ordinary, non-array, non-object) variables until your typing fingers got tired without affecting storage significantly. Unused variables don't affect run-time performance at all.

In my mind, a UDF computes a small number of results (frequently just one) based on a small number of arguments. A macro typically needs to calculate a bunch of results from a bunch of data that all needs to be looked at in aggregate.

SIN(x) is a perfect example of an appropriate UDF: it receives a value of x and returns its sine. It doesn't need to know where the variable came from. A sub that puts the sines of a list of numbers one cell to the right seems silly.

A UDF that computes inventory cost in FIFO fashion by looking at all previous sales and purchases could be made to work, but every line would need to duplicate all of the work of the FIFO formulas above, and then do one more calculation; a sub could generate results row by row without redundancy, but needs to know how data is arranged on the sheet (that's where named ranges come in).

IMO, here and elsewhere, Subs are overutilized when UDFs would be simpler and more reusable.

If you write a few hundred of each, the picture will emerge in your mind.

You're welcome.

Hi Bunny,

I've been thinking about this topic for a few weeks as I work with a Golf Scores workbook. If I used ranges in formulas and then move stuff around, like inserting or deleting rows or columns, Excel readjusts my formulas so they still work. If I write VBA code and look at row 7 for something that has moved, Excel doesn't adjust to fit my code. If I name a range and use in my VBA code Range("MyData") the code will work because it can find the range after inserting or deleting rows/columns.

I also use a lot of Dynamic Named Ranges so more rows can automatically increase the range of values.

At first I thought you wanted to use Global Variables in your VBA code that might be another option for you, that I also use.

After all these different ways of making my work able to deal with many different sets of data, I sometimes forget to change all the "hiding places" and my code fails, especially after not seeing it for a year or two as shg suggests.

Originally Posted by xlBunny

Any how my function works but why isnt VBA telling me Im missing the declaration.

Declaration of what?

Well how come VBA isnt saying "Variable not defined" for DateFormated

Please Login or Register  to view this content.

Maybe I should go brush up on functions so I know the dos & don’ts save you guys sometime in trying to explain everything to me. When I was starting out I didn’t like functions because stepping through the code they would take me outside the main sub routine & it was hard to keep my baring. So I avoided them. It’s like when you first learn to drive there’s so much going on. In time you get enough practice you can drive home and not even remember the journey.

Thank you @Marvin Global Variables is something I have been thinking about too.

VBA will tell you a variable isn't defined ONLY if you use Option Explicit at the top of a module.
http://www.cpearson.com/excel/declaringvariables.aspx

So this is how I should have done it?

Please Login or Register  to view this content.

The yellow font will burn your eyes caution it like sun glare. Wholly cow that bright.

I guess if you don’t declare the type for the Function its default is Variant. Glad I didn’t open a new thread for that, and this whole conversation is under one thread for somebody to maybe learn from.

Thanks so much guys your great Im off to go have more fun xling.

SHAZAM!!! Functions Calling Functions my code just got a lot cleanner.

In this instance, there is no variable to be declared. You are using the Date variable, in your UDF. Date is a built in function which returns the System Date. And your function is formatting it as "mm-dd-yyyy"

I think your original question about variables has probably been covered and you are now moving on to questions about User Defined Functions. Probably warrants a new thread, or two.

Regards, TMS

Hi just wanted to update this thread with a link to a follow-up question that I just asked. It seems a lot of people have questions on this because the views are pretty high. At least I think so because this thread isnt even a moth old and its has just over 400 views.

Thanks again to shg, Marvin, and TMS
I am xling thanks to you.