Rounding function in VBA does not display same value on worksheet

[profray]

I have imported strings that represent simple equations with a constant and a linear term. I want to split them apart so that I can used them for more calculations. It should look like this

sigma1	Constant	Linear
0.557-0.048M	-0.557	-0.048
0.599-0.054M	-0.599	-0.054
0.562-0.047M	-0.562	-0.047
0.492-0.035M	-0.492	-0.035
0.549-0.044M	-0.549	-0.044

<colgroup><col><col><col></colgroup><tbody>
</tbody>

But the terms (when given more format space) are actually like this:

			-0.5569999814	-0.0480000004
			-0.5989999771	-0.0540000014
			-0.5619999766	-0.0469999984
			-0.4920000136	-0.0350000001
			-0.5490000248	-0.0439999998

<colgroup><col><col></colgroup><tbody>
</tbody>

The VBA code reads the first column as a string then splits the string and converts them to single precision. That is when I apply the Round function in VBA as shown in the snippet below

Sub SplitString()
Dim wksdata As Worksheet
Dim c1 As Single, c2 As Single
Dim row As Integer
Dim sigma As String
Set wksdata = ActiveSheet
 For row = 4 To 8
 sigma = wksdata.Cells(row, 7) 'Get string
 c1 = Round(CSng(Left(sigma, 6)), 3) 'constant
 c2 = Round(CSng(Mid(sigma, 6, 6)), 3) 'linear
 wksdata.Cells(row, 8) = c1  'numbers into cells
 wksdata.Cells(row, 9) = c2  'next to string
 Next row
End Sub

 

[mikerickson]

And what is the question?

 

[joeu2004]

First, use type Double and CDbl instead of Single and CSng. That is likely the biggest reason for the "extra" decimal fraction dgitis.

Also, it is prudent to use type Long instead of Integer, especially for row numbers. That avoids any possible "overflow" error.

Second, use WorksheetFunction.Round instead of VBA Round. The latter does "banker's rounding" -- rounding "half" to even, whereas Excel Round always rounds "half" up.

That said, it should not be necessary to explicitly round contants or even convert text to numbers. The following should be sufficient:

Sub SplitString()
Dim c1 As Double, c2 As Double
Dim row As Long
Dim sigma As String
row = 5
sigma = "0.492-0.035M"
 c1 = Left(sigma, 6) 'constant
 c2 = Mid(sigma, 6, 6) 'linear
 Cells(row, 8) = c1  'numbers into cells
 Cells(row, 9) = c2  'next to string
End Sub

In fact, you do not even need to use c1 and c2.

Sub SplitString2()
Dim row As Long
Dim sigma As String
row = 6
sigma = "0.492-0.035M"
 Cells(row, 8) = Left(sigma, 5)  'numbers into cells
 Cells(row, 9) = Mid(sigma, 7, 5)  'next to string
End Sub

But note a significant difference in interpretation -- a correction, perhaps: the result of is +0.492 and +0.035 in the second example, instead of -0.492 and -0.035 in the first example.

 

[profray]

Apologies for not giving the punchline. The question is/was "How to I get a "truly" rounded number into the cell via VBA? I think the answer is below from next reply.

 

[profray]

Thanks, it works. I used doubles and it came out exact. Also your suggested SplitString2() worked as well. Also the Round function is not necessary here, checked it later, Thanks again

Sub SplitStringDBL()
Dim wksData As Worksheet
Dim c1 As Double, c2 As Double
Dim row As Long
Dim sigma As String
Set wksData = ActiveSheet
 For row = 4 To 8
 sigma = wksData.Cells(row, 7) 'Row 4, Column G to start on sheet
 c1 = Round(CDbl(Left(sigma, 5)), 3) 'Get left side (constant)
 c2 = Round(CDbl(Mid(sigma, 6, 6)), 3) 'Get right side (linear)
 wksData.Cells(row, 8) = c1  'Put numbers into cells
 wksData.Cells(row, 9) = c2  'next to string
 Next row
End Sub