Application.CleanString()会将我的字符串转换成什么?

huangapple go评论61阅读模式
英文:

What does Application.CleanString() convert my string to?

问题

以下是代码部分的中文翻译:

Sub getRows()

'计算给定表格中的行数
    rows_num = Selection.Tables(1).Rows.Count

'获取第3行到第N行,单元格2、3、5和7的值,并将它们保存为数字值,除了dGVS
    For i = 3 To rows_num
        With Selection.Tables(1).Rows(i)
            TK = Val(Replace(Replace(.Cells(2).Range.Text, ",", "."), "+", ""))
            dOSBL = Val(Replace(.Cells(3).Range.Text, ",", "."))
            dAFRN = Val(Replace(.Cells(5).Range.Text, ",", "."))
            dGVS = .Cells(7).Range.Text
        End With

'清理dGVS,因为否则会出现特殊字符
        dGVS = Application.CleanString(dGVS)

'计算所需的值
        kOSBL = TK - dOSBL
        kAFRN = TK - dAFRN

'应该检查dGVS是否为连字符,如果是,则将kGVS设置为相同的值,这个检查不起作用
        If dGVS = "-" Then
            kGVS = "-"
        Else
            dGVS = Val(Replace(dGVS, ",", "."))
            kGVS = TK - dGVS
        End If

'将计算出的值返回到所需的单元格
        With Selection.Tables(1).Rows(i)
            .Cells(4).Range.Text = "+" & kOSBL
            .Cells(6).Range.Text = "+" & kAFRN
            .Cells(8).Range.Text = "+" & kGVS
        End With
    Next i
End Sub

希望这有所帮助。如果您有任何其他问题,请随时提问。

英文:

I'm currently trying to take inputs from a table in word, convert the text to numbers, do some basic math on them and then return them to the desired cells.

A typical table looks something like this:Application.CleanString()会将我的字符串转换成什么?

I've made the following code (forgive my spaghetti):

Sub getRows()

'Counts the number of rows in the given table
    rows_num = Selection.Tables(1).Rows.Count

'Get the values of row 3 -> N, cell 2, 3, 5 and 7 and saves them as a numerical value, ecept for dGVS
    For i = 3 To rows_num
        With Selection.Tables(1).Rows(i)
            TK = Val(Replace(Replace(.Cells(2).Range.Text, ",", "."), "+", ""))
            dOSBL = Val(Replace(.Cells(3).Range.Text, ",", "."))
            dAFRN = Val(Replace(.Cells(5).Range.Text, ",", "."))
            dGVS = .Cells(7).Range.Text
        End With

'Cleans dGVS since I get a special char otherwise
        dGVS = Application.CleanString(dGVS)
        
'Calculates what I need
        kOSBL = TK - dOSBL
        kAFRN = TK - dAFRN
        
'Should check if dGVS is a hyphen and if so set kGVS to the same value, this check does not work
        If dGVS = "-" Then
            kGVS = "-"
        Else
            dGVS = Val(Replace(dGVS, ",", "."))
            kGVS = TK - dGVS
        End If
        
'Returns the calculated values to the desired cells
        With Selection.Tables(1).Rows(i)
            .Cells(4).Range.Text = "+" & kOSBL
            .Cells(6).Range.Text = "+" & kAFRN
            .Cells(8).Range.Text = "+" & kGVS
        End With
    Next i
End Sub

The only problem I seem to encounter is that the if-statement at the bottom doesn't seem to recognize the value of dGVS as a hyphen and therefore, I assume, the check fails and returns the following table:

Application.CleanString()会将我的字符串转换成什么?

Does this happen due to the Application.CleanString() or is it another problem entirely? Is there a way to figure out what symbol is actually contained in dGVS to make a proper check, or should I go about this entirely differetly?

A MsgBox print of dGVS returns the following:

Application.CleanString()会将我的字符串转换成什么?

答案1

得分: 1

通过使用AscW(dGVS)检查字符代码来解决了这个问题,对于这种情况中使用的连字符,它的代码是45,然后执行以下检查:

If AscW(dGVS) = 45 Then
    kGVS = Chr(45)
    Selection.Tables(1).Rows(i).Cells(8).Range.Text = kGVS
Else
    dGVS = Val(Replace(dGVS, """, "."))
    kGVS = TK - dGVS
    Selection.Tables(1).Rows(i).Cells(8).Range.Text = "+" & kGVS
End If

感谢Jonsson的建议。

英文:

Solved the problem by checking the character code with AscW(dGVS), which for the used hyphen in this case was 45 and then doing the following check:

If AscW(dGVS) = 45 Then
        kGVS = Chr(45)
        Selection.Tables(1).Rows(i).Cells(8).Range.Text = kGVS
    Else
        dGVS = Val(Replace(dGVS, ",", "."))
        kGVS = TK - dGVS
        Selection.Tables(1).Rows(i).Cells(8).Range.Text = "+" & kGVS
End If

Thank you Jonsson for the suggestion.

huangapple
  • 本文由 发表于 2023年3月9日 23:42:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/75686900.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定