Solved

How do I insert macro code into Excel Interop Worksheet?

Posted on 2008-10-23
4
3,998 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now