convert text to number - performance problem

V

Vladimir Sveda

In my VBA code I use:
....
For Each c In Range("MyRange").Cells
c.Value = CSng(c.Value)
Next c
....
and it is performance bootleneck of that code

Can you, please, suggest better code.
Thank you in advance!

Vlado
 
J

Jim Cone

Vlado,
What are you trying to accomplish?
Numeric values in cells are always type Double.
--
Jim Cone
Portland, Oregon USA



"Vladimir Sveda"
<Vladimir
[email protected]>
wrote in message
In my VBA code I use:
....
For Each c In Range("MyRange").Cells
c.Value = CSng(c.Value)
Next c
....
and it is performance bootleneck of that code
Can you, please, suggest better code.
Thank you in advance!
Vlado
 
J

Jacob Skaria

Hi Vladimir, can you paste the full code (or atleast the code above For
Each). Also usually range is specified as below

Dim MyRange As Range
Set MyRange = Range("A1:A10")
For Each c In MyRange.Cells
c.Value = CSng(c.Value)
Next c

If this post helps click Yes
 
J

JLGWhiz

Hi Vladimir, if the problem is the time it takes to loop through the range
and convert the individual values, then it might be better to just convert
only when they are used. That is, if a value from that range is needed for a
calculation or some other use, then use the CSng(value) at that time.
Otherwise, I believe you are stuck with doing the loop.
 
C

Charles Williams

Dim vArr as variant
dim j as long
dim k as long

varr=Range("MyRange").Value2

on error resume next
for j=lbound(varr,1) to ubound(varr,1)
for k=lbound(varr,2) to ubound(varr,2)
varr(j,k)=cdbl(varr(j,k))
next k
next j
Range("MyRange")=varr
 
V

Vladimir Sveda

Thanks to all, solved by "philosophy change" (and of course algorithm change)

Vlado
 
Top