vba 在 Excel / 宏中将字符串转换为双精度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12063887/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting String to Double in Excel / Macro
提问by Peter Jurkovic
I try create new function in Excel, witch will counting given values (something like SUM function, but only with given prefix).
我尝试在 Excel 中创建新函数，女巫会计算给定的值（类似于 SUM 函数，但只有给定的前缀）。
A
---------
1|AA30
2|AA10
3|BC446
4|AA10
// result will be 50 on SUM_PREFIX(A1:A4;"AA")
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.
问题是，当值的形式为 AA10,434 或 AA4.43 时。有人能帮我解决我的问题吗？这是我在 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
Thanks for help.
感谢帮助。
回答by ssarabando
CDblis locale-aware, so check if your Replaceis 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 Valinstead of CDblbecause Valonly recognizes "." as a valid decimal separator regardless of locale.
CDbl是语言环境感知的，因此请检查您的语言Replace是否正确（例如，在我的语言环境中，我必须将“.”替换为“,”才能使其工作）。如果您不想依赖区域设置感知代码，请使用Val代替，CDbl因为Val只识别“。” 作为有效的十进制分隔符，无论区域设置如何。
回答by Enival Bento
Function SUM_PREFIXO(DADOS As Range, PREFIXO As String) As Double
Dim result, NI 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 = "^[" + PREFIXO + "]+[0-9]+(\,|\.)?[0-9]?$"
.Global = True
End With
NI = DADOS.Rows.Count
For i = 1 To DADOS.Rows.Count
Debug.Print (DADOS.Cells(i, 1))
If objRegExp.Test(RetiraEspa?o(DADOS.Cells(i, 1))) = True Then
strVal = Trim(Replace(DADOS.Cells(i, 1), PREFIXO, ""))
Debug.Print strVal
strVal = Trim(Replace(strVal, ".", ","))
Debug.Print strVal
strVal = Trim(Replace(strVal, ",", ","))
Debug.Print strVal
result = result + CDbl(strVal)
End If
Next i
SUM_PREFIXO = result
End Function
'Com o código abaixo pode-se 'remover os espa?os extras entre as palavras de um texto: Function RetiraEspa?o(Texto)
'Com o código abaixo pode-se 'remover os espa?os extras entre as palavras de um texto: Function RetiraEspa?o(Texto)
Dim Vpalavra, inicio, termino, Wresultado
inicio = 1
Texto = UCase(Texto) & " "
Do Until InStr(inicio, Texto, " ") = 0
termino = InStr(inicio, Texto, " ")
Vpalavra = Mid(Texto, inicio, termino - inicio)
inicio = termino + 1
Wresultado = Wresultado & "" & Vpalavra
Loop
RetiraEspa?o = Trim(Wresultado)
End Function
结束函数
