Performance issues, looking for alternate solutions

[Guest]

We have a large spreadsheet we build every night for our floor traders, which
combines information from Excel (pricing from Bloomberg) and Access
(accounting data), using VBA to mediate.

There is a major performance problem I'd like to fix. One of the major steps
runs calculations combining this information, using sheets in Excel to do
this. This is very slow. The same code running in VBA arrays works much
faster, 2 to 3 times. The problem is that when the results are pasted back
into Excel, any existing formatting is erased.

What I'd like to do is keep the formatting that's currently on the sheet,
and paste in just the values we're calculated in the array. This has so far
eluded me. Any suggestions?

Maury

 

[kkknie]

Not sure what you mean by pasting back from an array. If it truly is
paste, you can use paste special:

Selection.PasteSpecial Paste:=xlPasteValues

 

[Jamie Collins]

Not sure what you mean by pasting back from an array. If it truly is a
paste, you can use paste special

And if it isn't a paste, you can use the Range's Value property e.g.

vntArray = Sheet1.Range("$C$2:$E$5").Value
Sheet1.Range("$C$2:$E$5").Value = vntArray

Jamie.

--