How do I call VBA subs from different spreadsheet from code behind form?

[clhamilton]

Inherited existing application, VBA validation subs are trigger from
Worksheet_Change and/or Workbook_SheetSelectionChange. Change subs call
subs from separate worksheet which error when called from Code Behide Form.

Form added to allow user input into multiple spreadsheets.

How do I call VBA subs from different spreadsheet from code behind form?

clhamilton

 

[Dave Peterson]

I'm not sure if this is what you mean, but you can use application.run to call
procedures in other workbook projects.

Saved from a post for a similar question:

Dim pWkbk as workbook
set pwkbk = workbooks("Personal.xls")
application.run "'" & pwkb.name & "'!macronamehere", "parm1", "parm2"

or if you're returning a value from a function:

dim res as string 'or variant or long or ...
res = application.run("'" & pwkb.name & "'!macronamehere", "parm1", "parm2")

You could also create a reference to this personal.xls workbook and call it just
like it was built into excel.

Tools|references
(but give the personal.xls's project a nice unique name (not VBAProject).

ps.

If you're using a function living in personal.xls inside a cell:
=personal.xls!functionnamehere(a1,b1,c1)

or save the file as an addin (*.xla) and use it in the cell like it's built into
excel:
=functionnamehere(a1,b1,c1)