4 Replies - 239 Views - Last Post: 21 January 2020 - 09:22 AM Rate Topic: -----

#1 ScottinTexas   User is offline

  • D.I.C Regular

Reputation: 13
  • View blog
  • Posts: 315
  • Joined: 13-March 12

Exposing a Method to Excel

Posted 31 December 2019 - 09:32 AM

I'm just testing calling a C# method from Excel VBA. Lot's of info on line, but with all of that this doesn't work and I can't see why. Here's the whole class library.

namespace ExcelTest
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [Guid("BE126ACC-BBA4-4FA3-8F80-27026AD24ABB")]
    [ComVisible(true)]
    public class ExcelToGUI
    {
        ObservableCollection<string> _strings = new ObservableCollection<string>();

        public ObservableCollection<string> Strings
        {
            get { return _strings; }
            set
            {
                _strings = value;
            }
        }

        public List<string> SetStrings(string[] inStrings)
        {
            foreach (string instr in inStrings)
            {
                Strings.Add(instr);
            }
            List<string> OutArray = new List<string>(Strings);
            return OutArray;
        }

        public ExcelToGUI()
        {

        }

    }
}



The AssemblyInfo.cs has the entry [assembly: ComVisible(true)]. I also put the attribute on the method, but that made no difference. The vba is this

Private Sub cmdDoIt_Click()
Dim ET As New ExcelTest.ExcelToGUI
Dim ary As Variant

    ary = ET.SetStrings(Range("A1:A6"))
    
End Sub



The error is a compile error on the VBA code. It does not recognize the Method, "Method or data member not found."

Yes, there is a reference to the assembly set in Excel. The object browser shows the ExcelToGUI class, but not the method.

What do you think is the problem?

Thanks for looking.

Is This A Good Question/Topic? 0
  • +

Replies To: Exposing a Method to Excel

#2 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7244
  • View blog
  • Posts: 24,556
  • Joined: 05-May 12

Re: Exposing a Method to Excel

Posted 31 December 2019 - 09:56 AM

You need to match the bit widths. If you have 32-bit Excel, you'll need to compile and register your assembly as a 32-bit COM object. If you have 64-bit Excel, you'll need to compile and register your assembly as a 64-bit COM object. Choosing "Any CPU" in your build options will only work if you happen to be on a 32-bit Windows, other than that, you'll need to explicitly choose x86 (for 32-bit) or amd64 (for 64-bit).
Was This Post Helpful? 0
  • +
  • -

#3 ScottinTexas   User is offline

  • D.I.C Regular

Reputation: 13
  • View blog
  • Posts: 315
  • Joined: 13-March 12

Re: Exposing a Method to Excel

Posted 31 December 2019 - 11:58 AM

Thank you for your quick reply. I made sure to build for 32 bit (as that is what Excel is). I get the same error. Meanwhile I have tried another example I found on line here

This worked up to the point of "Debugging into .Net from Excel." Very clear and concise explanation. If I can just figure out what I need to change in mine, I'll let you know. If you think of anything, please let me know!

Thanks again.
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7244
  • View blog
  • Posts: 24,556
  • Joined: 05-May 12

Re: Exposing a Method to Excel

Posted 31 December 2019 - 03:59 PM

I wonder if that string [] parameter doesn't marshal well. I wonder if VBA doesn't know how to find or call the method that takes a string array. Try creating another method that takes just a string like the sample that you linked to.
Was This Post Helpful? 0
  • +
  • -

#5 ScottinTexas   User is offline

  • D.I.C Regular

Reputation: 13
  • View blog
  • Posts: 315
  • Joined: 13-March 12

Re: Exposing a Method to Excel

Posted 21 January 2020 - 09:22 AM

I wanted to wrap this up. I ended up creating a CSV string and passing that to the method. It was a simple method that did some work and passed back a boolean and it worked just fine. I think part of the problem is that discussions published are written poorly and leave too much to guessing. And I guessed wrong. So here is how I did it.
1. Define the work in Excel VBA.
2. Dim a new instance of the C# class in Excel
3. At the point that the method is called, do whatever it takes to convert the passed data into a string and pass that.
4. X=cSharpInstance.Method (string object)

In C# set put this attribute on your public class "[ClassInterface(ClassInterfaceType.AutoDual)]."
In project properties under Application tab, click the button "Assembly Information" and check the box "Make Assembly COM visible."
Under the Build tab check the box "Register for COM interop."

If you want to debug while running then in the Properties under the Debug tab select "Start external program" and point it to Excel.exe.
Under "Start Options" enter the path and name of your Excel file.

Make sure your excel file is closed to build the app.

Things to figure out.
1. How to pass other objects.
2. How to return other objects from c#.

This is brief, but I wanted to give interested readers a starting point for doing this.

Thanks.

This post has been edited by ScottinTexas: 21 January 2020 - 09:25 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1