Malfunction of Excel-VBA own function when open ot

问题:

I have a malfunction of my own Excel-VBA function and I don't know why. I want to apply one polynomial or another (which coeficients are calculated in one sheet depending on some rules) depending on the value of the input parameter of the function CONVERTemf(E):

In the excel-sheet I have cells named: "coef0_1", "coef1_1", "coef2_1", "Emin_1", "Emax_1" [for the first polynomial]; "coef0_2", "coef1_2", "coef2_2", "Emin_2", "Emax_2" [for the second one].

If "E" is between "Emin_1" and "Emax_1" the function CONVERTemf(E) will apply the first polynomial, if "E" is between "Emin_2" and "Emax_2" it must apply the second one.

I have programmed something like this and it functions ok, BUT, when I open another excel-file (even an empty one!) the values previously calculated lose the result and appears "#¡VALOR!".

Thank you very much!

Function CONVERTemf(E as Variant)
    c0_1 = Range("coef0_1").Value
    c1_1 = Range("coef1_1").Value
    c2_1 = Range("coef2_1").Value

    c0_2 = Range("coef0_2").Value
    c1_2 = Range("coef1_2").Value
    c2_2 = Range("coef2_2").Value 

    Emfmin_1 = Range("Emin_1").Value
    Emfmax_1 = Range("Emax_1").Value
    Emfmin_2 = Range("Emin_2").Value
    Emfmax_2 = Range("Emax_2").Value

    Select Case E
        Case Emfmin_1 To Emfmax_1
            CONVERTemf = (c2_1 * E ^ 2) + (c1_1 * E) + (c0_1)
        Case Emfmin_2 To Emfmax_2
            CONVERTemf = (c2_2 * E ^ 2) + (c1_2 * E) + (c0_2)
    End Select
End Function

回答1:

This is what I meant by #2 in my comment:

Function CONVERTemf(E as Variant)
  With ThisWorkbook.Sheets("your sheet name")
    c0_1 = .Range("coef0_1").Value
    c1_1 = .Range("coef1_1").Value
    c2_1 = .Range("coef2_1").Value

    c0_2 = .Range("coef0_2").Value
    c1_2 = .Range("coef1_2").Value
    c2_2 = .Range("coef2_2").Value 

    Emfmin_1 = .Range("Emin_1").Value
    Emfmax_1 = .Range("Emax_1").Value
    Emfmin_2 = .Range("Emin_2").Value
    Emfmax_2 = .Range("Emax_2").Value
  End With
    Select Case E
        Case Emfmin_1 To Emfmax_1
            CONVERTemf = (c2_1 * E ^ 2) + (c1_1 * E) + (c0_1)
        Case Emfmin_2 To Emfmax_2
            CONVERTemf = (c2_2 * E ^ 2) + (c1_2 * E) + (c0_2)
    End Select
End Function

回答2:

The Range("coef0_1") does not specify where the Rangeshall be. So the ActiveSheet.Range("coef0_1") or in the case of named ranges Application.Range("coef0_1") is assumed. This will fail if the Workbook in which the Macro runs is not the active Workbook.

A solution which specifies the special Worksheet to which the name refers to will work. But a name can be created not only with scope of one Worksheet but also for the whole Workbook. If so then it is very ugly if it is then needed to specify the specific Worksheet to which the name really refers to. For example if coef0_1 refers to Sheet1 but Emin_1 refers to Sheet2 then it has to be ThisWorkbook.Sheets("Sheet1").Range("coef0_1") but ThisWorkbook.Sheets("Sheet2").Range("Emin_1").

If the names are in the scope "Workbook" then the following should also work:

Function CONVERTemf(E As Variant)
  With ThisWorkbook
    c0_1 = .Names("coef0_1").RefersToRange.Value
    c1_1 = .Names("coef1_1").RefersToRange.Value
    c2_1 = .Names("coef2_1").RefersToRange.Value

    c0_2 = .Names("coef0_2").RefersToRange.Value
    c1_2 = .Names("coef1_2").RefersToRange.Value
    c2_2 = .Names("coef2_2").RefersToRange.Value

    Emfmin_1 = .Names("Emin_1").RefersToRange.Value
    Emfmax_1 = .Names("Emax_1").RefersToRange.Value
    Emfmin_2 = .Names("Emin_2").RefersToRange.Value
    Emfmax_2 = .Names("Emax_2").RefersToRange.Value
  End With
    Select Case E
        Case Emfmin_1 To Emfmax_1
            CONVERTemf = (c2_1 * E ^ 2) + (c1_1 * E) + (c0_1)
        Case Emfmin_2 To Emfmax_2
            CONVERTemf = (c2_2 * E ^ 2) + (c1_2 * E) + (c0_2)
    End Select
End Function