Using C# in Excel

  • Thread starter Thread starter kean
  • Start date Start date
joe_bradley said:
Unfortunately, C# is not "integrated" into office, something you no doubt found out already. I believe the next version of office allows this, but even so that will not help everyone, there are many spreadsheets out using copious VBA code there that cannot be retired, so they will lurch onward zombie-like through the years, eating the brains of those unfortunate enough to have to maintain them.
Click to expand...

True and not true. Using C# class from VBA is in fact straight-forward. Almost all C# classes can be registered as COM easily (meaning you don't need to worry about the details). There is an example in my book.

As a matter of fact, we use VBA/Excel as front end to send jobs to a C# based pricing engine farm.
 
Excel Net said:
Looks like some interesting examples on how to utilize Excel object model from desktop applications, but from my experience most of the quants use Excel and .NET quite differently - Excel as a front end UI and .NET assemblies as a place to put calculation and other business logic, not the vice versa.
Click to expand...

Certainly. Excel IS a front end. By putting the logic inside an assembly (instead of VBA), it is now possible to make multiple facades for your core logic library. In addition to Excel, it can also be shared by a regular C# application on either a local or a remote machine, C++ application and even Unix application (e.g. Perl) via webservice.
 
zhouxing said:
As a matter of fact, we use VBA/Excel as front end to send jobs to a C# based pricing engine farm.
Click to expand...

Hi zhouxing,

How big is the pricing engine farm you use?

We found that the standard facilities for multithread processing in C# are lacking and we have to dish that effort. Have you seen the same behavior? Also, the C# threadpool is shared among all the programs running on the CLR (and, from I have found, the OS :( ). We didn't like that part.
 
An engine controller typically controls 30+ "slave" machines, each of which run3 4 pricing engines (a 4-core machine). We have multiple farms for different teams. One nice thing is that it serves both Windows clients and Unix clients :-)

Also, London Stock Exchange has a messaging based backbone transmission system is written exclusively in C#. I don't know its size now, but when I was there, it had hundreds of machines.

I am not saying C# is better than Java for distributed computing. They probably have similar capabilities, depending on the platform and, most importantly, architecture design. But it is possible to have reasonable scaled system written in C#.

To serve Microsoft products as client interface (e.g. Excel), C# is certainly much better than other technologies.
 
alain said:
Hi zhouxing,

How big is the pricing engine farm you use?

We found that the standard facilities for multithread processing in C# are lacking and we have to dish that effort. Have you seen the same behavior? Also, the C# threadpool is shared among all the programs running on the CLR (and, from I have found, the OS :( ). We didn't like that part.
Click to expand...

I haven't seen threading pool issues in the past (of course, you need to create your own thread, not use the default worker thread). But I do encounter problems in its garbage collection for large scaled messaging system (e.g. the one in London Stock Exchange). We actually got a Microsoft senior guy to do some investigation / customization for us at very lower level.
 
We found that the standard facilities for Thread Pooling offered by C# are extremely lacking. Also there is cap for only 25 threads per CPU or core (I don't remember exactly). So you have you roll your own and still can get contention with the OS. Our experience with the CLR hasn't been that good so far :(
 
alain said:
We found that the standard facilities for Thread Pooling offered by C# are extremely lacking. Also there is cap for only 25 threads per CPU or core (I don't remember exactly). So you have you roll your own and still can get contention with the OS. Our experience with the CLR hasn't been that good so far :(
Click to expand...

25 concurrent threads is the default, though you can adjust by changing the machine.config.

Actually it comes back to a more basic question. Is it really a good strategy to run 25 CPU intensive threads SIMULTANEOUSLY per CPU/core? Probably not. Running 1 or 2 CPU intensive thread with bunch of others low-CPU threads should be OK.
 
That discussion is for another thread. The limit is per CPU (not core which could be a problem since new multicore CPUs are popping everyday) and I can see plenty of reasons to run multiple threads (> 25) per multicore CPUs. It all depends on the application.
 
alain said:
That discussion is for another thread. The limit is per CPU (not core which could be a problem since new multicore CPUs are popping everyday) and I can see plenty of reasons to run multiple threads (> 25) per multicore CPUs. It all depends on the application.
Click to expand...

Of course, you almost for sure will have >25 threads. The point is whether all these threads are CPU heavy.
 
I have a Windows Form application where I have created a form and a control DataGridView.

