Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

The Visual basic Help has a topic: List of Worksheet Functions

Available to Visual Basic. But is does not say how to use them.

I would like to replace some too long formulas in my worksheet by

functions.

To be more specific, one of those (and it is way not the longest

one!) uses 2 Worksheet Functions:

=IF(ISNUMBER(VLOOKUP(J8;WebQuery;5;FALSE));VLOOKUP

(J8;WebQuery;5;FALSE);0)

where WebQuery is a named range including a column with values 1 to

10 (J8 would be 1), left to the columns brought back by the query.

I must first check if VLOOKUP brings back a number, because

different updates of the Webquery could bring different numbers

of "usable data", with a maximum of 10, but very often less, and then

instead of the numbers I need, I would get some text brought back by

the site where I make the query.

If those functions are available to VB as said in the Help, how can I

use them in a VBA function?

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.

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.

Didn't find what you were looking for?
Find more on Worksheet functions in VBA
Or get search suggestion and latest updates.

Related Topics:

- Adding a Worksheet_Event when adding a worksheet via VBA
- Cracking VBA password to access hidden worksheet ?
- Need help coping images (gif pictures) from worksheet to worksheet
- Need help coping images (gif pictures) from worksheet to worksheet
- Excel data transfer automatically from master worksheet to other worksheets
- Can you pass a worksheet as an argument to a function
- Help with Formatting a cell holding a custom worksheet function
- How does the Worksheets function work?
- Wierd problem in VBA code re Mid function
- description/help with own VBA functions in Excel
- SAS CDAGET function to use in VBA
- How to use add-in functions in VBA
- Help with VBA function checksum
- How to make VB6 function work in VBA
- VBA Excel Coding for dynamically changing function range
- How to make VB6 function work in VBA
- cells containing formula that refer to user-defined VBA function
- VBA Function in Excel 2000
- Looking for a topological map of VBA object/classes, methods, function
- Accessing help file info for non-Excel 2007 functions from VBA
- Convert code from excel 2000 vba to excel 2003 vba
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA