Run C# Array in Excel

richard smith

I have a C# array set-up like this:
Prefix = new string[2] { "Alpha", "Omega" };
fName = new string[2] { "123", "481" };

for (int i = 0; 0 < Prefix.Length; i++)
{
for (int x = 0; 0 < fName.Length; x++;
{
xlApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, xlApp, new object[], { "MacroName", Prefix, fName }
}
}

The problem I have is that Excel continues to cycle the elements in the array, and does not terminate the array once all elements have been processed. Is this a C# issue, or an issue in my Excel Macro?

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted answer
I mean that you're invoking the macro 4 times (2 x 2) with the same arguments as this line is independent of the 'for' variables 'i' and 'x':

xlApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, xlApp, new object[]{ "MacroName", Prefix, fName});
richard smith13y ago
Apalogies mate...some the code to cycle the array elements in my VBA had been commented out. Thank you for the support!
Vulpes13y ago
As you're now passing the whole arrays rather than particular elements, the error message makes no sense. The only thing that's different is that you're calling the macro a single rather than four times.

Was there an error message with the code as you originally had it (your opening post)?
richard smith13y ago
I removed the 2 if statements and have it now as this, but:
I have a C# array set-up like this:
Prefix = new string[2] { "Alpha", "Omega" };
fName = new string[2] { "123", "481" };

xlApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, xlApp, new object[], { "MacroName", Prefix, fName }

still get a byref argument type mismatch when it hits the Excel portion
richard smith13y ago
I'm not following you...I am only trying to invoke the for loop once, but used a double for loop so that the naming convention stays intact. Example, the naming convention should be:
Alpha123
Omega481

That is why I used a double for.
Vulpes13y ago
If your macro is expecting the whole arrays to be passed, then I don't understand why you're (in effect) invoking it 4 times using the double 'for' loop.

Shouldn't you just be invoking it the one time?
richard smith13y ago
Here is the Excel Procedure I am attempting to call:
Public Sub MacroName(Prefix, fName)
Call TestingProcedure(Prefix, fName)
End Sub

When I change my C# code to your suggestion I get a byRef type mismatch on the variables I try to pass?
Vulpes13y ago
It appears that you're passing the whole arrays each time rather than the elements at the appropriate indices. Try:

for (int i = 0; 0 < Prefix.Length; i++)
{
for (int x = 0; 0 < fName.Length; x++;
{
xlApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, xlApp, new object[]{ "MacroName", Prefix[i], fName[x]});
}
}