What is the point of just using worksheet  functions in a UDF? All you
are doing is adding a layer that needs to be navigated by the system to
get your result.

Sorry, I am really a beginner, I do not understand "UDF" although I
suspect that the "F" stands for function? What "UD"is, I don't know.

Now, what's the point?
I would not make a function for =SUM(F10:F19)!
I gave you this example because it used two Worksheet Functions, but
other cells formulas  are so long  they take almost 3 lines.
It's OK as long as I am testing, but once I'm finished I'd like to
replace them by functions...
And, most of all, I would like to know how to, because I am trying to
learn!
Repeat:
The Visual basic  Help has a topic: List of Worksheet Functions
Available to Visual Basic.
What does that mean? how is it "available"?
I have a long sub that needs to make calculations on data that come
from the spreadsheet, and using some Worksheet functions  inside a
function, if it is possible, would help  me a lot.

If you tell me it is NOT possible, I'll stop trying, and I'll live
with it.

Or if you tell me I would have to re-write the whole code of the
Worksheet Functions, this would also have no sense!

What is the problem with "adding a layer that needs to be navigated
by the system"?

Please, please, do at least one Google search before asking for help. My
Google search was: use worksheet  functions in vba

There were many thousands of hits, and the very first one was certainly
relevant. The third hit even gave an example of calling VLookUp!!! Please
try a search yourself.

UDF stands for User Defined Function. I.e. a VBA function written in the
Visual Basic Environment (VBE) as distinct from a built-in VBA function.

There is no specific  reason not to use worksheet  functions inside UDFs, but
it is less common than you'd expect at first. (1) people really don't
bother to simplify formulas  such as you are talking about - three-line
formulas aren't especially long  for experienced Excel'lers; (2) when you're
writing a UDF, you'll probably do things yourself using traditional
programming techniques and not bother with worksheet functions; (3) there is
a perception that calling worksheet functions  is slow (although I've never
checked this).

But I had done a search through your group then through other groups,
using "Worksheet functions  in VBA" without results.

So maybe I got lazy because everything seemed so easy for you all.

I have ordered a book that will probably teach me more.
It hasn't arrived yet.
Till then, I will try not to bother you with too simple questions.

I'll miss you though, because until I come to really interesting
questions for you, it will take a time.

To use them, you just type "Worksheetfunction.<name of function>". For
example to use COUNTA in VBA, you would type
"Worksheetfunction.CountA()". If you type "Worksheetfunction." in the
VB Editor you'll get a dropdown list  of the possible functions  you can
call from your VBA code.

Worksheet functions in VBA use the same arguments as the ones on the
worksheet. And keep in mind that if an equivalent VBA function exists,
you will not be able to use that worksheet  function in VBA.

If your problem is long  formulas in your worksheet (which I don't
understand as being a problem), the way you shorten them is by using
defined names.

See for example
www.tushar-mehta.com/.../named_formulas.html

One of your followup posts you mentioned 'I'm not going to rewrite
SUM' -- that is exactly what you are doing.

Function MyQuery(rng as Range, rngtoCheck as Range
MyQuery =
IF(ISNUMBER(VLOOKUP(J8;WebQuery;5;FALSE));VLOOKUP(J8;WebQuery;5;FALSE);0)
End Function

All you are doing is rewriting a built-in function. It's no different
than writing a function like

Function MySum(rng as Variant)
MySum = Worksheetfunction.Sum(rng)
End Function

I understand that in some longer VBA procedures, worksheet functions
might be used for certain calculations. But using VBA solely to insert
worksheet functions will serve no purpose other than to slow down
calculations.

One thing that I am glad about, is that at least, I now know how-to.
On the other side, I had not realized that it could slow down the
process, as a matter of fact, I thought it was the opposite.

As for defined names (or created names), I use them a lot!
I define names for ranges of cells or create names for single cells.

I went to look at the link that you gave to me, in case I would have
missed something important (I am new to vba  but I've been using Excel
for very long).
But no.
Defining or creating a name sometimes is a must, always makes the
formula easier to understand.
And most of all, if you move the cells (or the range) around in your
workbook, you can still refer to them wherever they are, because the
names move with them, if I express my-self well.

But it does not define the formula it-self to be used somewhere else.

Sure I can name a cell "ThisCell", and somewhere else use the
formula "=ThisCell", but it bring me the value of the content
of "ThisCell", and does not rewrite the formula that is in "ThisCell".

I guess that if I wanted to create functions  to replace  my very long
formulas, it was mostly... for esthetic reasons :-)

But you have proven me clearly that I was re-writing the Worksheet
function, so now I will use it very sparingly, only in a very long
sub.

You might also try the Excel list  at http://peach.ease.lsoft.com/
their Excel-G list is real great about helping you out.

