vba 为什么我在大多数情况下运行 UserForm 时会在 excel 2013 中收到运行时错误 -2147417848 (80010108)？

Why do I get run-time error -2147417848 (80010108) in excel 2013 most of the time I run UserForm?

提问by Daniil Koshelyuk

Task:
I work in Excel2013. I tried to write in VBA a userform to add parameters into dynamic named ranges. All named ranges are held in one sheet and were created using insert>table. I select the range, show existing values and get the new value. All went well untill I actually got to adding value to the range.

任务：
我在Excel2013工作。我尝试在 VBA 中编写一个用户表单以将参数添加到动态命名范围中。所有命名范围都保存在一张纸中，并使用 insert>table 创建。我选择范围，显示现有值并获取新值。一切都很顺利，直到我真正开始为这个范围增加价值。

Problem:
Excel shuts down most of the time when I try to run the UserForm. Saying:

问题：
当我尝试运行用户窗体时，Excel 大部分时间都会关闭。说：

"Run-time error '-2147417848 (80010108)' Method X of object 'Range' failed"

“运行时错误 '-2147417848 (80010108)' 对象 'Range' 的方法 X 失败”

with different methods ('_Default' last time I checked) at different stages of me breaking code down.

在我分解代码的不同阶段使用不同的方法（我上次检查时为“_Default”）。

Symtoms:

症状：

1. After this line as I found I get the error:

   Cells(y, x) = v
   

   where yand xare integers and va string I get from the userform. During the debug I checked all values are defined and have values. Moreover, Immediate windowwith the same numbers input manually (not as variables), works!

2. It mostly doesn't work, though it did follow through doing the job.

1. 在我发现这一行之后，我收到错误消息：

   Cells(y, x) = v
   

   其中y和x是整数和v我从用户表单中获得的字符串。在调试期间，我检查了所有值都已定义并具有值。此外，具有相同数字手动输入（不是作为变量）的立即窗口有效！

2. 它大多不起作用，尽管它确实完成了这项工作。

If somone could tell the reason why it breaks it would be greatly appreciated!

如果有人能说出它破裂的原因，将不胜感激！

Some of the captions and potential values are in Unicode in case it matters, though I tried putting it all in English as well.

一些标题和潜在值是 Unicode 以防万一，尽管我也尝试将其全部用英语。

Private Sub UserForm_Initialize()
    ' Preparing all controls of UserForm
    Sheet2.Activate
    Me.LB_parameter.SetFocus
    Me.LB_parameter.value = ""
    Me.LB_elements.RowSource = ""
    Me.L_element.Enabled = False
    Me.TB_element.Enabled = False
    Me.TB_element.Locked = True
    Me.Btn_Add.Enabled = False
    Me.Btn_Add.Locked = True
End Sub

Private Sub LB_parameter_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' Filling the existing list of values for the selected parametr
    If Me.LB_parameter.value <> "" Then
        Me.LB_elements.RowSource = "D_" & Me.LB_parameter.value & "s"
        Me.L_element.Enabled = True
        Me.TB_element.Enabled = True
        Me.TB_element.Locked = False
        Me.TB_element.SetFocus
    End If
End Sub

Private Sub TB_element_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ' Catching the event of filling out the potential new value
    Me.Btn_Add.Enabled = True
    Me.Btn_Add.Locked = False
    Me.L_element.Enabled = False
    Me.TB_element.Enabled = False
    Me.TB_element.Locked = True
End Sub

