A curious question

[davidm]

Can someone explain why EXCEL's built-in functions (like SUM, AVERAGE, IRR, NPV) cannot be replicated in terms of efficiency and speed using VBA? As a related issue, what language does EXCEL's code source use?

Just curious.

David.

[Bob Phillips]

- because Microsoft know all the ins and outs of Excel and the operating
system and so can take full advantage
- because VBA is not compiled
- because Excel uses compiled C code which is much faster than VB or VBA
- etc.

--

HTH

RP

"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> Can someone explain why EXCEL's built-in functions (like SUM, AVERAGE,
> IRR, NPV) cannot be replicated -in terms of efficiency and speed -using
> VBA? As a related issue, what language does EXCEL's code source use?
>
> Just curious.
>
> David.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=501971
>

[davidm]

<<because Excel uses compiled C code which is much faster than VB or VBA>>


Many thanks Bob. You have confirmed my sneaking suspicion. I will reserve a clarification on Compiled vs Interpreted language for another time.

David

[[email protected]]

Thanks for yur response. But i wanted to do the filtering operation
through VBA macro. so that the process can be automated.

[Bob Phillips]

You seem to be responding to the wrong thread.

--

HTH

RP

<[email protected]> wrote in message
news:[email protected]...
> Thanks for yur response. But i wanted to do the filtering operation
> through VBA macro. so that the process can be automated.
>

[Bob Phillips]

Don't forget also that when you use Excel, it is storing and updating
internal information all the time, so it has a whole bunch of information
that any user written routijne would probably have to determine. May not be
a big issue with say a simple SUM, but it could get critical in other areas,
such as determining the last value in a column.

There are so many reasons, which is why VBA should always be the last resort
IMO, never the first.

--

HTH

RP

"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> because Excel uses compiled C code which is
> much faster than VB or VBA
>
> Many thanks Bob. You have confirmed my sneaking suspicion. I will
> reserve a clarification on Compiled vs Interpreted language for another
> time.
>
> David
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=501971
>

[tony h]

VBA the last resort never the first?

Sounds like a point of view designed to raise the temperature. Or one for the pub!

Many is the time I have taken a traditional spreadsheet and re-written the calculations into VBA to make the processing quicker and more easily understood.

Regards,
Tony

[Norman Jones]

Hi Tony,

> Many is the time I have taken a traditional spreadsheet and re-written
> the calculations into VBA to make the processing quicker

I would normally expect Excel formulae or inbuilt functions to be faster
than a corresponding VBA solution


---
Regards,
Norman



"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> VBA the last resort never the first?
>
> Sounds like a point of view designed to raise the temperature. Or one
> for the pub!
>
> Many is the time I have taken a traditional spreadsheet and re-written
> the calculations into VBA to make the processing quicker and more
> easily understood.
>
> Regards,
> Tony
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=501971
>

[Bob Phillips]

I didn't say never, I said last resort.

--

HTH

RP

"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> VBA the last resort never the first?
>
> Sounds like a point of view designed to raise the temperature. Or one
> for the pub!
>
> Many is the time I have taken a traditional spreadsheet and re-written
> the calculations into VBA to make the processing quicker and more
> easily understood.
>
> Regards,
> Tony
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=501971
>

[Tom Ogilvy]

Turn on the macro recorder and then set your filter manually.

Now turn off the macro recorder and look at the recorded code. This will
show you how to do it with VBA. Debra's site also has VBA examples.

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Thanks for yur response. But i wanted to do the filtering operation
> through VBA macro. so that the process can be automated.
>