打开其他excel文件时，Excel-VBA自身功能的故障 [英] Malfunction of Excel-VBA own function when open other excel-file

问题描述

我自己的Excel-VBA功能发生故障，我不知道为什么。
根据函数CONVERTemf（E）的输入参数的值，我想应用一个多项式或另一个（根据一些规则，在一个表中计算系数）：

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):

在excel表格中，我有一个名为：
coef0_1，coef1_1，coef2_1，Emin_1，Emax_1[第一个多项式]
coef0_2，coef1_2，coef2_2，Emin_2，Emax_2[第二个]。

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].

如果E在Emin_1和Emax_1之间，函数CONVERTemf（E）将应用第一个多项式，如果E在Emin_2和Emax_2之间，则必须应用第二个。

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.

我已经编程了这样的东西，它的功能确定，但​​是当我打开另一个excel文件（甚至是空的一个！）以前计算的值失去结果，出现＃¡VALOR！。

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!".

非常感谢！

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

推荐答案

Range coef0_1）不指定 Range 的位置。所以 ActiveSheet.Range（coef0_1）或在命名范围的情况下 Application.Range（coef0_1）被假定。如果宏运行的工作簿不是活动的工作簿，则这将失败。

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.

指定名称引用的特殊工作表的解决方案将起作用。但是，不仅可以创建一个工作表的范围，而且可以为整个工作簿创建一个名称。如果是这样，那么如果需要指定名称真正引用的特定工作表，那么它是非常丑的。例如，如果 coef0_1 指 Sheet1 但 Emin_1 指的是 Sheet2 然后它必须是 ThisWorkbook.Sheets（Sheet1）。Range（coef0_1）但 ThisWorkbook.Sheets（Sheet2）。Range（Emin_1）。

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

这篇关于打开其他excel文件时，Excel-VBA自身功能的故障的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！