Excel Automation - Running an Excel Macro or Function

SHardy

Version 3.70.B

Hi,

I am using Excel automation and, while the workbook is open, I am wanting to run a macro. I have setup a very simple "Hello World" message macro to test with. I have entered the following line of code to run the macro:

XlApplication.Run('HelloWorld');

However, Navision returns the following error:

"The call to member Run failed. Microsoft Office Excel returned the following message: The macro 'HelloWorld' cannot be found."

Help!!! Why might this be?

Once I have successfully got this working, I then want to change it to run a function that requires a string to be passed to it and that will then return an integer. Any help with this would be appreciated. The code in Excel is as follows:

Function ReturnCellLength(CellRef As String) As Integer
On Error Resume Next
ReturnCellLength = 0
ReturnCellLength = Len(Range(CellRef).Value)
End Function

How would I call this function passing a cell reference as a string (e.g. "A1")?

Note: Both of these macros are within the "ThisWorkbook" module.

Thanks,
Simon

Marije_Brummel

I think the macro should be in either the personal modules or in the excel template (if you use any)

SHardy

Aha! That solved it. Something so simple, but not so obvious.

I have moved the macros onto a new module and all works fine.

Thanks.