range.formula问题

Question

你好，我有下一个问题，我有这样的代码：

Public Function AsentamientosCA(celda As String, cotaInst As Single, Zona As String, DensidadZona As Single, _
                                alturaLlenobajocelda As Single, PB As Boolean)

set ws= ThisWorkbook.Worksheets("hojaDestino")
RangoFechaAs = "A7:A" & DatosFechaL.Rows.Count + 6
ws.Range(Replace(RangoFechaAs, "A", "F")).Formula = "=C7 -" & cotaInst & "-(D7/100)"

End Function

Sub test()

dim celda As String, cotaInst As Single, Zona As String, DensidadZona As Single
dim alturaLlenobajocelda As Single, PB As Boolean

cotaInst = ThisWorkbook.Worksheets("a").Range("A2").Value
''CotaInst = 240.2 in my pc
'' in other computers CotaInst = 240,2

call AsentamientosCA(400, cotaInst, "2A", 21, 90, True)
End Sub

在我的pc机中工作很好，但是当我在其他pc上共享这段代码时，它就会失败，而这恰恰是因为我的pc中的cotaInst被认为是240.2，而在其他pc中，是否有办法改变这个变量，假设我的代码中只有一个外部变量？

因为我希望这个公式能在任何有宏的pc上运行。我很感激你的帮助。

Answer 1

这是由于不同的地区设置而发生的。在您的pc中，十进制分隔符是.，而在另一个pc中，是,。尝试以下操作(未测试的)

在这里，我将cotaInst As Single更改为cotaInst As String，并添加了几行来检查十进制分隔符。

Sub test()
    Dim celda As String, cotaInst As String, Zona As String, DensidadZona As Single
    Dim alturaLlenobajocelda As Single, PB As Boolean
    Dim decSep As String

    decSep = Application.DecimalSeparator

    cotaInst = ThisWorkbook.Worksheets("a").Range("A2").Value

    If decSep = "." Then cotaInst = Replace(cotaInst, ",", ".")

    Call AsentamientosCA(400, cotaInst, "2A", 21, 90, True)
End Sub


Public Function AsentamientosCA(celda As String, _
                                cotaInst As String, _
                                Zona As String, DensidadZona As Single, _
                                alturaLlenobajocelda As Single, PB As Boolean)

    RangoFechaAs = "A7:A" & DatosFechaL.Rows.Count + 6
    ws.Range(Replace(RangoFechaAs, "A", "F")).Formula = "=C7 -" & cotaInst & "-(D7/100)"
End Function