通过VBA Excel从数字中删除十进制

4 回答

• 5

  假设用户提供输入，这是一个X-Y问题IMO：您的目标是确保您的用户只能在该文本框中键入数字字符，而不是截断小数 .

  问题是文本框' Change 事件触发得太晚了 . 相反，处理 KeyDown - 我会做这样的事情：

  Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      If Not KeyCodeValidator.IsValidDigitInput(KeyCode.value) Then KeyCode.value = 0
  End Sub
  

  有 KeyCodeValidator 标准模块：

  Option Explicit
  Option Private Module
  
  Public Function IsValidAlphaInput(ByVal keyAscii As Long, Optional ByVal allowDeletion As Boolean = True, Optional ByVal allowNav As Boolean = True) As Boolean
      IsValidAlphaInput = (keyAscii >= vbKeyA And keyAscii <= vbKeyZ) Or (allowDeletion And IsDeletion(keyAscii)) Or (allowNav And IsNavKey(keyAscii))
  End Function
  
  Public Function IsValidDigitInput(ByVal keyAscii As Long, Optional ByVal allowDeletion As Boolean = True, Optional ByVal allowNav As Boolean = True) As Boolean
      IsValidDigitInput = (keyAscii >= vbKey0 And keyAscii <= vbKey9) Or (keyAscii >= vbKeyNumpad0 And keyAscii <= vbKeyNumpad9) Or (allowDeletion And IsDeletion(keyAscii)) Or (allowNav And IsNavKey(keyAscii))
  End Function
  
  Public Function IsValidAlphanumericInput(ByVal keyAscii As Long, Optional ByVal allowDeletion As Boolean = True, Optional ByVal allowNav As Boolean = True) As Boolean
      IsValidAlphanumericInput = IsValidAlphaInput(keyAscii) Or IsValidDigitInput(keyAscii) Or (allowDeletion And IsDeletion(keyAscii)) Or (allowNav And IsNavKey(keyAscii))
  End Function
  
  Public Function IsValidDecimalInput(ByVal keyAscii As Long, ByVal contents As String) As Boolean
      If IsValidDigitInput(keyAscii) Or keyAscii = Asc(".") Then
          IsValidDecimalInput = IsNumeric(contents & Chr$(keyAscii))
      End If
  End Function
  
  Private Function IsDeletion(ByVal keyAscii As Long) As Boolean
      IsDeletion = keyAscii = vbKeyDelete Or keyAscii = vbKeyBack
  End Function
  
  Private Function IsNavKey(ByVal keyAscii As Long) As Boolean
      IsNavKey = keyAscii = vbKeyTab Or keyAscii = vbKeyLeft Or keyAscii = vbKeyRight
  End Function
  

  如果要支持小数，则将文本框的当前内容传递给 IsValidDecimalInput （需要进行微调以支持国际小数分隔符） .

  我实际上把那个模块作为一个类，但是标准模块也很合适 .

  指出，在文本框中输入之后输入 - 防止用户的无效输入甚至在第一个位置输入 .

  回复于 2024-04-19T09:39:05+08:00
• 3

  请注意两种方法（来自OP的注释的 Format ，以及来自@Gary的 Split _截断或舍入（可能是数字）值，因此如果用户试图输入 123.54 ，则结果值将为 12354 ，当您可能需要时 124 （轮）或 123 （截断） .

  依赖_Change事件进行验证有一些限制：D和这个值可能应该被其他任何使用它作为输入的过程验证/操纵 .

  例：

  Option Explicit
  Private Sub CommandButton1_Click()
      Call ValidateMe(TextBox1)
      MsgBox (TextBox1.Value)
  
  End Sub
  
  Private Sub TextBox1_Change()
      ' do nothing
  End Sub
  
  Private Sub ValidateMe(obj As MSForms.TextBox)
      obj.Value = Format(obj, "0")
  End Sub
  

  回复于 2024-04-19T09:39:05+08:00
• 2

  如果TextBox1是 String 喜欢：

  1,234.56

  然后使用类似的东西：

  TextBox1 = Split(TextBox1,".")(0)
  

  EDIT#1:

  根据评论，应该使用：

  TextBox1 = Split(TextBox1 & Application.DecimalSeparator, Application.DecimalSeparator)(0)
  

  这将：

  • 在给定空字符串的情况下返回空字符串
    如果没有整数部分，则

  • 返回空字符串

  • 以与Locale无关的方式返回整数部分

  回复于 2024-04-19T09:39:05+08:00
• 0

  braX 发送此代码：

  TextBox1.Text = Format(TextBox1.Text, "#,###,###,##0")
  

  做得好 braX ,,谢谢，它运作正常...... :)

  回复于 2024-04-19T09:39:05+08:00