Excel VBA: Fehler beim Übertragen von Zahlen

[Thomas_Wilk_8c0dfb]

Hallo Experten,

ich habe ein kleines Problem und hoffe dass mir jemand bei dessen Lösung helfen kann:

Ich übertrage aus einem Zahlenstring (Single) Werte in eine Zelle mit folgendem Code:

Tabelle1.Cells(2, 1).Value = Round(sngOutput(1), 3)

Der vorher berechnete Wert von sngOutput(1) ist 0,7153076.
Mit Round(sngOutput(1), 3) möchte ich den Wert auf 3 Nachkommastellen runden, d.h. auf 0,715.

In die Zelle auf dem Excel Arbeitsblatt wird aber 0,714999973 übertragen.

Warum ist das so?

Vielen Dank.

Gruß
Thomas

[Villon]

Hallo Thomas,

das liegt an der Variablendeklaration als single, der 32 Bit (4 Bytes) lang ist. Bei Deklaration als Double (64 Bit) sollte die Ungenauigkeit verschwinden.

Ich hoffe, dass ist ausreichend.

Gruß

Frank

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

[Chewpapa_22ca98]

Hallo,

ich habe ein kleines Problem und hoffe dass mir jemand bei
dessen Lösung helfen kann:

Ich übertrage aus einem Zahlenstring (Single) Werte in eine
Zelle mit folgendem Code:

Tabelle1.Cells(2, 1).Value = Round(sngOutput(1), 3)

Der vorher berechnete Wert von sngOutput(1) ist 0,7153076.
Mit Round(sngOutput(1), 3) möchte ich den Wert auf 3
Nachkommastellen runden, d.h. auf 0,715.

In die Zelle auf dem Excel Arbeitsblatt wird aber 0,714999973
übertragen.

Warum ist das so?

das sieht nach einem heftigen Bug in der Verwaltung von Fließkommazahlen in Excel aus. Die darstellung von Binärzahlen kleiner als Eins und größer als Null ist recht kompliziert und passt nicht so recht zu unserem Dezimalsystem. Wenn die Anzahl der verfügbaren Bits begrenzt ist, kann es zu solchen fehlern kommen, wenn das Programm wirklich versucht, solche Zahlen Binär abzubilden.

Ich würde den Bug einfach zur Kenntnis nehmen und mit der Verwandlung in einen String umgehen. Versuch mal …

Tabelle1.Cells(2, 1).Value = CStr(Round(sngOutput(1), 3))

Gruß, Rainer

[Dirk_Pegasus]

Hallo Thomas,

bekanntermassen rechnet Excel manchmal falsch. Soll schon immer so sein und hängt wohl auch von den Zahlen ab. Als Workaround:

Function roundRealy(dez As Integer, oldVal As Double) As Double
 Application.Volatile
 roundRealy = CInt(10 ^ dez \* oldVal) / 10 ^ dez
End Function

Klappt auch für dez = 0 oder dez = -1 )

mfg

Dirk.Pegasus

[Chewpapa_22ca98]

Hallo Dirk,

Soll schon immer so sein und hängt wohl auch von den Zahlen ab.

das hängt einfach mit der Verwaltung des Fließkommas zusammen.

Ganze Zahlen sind aus Binärzahlen leicht zusammenzusetzen, da gibt es keine Probleme. Die Werte der Bits lassen sich ja einfach mit Excel darstellen, einfach 2^n bis 2^0 anzeigen lassen.
Setze die Reihe mal nach 2^-n fort und vesrsuche einen beliebigen Dezimalbruch kleiner Eins und größer Null daraus zu errechnen, dann wird das Problem ganz einfach verständlich.

Gruß, Rainer

[Dirk_Pegasus]

Hallo Rainer,

ich sehe ja ein, dass es technische Gründe dafür gibt und danke für die Erläuterung, aber:

Ist mir als Anwender doch egal! Das Ding soll machen was ich will, bzw. was ich ihm sage.

