 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
If you want that certain code is available in all your workbooks, then
use your PERSONAL.XLS or in Excel 2007-2016 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/10 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/10: 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
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-2016: 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-2016 :
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-2016 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-2016 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-2016: 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-2016 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.
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).
This custom function(UDF) gives you the ISO week number that is used in Europe and a lot of other countries. Note: In Excel 2010 and higher you can use the built-in WEEKNUM function(=WEEKNUM(C5,21), see the argument 21 that they add in Excel 2010. in Excel 2013 they finally add a new function named ISOWEEKNUM.
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)
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-2016 : 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-2016 : File>Options..Add-ins
In the "Manage:" dropdown (bottom of the dialog)
Choose "Disabled
Items
Go
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.
For Excel 2007-2016 you can try this add-in : Dynamic Menu for your favorite macros in Excel 2007-2016
Read also this article from Jan Karel Pieterse : http://www.jkp-ads.com/Articles/DistributeMacro00.asp