☰

如何修复 vba 类型不匹配？

tag-icon • tag-icon vba
如何修复 vba 类型不匹配？

我有一个编译错误消息，byref argument type mismatch指向 HTML 所在的 rg1。

能否请你帮忙？

Sub email_multi_ranges()

Dim OutApp As Object
Dim OutMail As Object
Dim rgl As Range, rg2 As Range, rg3 As Range, rg4 As Range
Dim str1 As String, str2 As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set rg1 = locate(1, 1, "Countries")
Set rg2 = locate(11, 1, "Countries")
Set rg3 = locate(3, 1, "Sheet2")
Set rg4 = locate(15, 2, "Sheet2")

str1 = "<BODY style=font-size:12ptsfont-family:calibri>" & _
"Hello Team, <br><br> Please see the figures below.<br>"

str2 = "<br>Best regards,<br>Tam"

On Error Resume Next
With OutMail
    .to = "test.com"
    .cc = "test1.com"
    .BCC = ""
    .Subject = "Country Info"
    .Display
    .HTMLBody = str1 & RangetoHTML(**rg1**) & RangetoHTML(rg2) & _
                RangetoHTML(rg3) & RangetoHTML(rg4) & str2 & .HTMLBody
                
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Function locate(y1 As Long, x1 As Long, sh As String) As Range
Dim y2 As Long, x2 As Long
ThisWorkbook.Sheets(sh).Activate

y2 = WorksheetFunction.CountA(Range(Cells(y1, x1), Cells(y1, x1).End(xlDown))) + y1 - 1
x2 = WorksheetFunction.CountA(Range(Cells(y1, x1), Cells(y1, x1).End(xlToRight))) + x1 - 1

Set locate = Sheets(sh).Range(Cells(y1, x1), Cells(y2, x2))

End Function

答案1

正如其他人所说，你将变量声明为，Dim rgl As Range, rg2 As Range, rg3 As Range, rg4 As Range但随后引用Set rg1 = locate(1, 1, "Countries")

如果您将Option Explicit其添加到的顶部sub，它会引发错误，因为您尚未声明rg1。

只需改为rgl，rg1它就可以工作。

今后的最佳做法可能是始终使用Option Explicit强制变量声明。

相关内容