最近写了个代码转汉字的VBA,使用VLookup时会报1004错误,经过dsd999网友的帮助,发现原始是数据类型的问题,如果代码里全是数字,需要使用CInt函数将传入的参数进行类型转换!具体转换的代码如下: Function toNamesOneByOne(codes As String, startRow As Long, endRow As Long) 'MsgBox codes 'MsgBox startRow 'MsgBox endRow Dim arrCode() As String Dim l As Long Dim i As Long Dim j As Long Dim names As String arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l tmpCode = arrCode(i) tmpName = tmpCode For j = startRow To endRow 'MsgBox Sheets("Sheet2").Range("B" & j).Value If tmpCode = Sheets("Sheet2").Range("B" & j).Value Then tmpName = Sheets("Sheet2").Range("A" & j).Value Exit For End If Next If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next toNamesOneByOne = names End Function '将逗号分隔的代码字符串,转换成逗号分隔的汉字字符串 'VBA 运行时错误1004 不能取得类WorksheetFunction的VLookup属性 Function toNames(codes As String) 'MsgBox codes Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l 'MsgBox arrCode(i) 'MsgBox Application.WorksheetFunction.VLookup(arrCode(i), Sheets("Sheet2").range("A2:B7"), 1, False) 'names = names + Application.WorksheetFunction.VLookup(arrCode(i), Sheets("Sheet2").range("A2:B7"), 1, False) tmpCode = arrCode(i) tmpName = tmpCode tmpName = Application.WorksheetFunction.Lookup(arrCode(i), Sheets("Sheet2").Range("B2:B7"), Sheets("Sheet2").Range("A2:A7")) If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next toNames = names End Function Function toNamesArg(codes As String, colCode As Range, colName As Range) 'MsgBox codes Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l tmpCode = arrCode(i) tmpName = tmpCode 'MsgBox tmpCode tmpName = Application.WorksheetFunction.Lookup(tmpCode, colCode, colName) If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next 'MsgBox names toNamesArg = names End Function Function toNamesRange(codes As String, rangeCodeName As Range) 'On Error Resume Next Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String 'MsgBox codes arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l tmpCode = arrCode(i) tmpName = tmpCode 'MsgBox tmpCode tmpName = Application.WorksheetFunction.VLookup(tmpCode, rangeCodeName, 2, 0) 'MsgBox tmpName If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next toNamesRange = names End Function Sub toNamesMacro() 'On Error Resume Next Dim codes As String Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String Dim rangeDict As Range codes = Range("A2").Value 'MsgBox codes arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String 'Dim colCode As Range 'Dim colName As Range 'Dim rangeCodeName As Range Set colCode = Worksheets("Sheet2").Range("B2:B7") Set colName = Worksheets("Sheet2").Range("A2:A7") Set rangeCodeName = Worksheets("Sheet2").Range("$B$2:$C$7") For i = 0 To l tmpCode = arrCode(i) 'MsgBox tmpCode 'MsgBox Application.WorksheetFunction.Lookup(tmpCode, colCode, colName) tmpCode = Application.WorksheetFunction.VLookup(tmpCode, rangeCodeName, 2, 0) 'MsgBox tmpCode Next Range("K2").Value = names End Sub Sub test() Set colCode = Worksheets("Sheet2").Range("B2:B7") answer = Application.WorksheetFunction.Min(colCode) 'MsgBox answer Set colName = Worksheets("Sheet2").Range("A2:A7") answer = Application.WorksheetFunction.Max(colName) 'MsgBox answer 'MsgBox Sheets("Sheet2").Range("A1").Value MsgBox ActiveWorkbook.names("gjBegin").RefersToRange.Value End Sub