This list seems kind of stuffy sometimes.

Do you know for definite that rewriting functions  in VBA slows stuff down at
all?
I know it would seem so... But is it a noticeable amount?

I have sort of done this numerous times with looooong formula just to get
the screen back estate back!.. And with no noticeable increase in time IMHO.

Just my two penneth.

Great post about the shell command BTW.

You have to remember that, in a sense, VBA is grafted onto Excel.
Every call to the worksheet  from VBA involves a time penalty, because
it has to negotiate with the worksheet and do some behind-the-scenes
work. So it's in your best interest to minimize the interaction as
much as possible. For example, instead of loop through 500 cells in a
range (generating 500 Range Objects), read the values into a array to
work with them, then write them back in one shot (potentially only
touching the worksheet twice).

I still don't understand why formulas  need to be shortened this way.
If your formulas are evaluating to values, how much screen real
estate could they possibly be taking up? One of the nicer features of
Excel 2007 is that it allows you to expand the formula bar to view
longer formulas.

I did not think that this topic would run around so long!
I have well understood what I previously did not, about the exchanges
between Excel and VBA and I got the message that long  formulas are
not that long!

Those long formulas  that I wanted to replace  by functions  are very
few, isolated (I mean not part of a long array of hundreds of cells),
and would be calculated only once a day, maximum, sometimes less.
I'll leave them as they are now.

I do not have long arrays filled with data that I have to go though
with a sub.

But I do have a quite long array that does not contain data, but
formulas (not very long formulas but hundred of them!)

Even if the lines contain only 0 values when the user starts, (they
will be filled day after day with the values derived from those
received by 2 of the 3 Web Queries when people use the spreadsheet,
the third one is used less often and does not serve to fill an array,
just update individual cells).
The cells in that long array are recalculated pretty often, and that,
even if have set my Excel options to calculate immediately except for
the tables.
In that array, the only cells that are not calculated by a formula
are in the first column (dates) that serve for the vlookup.

This also takes quite a long time, especially when one closes the
Workbook and worst, when Excel makes its preventive copies), when
they are all recalculated again, during which everything comes to a
halt until the book has been saved.
Because once the user has updated the web query, some days, he/she
still has lots if thinking to do in front of the workbook...

Now I wonder if it would not be better, in the sub that controls
the Web queries, to also add the formulas day by day, as the array
becomes populated.

In that case, the sub would use only one Worksheet function,
vlookup for the date, then the seven columns to the right of the
first one would be filled with their respective formulas, then the
result of the calculation (which then would concern only 7 cells),
would be copied and pasted.
In such a way, I think that the time ratio between using one
Worksheet function, as compared to recalculating all the cells (7 for
about 400 rows), would be favourable.

Don't you think?

Just wondering why speed is a big issue for you???
Are the times very very slow?? Do you think maybe you need to look elsewhere
to reduce the process time?
Processing arrays is usually pretty fast because they're in memeory.
Maybe changing the order of processing could speed things up.
What do you call "very long" and how many dimensions?
You can switch automatic calculate off and then switch it back on when
you're done.

Sorry if it seems I don't understand clearly.

Personally... I've always found Clarity for Maintenance a much bigger factor
than speed.

Since I started this topic, everyone prevented me from writing
functions, because they seemed to slow down thing so much!
For me time was not a factor before!

Now I am almost begging for permission to use a VBA function once in
a while:-)
In the spreadsheet for my own use, (there is absolutely no VBA in it)
I've been using daily it for years there are much MUCH more formulas
to calculate beside lots arrays, and it really slows down terribly.
So one in a while, I go over the formulas  that have been used and
just keep track of previous downloaded data, and I copy paste them.

Still it takes almost 15 seconds to close the book, and very often I
have to wait quite a while before being able to write something in
the book, because Excel make its safety copy.)

In this case, it might not be that bad. there is just one such array.

It was also by curiosity, I would like to see if I would be able to
program it, because I am learning.

> So one in a while, I go over the formulas  that have been used
> and just keep track of previous downloaded data, and I copy
> paste them.
I'm so sorry Louise, I don't think I fully understand. I think I may have
the wrong end of the stick here.
... And who's stopping you from writing functions!??

I can't think of anything offhand that can be done with functions  that can't
be done in VBA. In fact there is a lot of VBA code out there that has been
written to "improve" some of the functions!

15 Secs ... Well... I dunno if that's such a big deal really. But you also
say you are using this as a learning experience. I'd try writing VBA to do
what you want without using worksheet  functions at all... Just VBA. It will
definately slow you down... at first... because of the learning curve.. But
the gain in expertise will be immeasurable.

What's the longest formula you have?
As I said previously, I've written VBA functions just to get screen space
back!

