Speed of worksheet functions

[HarryS]

why does worksheetfunction.acos(c) take about 10 times the time of the
atn( sqr(1-c*c)/c)) method

 

[RickXL]

Hi.

I don't understand the details but I think of Excel as being made up of two parts: the worksheet part and the VB part.

There is a link between the two parts that is surprisingly fast. However, setting up that link to transfer data has a large overhead. So if you want to use a worksheet function from VB you have to wait for the link to be opened up. That is where the time will be spent.

So things that will take a lot of time will include those where you use VB to loop round a range of cells on the worksheet. Each transfer will incur an overhead.
Things that will be quicker than expected will be when you transfer a whole table of data between worksheet and VB using an array. This will transfer a lot of data with only one setup overhead.

Consequently, if you can perform all your calculations in VB then that will usually be much quicker than constantly referring to the worksheet - even if the calculation is essentially the same.

 

[HarryS]

Thanks Rick.. in doing astronomy functions high speed of trigonometric functions is essential. I had thought that a designed arcCos or ArcSin would be faster than going through the old atn() function and correction for quadrants..
There is a similar difference between BMP's. The use of a memory bit map and then StretchBit to put it to the HDC if many times faster that writing direct to the screen or to a Userform or Frame (hdc)

 

[RickXL]

What you say is correct.

When using a typical computer you never deal with the hardware directly. There are always a number of software layers between you and the hardware. Processing time increases as the number and complexity of the layers increases.

However, it is not always best to change programs to speed them up. You can end up with something that is complex and its workings are not obvious. This makes maintenance a problem. If you are going to write a program once and use it a million times then some optimization is a good idea but if you are working in a more experimental environment where you write a program once and run it once then change it clarity of coding is also important.

When you draw bits on a device context you are using optimized OS functions. When you write to a user form you are using VBA that communicates with the underlying app which then has to get the attention of the OS which checks to see if anything more important is going on before it performs the repaint operation.

 

[joeu2004]

I had thought that a designed arcCos or ArcSin would be faster than going through the old atn() function and correction for quadrants.

I suspect the VBA Atn expression is so much faster on Intel-compatible CPUs because there are hardware instructions to calculate arctan and sqrt.

I don't know how WorksheetFunction.ACos is implemented. But there is no hardware instruction to calculate arccos directly.

I think of Excel as being made up of two parts: the worksheet part and the VB part.

Yes: an Excel thread and a VBA thread.

There is a link between the two parts that is surprisingly fast. However, setting up that link to transfer data has a large overhead.

The "link" is interprocess communication, a feature provided by the O/S. There is no overhead to set it up, certainly not a per-message basis. But interprocess communication is relatively slow compared to a VBA function call.

So if you want to use a worksheet function from VB you have to wait for the link to be opened up.

I disagree. The WorksheetFunction methods does not communicate with the Excel thread. In contrast, the Evaluate function does.

The WorksheetFunction methods are machine-language implementations that are intended to emulate Excel functions exactly. (I found some corner-case differences.) They are called directly from VBA.

So things that will take a lot of time will include those where you use VB to loop round a range of cells on the worksheet. Each transfer will incur an overhead.

That would be true if "c" were a type Range variable. But in that case, the VBA Atn expression and WorksheetFunction.ACos function take about the same amount of time (on my computer). In fact, the VBA Atn expression takes a little longer, presumably because of the several references to "c".

Instead, I presume that "c" is a VBA variable, preferrably type Double. In that case, I can duplicate Harry's observation within reason. Thus, Excel is not involved in either calculation.

 

[ZVI]

I disagree. The WorksheetFunction methods does not communicate with the Excel thread. In contrast, the Evaluate function does.
The WorksheetFunction methods are machine-language implementations that are intended to emulate Excel functions exactly.
(I found some corner-case differences.) They are called directly from VBA.

Hi,

WorksheetFunction object belongs to Excel class object not to VBA one.

To prove this:
1. Press Alt-F11 to open VBE
2. Press F2 to go to the Object Browser
3. In the top left combobox choose VBA instead of the default All Libraries<all libraries=""></all>
4. Put into the searching combobox, which is below, the text WorksheetFunction and press Return
5. The result - No items found

Repeat the same with Excel library in point 3 to see that WorksheetFunction object belongs directly to the Excel object.

BTH, the full syntax is Application.WorksheetFunction where Application represents the entire Microsoft Excel application.

VBA online help also says that WorksheetFunction Object used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic.
Container represents its own interface (VBA specific) which can cause differences in functionality relative to the native Excel functions.

Regards,
Vlad

 

[HarryS]

Well that stirred up a bit more interest than I thought. As I remember it ( at 73 Memory not so good .. Hell and the Hereafter is trying to
recall what the hell am I here after in the shed ) Cos Sin are convergent series but now ACos ASin are best fit polynomials of degree 6 or 7 .
The point about the OLD (1990 ) astronomy software is they repeat many calculations... especially Index =1 => x ....=2 => y ...=3 => z
and recalculating values like CosLat, SinLat, each time... A class module can avoid this and have the advantages of CallByName for reading and listing
to a worksheet. Google to find Lat Long Time zone elevation DLSTime were not about when I was programming the PDP 8 or Wang (also 8K ) back in 1975

 

[HarryS]

To try to bypass the moderator and change the topic a bit

.. Some Class modules for Sun Rise Set Altitude Azimuth are about ...

But has anyone got a source of vba or VB source code for classes for Moon Rise Set Phase Alt Azi

After 40 years teaching I still do some part time teaching of high school science, maths and computing.
( and I try to refrain from reinventing the ellipse )

Please

 

[ZVI]

If the speed is critical then VBA analogues of WorksheetFunctions like ACos, ASin, Atan2 can be created. Let me know if it's required.
As the goal of the topic has been changed we now jump to here.