且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

excel宏:ByRef参数类型不匹配

更新时间:2023-12-01 07:54:28

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

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