Long formulas can also be quite difficult to work through, for example when
there are lot of IFs... Actually, the IF nest limit, though there are
workarounds, was, I believe, 7 for MSO 2003... Anyone know if that's changed
in MSO 2007 or wether I have it wrong?

I was just told (although not said exactly in those words),
it would be like re-inventing the wheel, but most of all that it
would slow the process so much.

And then I hear "what's the problem with slowing down?"
So I'll listen to everyone and make my own decisions, that will
depend on
1. how fast I can learn
2. what really makes a positive difference.

About long  formulas, there was a time when I had lots of "nested IFs"
(the max is seven effectively 7, at least in my 2002 version, I
needed 10, and each of the IFs was pretty long, this was ridiculous,
so I have changed the whole way I was doing to a much simpler formula.

Sometimes I do the reverse:
I had to extract a date recognizable by Excel as a date, from a very
long text string received by my Webquery.
There were so many things to consider: I wrote the process using many
different cells.
I had to redo it many time as the dates went on, until I saw it would
work for all dates.

[I find that Excel stings handling is not as good as in, let's say
Javascript.]

Now I could use all the formulas  from the different cells and produce
a very long single formula.
But this would be just making the whole more difficult for me to
read, if ever I had to redo it (I'll have too past 2009-12-31).

Those cells are beside a Web query that will not be seen anyway,
because, with the tips that I have received from this forum, the web
queries are now all in the same sheet that is hidden and protected,
when not used, and when used the screen is not updated.

As suggested by Jimmy, I am starting to rethink my worksheet  design,
as this project started much simpler, and grew a lot, as new ideas or
proposals kept coming in.
Now it's over, I do not add anything new, just make sure that this
one is pretty foolproof and works in all conditions, that change all
the time according to the results from the web queries

But as for my array that contains so many formulas not used, I
am not sure if you understood what my problem was.
If I can't find a solution by rethinking my design, (and after
consulting my dear new book thoroughly), I'll come back.

Lets's consider this topic closed, because we are really starting
to... get out of topic, which I usually hate on forums :-)

I read parts of this thread, then realized I did something different
and would like comments on this method vs. the others. If there were
comments regarding this method, I skipped over them.

For LOOKUP functions, I put everything for the operation in the sheet.
As if I was only using it in the sheet without code.
The to-look-up value is put in a cell by the VBA code. A LOOKUP
formula uses that value by reference, and the result is then read by VBA.

I figured the Excel code would be fast, therefore it would work
well, but then, I had no special speed requirement.

Does this method have any pros / cons vs. the others?

I don't like the idea of putting a value into a cell in order to get a
formula to calculate something and then to read it back with VBA. I would
not have confidence that the calculation would be done immediately (i.e. in
time for the statement that reads it). Indeed, the worksheet's calculation
might be set to "manual" which would likely cause the calculation not to be
done at all until you hit F9.

To ensure the sheet is up-to-date you would really need to get VBA to order
a sheet recalculation before reading the result cell.

As well as being a bit uncertain, this has got to be a slow way of doing it.
You're relying on the control flow moving from the VBA to Excel and back
again.

As well as this, you've now got an activity that is integral to your VBA,
but is not visible when reading your code - i.e. you also need comments to
explain what Excel is doing.

So, I see "cons". Unfortunately, I can't see any "pros" - apart from the
fact that you're comfortable with it, which is always a big "pro" in itself.

What does "Redux" mean please????????????????

According to Wikipedia it means "brought Back", "Revisited"

If you need the result in VBA, why not use another method like Find,
Autofilter, SpecialCells etc, to lookup the value you need? Why
bounce from VBA to Excel and back to VBA? For a few formulas  it won't
hurt, but it tends to encourage bad programming practice. Take
advantage of the built-in features of Excel (and VBA) and you'll be
better off in the long  run.

The OP's issue was covered and I had a closely/loosely
related question, so I hijacked the thread (continued it, but with a
slightly different issue) and decided to use the aparently obscure
term to indicate a change. Sorry. I thought it was didely known
within such educated circles as this. (;-)
Also, thanks to Wikipedia to know I used it correctly. Which makes
me wonder if there'll be a song for spelling Wikipedia...?
e-n-c-y-c-l-o-p-e-d-i-a (:-)
[[If you don't get this reference, you're too young to remember the
Micky Mouse Club TV show, but I digress]]

2- RE: Using Find, Autofilter, SpecialCells etc...
I don't know those, but am very comfortable with the sheet function
VLOOKUP...and didn't want to mess with trying to do the vba  version
discussed for the OP.

Also, I take Dave's comments as meant, to help, clarify. I *did* ask.

