如何仅在模块上限制VBA功能 [英] How to restrict VBA function only on module

问题描述

我对VBA的了解很少，并试图学习它.我已经制作了一个VBA脚本，如下所示:

I have very few knowledge of VBA and trying to learn it. I have made a VBA script which is something like this:

Function doIt()
Dim c As int...
.............
.............
.............
c = callFunction
...............
..............
End Function

Function callFunction(byVal num As Int)
..........
..........
..........
End Function

如您所见，callFunction是一个从主函数doIt调用的函数.假设callFunction计算一个整数的平方.整个VBA脚本都保存在C驱动器中相应AddIns文件夹下的addIn模块中.从excel工作表中调用时，doIt函数可以很好地工作.但是问题是，如果我从工作表中调用函数callFunction，它也可以工作.如何将callFunction仅限于addIn模块，以便只有模块才能使用它，并且如果有人从工作表中调用callFunction(2)，它将不会在工作表中给出2的平方?

as you can see callFunction is a function which is called from the main function doIt. Suppose callFunction calculates square of one integer. The whole VBA scripts is kept on an addIn Module under respective AddIns folder in C drive. The function doIt works well while called from an excel worksheet. But the problem is if I call the function callFunction from worksheet it also works. How can I restrict callFunction only to the addIn module so that only module can use it and if someone call callFunction(2) from worksheet it will not give the square of 2 in worksheet?

注意:即使我将其设为 Private ，仍可以从工作表中调用它.

Note: even if I make it Private, it can still be called from the worksheet.

推荐答案

我认为您不能阻止允许在VBA和Excel单元格中访问它的函数功能.

I don't think you can block function functionality which allows to access it both in VBA and Excel cell.

但是，我有一些变通办法，可让您创建在Cell中调用该函数时给出不同结果的函数，例如可能会返回一些信息(或标准错误)而不是计算结果.

However, I have some workaround idea which allows you to create function which gives different results when it is called in Cell, e.g. some info (or standard error) could be returned instead of calculation result.

以下是表示此功能的代码.我认为这很清楚而且可以理解，因此您不需要其他评论.

Here is the code presenting this functionality. I think it's quite clear and understandable so you would not need additional comments.

Function callFunction(ByVal num As Integer)

On Error Resume Next
    Dim tmpAdd
    tmpAdd = Application.ThisCell.Address

    If Err.Number = 0 Then
        If Left(Application.ThisCell.Formula, 13) = "=callFunction" Then
            'called from excel cell, but this function is called!
            'returns any type of standard function error

                callFunction = CVErr(XlCVError.xlErrNull)
                Exit Function
        End If
    End If
    'called from VBA/IDE environment or from other function
    'standard calculation

        callFunction = num ^ num

End Function

编辑受@DanielDusek答案的启发(但有点不完整)，我将Daniel和我的解决方案混合在一起.因此，新的，相当完整的代码:

Edit Inspired by @DanielDusek answer (but a bit incomplete one) I mixed both Daniel and mine solution into one. So, new and rather complete code:

Function callFunction(ByVal num As Integer)

    If TypeName(Application.Caller) = "Range" Then
        If Left(Application.ThisCell.Formula, 13) = "=callFunction" Then
            'called from excel cell, but this function is called!
            'returns any type of standard function error

                callFunction = CVErr(XlCVError.xlErrNull)
                Exit Function
        End If
    End If
    'called from VBA/IDE environment or from other function
    'standard calculation

        callFunction = num ^ num
End Function

如果在任何VBA函数/子例程中使用任何调用位置(间接使用)，则两种解决方案都将给出 num ^ num 个结果.在Excel单元格(直接使用)中调用时，它将提供 Error 值.

Both solution will give num ^ num result if used in any VBA function/subroutine whichever the place of calling (indirect use). It will give Error value when called in Excel cell (direct use).

这篇关于如何仅在模块上限制VBA功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！