How can I use sub from another workbook

Question

In the book PERSONAL.XLSB, I have this code:

Public Sub Password(ByVal Target As Range) a = "" For n = 1 To Len(Target) a = a & "*" Next n Target.NumberFormat = """" & a & """;""" & a & """;""" & a & """;""" & a & """" End Sub 

In my new book, I have this code:

 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Info.Range("AA9").Address Then Workbooks("PERSONAL.XLSB").Password Target End If End Sub 

I get an error: Compile error: Invalid use of property

Answer 1

Here's how you run a macro from your personal book:

 Application.Run "PERSONAL.XLSB!Password", Target 

[EDIT] It’s worth noting that instead of a loop to build the string * you can do this:

 Public Sub Password(ByVal Target As Range) Dim sMask as String sMask = Mid(WorksheetFunction.Rept(";""" & String(Len(Target.Value), "*") & """", 4), 2) Target.NumberFormat = sMask End Sub 

Answer 2

The Application.Run method shown by tigravatar works dynamically (i.e., everything is allowed at runtime), and this is the easiest way to quickly call some procedure.

If you access many procedures or use more functions or subnets, you can add a link to the project name Personal.xlsb (through Tools> Links). You should rename the VBA project codename Personal.xlsb from the default "VBAProject" to something unique, such as "PersonalLibrary" or something else. Then you add a link to PersonalLibrary.

Then you get full access to all public functions and subsystems in standard modules, any sheets and classes, early binding to classes and access to fields, properties and events.

This additionally comes with the benefits of intellisense and standard compile-time tests, such as checking method signatures (that is, that the function arguments or sub match what should be) and static typing.

(note that for Classes you will need to use the function in the standard module Personal.xlsb to return an instance of any class, since they are not "creative" external projects)

In your case, you can access it by adding a link, just like PersonalLibrary.Password(target)