Excel Hacks Ebook [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Excel Hacks Ebook [Electronic resources] - نسخه متنی

Raina Hawley, David Hawley

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










Hack 94 Distribute Macros






Although you can distribute a macro along with
a workbook, if you want to distribute only the
macro's functionality, an Excel add-in is the way to
go.



An Excel add-in is nothing more than
an Excel workbook that was saved as an add-in by selecting File
Save As... Microsoft Excel Add-in
(*.xla). Once it's saved and
reopened, the workbook will be hidden and can be seen only in the
Project Explorer via the VBE. It is not hidden in the same way as the
Personal.xls file, as this can be seen (and made
visible) via Windows Unhide.



Once you have completed the workbook you want to use as an add-in,
you need to save a copy of it. You can save it to any location you
want, but make sure to note where you placed it.



Open any workbook, and on the Tools
menu, select Add-Ins, then click Browse. Locate your add-in from
where you saved it, select it, and then click OK.



Ensure that your add-in is in the
Add-Ins Available: box and that the box is checked. Then click OK to
install the add-in. You can save most code to an Excel add-in without
too many changes. There are a few issues worth considering, however:





The ThisWorkbook object will always refer to the
add-in, not to the user's workbook. Use the
ActiveWorkbook object instead.





You
cannot refer to sheets in the ActiveWorkbook with
CodeNames.





You should always put toolbars, etc., back
to the way the user had them originally. There is nothing worse than
an add-in that changes all your Excel settings without your
knowledge.





Always include some sort of error handling
(yes, most add-ins will cause errors at some time).





Be very aware that the user might have many sorts of protection
applied. Never use code to unprotect any part of
the user's workbook. Simply display a message asking
the user to unprotect.





Make full and good use of the worksheet you have in the add-in. We
use the worksheet(s) to store user settings such as toolbars.





Holding down the Shift key will
not prevent add-in workbook events from running
(holding down the Shift key will prevent a normal
.xls file from running, however).





If you need to see or work with the add-in workbook again to
incorporate updates or modifications, go into the VBE while the
add-in is installed and, from the Properties window, select the
IsAddin property and set it to False. Saving the
workbook as an add-in sets this property to
True.





Apply
protection to the modules of your add-in by selecting Tools
VBAProject Properties Protection.






Once you have created your
add-in you will need to make the macros within it easy for the user
to run. This is best achieved by using the
Workbook_AddinInstall and
Workbook_AddinUnInstall events in the private
module of the ThisWorkbook object. Simply
double-click ThisWorkbook for the
*.xla file, and Excel will take you into the
private module where the code is placed, as shown in Figure 7-11.




Figure 7-11. Project Explorer with ThisWorkbook selected for an add-in named Number Manager.xla




Here is a simple example of the code:



Option Explicit
Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall( )
On Error Resume Next 'Just in case
'Delete any existing menu item that may have been left.
Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
'Add the new menu item and set a CommandBarButton variable to it
Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
'Work with the Variable
With cControl
.Caption = "Super Code"
.Style = msoButtonCaption
.OnAction = "MyGreatMacro" 'Macro stored in a Standard Module
End With
On Error GoTo 0
End Sub
Private Sub Workbook_AddinUninstall( )
On Error Resume Next 'In case it has already gone.
Application.CommandBars("Worksheet Menu Bar").Controls("Super Code).Delete
On Error GoTo 0
End Sub



This is all the code
you'll need to add a single menu item (called Super
Code) to the end of the existing worksheet menu bar as soon as the
user installs the add-in via Tools Add-ins. When the Super
Code menu item is clicked, a macro (that is within a standard module
of the add-in) is run. Remember that the preceding code
must be placed in the private module of
ThisWorkbook for the add-in.



If you want the Super Code menu item added, say, before the format
menu item, you can use this code:



Option Explicit
Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall( )
Dim iContIndex As Integer
On Error Resume Next 'Just in case
'Delete any existing menu item that may have been left.
Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
'Pass the index of the "Format" menu item number to a variable.
'Use the FindControl method to find its Index number. ID number _
is used in case of customization
iContIndex = Application.CommandBars.FindControl(ID:=30006).Index
'Add the new menu item and set a CommandBarButton variable to it.
'Use the number passed to our Integer variable to position it.
Set cControl = Application.CommandBars("Worksheet Menu Bar") _
.Controls.Add(Before:=iContIndex)
'Work with the Variable
With cControl
.Caption = "Super Code"
.Style = msoButtonCaption
.OnAction = "MyGreatMacro" 'Macro stored in a standard module
End With
On Error GoTo 0
End Sub



You would not have to change the Workbook_AddinUninstall(
)
code in this case.



In these examples, all the menu item code is in
Workbook_AddinInstall and
Workbook_AddinUnInstall. This is not a problem
when the code is adding only one menu item. If, however, you will be
adding more than one item and, perhaps, even submenus, you should
place the menu item code in a procedure (or two) inside a standard
module. Then use some code such as
this:



Private Sub Workbook_AddinInstall( )
Run "AddMenus"
End Sub
Private Sub Workbook_AddinUninstall( )
Run "DeleteMenu"
End Sub



In the standard module, put some code such as this:



Sub AddMenus( )
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
'(2)Set a CommandBar variable to the worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")
'(3)Return the index number of the Help menu. We can then use _
this to place a custom menu before it.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index
'(4)Add a control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)
'(5)Give the control a caption
cbcCutomMenu.Caption = "&New Menu"
'(6)Working with our new control, add a sub control and _
give it a caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With
'(6a)Add another sub control and give it a caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
'Repeat step "6a" for each menu item you want to add.
'Add another menu that will lead off to another menu
'Set a CommandBarControl variable to it
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
cbcCutomMenu.Caption = "Ne&xt Menu"
'Add a control to the sub menu just created above
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = "MyMacro2"
End With
On Error GoTo 0
End Sub
Sub DeleteMenu( )
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
End Sub



When using
the OnAction property, it is possible that you may
encounter problems if there is a macro in the user's
workbook that has the exact same name as a macro that resides in your
add-in. To play it safe, it is often a good idea to use a method like
this:



        With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = ThisWorkbook.Name & "!MyMacro2"
End With



By doing this, you ensure that Excel knows which macro you want run
when the user clicks the button. With these snippets of code,
you'll find it easy to distribute and use macros to
their fullest potential.




/ 136