我可以使用自定义函数从 csv 文件中提取数据吗？

如果问题是您需要完整路径，但只想传入文件名，则此更改

Function GetCSVCellValueFromRecord(csvFilePath As String, recordIndex As Long, _
                                  targetColumnName As String) As Variant
    Dim csvContent As String
    Dim lines() As String, headers() As String
    Dim columnIndex As Long, i As Long, ff As Integer
    
    ' Read the entire CSV file into a string
    ff = FreeFile 'don't use a hard-coded value...
    Open ThisWorkbook.Path & "\" & csvFilePath For Input As ff
    csvContent = Input$(LOF(ff), ff)
    Close ff
    '...etc

...只要所有 CSV 文件都位于保存带有 UDF 的工作簿的同一文件夹中，就应该执行所需的操作。

这是一个直接和具体回答 OP 问题的简单函数。

这已经在一个非常简单的 CSV 文件上进行了测试，如下所示 -

Option Explicit

Sub sbTest()
' MsgBox NotIn("abcdef", "ab")
' MsgBox IsIn("abcdef", "ab")
' MsgBox fnGetValueCSV("s.csv", 2, "CompanyName")
' MsgBox fnGetValueCSV("s.csv", -2, "CompanyName")
 MsgBox fnGetValueCSV("zz.csv", 20, "MaxCellVoltage")
End Sub

Public Function fnGetValueCSV(sCSVFileName As String, recordIndex As Long, targetColumnName As String) As Variant ' As String
' https://stackoverflow.com/questions/76947796/can-i-use-a-custom-function-to-pull-data-from-a-csv-file
' this is the spec  -  (csvFilePath As String, recordIndex As Long, targetColumnName As String) as Variant
' taskkill /F /IM excel.exe - kill excel.exe from the CMD line when it is in an infinite loop
' https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function
' https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/cverr-function
' https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/cell-error-values

    Dim strFileName As String
    Dim iTxtFile As Integer
    Dim strFileText As String
    Dim zz As Long
    Dim iHdrPos As Long
    Dim iNumOfCommas As Long ' the number of commas to find up to the targetColumnName
    Dim sComma As String
    Dim iCommaPos As Long
    Dim iCommaPos2 As Long
    Dim arrLines() As String
    sComma = ","
    
    On Error GoTo Label_CSV_File_Name_Error
    strFileName = ActiveWorkbook.Path & "\" & sCSVFileName ' CSV is in the same place as the Excel workbook.
    iTxtFile = FreeFile
    Open strFileName For Input As FreeFile
    strFileText = Input(LOF(iTxtFile), iTxtFile)
    Close iTxtFile
    On Error GoTo Label_Unknown_Error
    
    arrLines = Split(strFileText, vbCrLf) ' assumes the CSV file is perfectly formed with vbCrLf at the end of each line and nowhere else
    
    If recordIndex < 1 Then
        ' line 0 is the header line, line 1 is the first data line
        fnGetValueCSV = CVErr(2015) ' #VALUE!
        Exit Function
    End If
    
    If NotIn(arrLines(0), targetColumnName) Then
        ' return #VALUE! targetColumnName not found in header line
        fnGetValueCSV = CVErr(2015)  ' #VALUE!
        Exit Function
    End If
    
    iHdrPos = InStr(1, arrLines(0), targetColumnName) ' find the character position of the targetColumnName
    iNumOfCommas = iHdrPos - Len(Replace(Mid(arrLines(0), 1, iHdrPos), sComma, "")) ' the number of commas up to the target column name
    iCommaPos = 1
    For zz = 1 To iNumOfCommas Step 1 ' find the character position of the comma in the data line at recordindex
        iCommaPos = InStr(iCommaPos + 1, arrLines(recordIndex), sComma)
    Next zz
    iCommaPos2 = InStr(iCommaPos + 1, arrLines(recordIndex), sComma) ' the character position of the comma after the data we want
    fnGetValueCSV = Mid(arrLines(recordIndex), iCommaPos + 1, iCommaPos2 - iCommaPos - 1) ' finally return the value in the data line
    Exit Function
    
Label_CSV_File_Name_Error:
    ' return #NAME? file not found
    fnGetValueCSV = CVErr(2029) ' | xlErrName | 2029 | #NAME? |
    Exit Function

Label_Unknown_Error:
    fnGetValueCSV = "#ERROR!" & "**" & Err.Number & "**" & Err.Description
    Exit Function

End Function

Function NotIn(sText As String, sFind As String) As Boolean
    If InStr(1, sText, sFind) = 0 Then
       NotIn = True
    Else
       NotIn = False
    End If
End Function

帮助程序函数 NotIn 包装该函数，使其更容易理解代码，尤其是参数的圆周方式 用于搜索文本和要查找的文本。INSTR()

如果 CSV 文件在行尾以外的任何位置都有 CRLF，则例程将不起作用。显然，它也无法处理嵌入在数据文本值中的逗号。

下面是 Excel 生成的有效 CSV 文件的示例。它具有用双引号括起来的文本，其中文本字段中有一个 CRLF，例如以“507... 它还对文本字段中出现的双引号进行了转义。例如“冷呼叫的艺术”” 带有逗号的文本字段也将用双引号括起来。

1，Davolio，Nancy，销售代表，女士，08/12/1948,01/05/1992，“507 - 20th Ave. E. Apt. 2A“，Seattle，WA，98122，USA，（206） 555-9857,5467，，”教育包括 1970 年获得科罗拉多州立大学心理学学士学位。她还完成了《冷呼叫的艺术》。Nancy 是 Toastmasters International 的成员。