vba 执行 Excel 宏时出现“类型不匹配”错误

"Type Mismatch" error when executing Excel Macro

提问by Lahib

I have created a Macro to remove all borders from empty rows in my excel file. this is mig code:

我创建了一个宏来从我的 excel 文件中的空行中删除所有边框。这是 mig 代码：

Sub RemoveRows()
'
' RemoveRows Macro
'

'
    Range("A8").Select

    Dim checkval

    Dim RowAmount
    RowAmount = 93



    Do
        checkval = ActiveCell.Value
        If (checkval = "" Or checkval = Null) Then
            ActiveCell.EntireRow.Delete
        Else
            ActiveCell.Offset(1, 0).Select
        End If
        RowAmount = RowAmount - 1
    Loop While RowAmount > 0


End Sub

method for running the macro:

运行宏的方法：

public void RemoveRows_Macro(string fileName)
{
    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    xlWorkBook = xlApp.Workbooks.Open(fileName);
    xlApp.DisplayAlerts = true;

    //Run the macro
    xlApp.Run("RemoveRows", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    xlWorkBook.Save();

    xlWorkBook.Close(false);
    xlApp.Quit();
    releaseObject(xlApp);
    releaseObject(xlWorkBook);
}

My problem is that i can run this macro from my C# application without getting this error, but when others use my C# application they get this error: Run-time error '13': Type Mismatch

我的问题是我可以从我的 C# 应用程序运行这个宏而不会出现此错误，但是当其他人使用我的 C# 应用程序时，他们会收到此错误：运行时错误“13”：类型不匹配

what am i doing wrong ?

我究竟做错了什么 ？

回答by K_B

To stay away from cell formatting always use Value2.

要远离单元格格式，请始终使用Value2.

Always dimension the variables type, and use them when needed only, checkval isnt very usefull, you can directly check for ActiveCell.Value2 = ""or check IsEmpty(ActiveCell).

始终对变量类型进行标注，仅在需要时使用它们，checkval 不是很有用，您可以直接 check forActiveCell.Value2 = ""或 check IsEmpty(ActiveCell)。

If you really want to use this variable than dimension it as Variant.

如果您真的想使用此变量而不是将其标注为Variant.

Excel cell values are not NULL, they are at least empty or equal to an empty string (""), so doing the IsNullor = NULLcheck dont make sense in VBA. Skip the check for Null and it should work fine.

Excel 单元格值不是 NULL，它们至少为空或等于空字符串 ("")，因此在 VBA 中执行IsNull或= NULL检查没有意义。跳过对 Null 的检查，它应该可以正常工作。

回答by chris neilsen

Try this version

试试这个版本

It deals with

它处理

• possible entries that apearas empty cells but arn't (deletes them)
• Formula's that return ""(leaves them)
• greatly improves run time

• 这可能条目apear为空单元格，但arn't（删除它们）
• 返回的公式""（离开它们）
• 大大提高了运行时间

---

---

Sub RemoveRows()
    Dim dat As Variant
    Dim i As Long
    Dim rng As Range, rngDel As Range
    Dim str As String

    Set rng = Range("A8:A100")
    dat = rng.Formula

    For i = 1 To UBound(dat, 1)
        str = Replace$(dat(i, 1), Chr(160), "")
        str = Replace$(str, vbCr, "")
        str = Replace$(str, vbLf, "")
        str = Replace$(str, vbTab, "")
        If Len(Trim$(str)) = 0 Then
            If rngDel Is Nothing Then
                Set rngDel = rng.Cells(i, 1)
            Else
                Set rngDel = Application.Union(rngDel, rng.Cells(i, 1))
            End If
        End If
    Next

    If Not rngDel Is Nothing Then
        rngDel.EntireRow.Delete
    End If

End Sub

回答by glh

A couple of things to try:

有几件事可以尝试：

1. It is good practice to declare your variable types, in this case stringor variantor range.
2. Try to use the IsNull(checkval)function instead of checkval = Null.
   • As @Chris states, a cell is indeed Emptynot null, use isempty(a).
   • After some diding into this, what seems to be a common issue, I've come across a great solution that seems fool proof, use len(trim(a)) = 0as the check.
3. It is unnecessary to cycle through the cells by selecting them:

1. 声明变量类型是一种很好的做法，在本例中为stringorvariant或range。
2. 尝试使用该IsNull(checkval)函数而不是checkval = Null.
   • 正如@Chris 所说，单元格确实Empty不是null，请使用isempty(a).
   • 在对此进行了一些研究之后，这似乎是一个常见问题，我遇到了一个很好的解决方案，它似乎是万无一失的，len(trim(a)) = 0用作检查。
3. 没有必要通过选择它们来循环浏览单元格：

Code:

代码：

Sub RemoveRows() 
'
' RemoveRows Macro 
'

' 

    Dim checkval as long, RowAmount as long
    checkval = 8
    RowAmount = 93

    Do 

        'If trim(range("A" & checkval)) = "" Or _
        '       isempty(range("A" & checkval)) Then 'thanks chris
        If Len(Trim(range("A" & checkval))) = 0 then 
            range("A" & checkval).EntireRow.Delete 
        Else 
            checkval  = checkval + 1
        End if
        RowAmount = RowAmount - 1 

    Loop While RowAmount > 0

End Sub