Best CPU for Excel VBA (hardward question)

[kalong08]

I have a tedious VBA programme that requires several minutes to run on my two current computers. I have already tried hard to improve the code according to the guidelines found from other websites. Hardware upgrade may be the only improvement for my situation.

The capabilities of my current computers are shown below:

Computer 1:

8192MB RAM

Intel(R) Core(TM) i7-8550U CPU @1.8GHz (8 CPUs), ~2.0GHz

Computer 2:

RYZEN™ 9 4900H CPU

16GB RAM

I would like to purchase a new computer with a suitable CPU. I have checked that VBA cannot run on multi-thread and it can only use 1 processor at one time. Therefore, I am wondering the best CPU for VBA shall be determined by their Single Thread Performances. Here is a list of CPUs ranked by their Single Thread Performances. PassMark CPU Benchmarks - Single Thread Performance

Intel Core i9-11900K @ 3.50GHz is ranked first. However, I have found out that it has 8 Cores and 16 Threads. I am wondering if VBA can really utilize all its cores. I am not sure if it is really the best CPU for VBA.

VBA uses 1 processor only at a time. Does it mean I shall find a CPU with the least number of Cores but with highest clock speed? May I further I ask which CPU is the most suitable for running a VBA?

Greatly appreciate for your kind attention.

Thanks!

 

[Norie]

What have you done to try and improve the code?

 

[kalong08]

Basically, I have followed the website:
Optimize VBA Code to run Macros Faster

My programme is using a lot of Variants and not using Option Explicit. Does it affect much?
I am wondering if I need to change it.

 

[Norie]

Some of the tips there are quite useful, some I'm not too sure about - for example, I'm not sure how combining multiple lines on one line separated by a colon would speed things up.

I don't think not using Option Explicit would make a lot of difference efficiency wise but I would highly recommend using it to avoid other problems - it could actually help speed up writing the code.

There is one major thing not mentioned on that page, avoid+ writing/reading directly to/from sheets if possible.

Whether that would be relevant in your situation is hard to tell without knowing more about what you are trying to do with the code.

P.S. Another simple thing to aid with efficiency, don't use Integer use Long (integer).

 

[diddi]

i find optimal speed by not using sheets at all. i run purely on userforms with all storage in text files and all calculations from RAM. that said, i trialled a basic compiler that compiled code very much like VBA into addins for excel. on a prime number benchmarch it was like 100 times quicker running inside excel than the equivalent VBA!

you could post some code for others to look at if you please

 

[MARK858]

There is one major thing not mentioned on that page, avoid+ writing/reading directly to/from sheets if possible.

I must agree with Norie, interacting with the sheet multiple times is one of the main reasons code runs slowly which is one of the reasons you see codes posted using Arrays, Scripting Dictionary etc. so they only interact once with the worksheet.

As for the article I also haven't noticed or seen anyone claim that writing on a single line with separators makes the code noticeably faster (it can make it harder to read though) and as for

It is faster to avoid the PasteSpecial totally with

Sheets("Source").Range("A1:E10").Copy Sheets("Destination").Range("A1")

Which you also don't need the Application.CutCopyMode = False line for.

 

[diddi]

also using With blocks speeds things up because excel is not having to re-reference sources