Solved

How do I insert macro code into Excel Interop Worksheet?

Posted on 2008-10-23
4
4,090 Views
Last Modified: 2013-12-17
I'm generating Excel worksheets from .Net windows app written in C#. I have a hard time inserting VBA/Macro code into specific worksheets.

I've successfully managed to insert code so it comes out in a Module (Module1), but I want to have the code in the sheets instead to be able to access objects of that sheet.

How do I add code to the sheet?

Furthermore, how do I attach an added control (checkbox) to the VBA macro?

Best regards
// Get the string containing the VBA code
String sCode  = Properties.Resources.ResourceManager.GetString("AssortmentReportVBA");
 
// Create a new VBA code module.
VBIDE.VBComponent oModule = book.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
                
                                
// Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(sCode);
 
 
 
 
Worksheet scSheet = book.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet;
            
scSheet.Name = "Sheet_1";
                                    
// I want this checkbox to run a macro when clicked:
Range viewBoxCursor = viewBoxHeader.get_Offset(1, 0);
            
Shape checkBox = scSheet.Shapes.AddOLEObject("Forms.CheckBox.1", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, viewBoxCursor.Left, viewBoxCursor.Top, viewBoxCursor.Width, viewBoxCursor.Height);
            checkBox.Name = "volumeCheckBox";
            OLEObject sheetBox = (OLEObject)scSheet.OLEObjects(checkBox.Name);
            sheetBox.Object.GetType().InvokeMember("Caption", System.Reflection.BindingFlags.SetProperty, null, sheetBox.Object, new object[] { "Volume" });
 
// All of the above works -  Here's where the trouble starts - how do I add code for this sheet and connect to the macro? This below does not work...
 
scSheet.Scripts.Add(scSheet.Cells.get_Range("A1", "A1"),
        Microsoft.Office.Core.MsoScriptLocation.msoScriptLocationInBody,
        Microsoft.Office.Core.MsoScriptLanguage.msoScriptLanguageVisualBasic,
        "ScriptOne", String.Empty, @"MsgBox ""This is ScriptOne.""");

Open in new window

0
Comment
Question by:jenmat
  • 2
4 Comments
 
LVL 4

Assisted Solution

by:JurgenDB
JurgenDB earned 125 total points
ID: 22786922
You can get the code-behind-sheet with something like
book.VBProject.VBComponents("Sheet1")
where Sheet1 is the codename of the sheet
0
 

Author Comment

by:jenmat
ID: 22794355
Sounds like just what I'm after, but...

VBIDE.VBComponent oModule = book.VBProject.VBComponents("Sheet1");
does not compile:
Microsoft.Vbe.Interop._VBProject.VBComponents' is a 'property' but is used like a 'method'

and when I elaborated and tested
VBIDE.VBComponent oModule = book.VBProject.VBComponents.Item(scSheet.Name);
Or
VBIDE.VBComponent oModule = book.VBProject.VBComponents.Item(scSheet.Name);
it gives exception -  Subscript out of range, so no luck yet.

0
 

Accepted Solution

by:
jenmat earned 0 total points
ID: 22794474
This worked though...

VBIDE.VBComponent oModule = book.VBProject.VBComponents.Item(1);
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question