如何在VBA中将货币转换为双倍? [英] How to convert currency into double in VBA?

问题描述

我有三个文本框，我得到的值是这样的:

I have three textboxes and I get their value like this:

Dim X, Y, W As Double
X = DLookup("Summ", "tblPlatej", "ID= " & Form_frmPlatej!ID)
Y = DLookup("Deposit_before", "tblPlatej", "ID= " & Form_frmPlatej!ID)
W = DLookup("Monthly_payment", "tblPlatej", "ID= " & Form_frmPlatej!ID)

但是当我这样更改文本框的值时

But when I change the value of textbox like this

Form_frmPlatej.Deposit_before = X - W + Y

我收到类型不匹配"错误.所有文本框均为货币.如何计算新记录并将该数字放入"Deposit_before"文本框中?

I get a Type mismatch error. All textboxes are currency. How do I calculate new record and put that number in the "Deposit_before" textbox?

总和，之前的存款，每月的付款是我表中的货币数据类型. Deposit_before大部分为负.

Summ, Deposit_before, Monthly_payment are currency data type in my table. Deposit_before is mostly negative.

这是我整个按钮点击的代码

Here is my whole code for button click

Private Sub Command13_Click()

a1 = DLookup("Inhabitant", "tblClient", "ID = " & Form_frmMain!ID)
B1 = DLookup("PriceTBO", "tblPrice")
c1 = DLookup("Republican", "tblClient", "ID = " & Form_frmMain!ID)
d1 = DLookup("Regional", "tblClient", "ID = " & Form_frmMain!ID)
e1 = DLookup("Local", "tblClient", "ID = " & Form_frmMain!ID)

A = DLookup("IDP", "tblPlatej", "ID= " & Form_frmPlatej!ID)
B = DLookup("Type_of_payment", "tblPlatej", "ID= " & Form_frmPlatej!ID)
C = DLookup("Year", "tblPlatej", "ID= " & Form_frmPlatej!ID)
D = DLookup("Month", "tblPlatej", "ID= " & Form_frmPlatej!ID)

Y = DLookup("Deposit_before", "tblPlatej", "ID= " & Form_frmPlatej!ID) // Problem here
W = DLookup("Monthly_payment", "tblPlatej", "ID= " & Form_frmPlatej!ID) //Problem here
X = DLookup("Summ", "tblPlatej", "ID= " & Form_frmPlatej!ID)

i = Form_frmPlatej.Month.ListIndex
j = Form_frmPlatej.Year.ListIndex
den = DLookup("Date", "tblPlatej", "IDP = " & Form_frmPlatej!IDP)

If X <> " " Then
With Me.Recordset
If Me.Recordset.BOF = False And Me.Recordset.EOF = False Then
.MoveFirst
End If
.AddNew
.Edit

Form_frmPlatej.Deposit_before = X - W + Y  //Problem here

Form_frmPlatej.IDP = A + 1
Form_frmPlatej.Type_of_payment = B
If i = 11 Then
Form_frmPlatej.Year = Year.ItemData(j + 1)
i = -1
Else
Form_frmPlatej.Year = Year.ItemData(j)
End If

Form_frmPlatej.Month = Month.ItemData(i + 1)
Form_frmPlatej.Date = DateAdd("m", 1, den)

If c1 <> 0 Then
Form_frmPlatej.Monthly_payment = (a1 * B1) - (c1 * (a1 * B1)) / 100

ElseIf d1 <> 0 Then
Form_frmPlatej.Monthly_payment = (a1 * B1) - (d1 * (a1 * B1)) / 100

ElseIf e1 <> 0 Then
Form_frmPlatej.Monthly_payment = (a1 * B1) - (e1 * (a1 * B1)) / 100
Else
Form_frmPlatej.Monthly_payment = a1 * B1
End If
.Update

End With

Else
MsgBox ("Please enter number")
End If

End Sub

我完全困惑.

推荐答案

我敢打赌，您的问题如下.当你这样说:

I bet your problem is the following. When you say this:

Dim X, Y, W As Double

您认为，您已经做到了:

Dim X As Double, Y As Double, W As Double

但是您确实所做的是这样的:

but what you've really done is this:

Dim X
Dim Y
Dim W As Double

这是经典的VBA错误.大多数VBA程序员都做到了，这就是为什么大多数VBA程序员都回退到每个Dim语句仅声明一个变量(即每行一个)的原因.否则，很容易犯该错误，并且以后很难发现它.

This is a classic VBA mistake. Most VBA programmers have made it, and that's why most VBA programmers fall back on declaring only one variable per Dim statement (i.e. one per line). Otherwise it's way too easy to make that mistake, and difficult to spot it afterwards.

因此，对于Dim X和Dim Y，您已隐式声明X和Y为Variant类型(等同于Dim X As Variant和Dim Y As Variant).

So with Dim X and Dim Y you've implicitly declared X and Y as Variant type (equivalent to Dim X As Variant and Dim Y As Variant).

为什么这很重要?当您这样说时:

Why does this matter? When you then say this:

X = DLookup("Summ", "tblPlatej", "ID= " & Form_frmPlatej!ID)
Y = DLookup("Deposit_before", "tblPlatej", "ID= " & Form_frmPlatej!ID)

可能是这两个DLookup中的一个意外返回了不是数字的内容，例如字符串.您的变体X或Y将接受此请求而不会抱怨； Variant会在分配的右侧获取事物的类型.

maybe one of those two DLookup unexpectedly returns something that isn't a number, for example a string. Your variant X or Y will accept this without complaining; the Variant acquires the type of the thing on the right hand side of the assignment.

但是，当您尝试使用这些值进行数学运算时，如果X和/或Y是字符串，则X - W + Y将引发类型不匹配错误.

However, when you try to do math with these values, X - W + Y will throw a type mismatch error if X and/or Y is a string.

另请参阅我的较早答案，在此我重复使用了一些措辞: https://stackoverflow.com/a/11089684/119775

See also this earlier answer of mine, from which I reused some of the wording: https://stackoverflow.com/a/11089684/119775

这篇关于如何在VBA中将货币转换为双倍?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！