Private Sub Btn_Add_Click()
    If Me.TB_element.Text = "" Then
        ' Check if Empty
        MsgBox ("?? í? a?è?à?è ?íà÷?íè?!")
        ' Reset the UserForm
        Me.Btn_Add.Enabled = False
        Me.Btn_Add.Locked = True
        Me.L_element.Enabled = True
        Me.TB_element.Enabled = True
        Me.TB_element.Locked = False
        Me.TB_element.SetFocus
    Else
        ' check if exists
        Dim str
        For Each str In range("D_" & Me.LB_parameter.value & "s")
            If Me.TB_element.Text = str Then
                MsgBox ("?a???íí?? ?íà÷?íè? ó?? ?óù??òaó?ò!")
                ' reset the UserForm
                Me.Btn_Add.Enabled = False
                Me.Btn_Add.Locked = True
                Me.L_element.Enabled = True
                Me.TB_element.Enabled = True
                Me.TB_element.Locked = False
                Me.TB_element.SetFocus
                Me.TB_element.value = ""
                Exit Sub
            End If
        Next str
        ' add to the range here
        Dim x As Integer, y As Integer, v As String
        y = range("D_" & Me.LB_parameter.value & "s").Rows.Count + 2
        x = Me.LB_parameter.ListIndex + 1
        v = Me.TB_element.value
        ' Next line causes break down
        Cells(y, x) = v
        MsgBox ("?? ??áàaè?è y??ì?íò:'" & v & "' ??? ?àeàì?òeà '" & Me.LB_parameter.value & "'.")
        ' Reset the Userform
        Me.LB_parameter.SetFocus
        Me.LB_parameter.value = ""
        Me.LB_elements.RowSource = ""
        Me.L_element.Enabled = False
        Me.TB_element.Enabled = False
        Me.TB_element.Locked = True
        Me.Btn_Add.Enabled = False
        Me.Btn_Add.Locked = True
    End If
End Sub

Sheet I add values to the parametrs and namedranges window:

工作表我将值添加到参数和命名范围窗口：

The UserForm layout:

用户窗体布局：

回答by Mathieu Guindon

Cells(y, x) = v

Cells(y, x) = v

This call is shorthand for this:

此调用是对此的简写：

ActiveSheet.Cells(y, x).Value = v

I'm not sure why it's crashing on you, but the _Defaultproperty of a Rangeobject being its Value, what I'd try here is being more explicit about what I'm trying to achieve, namely:

我不确定为什么它会在你身上崩溃，但是对象的_Default属性Range是它Value，我在这里尝试的是更明确地说明我想要实现的目标，即：

• Exactly which Worksheetis supposed to get modified?
• Exactly which Rangeis being referred to?

• 究竟哪个Worksheet应该被修改？
• 究竟指的Range是哪个？

I very very very seldom work with ActiveSheet- most of the time I know exactlywhat object I'm working with. Try using an object. You can create a new one:

我非常非常非常很少使用ActiveSheet- 大多数时候我确切地知道我正在使用什么对象。尝试使用对象。您可以创建一个新的：

Dim target As Worksheet
Set target = ThisWorkbook.Worksheets("pl")

...Or you can give the sheet a code namein the propertiestoolwindow (F4):

...或者您可以在属性工具窗口 ( ) 中为工作表指定代码名称：F4

That (Name)property defines an identifier that you can use in VBA code to access a global-scope object that represents that specific worksheet. Assuming that's Sheet1, you could do this:

该(Name)属性定义了一个标识符，您可以在 VBA 代码中使用该标识符来访问代表该特定工作表的全局范围对象。假设是Sheet1，你可以这样做：

Sheet1.Cells(x, y) = v

If that still fails, then you can be even more specific about the Rangeobject you're accessing and the property you're setting:

如果仍然失败，那么您可以更具体地了解Range您正在访问的对象和您正在设置的属性：

Dim target As Range
Set target = Sheet1.Cells(x, y)
target.Value = v

Normally that wouldn't make a difference though. But I see you're making Rangecalls, which are alsoimplicitly calling into the ActiveSheet.

但通常这不会有什么不同。但是我看到您正在Range拨打电话，这些电话也在隐式地调用ActiveSheet.

I'd start by eliminating these, and working off an explicit object reference.

我将从消除这些开始，并处理一个明确的对象引用。

Then I'd work on getting the spreadsheet logic out of the form; that button click handler is doing way too many things - but I digress into Code Reviewterritory - feel free to post your code there when you get it to work as intended!

然后我将努力从表单中获取电子表格逻辑；那个按钮点击处理程序做了太多的事情——但我离题了代码领域——当你让它按预期工作时，请随意在那里发布你的代码！

回答by Daniil Koshelyuk

Looks like the problem lies in my version of Excel. Not sure if the problem is in my copy or in the 2013 in general. In Excel 2007 on the same machine the UserForm with given suggestions worked continuously without any errors at all! Will update in comments later as I try it in different versions.

看起来问题出在我的 Excel 版本中。不确定问题是在我的副本中还是在 2013 年中。在同一台机器上的 Excel 2007 中，带有给定建议的用户窗体连续工作，完全没有任何错误！稍后会在评论中更新，因为我在不同版本中尝试过。