CODEWITHSUNDEEP
vbaexcel
Behseini
Behseini

Reputation: 6328

How to save VBA in a in or as Macro

I know How to Run a macro which is Recorded in Macro-enabled excel file but in case of Creating a Macro in Visual basic application - from a module I do not know how to save and run them for future use? Is there any way that I can save cosdes in visual basic As a MACRO? so user just run it from the list of existing Macros?

Can you please let me know how to fix this?

Upvotes: 0

Views: 41632

Answers (1)

user2140173
user2140173

Reputation:

  • Can you please let me know how to fix this?

Follow these steps

Open your VBE ( Visual Basic Editor ) with ALT+F11 (or Developer tab then Visual Basic)

Right-click the VBA Project and Insert a Module (Standard coding module - not a class nor userform)

Copy-Paste the below code

Sub YouCanSeeMe()
    MsgBox "I am visible in the list of macros!"
End Sub

Private Sub YouCannotSeeMe()
    MsgBox "I am not shown in the list because I am private" & vbCrLf & _
            "You can only call me from VBA Code"
End Sub

Public Sub IAmPublic()
    MsgBox "I am public so you can see me"
End Sub

Public Sub IAmPublicButYouCannotSeeMe(Optional bool As Boolean)
    MsgBox "I am public but you can't see me in the list of macros " & vbCrLf & _
            "because I need a parameter to run!"
End Sub

now, go back to the Developer tab and click Macros ( or ALT+F8 in the spreadsheet view )

You should now see which subs are visible in the list and which are not.

enter image description here

As you can see it's all down to the access modifiers (Public/Private or default which is Public) and also the visibility in the macros list depends on the amount of parameters Sub requires.

Note there is also a Friend keyword but it's not relevant in this case. You can google it if you want though.

now if you wanted to save some macros for future make sure that your Explorer is showing you all hidden files because AppData is a hidden folder and if you don't see hidden files then you can't save your personal workbook in there.

Full description step-by-step is here

but in easy words

you can save the workbook you just made with the above code in

C:\Users\\AppData\Roaming\Microsoft\Excel\XLSTART

save is as Excel Binary Workbook or .xlsb extension.

Now everytime you start Excel you have access to the macros that were saved in that workbook.

Note this only works on your machine.

Upvotes: 3

Related Questions