How to restrict VBA function only on module

2
votes

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

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?

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

2
excelvba

2 Answers

1
votes

I don't think you can block function functionality which allows to access it both in VBA and Excel 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

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

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).

1
votes

With Application.Caller property you can determine who called your function and if it was called from worksheet you can raise error or return what ever you want but different from you propper calculated result.

Private Function callFunction(ByVal num As Integer) As Variant
    If (TypeName(Application.Caller) = "Range") Then
        ' function was called from worksheet
        callFunction = "Invalid procedure call" ' or raise error Err.Raise Number:=5
        Exit Function
    End If

    ' continue ...
    callFunction = num * num
End Function

About Application.Caller: http://msdn.microsoft.com/en-us/library/office/ff193687.aspx