在Excel/宏中将字符串转换为Double [英] Converting String to Double in Excel / Macro
问题描述
我尝试在Excel中创建新函数,女巫将计算给定值(类似于SUM函数,但仅具有给定前缀).
I try create new function in Excel, witch will counting given values (something like SUM function, but only with given prefix).
A
---------
1|AA30
2|AA10
3|BC446
4|AA10
// result will be 50 on SUM_PREFIX(A1:A4;"AA")
问题是,如果值的格式为例如AA10,434或AA4.43.有谁能帮助我解决我的问题?这是我在VB中的第一个脱衣舞.
Problem is, when the value is in the form e.g AA10,434 or AA4.43. Could me anyone help me with my problem? This is my first stript in VB.
Function SUM_PREFIX(Data As Range, prefix As String) As Double
Dim result As Double
Dim strVal As String
Dim i As Integer
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
.IgnoreCase = True
.MultiLine = False
.Pattern = "^[" + prefix + "]+[0-9]+(\,|\.)?[0-9]?$"
.Global = True
End With
For i = 1 To Data.Rows.Count
Debug.Print Data.Cells(i, 1)
If objRegExp.Test(Data.Cells(i, 1)) = True Then
strVal = Replace(Data.Cells(i, 1), prefix, "")
Debug.Print strVal
strVal = Trim(Replace(strVal, ",", "."))
Debug.Print strVal
result = result + CDbl(strVal)
End If
Next i
SUM_PREFIX = result
End Function
感谢您的帮助.
推荐答案
CDbl
可以识别语言环境,因此请检查您的 Replace
是否正确(例如,在我的语言环境,我必须将."替换为,"以使其正常工作).如果您不想依赖可识别语言环境的代码,请使用 Val
而不是 CDbl
,因为 Val
仅识别".作为有效的小数点分隔符(与语言环境无关).
CDbl
is locale-aware, so check if your Replace
is correct (for example, in my locale, I have to replace "." by "," in order for it to work).
If you don't want to rely on locale-aware code, use Val
instead of CDbl
because Val
only recognizes "." as a valid decimal separator regardless of locale.
这篇关于在Excel/宏中将字符串转换为Double的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!