excel宏：ByRef参数类型不匹配 [英] excel macro: ByRef argument type mismatch

问题描述

我写的代码如下：

Call search(xx, yy, "APM Output", ">> State Scalars", label1)

label1:
      ...........

这是Sub搜索的脚本

Sub search(row As Variant, col As Variant, wkst As String, str As String, label_num As Name)
For row = 1 To 100
  For col = 1 To 100
    strTemp = Worksheets(wkst).Cells(row, col).Value
    If InStr(strTemp, str) <> 0 Then
        GoTo label_num
    End If
  Next
Next
End Sub

我想先调用子搜索（..），然后转到label1。
问题是这样说，label_num的ByRef参数类型不匹配在Sub search（..，..，..， label_num ）中应该是label_num的正确类型？

i want to call sub search(..) firstly, and then go to label1. The problem is that is says that "ByRef argument type mismatch" for label_num.What should be the correct type for label_num in Sub search(..,..,..,label_num)?

我添加一些原始脚本，这些是我想要转换成一个sub（）

i add some original script, these are what I want to convert into a sub()

For xx = 1 To 100
    For yy = 1 To 100
        strTemp = Worksheets("APM Output").Cells(xx, yy).Value
        If InStr(strTemp, ">> State Scalars") <> 0 Then
            GoTo label1
        End If
    Next
Next
label1:
    For uu = 1 To 100
        For vv = 1 To 100
            strTemp = Worksheets("APM Output").Cells(uu, vv).Value
            If InStr(strTemp, ">> GPU LPML") <> 0 Then
                GoTo label2
            End If
        Next
    Next
label2:
    For mm = 1 To 100
        For nn = 1 To 100
            strTemp = Worksheets("APM Output").Cells(mm, nn).Value
            If InStr(strTemp, ">> Limits and Equations") <> 0 Then
                GoTo label3
            End If
        Next
    Next

推荐答案

作为一个很好的做法，请避免使用标签，不惜一切代价！

As a good practise, please avoid using label at all cost!

我将回答你只是修改你的代码，我想你想保存
xx，yy，uu，vv，mm，nn的值

I am going to answer you just modifying your code, I guess you want to save the values of xx,yy,uu,vv,mm,nn

以下代码是如何避免使用标签

the following code is how to avoid using label

Dim found1 As Boolean
Dim found2  As Boolean
Dim found3 As Boolean
  found1 = False
  found2 = False
  found3 = False
  For i = 1 To 100
   For j = 1 To 100
       strTemp = Worksheets("APM Output").Cells(i, j).Value
       If InStr(strTemp, ">> State Scalars") <> 0 And Not found1 Then
           found1 = True
           xx = i
           yy = j
       End If

       If InStr(strTemp, ">> GPU LPML") <> 0 And Not found2 Then
           found2 = True
           uu = i
           vv = j
       End If
       If InStr(strTemp, ">> Limits and Equations") <> 0 And Not found3 Then
           found3 = True
           mm = i
           nn = j
       End If
   Next j
Next i

将您的功能设为子，只需执行

to make your function into a sub, simply do

Sub my_search(ByRef rowNum As Long, ByRef colNum As Long, ByVal searchString As String, ByVal height As Long, ByVal width As Long, ByRef ws As Worksheet)
Dim i As Long
Dim j As Long
Dim found As Boolean
found = False
Dim strTemp
With ws
    For i = 1 To height 
       For j = 1 To width 
           strTemp = ws.Cells(i, j).Value
           If InStr(strTemp, searchString ) <> 0 And Not found1 Then
               found = True
               rowNum = i 'assigning rowNum 
               colNum = j 'assigning colNum
               Exit For
           End If
       Next j
       If found Then
        Exit For
        End If
    Next i

End With
End Sub

并调用它3次，例如
：

and call it 3 times, for example:

my_search xx,yy,">>State Scalars", 100, 100, Worksheets("APM Output")

这篇关于excel宏：ByRef参数类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！