数据库中的VBA点加载到文本框中为逗号 [英] VBA dots from database get loaded into textbox as comma

问题描述

我知道标题听起来很奇怪，所以我将从截图开始：

I know the Headline sounds odd so I will start off with a screenshot:

正如你所看到的，问题是当我在UserForm中查找一个ID时，这一点突然变成一个逗号。

As you can see, the problem is that the point suddenly changes to a comma when I look up an ID in the UserForm.

在回顾信息之前，我保存所有信息非常简单：

Before recalling Infos, I am saving all Information rather straightforward:

with ws
    Range("BH" & lastRow).value = Me.payinfoOnTime
    Range("BI" & lastRow).value = Me.payinfo30
    Range("BJ" & lastRow).value = Me.payinfo60
    Range("BK" & lastRow).value = Me.payinfo90
    Range("BL" & lastRow).value = Me.payinfo90more
End with

调用搜索的ID的相应信息是通过以下方式完成的：

Recalling the respective info for a searched ID is done by:

Set FoundRange = ws.Range("D4:D500").Find(What:=Me.SearchSuppNo, LookIn:=xlValues)

With ws
    Me.SEpayinfoontime = FoundRange.Offset(0, 56)
    Me.SEpayinfo30 = FoundRange.Offset(0, 57)
    Me.SEpayinfo60 = FoundRange.Offset(0, 58)
    Me.SEpayinfo90 = FoundRange.Offset(0, 59)
    Me.SEpayinfo90more = FoundRange.Offset(0, 60)
end with

问题是后来的计算离子的分数取决于那些文本框，我经常收到错误，除非我总是手动将逗号更改为点。

The Problem is that later calculations for scores are depending on those textboxes and I constantly get an error, unless I always manually change the commas back to points.

任何想法，我可以如何解决这个问题？ / p>

Any ideas how I can fix this?

推荐答案

行：

Me.SEpayinfoontime = FoundRange.Offset(0, 56)

其实是：

Me.SEpayinfoontime.Value = FoundRange.Offset(0, 56).Value

填写 MSForms .TextBox 使用 .Value 属性（键入 As Variant ），像你隐含地，并在右侧提供一个数字，编译器将值作为一个数字传递给 TextBox ，然后该值自动转换为 TextBox 。

When you populate an MSForms.TextBox using the .Value property (typed As Variant), like you implicitly do, and providing a number on the right side, the compiler passes the value to the TextBox as a number, and then the value is automatically converted to string inside the TextBox.

正是这种转换发生的方式似乎没有记录，它会出现它有一个问题。

Exactly how that conversion happens does not appear to be documented, and from experiment, it would appear there is a problem with it.

当你刚刚启动Excel，它会出现分配 .Value 将使用 en-us 语言环境转换该数字，即使您的系统区域设置不同。但是，一旦您进入控制面板并将当前的区域设置更改为其他地方， .Value 开始尊重系统区域设置，并根据目前的情况更改其结果

When you freshly start Excel, it would appear assigning .Value will convert the number using the en-us locale, even if your system locale is different. But as soon as you go to the Control Panel and change your current locale to something else, .Value begins to respect the system locale, and changes its result depending on what is currently selected.

不应该发生，我会将其视为Excel错误。

It should not be happening and I would see it as an Excel bug.

但是，如果您转而指定 .Text 属性，则使用当前系统小数点将该数字转换为字符串，并且该转换发生在 TextBox ，因为编译器知道 .Text 是一个字符串，所以它将右侧的数字转换为字符串

But if you instead assign the .Text property, the number is converted to string using the current system decimal dot, and that conversion happens outside of the TextBox, because the compiler knows .Text is a string, so it converts the right-hand side number to string beforehand.

所以在你的情况下，我会：

So in your situation I would:

• Make确定我一直使用 .Text 属性：

Me.SEpayinfoontime.Text = ...

确保我明确使用正确的类型的函数来转换文字和数字之间：

Make sure I explicitly use the correct kind of functions to convert between text and numbers:

Me.SEpayinfoontime.Text = CStr(FoundRange.Offset(0, 56).Value)

MsgBox CInt(Me.SEpayinfoontime.Text) / 10

虽然此步骤是可选的，代表我的个人喜好。鉴于这是作业左侧的字符串，VB将自动使用 CStr 。

although this step is optional and represents my personal preference. Given that it's a string on the left side of the assignment, VB will use CStr automatically.

转到Excel的设置，确保设置了使用系统分隔符勾号。

Go to Excel's settings to make sure the "Use system separators" tick is set.

• 如果不是 En-Us ，我会选择 En-我们确保小数点分隔符是一个点。

• If it is not En-Us, I would select En-Us to make sure the decimal separator is a dot there.

这篇关于数据库中的VBA点加载到文本框中为逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！