Dazu noch eine Ankedote (?):
Früher hab ich mal mit „Borland Turbo Pascal“ entwickelt. Da wollte ich Massen (zB 200,000 m³) aufteilen. Danach zur Kontrolle die nicht verteilte Menge berechnen und die muss ja 0 sein! Ging dann aber nicht, da immer so ein Restchen blieb. Anruf bei Borland: „Das macht man ja auch nicht. Man geht über eine Fehlerschranke!“ Hat denn auch geholfen, aber nicht das Problem gelöst!

mfg

Dirk.Pegasus

[Chewpapa_22ca98]

Hi Dirk,

Ist mir als Anwender doch egal! Das Ding soll machen was ich
will, bzw. was ich ihm sage.

ich halte das ja auch für einen Bug, aber die Daten werden nun mal so verwaltet. Wenn man weiß wo der Fehler her kommt, kann man ihn manchmal vermeiden.

Dazu noch eine Ankedote (?):
Früher hab ich mal mit „Borland Turbo Pascal“ entwickelt. Da
wollte ich Massen (zB 200,000 m³) aufteilen. Danach zur
Kontrolle die nicht verteilte Menge berechnen und die muss ja
0 sein! Ging dann aber nicht, da immer so ein Restchen blieb.
Anruf bei Borland: „Das macht man ja auch nicht. Man geht über
eine Fehlerschranke!“ Hat denn auch geholfen, aber nicht das
Problem gelöst!

*gg* Jetzt ist es zu spät, ich hätte vorgeschlagen mit Brüchen zu rechnen, statt mit Dezimalbrüchen. Das ist bestimmt realisierbar (noch nicht versucht) und arbeitet dann mit ganzen Zahlen auch fehlerfrei.

Gruß, Rainer

[Thomas_Wilk_8c0dfb]

Hallo Rainer,

wenn ich die Zahlen als String übertrage, dann bekomme ich entweder einen Fehler in der Zelle („Die Zahl in dieser Zelle ist als Text formatiert […]“ - bei Zahlen kleiner Eins) oder Excel macht z.B. aus 1,715 eine 1715,000000000 (bei Zahlen größer Eins).

Ist also nicht wirklich eine schicke Lösung…

Danke dir trotzdem.

Gruß
Thomas

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

[Thomas_Wilk_8c0dfb]

Hallo Thomas,

das liegt an der Variablendeklaration als single, der 32 Bit
(4 Bytes) lang ist. Bei Deklaration als Double (64 Bit) sollte
die Ungenauigkeit verschwinden.

Ich hoffe, dass ist ausreichend.

Gruß

Frank

Hallo Frank,

leider macht Excel mit Double-Variablen den gleichen Mist.

Gruß
Thomas

[Chewpapa_22ca98]

Hi Thomas,

Ist also nicht wirklich eine schicke Lösung…

schade. So etwas passiert, wenn man nicht testen kann, ich habe kein Excel und mein VB macht den Fehler nicht. Man kann noch mehr daran herumschauben, aber wann das Ergebnis aussieht, wie gewünscht, kann ich nicht sagen. Das Koma, das scheinbar von CStr() erzeugt wird mit Replace wieder un einen Punkt zu verwandeln und aus dem Ergebnis wieder ein Single zu machen könnte funktionieren, muss aber nicht und elegant wäre die Lösung erst Recht nicht.

Hat denn der Tipp von Dirk ( … DEC() oder CDbl()… ) funktioniert?

Hast Du schon versucht, das Problem mit Format() zu lösen? (wieder nicht getestet. )

Gruß, Rainer

[Chewpapa_22ca98]

Hi Thomas,

in VB sieht diese Lösung sehr gut aus:

