Code Transferability Tutorial

CODE TRANSFERABILITY TUTORIAL
I will use the Random Number Generator file from VBA Example Files and Code to explain the main concepts.

To extend the file for use in any workbook we must store the code and UserForm from the "Random Number Generator" file into your PERSONAL.XLS file. If you have no idea what your PERSONAL.XLS file is then yours probably doesn't exist yet - to create it you need to record and save a macro to it.
Therefore, to create it if it doesn't exist we must now record a macro - it can be as simple as starting and stopping the Macro Recorder without actually have done anything to the Worksheet - but first do the following after opening the recorder (Either choose "Tools -> Macro -> Record New Macro..." from the Menu Bar or choose "View -> Toolbars -> Visual Basic" from the Menu Bar and then click on the blue circle).

In the "Store macro in:" Listbox - Select the "Personal Macro Workbook" option.

Next click on OK and then stop the recording immediately by clicking on the blue square.

1. Open the Visual Basic Editor (press "ALT+F11"). You should see both "VBAProject (PERSONAL.XLS)" and "VBAProject (Random Number Generator)" in the Project Explorer Window.
   Expand the 'Forms' Directory from the "VBAProject (Random Number Generator)", click and drag the 'frmRandom' form anywhere in the "VBAProject (PERSONAL.XLS)" tree.
   
   
2. Double-click on the "VBAProject (PERSONAL.XLS)" to open its directory tree structure. There should already be a "Module 1" in here due to your earlier recording - if you double click on this it will open an Editor window. In this window you should see the following code:
   

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 18/07/2001 by <Your Name Here>
'

'
End Sub

Now delete all of this code and in its place instead type:

Sub GenRand()
frmRandom.Show
End Sub

3. Next exit the VBA Editor and return to the Worksheet. Record another Macro but fill in the details as below:
   

   

   Notice the "Macro name:" - Type in the name "runRandom",
   the "Shortcut key:" entry - click in Text Box and press keys "SHIFT+Q" and
   the "Store macro in:" Listbox - Select the "Personal Macro Workbook" option.
   Click OK and then immediately stop the recording of the macro.
   
   (Note that "SHIFT+Q" or upper case "q" is a convenient Shortcut key that does not in general have any other function associated with it - try others instead if you wish but be prepared for error messages)
   

4. Get back into the VBA Editor (press "ALT+F11"). Double-click on the "VBAProject (PERSONAL.XLS)" to open its directory tree structure. Open once again the "Module 1" - your new recording should be in here too. If you double click on this it will open the Editor window in you should see the following code (under your previously altered "GenRand()" Subroutine).
   

Sub runRandom()
'
'runRandom Macro
' Macro recorded 18/07/2001 by <Your Name Here>
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
End Sub

Now modify this code by adding the bold text below:

Sub runRandom()
'
'runRandom Macro
' Macro recorded 18/07/2001 by <Your Name Here>
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Application.run "PERSONAL.XLS!GenRand"
End Sub

5. Whilst still in the "VBAProject (PERSONAL.XLS)" directory structure, click on the "Save" icon to save your new PERSONAL.XLS file.
   
   
6. Finally, exit the VBA Editor and return to the Worksheet. Click on the "New File" icon and in this new Workbook click in any cell and then press "CTRL+SHIFT+Q".
   If you have done the above steps correctly then it should run the Random Number Generator and you will see the UserForm (called frmRandom) thus:

7. Close Excel completely, Open a new Excel file and again press "CTRL+SHIFT+Q" to test again.
   
   
8. NOTE THAT IF YOU CANNOT FIND THE "VBAProject (PERSONAL.XLS)" IN THE VBA EDITOR AFTER YOU HAVE PREVIOUSLY SAVED IT WITH CODE IN, OR IF YOU EXPERIENCE AN ERROR MESSAGE SUCH AS THE FOLLOWING WHEN ATTEMPTING TO RUN A MACRO FROM THE WORKSHEET,

THEN TRY THE FOLLOWING:-

• In any Excel file choose "Window -> Unhide...", select Personal.xls and click on OK. Then close Excel completely and when asked if you want to save changes to PERSONAL.XLS, click "Yes"
  
  
• Next, open Excel again and in any Excel file choose "Tools -> Macro -> Macros" from the Menu Bar. Find and select the name of one of your macros in PERSONAL.XLS, for instance: PERSONAL.XLS!Macro1. Then click on "Edit". This should now automatically open up your code in the "VBAProject (PERSONAL.XLS)" directory in the VBA Editor. You can now do as you wish within the VBA environment.
  
  

The above description of how to "embed" a Program/UserForm/etc into your Personal Excel Workbook, which can then be used in any other Excel file either by a Shortcut Key as above or by including an embedded Button, etc, in the Worksheet, is a very useful and timesaving technique.

Your code is now transferable!

Links