Create macro in excel from VB application

Hi All

I am trying to create a excel macro from VB.
In a VB application i create an excel sheet, workbook
and worksheet.

I would very much like to create a macro. This could
fx. be a simple macro that sets the font size.

After saving the sheet (from VB), I would like to be able to see the macro in excel (alt-F8).

I can see that excel.application in VB has a Sub called MacroOptions, where it is possible to set the ShortCutKey.
There must be some way, to create the macro!?
LVL 1
eli_frandsenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
   With wkbk
        .VBProject.VBComponents.Add vbext_ct_StdModule ' = 1 if you don't have the library referenced
        .VBProject.VBComponents("Module1").CodeModule.AddFromString "Public Sub Macro1()" & vbLf & "Msgbox " & Chr(34) & "Hello" & Chr(34) & vbLf & "End Sub" & vbLf
    End With

This assumes that wkbk is an excel.workbook object that you have already instantiated.

The code simply adds a standard module to the workbook and adds the text:

Public Sub Macro1()
Msgbox "Hello"
End Sub

To the module created. When you save the workbook this macro will still be there.
0
 
eli_frandsenAuthor Commented:
Hello TimCottee

I just knew there had to be a way. And I had thought of
modules but could not quite figure out how to insert them.

Thank you very much, You saved my day.

Regards
Eli Frandsen
0
 
eli_frandsenAuthor Commented:
Hello TimCottee

I just knew there had to be a way. And I had thought of
modules but could not quite figure out how to insert them.

Thank you very much, You saved my day.

Regards
Eli Frandsen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.