My C Sharp Code recognizes the DataGridView as a object since I inlcuded System.Windows.Forms

Now I am creating a .dll and want to pass the DataGridView in the .dll as a "DataGridView" Object.

I added the reference of windowsFormsIntegration in my project but it does not seem to work.

Any Ideas how can I do it?
 
Aditya Chitral said:
I have a Windows Form application where I have created a form and a control DataGridView.

My C Sharp Code recognizes the DataGridView as a object since I inlcuded System.Windows.Forms

Now I am creating a .dll and want to pass the DataGridView in the .dll as a "DataGridView" Object.

I added the reference of windowsFormsIntegration in my project but it does not seem to work.

Any Ideas how can I do it?
Click to expand...

I figured out one way.

Make a normal windows form application and change the project output to class library and build the project again. A dll would be formed.

Any other way to do it?
 
Aditya Chitral said:
I figured out one way.

Make a normal windows form application and change the project output to class library and build the project again. A dll would be formed.

Any other way to do it?
Click to expand...

You can only reference a library, not an .exe. Maybe this restriction is a bit strange, but unfortunately it is there until MS removes it.

On a positive side, this may somewhat enforce good architecture design. i.e. separating business logic from presentation. Business logic code should be in a library. Exe should reference the library and including some presentation code (being a Windows Form? ASP.NET? even Excel). In this way, you should unlike hit into a situation where you need to reference an .exe in the first instance.
 
I built my dlls in Csharp and in the project properties checked the option for "Register for COM" Interop.

I also wrote wrote the register and unregister functions in my dll. When I compile the code and deploy it in the local machine, it works fine i.e. Using Excel Automation, it detects my dll and I can use the function.

However taking the excel on another machine does not find my dll.

Any insights?
 
Aditya Chitral said:
I built my dlls in Csharp and in the project properties checked the option for "Register for COM" Interop.

I also wrote wrote the register and unregister functions in my dll. When I compile the code and deploy it in the local machine, it works fine i.e. Using Excel Automation, it detects my dll and I can use the function.

However taking the excel on another machine does not find my dll.

Any insights?
Click to expand...

Assuming it is an automation add-in, do one of the following:

1] in Excel, Tools -> Add-Ins -> Automation -> Browser and pick up your DLL
2] run regasm on your new machine
 
zhouxing said:
Assuming it is an automation add-in, do one of the following:

1] in Excel, Tools -> Add-Ins -> Automation -> Browser and pick up your DLL
2] run regasm on your new machine
Click to expand...

So will I have to run regasm on every machine I want to use the dll? The users of my dll are persons who would not be courageous enough to run an exe from windows folder.
 
I usually write a very simple script (.bat?) for deployment which basically copies the DLL to a local directory and run regasm if this is not the first time deployment. Please note that as long as the DLL is registered once, you don't need to re-register even if there are massive change within the newer version of DLL.

Alternatively, if you don't like the script idea, instruct the user to use the (1) approach and tell them where the DLL is. Again, they only need to browse & pick up the DLL once. Afterward, it will automatically appear in the add-in list.
 
..or you could just write a standard XLL in C# and give that to your user. No registering required. Just tell your user to double-click on the XLL and it will just open in Excel, end of story. Also your the function wizard will display descriptions of your functions and parameters, much more user-friendly.

Oh by the way, doesn't usage of RegAsm require Admin rights on the machine ??

This thread :
http://www.wilmott.com/messageview.cfm?catid=10&threadid=70576

goes into more detail, with a touch of entertainment thrown in for good measure :D

Here you can watch a demo ( there's no sound, might need to fullscreen it)
YouTube - Howto create a C# XLL addin using XLW Visual Studio



Hey zhouxing, how's my main man ??;)
 

Similar threads

M
Build and Debug Excel add-in using XLW Project and Visual Studio 2022 C++
Replies
1
Views
2K
Daniel Duffy
Daniel Duffy
L
Financial Instrument Pricing Using C++
Replies
6
Views
4K
Daniel Duffy
Daniel Duffy
EliC
Making An API Call Using cURL in C++
Replies
7
Views
6K
EliC
EliC
J
Calculating Betas in Excel
Replies
0
Views
1K
Jonny Hilton
J
M
Problem ny using C # in Excel with Debug (xlw)
Replies
3
Views
2K
JKPappas
J
Back
Top Bottom