VBA怎么实现数组公式赋值给变量

请问是什么样的数组公式？应用在什么样的过程中？

19903841088
我有群学习

=MIN(IF(B:B=$E$3,ROW(B:B),65536))
=MAX(IF(B:B=$E$3,ROW(B:B),0))

sub和function内容如下，供参考。
效果图：

过程代码：
Sub FirstRow_LastRow()
Dim MaxRow As Long, iRow As Long, vE3 As String
Dim fRow As Long, lRow As Long
MaxRow = Range("B" & Rows.Count).End(xlUp).Row
vE3 = Range("E3").Value
For iRow = 2 To MaxRow
If Cells(iRow, "B") = vE3 Then
fRow = iRow
Exit For
End If
Next iRow
For iRow = MaxRow To 2 Step -1
If Cells(iRow, "B") = vE3 Then
lRow = iRow
Exit For
End If
Next iRow
MsgBox "First Row: " & fRow & Chr(10) & "Last Row: " & lRow
End Sub
自定义函数：

Function FirstRow(rng As Range, ColNum As Integer) As Long
Dim MaxRow As Long, iRow As Long
Dim sValue As String, fRow As Long
MaxRow = Cells(Rows.Count, ColNum).End(xlUp).Row
sValue = rng.Value
For iRow = 2 To MaxRow
If Cells(iRow, "B") = sValue Then
fRow = iRow
Exit For
End If
Next iRow
FirstRow = fRow
End Function
Function LastRow(rng As Range, ColNum As Integer) As Long
Dim MaxRow As Long, iRow As Long
Dim sValue As String, lRow As Long
MaxRow = Cells(Rows.Count, ColNum).End(xlUp).Row
sValue = rng.Value
For iRow = MaxRow To 2 Step -1
If Cells(iRow, "B") = sValue Then
lRow = iRow
Exit For
End If
Next iRow
LastRow = lRow
End Function

Option Explicit
Dim filepath As String
Dim wb As Workbook
Sub import()
Dim i As Integer
Dim sname As String
filepath = Application.GetOpenFilename()
Set wb = GetObject(filepath)
For i = 3 To wb.Sheets.Count
sname = wb.Sheets(i).Name
Cells(i, 1) = sname
Cells(i, 2) = Split(sname, ".")
Next
MsgBox ("目录导入完成。")
End Sub
Sub data()
Dim st As Range
Dim x As Integer
Dim y As Integer
Dim m As Integer
Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim cellname As String
Dim stname As String
a = Range("F3")
b = Range("F4")
For c = a To b
cellname = wb.Sheets(c).Range("Title")
stname = "cell" & Mid(cellname, Len(cellname) - 8, 1) & "-" & Right(cellname, 1)
Application.DisplayAlerts = False
wb.Sheets(c).UsedRange.Copy Sheets(stname).Range("A1")
x = wb.Sheets(c).Range("myrow2").Row
y = wb.Sheets(c).Range("myrow3").Row
m = y - x + 1 - 7
If m = 11 Then
For i = 1 To 2
Sheets(stname).Rows(x - 4).Insert
Next i
For j = 1 To 3
Sheets(stname).Rows(y - 2).Insert
Next j
ElseIf m = 12 Then
Sheets(stname).Rows(x - 4).Insert
For j = 1 To 2
Sheets(stname).Rows(y - 3).Insert
Next j
ElseIf m = 13 Then
Sheets(stname).Rows(y - 4).Insert
End If
Next c
wb.Close
MsgBox ("数据导入完成。")
End Sub

Sub FirstRow_LastRow()
Dim arr, MaxRow As Long, iRow As Long, vE3 As String
Dim fRow As Long, lRow As Long
arr = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
MaxRow = UBound(arr)
vE3 = Range("E3").Value
For iRow = 2 To MaxRow
If Cells(iRow, "B") = vE3 Then
fRow = iRow
Exit For
End If
Next iRow
For iRow = MaxRow To 2 Step -1
If Cells(iRow, "B") = vE3 Then
lRow = iRow
Exit For
End If
Next iRow
MsgBox "First Row: " & fRow & Chr(10) & "Last Row: " & lRow
End Sub

定义arrShtName数组，选择工作簿文件之后，将其中的工作表名称存入数组
Sub import()
Dim i As Integer
Dim sname As String
Dim arrShtName, cnt As Integer
filepath = Application.GetOpenFilename()
Set wb = GetObject(filepath)
ReDim arrShtName(1 To wb.Sheets.Count)
For i = 1 To wb.Sheets.Count
arrShtName(i) = wb.Sheets(i).Name
Next
MsgBox ("工作表名已存入数组。")
End Sub