excel 2002 formulas ebook

Podstrony

[ Pobierz całość w formacie PDF ]
.Assume that you have a custom function named ZAPSPACES and that it s storedin Myfuncs.xls.To use this function in a formula in a workbook other thanMyfuncs.xls, you need to enter the following formula:=Myfuncs.xls!ZAPSPACES(A1:C12)If you create an add-in from Myfuncs.xls and the add-in is loaded, you can omitthe file reference and enter a formula like the following:=ZAPSPACES(A1:C12)4800-x Ch23.F 8/27/01 11:59 AM Page 617Chapter 23: Function Procedure Basics 617Creating an add-in from a workbook is simple.The following steps describe howto create an add-in from a normal workbook file:1.Develop your functions, and make sure that they work properly.2.Activate the VB Editor and select the workbook in the Project window.Choose Tools xxx Properties and click the Protection tab.Select the LockProject for Viewing checkbox and enter a password (twice).Click OK.You only need to do this step if you want to prevent others from viewingor modifying your macros or custom dialog boxes.3.Reactivate Excel.Choose File Properties, click the Summary tab, andenter a brief, descriptive title in the Title field and a longer description inthe Comments field.This step is not required, but it makes the add-in easier to use by display-ing descriptive text in the Add-Ins dialog box.4.Select File Save As.5.In the Save As dialog box, select Microsoft Excel add-in (*.xla) from theSave As Type drop-down list (see Figure 23-10).6.If you don t want to use the default directory, select a different directory.7.Click Save.A copy of the workbook is saved (with an.xla extension), andthe original XLS workbook remains open.Figure 23-10: Saving a workbook as an add-in4800-x Ch23.F 8/27/01 11:59 AM Page 618618 Part VI: Developing Custom Worksheet FunctionsA Few Words about SecurityMicrosoft has never promoted Excel as a product that creates applications with securesource code.The password feature provided in Excel is sufficient to prevent casualusers from accessing parts of your application that you want to keep hidden.But, thetruth is, several password-cracking utilities are available.The security features in Excel2002 are much better than those in previous versions, but it s possible that these canalso be cracked.If you must absolutely be sure that no one ever sees your code orformulas, Excel is not your best choice as a development platform.A workbook that you convert to an add-in must have at least one worksheet.For example, if your workbook contains only chart sheets or Excel 5/95 dia-log sheets, the Microsoft Excel add-in (*.xla) option does not appear in theSave As dialog box.With previous versions of Excel (before Excel 97), to modify an add-in, youhad to open the original XLS file, make your changes, and then recreate theadd-in.For Excel 97 and later versions, this is no longer necessary.As long asthe add-in is not protected, you can make changes to the add-in in the VBEditor, and then save your changes.If the add-in is protected, you must enterthe password to unprotect it.Therefore, with Excel 97 or later, keeping anXLS version of your add-in is not necessary.After you create your add-in, you can install it by using the standard procedure:Select Tools Add-Ins, and click the Browse button in the Add-Ins dialog box.Then, locate your *.xla file.SummaryThis chapter covered some essential details to help you develop effective customfunctions.I discussed the type of arguments that you can use, and I described howto make your function appear in a specific category in the Paste Function dialogbox.This chapter also presented some techniques to help debug functions andended with instructions for creating an add-in to hold your functions.The next chapter discusses VBA programming concepts.4800-x Ch24 [ Pobierz całość w formacie PDF ]

zanotowane.pl

doc.pisz.pl

pdf.pisz.pl

bezglutenowo.pev.pl