Excel Articles at JKP Application Development Services http://www.jkp-ads.com/Articles/ExcelPages.htm Jan Karel Pieterse My free Flexfind tool for Excel has been updated to build 560. I've fixed a bug in searching and replacing in chart series. http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp Mon, 11 Aug 2008 18:45:00 GMT jkp-ads085 Jan Karel Pieterse Excel has a host of built-in functions to help you do your computations. But sometimes you need to write your own functions using VBA. These functions are called "User Defined Functions" (UDF). UDF's typically are placed in addins. As long as the addin is installed, the UDF's work as expected. You get into trouble when the location of the addin changes, for example because you have distributed the Addin to your co-workers and they have installed it to their local user addin folder (which is different for each user!). As soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors. Also, the complete path to the original location of the add-in is shown in each formula: ='C:\data\FixLink2UDF.xla'!UDFDemo(A1,A2). This article describes a couple of ways to avoid/resolve this situation. http://www.jkp-ads.com/articles/FixLinks2UDF.asp Mon, 02 Jun 2008 18:15:00 GMT jkp-ads084 Jan Karel Pieterse For a project I needed a quick way to display the content of an array to the user. I didn't want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. So I figured I'd put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown. That proved far from easy... http://www.jkp-ads.com/articles/AutoSizeListbox.asp Wed, 14 May 2008 18:15:00 GMT jkp-ads083 Jan Karel Pieterse Updated Name Manager: Fixed a bug: when hidden name is edited, it used to become unhidden. When one adds names and the filter is set to show hidden names, new names are hidden by default. http://www.jkp-ads.com/officemarketplacenm-en.asp Tue, 13 May 2008 18:45:00 GMT jkp-ads082 Jan Karel Pieterse Updated Name Manager: Fixed a bug in the used names filter avoiding false positives on names which resemble a column reference. http://www.jkp-ads.com/officemarketplacenm-en.asp Tue, 13 May 2008 08:00:00 GMT jkp-ads081 Jan Karel Pieterse Updated SetupUtility: Accounted for the fact the Application.UserLibraryPath does contain a trailing pathseparator, unlike any other path in Excel's object model. http://www.jkp-ads.com/download.asp#SetupUtility Tue, 06 May 2008 10:55:00 GMT jkp-ads080 Jan Karel Pieterse Updated Name Manager: Fixed a bug in the Areas filter. http://www.jkp-ads.com/officemarketplacenm-en.asp Wed, 23 Apr 2008 15:45:00 GMT jkp-ads079 Jan Karel Pieterse My free Flexfind tool for Excel has been updated to version 5.3, build 558. I've improved the UI and the selecting of found items. http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp Fri, 28 Mar 2008 13:45:00 GMT jkp-ads078 Jan Karel Pieterse Updated Name Manager: I've Updated the unused names filter to include objects and VBA and fixed a bug affecting Excel 2000 users only. http://www.jkp-ads.com/officemarketplacenm-en.asp Fri, 28 Mar 2008 13:45:00 GMT jkp-ads077 Jan Karel Pieterse RefTreeAnalyser has been updated with hotkeys and improved reporting. Do you have a license? then get this free download to update!. Ever had to work out the logic of other people's Excel files? Ever had to untie the spaghetti-knots of a large Excel workbook's formulas? Then you know what a nightmare this can be! Now there is the RefTreeAnalyser! With this tool, finding out how a cell in a workbook derives its results and what other cells depend on the cell is a breeze. http://www.jkp-ads.com/RefTreeAnalyser.asp Mon, 10 Mar 2008 08:10:00 GMT jkp-ads076 Jan Karel Pieterse Updated Name Manager: Fixed a bug regarding editing a name in R1C1 mode and changed the way the create name dialog works. http://www.jkp-ads.com/officemarketplacenm-en.asp Wed, 13 Feb 2008 18:15:00 GMT jkp-ads075 Jan Karel Pieterse RefTreeAnalyser has been updated with a help file and is now accessible through the cell rightclick menu. Do you have a license? then get this free download to update!. Ever had to work out the logic of other people's Excel files? Ever had to untie the spaghetti-knots of a large Excel workbook's formulas? Then you know what a nightmare this can be! Now there is the RefTreeAnalyser! With this tool, finding out how a cell in a workbook derives its results and what other cells depend on the cell is a breeze. http://www.jkp-ads.com/RefTreeAnalyser.asp Wed, 13 Feb 2008 06:10:00 GMT jkp-ads074 Jan Karel Pieterse Updated Name Manager: Removed a control on a form that caused compile errors for some users. http://www.jkp-ads.com/officemarketplacenm-en.asp Fri, 18 Jan 2008 19:25:00 GMT jkp-ads073 Jan Karel Pieterse You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated. What to do? The only way I find reliable is to catch all possible paste operations. But this isn't very easy, since there are a zilion ways to paste... http://www.jkp-ads.com/Articles/CatchPaste.asp Mon, 17 Dec 2007 18:10:00 GMT jkp-ads072 Jan Karel Pieterse Now in one convenient place: the most recent references to articles from what I think are the most important Excel Blogs in the world. http://www.jkp-ads.com/ExcelHeadlines.asp Fri, 07 Dec 2007 18:10:00 GMT jkp-ads071 Jan Karel Pieterse Updated Name Manager to add a tiny but very useful toolbar to the VBE, which enables you to select from available range names and paste into your code. http://www.jkp-ads.com/officemarketplacenm-en.asp Thu, 06 Dec 2007 18:00:00 GMT jkp-ads070 Jan Karel Pieterse De Name Manager is uitgebreid met een o zo handig toolbartje waarmee je in de Visual Basic Editor een bereiknaam kunt selecteren in in je code laten plakken. http://www.jkp-ads.com/officemarketplacenm-nl.asp Thu, 06 Dec 2007 18:00:00 GMT jkp-ads069 Jan Karel Pieterse Ever had to work out the logic of other people's Excel files? Ever had to untie the spaghetti-knots of a large Excel workbook's formulas? Then you know what a nightmare this can be! Now there is the RefTreeAnalyser! With this tool, finding out how a cell in a workbook derives its results and what other cells depend on the cell is a breeze. http://www.jkp-ads.com/RefTreeAnalyser.asp Mon, 03 Dec 2007 10:55:00 GMT jkp-ads068 Jan Karel Pieterse Ooit de logica achter een Excel bestand van iemand anders moeten uitzoeken? Ooit de spaghetti berg aan formule verwijzingen moeten ontwarren? Dan weet u wat een nachtmerrie dat kan zijn.! Nu is er de RefTreeAnalyser! Met deze tool is het uiterst eenvoudig om erachter te komen hoe een cel aan haar informatie komt en welke andere cellen hun waarde aan deze cel ontlenen. http://www.jkp-ads.com/RefTreeAnalyserNL.asp Mon, 03 Dec 2007 10:55:00 GMT jkp-ads067 Jan Karel Pieterse I just learnt that using a table from workbook 1 in workbook 2 requires a special syntax. I have updated the article so it includes this information. http://www.jkp-ads.com/articles/Excel2007Tables.asp Wed, 14 Nov 2007 07:35:00 GMT jkp-ads066 Jan Karel Pieterse My free Flexfind tool for Excel has been updated to build 552. Fixed problems with using wildcard search and replace. http://www.jkp-ads.com/download.asp Mon, 08 Oct 2007 14:15:00 GMT jkp-ads065 Jan Karel Pieterse Dit artikel is onlangs (in het Nederlands) geplaatst op de website "Microsoft voor ondernemers". De link naar het artikel op de Microsoft site vindt u op bij het artikel op mijn site. http://www.jkp-ads.com/articles/Excel2007Tables.asp Fri, 28 Sep 2007 10:37:00 GMT jkp-ads064 Jan Karel Pieterse This article of mine was recently published (in Dutch of course) on the Dutch Microsoft website. The link to the MSFT site is mentioned at the top of the article. http://www.jkp-ads.com/articles/Excel2007Tables.asp Fri, 28 Sep 2007 10:37:00 GMT jkp-ads063 Jan Karel Pieterse Updated Name Manager to fix a small bug. Now pivot table source ranges are updated when renaming a name. http://www.jkp-ads.com/officemarketplacenm-en.asp Tue, 18 Sep 2007 07:00:00 GMT jkp-ads062 Jan Karel Pieterse Name Manager is bijgewerkt naar build 588 waarmee een kleine bug is gefixt. Nu worden de bronbereiken van draaitabellen ook aangepast bij het hernoemen van een bereiknaam. http://www.jkp-ads.com/officemarketplacenm-nl.asp Tue, 18 Sep 2007 07:00:00 GMT jkp-ads061 Jan Karel Pieterse This utility enables you to copy the components from the VBAProject of workbook A to Workbook B. Updated to build 005: Fixed not copying class modules. http://www.jkp-ads.com/download.asp Tue, 11 Sep 2007 15:45:00 GMT jkp-ads060 Jan Karel Pieterse My free Flexfind tool for Excel has been updated to build 551. Fixed some problems with searching in protected worksheets. http://www.jkp-ads.com/download.asp Wed, 08 Aug 2007 17:30:00 GMT jkp-ads059 Jan Karel Pieterse This utility enables you to copy the components from the VBAProject of workbook A to Workbook B. Updated to build 004: Existing modules are overwritten in stead of added. http://www.jkp-ads.com/download.asp Mon, 06 Aug 2007 17:05:00 GMT jkp-ads058 Jan Karel Pieterse My free AutosafeVBE for Excel has been updated to build 026. Important fixes for the Excel version are included, so users are recommended to download. http://www.jkp-ads.com/download.asp Mon, 06 Aug 2007 07:35:00 GMT jkp-ads057 Jan Karel Pieterse On this page I explain how you can change properties of form controls on worksheets by editing the xml inside an Excel file's internals directly. http://www.jkp-ads.com/articles/Excel2007FileFormat01.asp Fri, 08 Jun 2007 18:25:00 GMT jkp-ads056 Jan Karel Pieterse This utility enables you to copy the components from the VBAProject of workbook A to Workbook B. http://www.jkp-ads.com/download.asp Fri, 01 Jun 2007 18:25:00 GMT jkp-ads055 Jan Karel Pieterse My free Flexfind tool for Excel has been updated to build 550. Important fix: When replacing text in Values, previous builds would also replace in cells with a formula. Build 550 fixes that issue. http://www.jkp-ads.com/download.asp Thu, 31 May 2007 09:15:00 GMT jkp-ads054 Jan Karel Pieterse On this new page I'll explain how you can read and write to a cell on a worksheet by editing the xml inside an Excel file's internals directly. http://www.jkp-ads.com/articles/Excel2007FileFormat.asp Thu, 17 May 2007 15:15:00 GMT jkp-ads053 Jan Karel Pieterse If you do some VBA programming regularly, you'll be acquainted with the Object browser, which neatly shows you all objects, properties and methods belonging to any object in Excel (or any other library you have referenced in your VBA project). Whilst this is a tremendous tool to look for information on just a couple of properties I find it not very handy when you e.g. need to get all of an objects properties into your VBA code. So because I like a challenge I decided to build my own object browser, which is shown to you on this new page. http://www.jkp-ads.com/articles/ObjectLister.asp Mon, 14 May 2007 14:00:00 GMT jkp-ads052 Jan Karel Pieterse In "Working with Tables in Excel 2007" I promised to add a page about working with those tables in VBA too. Well, here you go. http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp Fri, 27 Apr 2007 18:45:00 GMT jkp-ads051 Jan Karel Pieterse I have updated this article because I got some reports on problems with Excel 2007. http://www.jkp-ads.com/articles/updateanaddin.asp Mon, 23 Apr 2007 10:20:00 GMT jkp-ads050 Jan Karel Pieterse Sometimes I want to automate the editing of some parts of my VBA code. One thing I wantd to do lately, was search for mutliple of lines of codes (and of course replace them with new lines). I've now released a tool that enables you to do just that! http://www.jkp-ads.com/download.asp#ExcelVBEMultilineSR Thu, 12 Apr 2007 15:30:00 GMT jkp-ads049 Jan Karel Pieterse Since I revised an Excel VBA book, I decided it was time to add a page on books on Excel to my website. The page only lists this one book for now, but I intend to add more in the coming weeks. http://www.jkp-ads.com/books.asp Tue, 03 Apr 2007 08:30:00 GMT jkp-ads048 Jan Karel Pieterse This article first shows how Excel manages addins in the registry. Then it shows scripts how to do this during a setup routine using the program called Setup Factory. Being an MVP has some benefits. One of them is that some software companies offer Not For Resale (NFR) products to them for free. Of course these companies hope the MVP's will advocate their products. Well, for this one I will do so: Setup Factory. I've started using this tool just a week ago and I must say I'm most impressed with it. http://www.jkp-ads.com/articles/AddinsAndSetupFactory.asp Wed, 21 Mar 2007 09:10:00 GMT jkp-ads047 Jan Karel Pieterse I've updated Autosafe to include the 'updating of an addin' method I described earlier. http://www.jkp-ads.com/download.asp#autosafe Tue, 20 Mar 2007 13:10:00 GMT jkp-ads046 Jan Karel Pieterse If you have created a nice add-in, an updating mechanism is an easy way to ensure your users get any bugfixes you did automatically . This article demonstrates how you can implement this functionality in your addin. http://www.jkp-ads.com/articles/updateanaddin.asp Mon, 19 Feb 2007 09:10:00 GMT jkp-ads045 Jan Karel Pieterse I've added some information on troubleshooting startup trouble caused by COM addins. http://www.jkp-ads.com/articles/startupproblems.asp Fri, 09 Feb 2007 17:40:00 GMT jkp-ads044 Jan Karel Pieterse Toegevoegd is een aantal tips om problemen veroorzaakt door COM invoegtoepassingen te onderzoeken. http://www.jkp-ads.com/articles/startupproblemsnl.htm Fri, 09 Feb 2007 17:40:00 GMT jkp-ads043 Jan Karel Pieterse Autosafe is now freeware. Autosafe backs up your work in Excel. When you restart Excel after a crash, you're offered to restore backed up copies of your files. You can also restore previous backup copies from your recycle bin to revert back to an older version of your work. This new version of Autosafe will work with long path+filenames (previous versions didn't). http://www.jkp-ads.com/download.asp Thu, 01 Feb 2007 09:31:00 GMT jkp-ads042 Jan Karel Pieterse With the release of Office 2007, everything has changed regarding user interface programming in Excel. I've updated my set of articles on distributing macros today, with special sections on Excel 2007 where appropriate. http://www.jkp-ads.com/articles/distributemacro00.htm Fri, 26 Jan 2007 11:50:00 GMT jkp-ads041 Jan Karel Pieterse Build 580 of Name Manager fixes a rarely occuring problem which prevented it from loading, showing an "Invalid Picture" error. http://www.jkp-ads.com/officemarketplacenm-en.asp Thu, 18 Jan 2007 08:10:00 GMT jkp-ads040 Jan Karel Pieterse With the release of Excel 2007, Microsoft has introduced a new concept of working with tables of data. This new functionality is (not surprisingly) called "Data Tables". This article introduces you into the concepts of working with Data Tables and shows you how they may help you in your everyday Excel use. http://www.jkp-ads.com/articles/Excel2007tables.asp Fri, 12 Jan 2007 13:30:00 GMT jkp-ads039 Jan Karel Pieterse I've added an Excel 2007 version of Flexfind. Only change: inserts a control on the ribbon for easy access. http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp Tue, 19 Dec 2006 08:40:00 GMT jkp-ads038 Jan Karel Pieterse Als je net zoals ik een VBA programmeur bent, dan vindt je het wellicht ook belangrijk hoe de verschillende vensters van de Visual Basic Editor zijn geplaatst. Soms maakt de VBE er een rommeltje van en het kan op zijn zachtst gezegd een uitdaging zijn om ze weer allemaal op hun plekje te krijgen. In dit artikeltje laat ik je zien hoe je dat kunt doen. http://www.jkp-ads.com/articles/VBEDockingNL.asp Fri, 15 Dec 2006 15:45:00 GMT jkp-ads037 Jan Karel Pieterse If you're a VBA programmer like me, you're probably picky about your window arrangements in the Visual Basic Editor. Sometimes it messes things up and it can be a challenge to get those windows where you want them. Here are some tips to get things back in order. http://www.jkp-ads.com/articles/VBEDocking.asp Wed, 06 Dec 2006 18:00:00 GMT jkp-ads036 Jan Karel Pieterse Build 577 of Name Manager fixes a small bug related to renaming local names. http://www.jkp-ads.com/officemarketplacenm-en.asp Wed, 29 Nov 2006 15:45:00 GMT jkp-ads035 Jan Karel Pieterse I have uploaded an Excel 2007 RTM version of Build 576 of Name Manager today. http://www.jkp-ads.com/officemarketplacenm-en.asp Fri, 24 Nov 2006 10:15:00 GMT jkp-ads034 Jan Karel Pieterse Build 576 of Name Manager fixes a small bug related to use of Name Manager in an Office MUI language situation. Added functionality: delete names by pressing the del key. http://www.jkp-ads.com/officemarketplacenm-en.asp Wed, 15 Nov 2006 14:00:00 GMT jkp-ads033 Jan Karel Pieterse Build 575 of Name Manager fixes a small bug related to renaming a name which is not in use. http://www.jkp-ads.com/officemarketplacenm-en.asp Sat, 04 Nov 2006 15:04:00 GMT jkp-ads032 Jan Karel Pieterse This new version of Autosafe will work with long path+filenames (previous versions didn't). http://www.jkp-ads.com/download.asp Fri, 27 Oct 2006 12:15:00 GMT jkp-ads031 Jan Karel Pieterse Deze versie maakt gebruik van Autosafe mogelijk in situaties met lange bestands en padnamen. http://www.jkp-ads.com/downloadNL.asp Fri, 27 Oct 2006 12:15:00 GMT jkp-ads030 Jan Karel Pieterse For those of you who are using and testing Excel 2007 (Beta 2), Name Manager for Excel 2007 is available on this page. http://www.jkp-ads.com/officemarketplacenm-en.asp Mon, 18 Sep 2006 10:40:00 GMT jkp-ads029 Jan Karel Pieterse Recently someone asked me if it was possible to transpose a table of cells, but in such a way that the formula of each cell is kept intact. I decided VBA was the way to go and produced a small but very useful little routine for that. http://www.jkp-ads.com/articles/formulatranspose.asp Fri, 15 Sep 2006 18:00:00 GMT jkp-ads028 Jan Karel Pieterse Recentelijk vroeg mij iemand of het mogelijk is om een tabel met cellen te transponeren, zodanig dat de formule van elke cel intact blijft. Ik besloot dat VBA de aangewezen weg was om dit te bereiken en heb een kleine maar handige subroutine gemaakt die dat doet. http://www.jkp-ads.com/articles/formulatransposenl.asp Fri, 15 Sep 2006 18:00:00 GMT jkp-ads027 Jan Karel Pieterse New features: 1. Rename a name. Name Manager now includes a rename option, which will enable you to change the name of a name and have all objects that use that name update too. 2. View where name is in use. A treeview shows all locations where a name is used, including VBA code. http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp Mon, 12 Jun 2006 18:40:00 GMT jkp-ads026 Jan Karel Pieterse Nieuwe mogelijkheden: 1. Hernoem een naam. Name Manager geeft u nu de mogelijkheid om een naam te hernoemen. Hierbij worden alle formules die gebruik maken van die naam ook bijgewerkt, evenals mogelijke objecten (grafieken, besturingselementen e.d.); 2. Toon waar een naam precies in gebruik isinclusief in uw VBA code. http://www.jkp-ads.com/OfficeMarketPlacenm-nl.asp Mon, 12 Jun 2006 18:40:00 GMT jkp-ads025 Jan Karel Pieterse De Application.InputBox functie is zeer handig om een bereik door de gebruiker te laten selecteren, waarmee vervolgens in de VBA code verder gewerkt kan worden. Helaas bevat deze functie een bug (all huidige Excel versies hebben deze bug). Wanneer er op het werkblad waarop een bereik wordt geselecteerd voorwaardelijke opmaak is ingesteld waarbij bovendien gebruik is gemaakt van de optie "formule is", dan kan de functie een lege string als resultaat teruggeven. http://www.jkp-ads.com/articles/SelectARangeNL.asp Tue, 30 May 2006 17:30:00 GMT jkp-ads024 Jan Karel Pieterse NavigatorXL ends the spreadsheet chaos and smoothens the path for really value-added business intelligence solutions. With NavigatorXL you can: Distribute updated spreadsheets throughout your business organization. Secure your spreadsheets so department managers are only allowed to see their department's data. Combine multiple data sources into one spreadsheet. Combine local spreadsheet with updated budgets with actual data from your corporate system. Collect updated forecast figures entered in a sales pipeline spreadsheet. Create an Excel form (expense report, timesheet...) and collect and share the data centrally. http://www.jkp-ads.com/navigatorxl.asp Sun, 21 May 2006 14:50:00 GMT jkp-ads023 Jan Karel Pieterse If you have created a nice add-in, a setup utility is an easy way for your users to install your addin. This free utility just needs you to change 4 cells to roll your own setup file. Update: Now automatically removes invalid addins from addin list (courtesy: Richard Reye). http://www.jkp-ads.com/download.asp Thu, 11 May 2006 08:45:00 GMT jkp-ads022 Jan Karel Pieterse The Application.InputBox function is very useful to get a range from the user. Unfortunately, this function exposes a bug in Excel (all current versions!). If the sheet on which a (range of) cell(s) is selected contains conditional formatting using the : "Formula Is" option, the function may fail, returning an empty range.The only reliable workaround is to build a userform to request the range from the user, which I have included as a download. http://www.jkp-ads.com/articles/SelectARange.asp Thu, 04 May 2006 10:30:00 GMT jkp-ads021 Jan Karel Pieterse I have fixed a couple of bugs in Beta 4 of the Name Manager (build 554). http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp Thu, 04 May 2006 10:20:00 GMT jkp-ads020 Jan Karel Pieterse I have fixed a couple of bugs in Beta 4 of the Name Manager (build 550) and added a feature: show a list of all places where a name is in use. http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp Fri, 28 Apr 2006 10:45:00 GMT jkp-ads019 Jan Karel Pieterse New feature: Rename a name. Name Manager now includes a rename option, which will enable you to change the name of a name and have all objects that use that name update too. This new feature has yet to be tested thoroughly though, so that is why this version is still in Beta stage. Test drive version 4 and report any problems to me. http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp Tue, 11 Apr 2006 09:00:00 GMT jkp-ads018 Jan Karel Pieterse Microsoft Office 2003 Professional is de eerste Office versie die de XML standaard echt serieus neemt. De XML standaard is ingesteld om het markeren van gegevens (met name op het web) te vergemakkelijken. De standaard vindt steeds meer toepassing. Een bekend voorbeeld zijn RSS feeds waarmee men nieuws kan vergaren van internet pagina's. Deze zogeheten RSS-feeds zijn niets anders dan XML bestandjes. Dit artikel geeft een inleiding in wat XML is en wat er (met name in Microsoft Excel) mee gedaan kan worden. http://www.jkp-ads.com/Articles/XMLAndExcel00NL.asp Wed, 05 Apr 2006 07:00:00 GMT jkp-ads017 Jan Karel Pieterse In Microsoft Word kan men wijzigingen die door VBA code in een document worden aangebracht standaard ongedaan maken. In Excel is dit helaas niet het geval. Vrijwel alle macro commando's legen de Undo "stack" en de acties zelf kunnen niet meer ongedaan worden gemaakt. Om dit toch mogelijk te maken, moet men code schrijven die: De vorige staat van alles dat gewijzigd gaat worden opslaat; Een routine instelt die uitgevoerd moet worden wanneer de gebruiker voor "ongedaan maken" kiest; Die routine programmeren, die de wijzigingen die door de code zijn uitgevoerd weer ongedaan maakt, in omgekeerde volgorde. In dit artikel wordt gedemonstreerd hoe twee klasse modules kunnen worden ingezet om een (min of meer) generieke "undo handler" te bouwen, welke in elk Excel VBA project kan worden ingezet. http://www.jkp-ads.com/Articles/UndoWithVBA00NL.asp Tue, 04 Apr 2006 08:20:00 GMT jkp-ads016 Jan Karel Pieterse A heads-up to all VBA developers. I've just updated AutosafeVBE, the utility that backups your VBA projects. Now handles up to 9999 generations! http://www.jkp-ads.com/download.asp Thu, 23 Mar 2006 18:40:00 GMT jkp-ads015 Jan Karel Pieterse Unlike Word, changes made to an Excel workbook by using VBA cannot be undone easily. One has to write code that: 1. Stores the previous state of anything that (possibly) needs to be undone. 2. Sets a sub to be run when the user selects undo. 3. Write that subroutine, which reverses the changes last made by your code. In this article I will demonstrate how one can use two class modules to create a (more or less) generic undo handler which one can plug into any project. http://www.jkp-ads.com/Articles/UndoWithVBA00.asp Thu, 09 Mar 2006 06:50:00 GMT jkp-ads014 Jan Karel Pieterse I have recently added a database to my site. Now I am learning classic ASP and VBScript (I know, why not .NET?) to use this database and put some interactivity on my site. Take a look! Some articles now have a set of stars at their final page with which you can rate them. http://www.jkp-ads.com/index.asp Fri, 24 Feb 2006 19:10:00 GMT jkp-ads013 Jan Karel Pieterse When one writes a User Defined Function in Excel VBA, this function appears in the function wizard under the category "Custom". It is possible to change the function category by changing the "macro properties" of the UDF. Unfortunately, VBA does not allow specifying descriptions for the UDF's arguments. One can do this using the old XLM REGISTER function in conjunction with the ExecuteExcel4Macro function from within VBA. This function only accepts 255 characters or less, insufficient for UDF's with more than just one or two arguments. This article describes how this can be overcome, using an ancient XLM macro sheet together with some trickery invented by Laurent Longre. http://www.jkp-ads.com/Articles/RegisterUDF00.htm Fri, 17 Feb 2006 08:20:00 GMT jkp-ads012 Jan Karel Pieterse When working with Excel worksheets with a lot of used columns, it would be easy if one could have a way to show the content of a column on the same row in a window. This new WatchOtherCell utility does just that. http://www.jkp-ads.com/Download.htm#WatchOtherCell Thu, 09 Feb 2006 14:20:00 GMT jkp-ads011 Jan Karel Pieterse Excel provides a very useful option to gather data from websites, called web queries. These have been introduced with Excel 97 and have been further enhanced in the versions released after Excel 97. This article shows how you can setup a web query so that you can make the worksheet dynamically update based on the values in so called parameter cells. http://www.jkp-ads.com/Articles/WebQuery.htm Tue, 03 Jan 2006 20:00:00 GMT jkp-ads010 Jan Karel Pieterse Sometimes Excel VBA behaves unexpectedly. Here is an example: You are running code that is meant to open one or more files using the Workbooks.Open command. As soon as you hold down the shift key when this command is being processed, macro execution stops and your program is terminated entirely. This will even happen if you are e.g. editing an email message in Outlook whilst Excel VBA is processing the code in the background. http://www.jkp-ads.com/Articles/WorkbookOpenBug.htm Fri, 11 Nov 2005 12:30:00 GMT jkp-ads009 Jan Karel Pieterse Het gebeurt regelmatig, dat ik een bereik met formules heb dat op zich goed werkt, maar dat een foutcontrole nodig heeft, bijvoorbeeld om delen door nul te voorkomen. Dit artikeltje toont wat VBA code om de formules in een reeks cellen te converteren zodat ze een foutcontrole krijgen. http://www.jkp-ads.com/Articles/FormulaWrapperNL.htm Mon, 31 Oct 2005 12:30:00 GMT jkp-ads008 Jan Karel Pieterse Dit artikel is nu volledig in het Nederlands beschikbaar!!! Beschrijving: Excel is een van de meest gebruikte toepassingen in het bedrijfsleven. Het pakket staat bol van de standaard functies. Daarnaast biedt het zoals bekend de mogelijkheid taken te automatiseren via macro's geschreven in VBA. Veel gebruikers starten met het opnemen van macro's, komen vervolgens tot de ontdekking dat de macro recorder geen code oplevert die algemeen toepasbaar is en starten zo zelf met het programmeren. Er komt soms een moment, waarop de gebruiker een macro heeft ontwikkeld die anderen ook graag zouden willen gebruiken. Dat is het moment dat een dergelijk macro zal moeten worden omgezet naar een voor anderen bruikbare utility. Dit artikel geeft een overzicht van de zaken die daarbij aan bod kunnen komen. http://www.jkp-ads.com/Articles/DistributeMacro00NL.htm Tue, 25 Oct 2005 13:30:00 GMT jkp-ads007 Jan Karel Pieterse Events zijn een krachtig hulpmiddel bij het programmeren met Excel. Events maken het mogelijk te reageren op acties van de gebruiker zoals het bewerken van een cel of het klikken van de knop Afdrukken. Wanneer een applicatie gebruik maakt van events, dan is het ook vaak nodig de controle te hebben over het feit of de code in het event wel of niet wordt uitgevoerd (bijvoorbeeld om te voorkomen dat de event code in een oneindige lus geraakt of om programmacode de mogelijkheid te geven acties uit te voeren die het event voorkomt). Dit artikel toont hoe u events onder controle krijgt. http://www.jkp-ads.com/Articles/NoEvents00NL.htm Fri, 07 Oct 2005 17:50:00 GMT jkp-ads006 Jan Karel Pieterse Events are a powerful aspect of Excel programming. They enable you to make your application respond to user actions such as entering data into cells or clicking the print button. If your application uses events, you will probably also need to be able to control whether or not an event executes its code or not (e.g. to avoid event looping or to enable your code to do things you are preventing your user to do through the user interface). This article shows a method to gain fine control over which event fires and which does not. http://www.jkp-ads.com/Articles/NoEvents00.htm Wed, 14 Sep 2005 08:30:00 GMT jkp-ads005 Jan Karel Pieterse Excel is een van de meest gebruikte toepassingen in het bedrijfsleven. Het pakket staat bol van de standaard functies. Daarnaast biedt het zoals bekend de mogelijkheid taken te automatiseren via macro's geschreven in VBA. Veel gebruikers starten met het opnemen van macro's, komen vervolgens tot de ontdekking dat de macro recorder geen code oplevert die algemeen toepasbaar is en starten zo zelf met het programmeren. Er komt soms een moment, waarop de gebruiker een macro heeft ontwikkeld die anderen ook graag zouden willen gebruiken. Dat is het moment dat een dergelijk macro zal moeten worden omgezet naar een voor anderen bruikbare utility. Dit artikel geeft een overzicht van de zaken die daarbij aan bod kunnen komen. http://www.jkp-ads.com/Articles/DistributeMacro00NL.htm Tue, 23 Aug 2005 18:50:00 GMT jkp-ads004 Jan Karel Pieterse Oftentimes I have a range filled with formulas that as such work correctly, but need an error checking mechanism. For example to account for cells that are empty. This article shows some code to achieve that. But there is more. I plan to add functionality as we get along. http://www.jkp-ads.com/Articles/FormulaWrapper.htm Tue, 19 Jul 2005 20:40:00 GMT jkp-ads003 Jan Karel Pieterse In Excel you can give a range of cells a name. When you do that, you can use this name instead of the address to point to that range. In general it is easier to remember a name than a range address of the start and end of a range of cells. Using names has a couple of advantages: ranges are easier to find, formulas are simpler to understand and maintenance of the spreadsheet model becomes more reliable. Furthermore, some things in Excel can only be achieved by using defined names. But much more is possible with defined names than just naming a range of cells. Because you can also put a formula into a name, a whole world of possibilities opens up! Even more so, since you can also put the old Excel 4 macro functions to use in a defined name. This enables you to do things normally only possible using VBA. http://www.jkp-ads.com/Articles/ExcelNames.htm Wed, 6 Jul 2005 20:00:00 GMT jkp-ads002 Jan Karel Pieterse In Excel is het mogelijk een bereik van cellen een naam te geven. Wordt dat gedaan, dan kan voortaan deze naam gebruikt worden om naar dat bereik te verwijzen, in plaats van het adres. Over het algemeen is een naam van een bereik makkelijker te onthouden dan het adres van de begin- en eindcel van een bereik. Het gebruik van namen heeft een aantal voordelen: bereiken zijn eenvoudiger terug te vinden, formules zijn makkelijker te begrijpen, enzovoorts. Verder zijn er zaken in Excel die alleen mogelijk zijn door een bereik eerst een naam te geven. Maar er kan veel meer met namen in Excel. Er kunnen ook formules in gebruikt worden en dat opent een wereld van mogelijkheden. Sterker nog, omdat tevens de oude Excel 4 macro functies in een gedefinieerde naam mogen worden toegepast zijn dingen mogelijk die normaal gesproken alleen via een VBA macro kunnen worden gerealiseerd. Dit artikel is reeds eerder in het Office magazine (nummer 21, april 2004) verschenen. http://www.jkp-ads.com/Articles/ExcelNamesNL.htm Tue, 28 Jun 2005 09:40:00 GMT jkp-ads001