Tabelle1.Cells(2, 1).Value = Format(sngOutput(1), ‚#.000‘)

Die Rundungsregel ist aber nicht die gleiche!

Mit Round wird aus …

1.25 -> 1.2
1.15 -> 1.2

aber mit Format …

1.25 -> 1.3
1.15 -> 1.2

Gruß, Rainer

[Thomas_Wilk_8c0dfb]

Hallo Dirk,

Function roundRealy(dez As Integer, oldVal As Double) As
Double
Application.Volatile
roundRealy = CInt(10 ^ dez * oldVal) / 10 ^ dez
End Function

Ich bekomme die Funktion irgendwie nicht zum Laufen. Bitte hilf mir auf die Sprünge. Hier mein Code:

Dim dblZahl1, dblZahl2, dblAusgabe As Double

dblZahl1 = CDbl(TextBox1.Value)
dblZahl2 = CDbl(TextBox2.Value)

dblAusgabe = dblZahl1 * dblZahl2

'nun der Funktionsaufruf:
Tabelle1.Cells(2, 5).Value = roundRealy(dez:=3, oldVal:=dblAusgabe)

Ich bekomme den Fehler:
„Argumenttyp ByRef unverträglich“

Was mache ich falsch?

Vielen Dank!

Gruß
Thomas

[Thomas_Wilk_8c0dfb]

Hallo Rainer,

Hat denn der Tipp von Dirk ( … DEC() oder CDbl()… )
funktioniert?

Damit komme ich im Moment noch nicht wirklich zurecht (s. Antwort auf Dirks Eintrag).

Hast Du schon versucht, das Problem mit Format() zu lösen?
(wieder nicht getestet. )

Ja, habe ich getestet.
Format(sngAusgabe, „#.000“) macht aus 8,07799 ein 8078.
Format(sngAusgabe, „#,000“) macht aus 8,07799 ein 8,000.
Format(sngAusgabe, „#,###“) macht auch aus 8,07799 ein 8,000.

Gruß, Thomas

[Chewpapa_22ca98]

Hallo,

ich habe das mal alles durch VB laufen lassen und komme auf keinem Weg zu Deinem Ergebnis. Selbst wenn ich sgnAusgabe als String definiere oder einen String mit einem Komma statt Dezimalpunkt (wie in Deinem Text) an eine Single-Variable übergebe … ich komme nicht zu der Ausgabe, die Du schreibst.

 Dim sngAusgabe As Single
 sngAusgabe = 8.07799
 Me.Caption = Format(sngAusgabe, "#.000")

… zeigt mir ganz brav ‚8,078‘ an. Ich kann Den Fehler bei mir nicht reproduzieren.

Gruß, Rainer

[Reinhard_fb8cb7]

Excel VBA: Rundung von Zahlen
Hallo Thomas,

Function roundRealy(dez As Integer, oldVal As Double) As
Double
Application.Volatile
roundRealy = CInt(10 ^ dez * oldVal) / 10 ^ dez
End Function

Ich bekomme die Funktion irgendwie nicht zum Laufen. Bitte

Dim dblZahl1, dblZahl2, dblAusgabe As Double

du hast hier dblZahl1,dblZahl2 als Variant definiert,deklariert, sicher meintest du es so:

Dim dblZahl1 As Double, dblZahl2 As Double, dblAusgabe As Double

'nun der Funktionsaufruf:
Tabelle1.Cells(2, 5).Value = roundRealy(dez:=3,
oldVal:=dblAusgabe)
Ich bekomme den Fehler:
„Argumenttyp ByRef unverträglich“

Die Fehlermeldung deutet daraufhin, du solltest mal in der Hilfe nach ByRef und ByVal forschen

dann kommst du evtl. auf:
Function roundRealy(ByVal dez As Integer, ByVal oldVal As Double) As …

Weiterhin, ein Funktionsaufruf wie
Tabelle1.Cells(2, 5).Value = roundRealy(dez:=3, oldVal:=dblAusgabe)

ist mir unbekannt und ungewöhnlich, was aber nicht gleichzeitig heißen soll, er ginge so nicht, habs nur noch nicht getestet, Excel bietet immer Überraschungen *gg*
Probiers besser mal so:

Tabelle1.Cells(2, 5).Value = roundRealy(3,dblAusgabe)

Ich habe jetzt nur diese Anfrage beantwortet, deine Ausgangsfrage muß ich mir noch anschauen, irgendwie hakt w-w-w grad, dauert ewig hier die Beiträge zu lesen

Gruß
Reinhard

[Reinhard_fb8cb7]

Fehler nicht nachvollziehbar
Hi Thomas,

Ich übertrage aus einem Zahlenstring (Single) Werte in eine
Zelle mit folgendem Code:
Tabelle1.Cells(2, 1).Value = Round(sngOutput(1), 3)
Der vorher berechnete Wert von sngOutput(1) ist 0,7153076.
Mit Round(sngOutput(1), 3) möchte ich den Wert auf 3
Nachkommastellen runden, d.h. auf 0,715.
In die Zelle auf dem Excel Arbeitsblatt wird aber 0,714999973
übertragen.
Warum ist das so?

k.A. ich kann das Problem in XL97 und XL2002 auf WinXP nicht nachvollziehen,

Option Explicit
Sub tt()
Dim sngOutput(1) As Single
sngOutput(1) = 0.7153076
Tabelle1.Cells(2, 1).Value = Application.WorksheetFunction.Round(sngOutput(1), 3)
End Sub

Getestet mit XL97 und XL2002, in A2 erscheint wie geplant 0,715
Formatiere ich A2 als Zahl mit 5 Nachkommastellen erscheint in A2 0,71500
XL97-Vba kennt kein Round, auf welche XL-Version beziehst du dich?
Ist es XL auf einem MAC oder Windows-PC?
Welches Betriebssystem, Version?

Nachfolgend Code und Ergebnis mit mehreren Zahlen.

Gruß
Reinhard

Code:

Sub ttt()
' Spalte B mit \>=3 Nachkommastellen formatieren
Dim sngOutput(1) As Single, Z As Byte
For Z = 1 To 6
 sngOutput(1) = Rnd()
 Tabelle1.Cells(Z, 1).Value = sngOutput(1)
 Tabelle1.Cells(Z, 2).Value = Application.WorksheetFunction.Round(sngOutput(1), 3)
Next Z
End Sub

erzeugt dieses Ergebnis:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │
──┼────────────┼─────────┤
1 │ 0,70554751 │ 0,70600 │
──┼────────────┼─────────┤
2 │ 0,53342402 │ 0,53300 │
──┼────────────┼─────────┤
3 │ 0,57951862 │ 0,58000 │
──┼────────────┼─────────┤
4 │ 0,28956246 │ 0,29000 │
──┼────────────┼─────────┤
5 │ 0,30194801 │ 0,30200 │
──┼────────────┼─────────┤
6 │ 0,7747401 │ 0,77500 │
──┴────────────┴─────────┘
Zahlenformate der Zellen im gewählten Bereich:
A1:A6
haben das Zahlenformat: Standard
B1:B6
haben das Zahlenformat: 0,00000

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Vielen Dank.

Gruß
Thomas

[Peter_TOO]

Hallo Dirk,

Dazu noch eine Ankedote (?):
Früher hab ich mal mit „Borland Turbo Pascal“ entwickelt. Da
wollte ich Massen (zB 200,000 m³) aufteilen. Danach zur
Kontrolle die nicht verteilte Menge berechnen und die muss ja
0 sein! Ging dann aber nicht, da immer so ein Restchen blieb.
Anruf bei Borland: „Das macht man ja auch nicht. Man geht über
eine Fehlerschranke!“ Hat denn auch geholfen, aber nicht das
Problem gelöst!

Unter CP/M gab es CBASIC, das hat mit 14 Stellen BCD gerechnet. Damit gab es solche Probleme nicht.

MfG Peter(TOO)