Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Personal.xls Tutorial (All)

    By request, I have combined information from several different answers I have written here and added some additional information to make a little tutorial on Personal.xls.

    Personal.xls Tutorial

    Excel provides a special workbook called Personal.xls where you can put any macros and User Defined Functions that you want to be available for all of your workbooks to use. This workbook is hidden, so that you would not normally see it in the list of open workbooks when you click on the Window menu command. When you install Excel, this workbook is not created by the installation process, so this tutorial is to explain how to get Excel to create a Personal.xls for you, how to put your macros and User Defined Functions into it, and how to use those macros and User Defined Functions.

    Personal.xls must be created in the correct directory or it will not work. It also should have some special attributes like being hidden. The best way to create your Personal.xls is to get Excel to create it for you. You do this by recording a macro and telling Excel to put it into Personal.xls. When you do this, Excel will automatically create Personal.xls if it does not already exist, put it in the correct place, and give it all of the correct attributes. You can use the following procedure to create your Personal.xls workbook:

    1- Start Excel with a new empty workbook.
    2- Click on the Tools menu.
    3- Click on the Macro command so that the submenu flies out.
    4- In the submenu click on "Record New Macro.
    5- In the Record Macro dialog box click on the arrow in the "Store macro in" box.
    6- Select "Personal Macro Workbook" from the list.
    7- Change the Macro Name if you like, but this is not necessary.
    8- Click on OK. You should get a small toolbar that says "Stop Recording" in the title bar, and that has a button that looks like the Stop button on a VCR.
    9- Click on any cell in the worksheet to select it.
    10- Click on the Stop Recording button in the small toolbar. The toolbar should disappear.

    You should now have a Personal.xls file. To see what has been done, do the following:

    1- Press Alt+F11. This should open the Visual Basic Editor.
    2- On the left side of the screen should be the project explorer that should show the workbook you are working in (probably named "Book1") and your Personal.xls workbook. The Personal.xls workbook should have a Modules collection that contains a Module1 object. Double click on the Module1 object and the macro you just recorded should be in the right window. It should looks something like this:

    <pre>Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 2/19/2002 by Legare Coleman
    '

    '
    Range("B1").Select
    End Sub
    </pre>


    3- This is a pretty useless macro, and you can now select the code by clicking and dragging and then delete it.

    If you put macros into Personal.xls, then those macros will be in the list when you go to the Tools menu, select Macro and then select Macros from the submenu. Just select the macro and click the run button. You can also assign macros in Personal.xls to buttons on your command bars. I have the following macro in my Personal.xls file assigned to a button on my tool bar to put the current date and time into the current cell and then move one cell to the right.

    <pre>Sub InsertDateTime()
    ActiveCell.Value = Now()
    ActiveCell.Offset(0, 1).Select
    End Sub
    </pre>


    If you put a User Defined Function into your Personal.xls, you can use this function in any workbook. However, when you enter the function you must also tell Excel that it is located in Personal.xls. If you have a User Defined Function named MyFunction in your Personal.xls, you could use the following in a cell to execute this function:

    <pre>=PERSONAL.XLS!MyFunction()
    </pre>

    Legare Coleman

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Personal.xls Tutorial (All)

    Legare

    Thanks, neat stuff in that message. I would also like to add that if the read-only attribute is set to the Personal.XLS workbook, some of the macro viruses that used to attach themselves to this workbook will not be able to giving us an additional level of "Security"

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Personal.xls Tutorial (All)

    Legare, in VBA how do you call a subroutine that is in Personal.xls? In other words, I have created a macro in a workbook and in this macro I want to call subroutine P which is in Personal.xls, Module1. Help says to use a fully qualified name like ProjectName.ModuleName.ProcedureName, so it must be ProjectName.Module1.P, but I cannot figure out the ProjectName for Personal.xls. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    I would use Application.Run, like this:

    <pre> Application.Run ("Personal.xls!InsertDateTime")
    </pre>

    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Everytime I open Excel it opens a "personal.xlt" file as opposed to a "personal.xls" file.
    Anyone know how to fix this?

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Legare, you are so right!.
    I use Personal.xls a lot. I store all sorts of macros that I use in other worksheets.
    For example, I like to have a uniform footer in my reports, which include the full pathname of the file, page nos and date etc.
    I have included a sample of my footer macro so others might enjoy. It is a simple macro but saves heaps of time on every new worksheet.

    Sub FormatFooter()
    '
    ' Format Footer Macro
    ' Macro recorded 21/03/2001
    '
    '
    Filpath = ActiveWorkbook.Path
    Filname = ActiveWorkbook.Name
    FullName = Filpath & "" & Filname
    With ActiveSheet.PageSetup
    .LeftFooter = "&""Arial Narrow,Regular""&9File: " & FullName
    .RightFooter = "&""Arial Narrow,Regular""&9Prepared by Ross" & Chr$(13) & "Printed on : &9&D &T"
    End With
    End Sub

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Do you have a Personal.xlt file in your XLStart directory? If so, rename it to something else, XPersonal.xlt. Follow the procedure in my original post to create a Personal.xls file. Go into the Visual Basic Editor and copy all of the macros from XPersonal.xlt and paste them into Personal.xls. Exit from Excel and remove XPersonal.xlt from XLStart.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Legare, I found your great post. after a search for Excel Utility Library. The resuts (of the search) support my contention that VBA libraries are porr;y documented in Office97/SR2.


    I am currently aware of three ways of assembling a library of utility VBA code in Excel97, and would appreciate feedback.

    I am creating several Excel97 applications, and will want to store utility procedures of VBA code, of use to me, the developer, in a central place, a library, from which I can use them at will. The library code can be released with the application(s), but I would prefer that my utility code be not visible. The application code can be visible.

    In Word97 I created a template Utils.DOT which now holds 400+ procedures and makes for rapid development of new applications in Word. I would like to have the same facilities in Excel.


    1) Personal.XLS; I can create a workbook and access its Public procedures. The name seems to have magical properties, but I'm not sure why. I find little documentation in the Help files.

    2) Utils.XLS; I create this regular workbook and can access procedures in its modules by the regular procedure CALL.

    3) Utils.XLA; I create this by saving my Utils.XLS as an XLA type of file.


    What are the relative advantages of each of the three methods?

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    The three methods you describe indeed have their own peculiarities:

    - personal.xls: Meant to be used for PERSONAL use and contains your own macros, in principle not intended for others. Personal.xls is loaded hidden and also does not prevent a blank workbook to be opened at startup. Somehow XL does not count it when deciding whether or not to load a blank workbook.
    Distributing that file will overwrite the users' own personal.xls, which might upset him/her <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    - Util.xls: IMO the best place to store code you use frequently. Easy to distribute as well, just copy to floppy.

    - Util.xla : a good way to give your utils to others so they can install/uninstall them as a normal add-in.

    Clear as mud?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Personal.xls Tutorial (All)

    (This partly overlaps with Jan Karels reply)

    The name Personal.xls is "built in" - when you record a macro and specify that it is to be stored in your Personal Macro Workbook, Excel will create Personal.xls in the XLStart folder if it doesn't exist yet. Personal.xls is hidden by default, which is convenient - there is no need to see the empty worksheet it contains.

    Personal.xls, as the name indicates, is a great place to store your personal macros. It is not a good place to store macros you want to distribute - you don't want to overwrite the Personal.xls workbook of your users by your private copy. In this way, it is comparable to Normal.dot in Word (but not in other ways - Personal.xls has nothing to do with the default formatting of Excel workbooks, it is a macro container only).

    Add-ins are more convenient for distributing code, and also for some aspects of personal use. For instance, if you create a custom function in a .xls workbook (Personal.xls or otherwise), you have to prefix the function with the workbook name:

    =Personal.xls!MyFunction(A1)

    If you put the function in an .xla add-in and load that add-in, you don't need to prefix the function with the name of the add-in:

    =MyFunction(A1)

    On the other hand, procedures (subs) in an add-in are not visible in the Tools/Macro/Macros... window, whereas procedures in a .xls file are (you can still type the name of the sub to execute it).

    One other point: add-ins are hidden by default - you don't have to worry about accidentally leaving a worksheet visible. If you temporarily unhide Personal.xls or another workbook, and then save it, it will come up visible next time.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Jan Karal and Hans seemed to have covered this subject very well. Basically, all of the methods have their uses.
    Legare Coleman

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Jan, Hans and Legare, Thank you (all) for the input.

    As a developer, I appreciate your comments about Personal.XLS. I have deleted all traces of Personal.XLS from my system while I pursue the development of procedure libraries in Excel.

    I'm trying to determine how to release a library of code with my application.

    So far the business of rendering the utility library source code invisible seems to be, as in Word, a matter of Locking the project for viewing. This is independent of the utility source code being stored in an XLS, and XLT or an XLA.


    I have essayed with each of the three forms (XLS, XLT, XLA) and so far have not found a significant difference in the effect on my application. The attached TXT file journals my experiments in detail.

    My Application consists of
    <pre>Sub TEST()
    MsgBox strDate(Now())
    End Sub
    </pre>

    where the Public Function strDate resides in my utility library.


    I had anticipated that having the Utilities in the Startup folder (as an XLS or XLT or XLA) would remove the need for me adding a reference from my Application workbook. This appears not to be so.


    So I'm still confused as to the differences between the three forms, and hence the rationale for choosing any one over the other.
    Attached Files Attached Files

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Here is what I do:

    - have a library of modules that contain certain subs and or functions which are related (be it text file or in a single or more Excel workbooks, makes no real difference)
    - copy a module into a project that needs it's functionality.

    I have e.g. a module named WindowsTempDirect that contains these two functions (and their declarations):

    Option Explicit
    '************************************************* *
    '* *
    '* All subs and functions in this module: *
    '* Copyright
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    > Add-ins are more convenient for distributing code, and also for some aspects of personal use. For instance, if you create a custom function in a .xls workbook (Personal.xls or otherwise), you have to prefix the function with the workbook name: =Personal.xls!MyFunction(A1)


    Hans, I'm not sure that I understand this.

    I have created a Utils.XLS (project Ut) with a module "U" in my XLStart folder and closed that workbook. I have created an Application.XLS with a User module in which I have set the Reference to Utils.XLS and in the Application.XLS I have a macro:
    <pre>Sub TEST()
    MsgBox Ut.U.strDate(Now())
    MsgBox U.strDate(Now())
    MsgBox strDate(Now())
    End Sub
    </pre>


    The macro TEST runs quite happily from Excel (Tools, Macro, Macros, ...) and all three formats of calling the public procedure in Utils.XLS return a result. Excepting if I had duplicate procedure names and had to differentiate between them, I've not needed to write the file name ("utils.XLS") explicitly.


    Undaunted, I load the Utils.XLS, save it as an XLA in the XLStartup folder, then delete the Utils.XLS. I exit and reload Excel, load my Application.XLS, reset the references to point to Utils.XLA, and everything functions as before - I can run TEST as a macro and I don't need the explicit file reference.

    At this point I see no difference between an XLS and an XLA as a library of source code.

    Undaunted, I load the Utils.XLS (not deleted but shunted off to a floppy), save it as an XLT in the XLStartup folder, then delete the Utils.XLS. I exit and reload Excel, load my Application.XLS, reset the references to point to Utils.XLT, and everything functions as before - I can run TEST as a macro and I don't need the explicit file reference.

    At this point I see no difference between an XLS,an XLT and an XLA as a library of source code.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls Tutorial (All)

    Jan Karel , thanks for this. Please see my immediately preceding reply to Hans. There is much that i do not understand.


    >If you want to call a function in another workbook (project) you always need either a reference to that project or you need to use the Call statement, explicitly referencing the workbook and sub/function name.

    This probably explainspart of my dilemma - why I haven't needed to use an absolute file reference; it is because I am setting a Reference from my Application.XLS to the Utility XLS/XLT/XLA.


    That said, why would I elect to maintain my libary as Utils.XLA or Utils.XLT instead of keeping it as plain old Utils.XLS? So far I can't see any distinction between the three methods.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •