更改单元格的数字格式，该格式使用Visual Basic插入Currecny

Question

我想将其类型添加到Excel表中。我为此创建了以下代码。

Public Sub WorkSheetForCurrencies(ByRef ProductsCollection As VBA.Collection, ByRef cur As String, ByRef ExgRate As Double, ByRef symbol As String)
Dim index As Long
Dim objProduct As Product
index = 2
For Each objProduct In ProductsCollection
    If symbol = "£" Then
    'Way 01:
        mobjCSVWorkSheet.Cells(index, 8).NumberFormat = "-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* " - "??_-;_-@_-"
    ElseIf symbol = "$" Then
    'Way 02:
        mobjCSVWorkSheet.Cells(index, 8).Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* " - "??_);_(@_)"
    ElseIf symbol = "€" Then
        mobjCSVWorkSheet.Cells(index, 8).NumberFormat = "_([$€-2] * #,##0.00_);_([$€-2] * (#,##0.00);_([$€-2] * " - "??_);_(@_)"
    End If
    mobjCSVWorkSheet.Cells(index, 8) = objProduct.SalesUnitPrice * CCur(ExgRate)
    index = index + 1
Next objProduct
End Sub

我尝试了两种方法来更改数字格式，如上述代码中。但是这些给出键入不匹配错误。请告诉我我必须更改什么才能更改单元格格式。谢谢

Answer 1

这是您的问题：

"-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* " - "??_-;_-@_-"

这将评估为

"-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* "

减去

"??_-;_-@_-"

由于您正在在字符串上执行算术操作，因此它试图将两侧转换为数字值，并且失败。

尝试将双引号加倍，例如

"-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* "" - ""??_-;_-@_-"