Can you pls explain how to compile VBA Excel code.
Do you mean hitting in VBA editor - Debug -> Compile VBA project. I am already doing this.
Do you mean something else?
You could be missing something. This is VB6 code, not VBA code. I wouldn't think this code would be applicable to VBA.
In many earlier posts in this thread, folks have referred to Excel VBA. I was able to run this code in VBA after changing App to Application at 2 separate instance. So, this code definitely runs on VBA. Maybe not faster though - can you please confirm that it is not faster in VBA but faster in VB6? Thanks
I don't think I'll try to confirm it, but VB6 compiles to a native executable, so the code could be very quick compared to VBA. VB6 is over 20 years old, and hasn't been improved upon.
VBA on the other hand has continued to evolve with Microsoft office.
It is possible that 10 years or so ago when this thread was started, that the code may have been an improvement for VBA as well, I don't know, I haven't read the thread. But since VBA has continued to evolve, it wouldn't surprise me that the VBA of today is faster than the VBA of ten years ago, and that intrinsic functions have been improved so using the builtin split today would be better than the builtin split from 10 years ago, and better than using alternatives written in VBA itself.
VBA on the other hand has continued to evolve with Microsoft office.
It is possible that 10 years or so ago when this thread was started, that the code may have been an improvement for VBA as well, I don't know, I haven't read the thread. But since VBA has continued to evolve, it wouldn't surprise me that the VBA of today is faster than the VBA of ten years ago, and that intrinsic functions have been improved so using the builtin split today would be better than the builtin split from 10 years ago, and better than using alternatives written in VBA itself.
I wouldn't be surprised on the opposite either: if now it is slower.
For the OP: in any case, when it says faster than the native Split function, it is talking about the normal situation, and the normal situation in this case is VB6's compiled code.
The folks can mention in the thread that the code runs also fine on VBA, but that doesn't mean that it will be faster than the native VBA's Split function, which is compiled.
The folks can mention in the thread that the code runs also fine on VBA, but that doesn't mean that it will be faster than the native VBA's Split function, which is compiled.
Thank you both for your help. Seems I am running it correctly. I will have to find some other way to speedup my code. Thanks!
p.s I took a look back and read through the whole thread. Although a few posts mention modifying and using the code with VBA, there is no evidence that anyone benchmarked the result to see if it was actually faster in VBA over the inbuilt function. It may never have been faster. The code wasn't written for VBA and may never have provided any improvement for VBA.
If your testing finds it to be slower now, it doesn't matter if it was faster or slower back then. I wouldn't bother using it if it wasn't faster, or even if it was only slightly faster than the inbuilt functionality.
By the way, the original speed benchmarks that meri did, and where he refers to VBA.Split, that is not indicative that the code was run or had anything to do with VBA when Meri wrote it. Originally, Visual Basic and VBA had two separate devlopment paths and teams, because VB predated VBA and VBA was based on VB. After VB3, (i.e. starting with VB4), the core of the Visual Basic Language development was merged, and the VBA namespace became the common baseline, so VB4, 5 and 6 have many methods that are VBA.xxxx because of the common code base, but of course VB normally compiles to a native executable, whereas VBA doesn't. Meri's code was meant to speed up VB6 and provide Split/Join capability to VB5, but whether it improved VBA was not a given. It is possible that it never did.
Ahh... yes, when you read VBA.[something] in the context of VB6 code / VB6 programs, it just means a library named VBA, but it is a VB6's library that has that name (VBA), it does not refer to Office's VB environment.
Thank you both for your help. Seems I am running it correctly. I will have to find some other way to speedup my code. Thanks!
you can try,some method will be speed on vba.
I was originally interested in this, but I rarely use excel VBA. In theory, most of the codes of VB6 and VBA are common. You only need to test. Of course, he can’t choose to compile and optimize, but they are different. The method speed is still quite different.