FYI: It works well for me. My reasoning was that the basic  Excel
formula functions  must be coded pretty tightly since they do lots of
computation in complex sheets and it all seems to happen very fast as
far as I can tell. In addition, the order of sheet operations must be
well organized for the sheet calculations to work correctly. So, why
not use that instead of code I write which must be interpreted (or so
it seems) and could be slow (since I'm still learning and without
formal VBA training) and potentially have errors which I'll have to fix.

3 - RE: Bouncing back and forth 'tween VBA & Sheet calc.
Uh.... See above.


4 - I thought my method was taking "advantage of the built-in features
of Excel (and VBA)"

I learned long  ago that there can be many ways to code a particular
solution and none are necessarily wrong, if they work. There are just
advantages and disadvantages and as long as the disadvantages are
acceptable/unimportant, go with it.

I'm not sure I understand " encourage bad programming practice". I
guess I was implicitly assuming that the Sheet function operates as a
subroutine which is already proven. I'm just calling it.
Reinventing the wheel and all that.
Thinking about the sheet functions, here's my rationale - right or
wrong. Sheet formulas  must be evaluated whenever a cell value change
occurs and in the order which allows proper propagation throughout the
sheet's (possibly) many formulae. Therefore, any change must be
handled completely and immediately after an input variable changes.
I get the impression that his thoughts are
from inference and his expierence rather than someting specified in
formal (uSoft) VBA training. Not to say they are not to be given some
weight, but not from specific, official cautions about the operation
of VBA/EXCEL.
If I continue to use this method and experience some problems, I'll
try to keep his cautions in mind and consider using VBA functions/code
to resolve them.

My point was, if you are using VBA merely to read the result
of a formula, perhaps there is a way to recode the routine to do the
calculation exclusively in VBA, to reduce the unnecessary back and
forth between the worksheet  and the object model. That would be
"taking advantage of the built-in functions  of Excel (and VBA)".

My comments were directed towards those interested in
improving their VBA routines (and formula writing) in terms of
efficiency.

In my experience, there are many ways to write formulas  and
code, but some are much better (relatively speaking) than others. The
most popular example I can think of is using VBA to loop through a
range of cells. Oftentimes there are ways to rewrite a routine to
take advantage of built-in functions (the Find, SpecialCells, etc I
mentioned earlier) that will result in speed gains of orders of
magnitude. In other words, all ways are not roughly equivalent, and
there are clear advantages to using one process over another.
Trying to find the most efficient way to complete a task will lead to
benefits in your own programming in the long  term.

It has nothing to do with right or wrong; it's all in terms
of speed and efficiency.

If your way leads to correct results, there is nothing wrong
with that. In absolute terms, you probably won't see (or notice) much
benefit in re-factoring.

Ahh. I see and understood. I see an issue with the term
"formula". For a purely mathematical formula, I agree 100%.
Calculate in VBA and I do. Other manipulations are another story.
My example is for VLOOKUP. A sheet function I am very familiar
with. Technically it is an Excel "formula" but for a multi column
table is a relatively complex operation to code in VBA. A case which
makes the use of the sheet function (arguably) easier. Having the
table visible in the sheet is a plus compared to something like
loading arrays. It also appears to be an immediately executed function.
Another thing to consider is the rich capability of ether Excel or
VBA. One has a hard time simply knowing all the available functions
in a given version of Excel vs. VBA; much less being familiar with them.
There are situations where I use a specially construced
stringvariable and a calculation to determine an index with Instr$

- The line of information in an Excel cell that begins with an equals sign
is called a "formula". Doesn't matter if it's simply =A1+1, or extremely
complex, it's still called a formula.

- VLookUp is an Excel function. By itself, it is not a "formula". However,
it will normally be used IN a formula.

Although it is quite possible to put values in cells, allow VLookUp to do
its thing, and then grab the value back out of the formula's cell, it is
also possible to simply call VLookUp directly in your VBA code. The
parameters to the VLookUp call can be ranges (named or unnamed) of cells in
your worksheet  and/or local VBA variables, and the result will simply be
returned to a VBA variable like any other function call. This keeps your
VLookUp calls, etc, firmly within your VBA, while still giving you access to
their power.

You might want to rethink your worksheet  design, if you have so many
formulas that the workbook is slowing down. Although even just a few
array formulas  (if they reference enough cells) can do this.

There might be a language problem here ...

A function like

> Function MyQuery(rng as Range, rngtoCheck as Range
> MyQuery =
> IF(ISNUMBER(VLOOKUP(J8;WebQuery;5;FALSE));VLOOKUP(J8;WebQuery;5;FALSE);0)
> End Function

is not rewriting the function, it is using (or calling, or encapsulating)
it.

"Rewriting" would involve writing your own version of VLookUp from scratch.

I agree, but my point was that if your function *only* calls
worksheet functions, it's wasting processor time.