Could you re-write every built-in function in VBA?

[nick93]

Title. Could you? I've been wondering for a long time...

[kev_]

no I couldn't - not enough years left in the locker and microsoft keeps adding new functions ...

and I wouldn't want to use them anyway - UDF would be slower than built-in functions..so why bother....

[nick93]

I meant like, theoretically... is there any function that does stuff that can't be replicated by VBA? guess that's the real question :P

[shg]

Everything but the IF function, I expect, for its ability to minimally evaluate its arguments.

[MrShorty]

Off the top of my head, I cannot think of any of Excel's built in functions that could not be replicated in VBA or other programming language. As kev_ notes, there could be some discussion about whether VBA is a good programming language for performing any of these tasks, but I would be optimistic that the algorithms behind any of Excel's functions could be replicated in VBA.

[Leith Ross]

Theoretically, yes they all could be recreated. VBA's extensible programming allows for using other scripting languages and libraries to expand it capabilities. It would not always yield the most efficient code to perform the task due to so many levels of indirection during execution. Low level langage programming always yields the best in speed but is far more difficult to code in. The programmer ultimately is faced with trade offs between speed, flexibility, and efficiency.

[shg]

VBA couldn't do the selective argument evaluation that IF does.

[Leith Ross]

VBA could use other scripts to create a praser to solve the issue. It may not be pretty but possible.

[kev_]

how about a Forum team-building exercise?
- ask for volunteers to take part in the challenge
- one volunteer to list all the Excel functions
- every other volunteer to adopt one Excel function
- adopted function to be replicated without using any Excel function or VBA function that mimics an Excel function I

[shg]

Gosh, that sounds like fun ...

[mikerickson]

VBA couldn't do the selective argument evaluation that IF does.

I'm not sure what you mean.
What values would this return different than Excel's IF.

Please Login or Register  to view this content.

[shg]

Watch this evaluate (or not; you know what it does ):

=IF(SQRT(4)=2, 1, NORM.S.INV(RAND()))

In VBA, you'll get handed the result of the value-if-false clause. The IF function doesn't bother evaluating it.

[nick93]

VBA couldn't do the selective argument evaluation that IF does.

wait, why not? Why is excel's IF any different than VBA's? I though I could write it in VBA in 1 minute unless I don't get what you're saying...

how about a Forum team-building exercise?
- ask for volunteers to take part in the challenge
- one volunteer to list all the Excel functions
- every other volunteer to adopt one Excel function
- adopted function to be replicated without using any Excel function or VBA function that mimics an Excel function I

I liked the idea but we'd probably have to narrow it down to some function categories (probably exclude all financial and mathematical functions). after all there's like hundred of functions. Also off the top of my head I literally can't think of any basic function that I can't write in VBA. Then there's functions that it's not clear what they do or what the algorithm is. An example of the former is ENCODEURL I guess. But I suppose it's just a matter of understanding each function. So I guess it would be kind of pointless :p

In VBA, you'll get handed the result of the value-if-false clause. The IF function doesn't bother evaluating it.

I suppose you're right but it would still work. I'm not saying that under the scenes Excel functions aren't faster. I'm saying they can probably all be replicated by VBA...

[mikerickson]

Watch this evaluate (or not; you know what it does ):

=IF(SQRT(4)=2, 1, NORM.S.INV(RAND()))

In VBA, you'll get handed the result of the value-if-false clause. The IF function doesn't bother evaluating it.

with the code in post #11,

=myIF(mySQRT(4)=2, 1, myNORM.S.INV(myRand())) should return the same results (assuming mySQRT works as SQRT)

[kev_]

@shg - your enthusiasm is to be applauded.....but....what a mammoth project.........scares me ..... and it was my (very) tongue-in-cheek suggestion.....

[xladept]

Hey Kev.

I read post #10 as sarcasm - given away by the "Gosh"

[kev_]

@xladept I was taught by my mother that "only the Brits" understand sarcasm...
Funnily enough "gosh" makes it sound less sarcastic.

[xladept]

@ Kev - I read that as sarcasm

[kev_]

@xladept - that is quite amazing.... "one nation divided by a common language" is clearly no longer true

[xladept]

Awwww - Gosh

[shg]

with the code in post #11,

=myIF(mySQRT(4)=2, 1, myNORM.S.INV(myRand())) should return the same results (assuming mySQRT works as SQRT)

I wasn't arguing that you can't achieve the same functional result, mike, only that a VBA function can't do its own argument evaluation. I think it was a nice touch by Excel's designers, especially considering that about half of all functions used in Excel are IF.

@ Kev - I read that as sarcasm

Bingo.