vba Excel:子中的参数。有什么变化?

称呼

我在 Excel 中有一个像这样的子:

Sub epign()
et = 4529
ep = 1
fc = 3
ent = 9992
Dim resultado As Variant
Dim myRange As Range
Ruta = "C:\TRABAJO\MisEstados\"
libro = Ruta + CStr(et) + "\" + CStr(et) + "-data.xlsx"
Nombre = "'" + libro + "'!D_" + CStr(ep)
soloLibro = CStr(et) + "-data.xlsx"
ws = "D" + CStr(ep)
resultado = 0
Set myRange = Workbooks(soloLibro).Worksheets(ws).Range("D_" + CStr(ep))
resultado = Application.VLookup(ent, myRange, fc, False)
End Sub

这很好用,结果是一个正数。

但是,我试图从这样的函数中使用这个子函数:

Function epig(et As Integer, ep As Integer, fc As Integer, ent As String) As Variant
Call epign(et, ep, fc, ent)
epig = resultado
End Function

Sub epign(et As Integer, ep As Integer, fc As Integer, ent As String)
Dim resultado As Variant
Dim myRange As Range
Ruta = "C:\TRABAJO\MisEstados\"
libro = Ruta + CStr(et) + "\" + CStr(et) + "-data.xlsx"
Nombre = "'" + libro + "'!D_" + CStr(ep)
soloLibro = CStr(et) + "-data.xlsx"
ws = "D" + CStr(ep)
resultado = 0
Set myRange = Workbooks(soloLibro).Worksheets(ws).Range("D_" + CStr(ep))
resultado = Application.VLookup(ent, myRange, fc, False)
End Sub

尝试在 Excel 单元格上使用它时:

=epig(4529;1;3;"9992")

结果是错误 2042。

为什么?有什么区别?

被骚扰的爸爸

要返回函数的结果,请将结果分配给函数的名称。要在您的电子表格中使用一个函数,它必须是 Public 并且在一个普通模块中(不是那些附加到工作簿或工作表的模块)您只需要:

 Public Function epig(et As Integer, ep As Integer, fc As Integer, ent As String) As Variant
 Dim resultado As Variant
 Dim myRange As Range
 Ruta = "C:\TRABAJO\MisEstados\"
 libro = Ruta + CStr(et) + "\" + CStr(et) + "-data.xlsx"
 Nombre = "'" + libro + "'!D_" + CStr(ep)
 soloLibro = CStr(et) + "-data.xlsx"
 ws = "D" + CStr(ep)
 resultado = 0
 Set myRange = Workbooks(soloLibro).Worksheets(ws).Range("D_" + CStr(ep))
 resultado = Application.VLookup(ent, myRange, fc, False)
 epig = resultado  'add this line

 End Sub

然后您可以在单元格中输入 =epif(4529, 1, 3,9992) 并获得结果

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章