របៀបបង្កើត និងប្រើប្រាស ឯកសារផ្ទាល់ខ្លួនជាមួយកូដ VBA

Information

If you want that certain code is available in all your workbooks, then use your PERSONAL.XLS or in Excel 2007-2013 your PERSONAL.XLSB file.

What is it:
This is a hidden workbook that opens when you start Excel.
The code you copy in this workbook is available in all workbooks that are open in Excel.

Where is it:
If it exists, you can find the file in the Excel startup folder.
Windows XP
C:\Documents and Settings\Ron\Application Data\Microsoft\Excel\XLSTART
In Vista or Windows 7/8 look here
C:\Users\Ron\AppData\Roaming\Microsoft\Excel\XLSTART
Note: Ron is the username in the path above

With code you find the correct path on your machine with this line

1) Open excel
2) Hit alt-F11 to get to the VBE
3) Hit ctrl-g to see the immediate window and type this:
4) ?Application.StartupPath
5) Press enter

You’ll see the startup path returned

Note: Application Data and AppData are hidden folders. To show them follow the steps below

Open Windows Explorer
Vista and Windows 7/8: Click on Organize and then on Folder and search options
Windows Xp : Click on Tools and then on Folder Options
Then on all systems on the View tab select show hidden files and folders

How do you create the file if it does not exist:

If it does not exist then record a dummy macro and change the "Store macro in:" drop down to Personal Macro Workbook. Excel creates the file for you then in the correct folder.

Excel 97-2003: Tools>Macro…Record New Macro.

Excel 2007-2010: On the “Developer” tab click on Record macro.

To display the Developer tab in Excel 2007 go to Office Button >Excel Options...Popular
In Excel 2010-2013 : File>Options..Customize Ribbon, check Developer in the Main Tabs list.

Or faster click the button on the bottom left corner of the Excel 2007-2013 window to start the recording.

Press OK in the dialog that you see now

Because we only want Excel to create the file for us we can stop the recording directly.
Press the Stop Recording button or use Tools>Macros…Stop recording in Excel 97-2003

In Excel 2007-2013 you can press the Stop Recording Button on the Developers tab or click the
button on the bottom left corner of the Excel window to stop the recording.

Now we use Alt F11 to go to the VBE editor(working in all Excel versions) Or:
Excel 97-2003: Tools>Macro>Visual Basic Editor.
Excel 2007-2013: Click on the button named “Visual Basic” on the “Developer” tab
Note: I use the name PERSONAL.XLS in the screenshots below but in Excel 2007-2013 the name is PERSONAL.XLSB. To be sure that we see the Project Window, Code window and the Properties window we use one time.

Ctrl-r (to see the project explorer)
F7 (to view the code window)
F4 (to view the properties window)

Now we give the PERSONAL.XLS(b) a unique project name (Default is VBAProject)
Select the PERSONAL.XLS(b) in the project window and change the name in the properties window.

Now use Ctrl-s or File>Save in the VBA editor to save the PERSONAL file

We are now ready to add code (macros or/and functions) to this file so we can use it in all workbooks.
With Insert>Module in the VBA editor you can also add more modules to the PERSONAL.XLS. This way you can organize your code in this file. Use only code here that you use yourself and not code that you use in files that you distribute to other users. In this case add the code in the modules of the workbook itself. Or distribute your VBA macros as a Add-in.

Example 1 : Macro

Public Sub Test()
    MsgBox "Insert your favorite code here"
End Sub

1) Select the PERSONAL.XLS(b) in the project window and click on the + before it
2) Then click on the + before Modules
3) Double click on Module1 to open the code window on the right
4) We copy/paste the example macro in this module.
5) Now use Ctrl-s or File>Save in the VBA editor to save the PERSONAL.XLS(b).
6) We use Alt–q to go back to Excel or use "File >Close and Return to Microsoft Excel".
7) You can run the macro then with Alt-F8 or Tools>Macro…Macros / Developer tab>Macros

Note: You can delete the dummy macro that we used to create the PERSONAL.XLS(b).

Example 2: Function

This function(UDF) gives you the ISO week number that is used in Europe and a lot of other countries.
Note: This function is only available in Excel 2010 and higher (=WEEKNUM(C5,21), see the argument 21.

Public Function IsoWeekNum(d1 As Date) As Integer
' Daniel Maher
    Dim d2 As Long
    d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
    IsoWeekNum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function

1) Select the PERSONAL.XLS(b) in the project window and click on the + before it
2) Then click on the + before Modules
3) Double click on Module1 to open the code window on the right
4) We copy/paste the example function in this module.
5) Now use Ctrl-s or File>Save in the VBA editor to save the PERSONAL.XLS(b).
6) We use Alt–q to go back to Excel or use "File >Close and Return to Microsoft Excel".

Now we can use this in a worksheet cell with the date in A1

=PERSONAL.XLS!IsoWeekNum(A1)

If you create a reference in the workbook where you want to use it to your
PERSONAL.XLS(b) then you can use

=IsoWeekNum(A1)

Go to Tools>References in the VBA editor with the workbook selected in the project window and
add a check mark before RonPersonal. (Save the workbook then)

Problems with the PERSONAL.XLS(B) workbook

 What to do if your PERSONAL.XLS(b) is visible

When you open Excel and you see PERSONAL.XLS(B) in the title bar you can use this to hide it.

Excel 97-2003 : Window>Hide
Excel 2007-2010 : On the View tab in the window group choose Hide

Then close Excel and say Yes to save the changes to your PERSONAL file.

PERSONAL.XLS(b) will not open but is in the correct location
When Excel think the file is corrupt it can disable the file.
You can re-enable it here but if it is really corrupted replace it with a backup.

Excel 2002-2003 : Help>About MS Excel>Disabled items
Excel 2007 : Office button>Excel Options..Add-ins
In the "Manage:" dropdown (bottom of the dialog)
Choose "Disabled Items
Go
Excel 2010-2013 : File>Options..Add-ins
In the "Manage:" dropdown (bottom of the dialog)
Choose "Disabled Items
Go

Use a Add-in

You can also create an add-in to store code that you and others want to use in all workbooks.
This is a good way if you want to distribute code to other users.
Read this article from Jan Karel Pieterse

http://www.jkp-ads.com/Articles/DistributeMacro00.asp

Posted in: Excel,អត្ថបទ