What's new on the site?

This page lists, in reverse chronological order, updates to the web site. It does not include minor changes or formatting changes.

26-December-2019
Excel drawing functionality to create project plot plan and process flow diagram
Introduces an Excel VBA application to create an Excel drawing. The drawing functionality built in the Excel are utilized to help draw project drawings such as plot plan, PFD, PID and so on.
Excel Drawing

29-November-2019
Initialize the worksheet by cleaning up all the previous works on a worksheet.
Introduce VBA methods and properties to get the working worksheet to be initialized by removing the previous work, all or a part. If you want to remove all the entries, it is simpler to delete the working worksheet and generate a new worksheet. You can use some of the methods and properties to clean up partially.
Initialize the worksheet by cleaning up all the previous works on a worksheet

24-November-2019
Add Controls, Form Control and ActiveX Control on the worksheet dynamically
Add the Form controls and ActiveX controls on the worksheet dynamically.
Assign the event handling Macros to the corresponding Form and ActiveX controls.
Encapsulate the Macros in the Class module rather than in the worksheet code module.
Add Form controls and ActiveX controls dynamically.

19-November-2019
Copy a picture, paste to another worksheet and resize the picture
Copy a picture in a worksheet, paste it to another worksheet, position and resize the picture Copy, paste, re-position and resize the picture

19-November-2019
Data entry form - Excel built-in function and emulate Excel function beyond Excel basic functionality
The downloadable program is converted into Excel Add-in. Once installed, the program is ready for use for all the active Excel files.
Data entry form-Beyond Excel built-in form

15-November-2019
Filter the data table by column horizontally - Excel VBA
The function to sort columns horizontally is removed. Heading does not have to be arranged at top row and you can specify the row number where column headings are arranged.
Filter the data table by column

13-November-2019
Userform dynamic controls-Add, remove controls dynamically and create event procedure of the dynamically added controls
When the contents of the userform can not be determined at design time, we need to add controls to and remove controls from the userform at run time. The event handling of the dynamically added controls is also to be designed dynamically using the class module. This page discusses how to work with a dynamic userform.
Userform dynamically add and manage controls

12-November-2019
Class module encapsulating Window API functions
Encapsulate multiple Windows application programming interface (API) functions in a class module to execute a single procedure
Class module encapsulating Window API functions

3-November-2019
ADO connect is altered from previous Excel Driver to Access Database.
ADO connect to Access Database is the new basis for the programs discussed in the pages below.
  1. P6-project calendar based on p6 xer file
  2. P6-Project activity data table based on p6 xer file
  3. P6-project summary based on p6 xer file

3-November-2019
ADO (ActiveX Data Objects) to pull external Excel data and use SQL (Structured Query Language)
Discuss the methods to pull external data residing in the Excel worksheet using SQL query statement
ADO to pull external Excel data and to use SQL
  1. ADO(ActiveX Data Object) is connected to Excel Driver or Access Database
  2. The needed object libraries are referenced either by early binding or late binding.
  3. Four (4) different method for the same outcome are discussed.
  4. The VBA complex scripts to manipulate external data sets can be replaced by simple but powerful SQL query statement.

9-August-2019
Update Fish Bone Diagram
Fish Bone Diagram generator is improved by way of:
  1. Additional level 0 level added.
  2. The ability to create skeleton fishbones without showing all of the subsystem detail on the fishbone.
  3. Improved filtering and progress input detail fields. i.e. the ability to create highly specific filtered fishbones by contractor and or responsible person / party.
  4. Adding Progress tracking by ITR / punchlist etc.
  5. Improved checking of clashes when generating the fishbone to ensure parameters in terms of number of levels etc are suitable.

6-May-2019
Update Sunstone
The project management program running in the Excel environment in contrast to P6 or MS project is updated reflecting feedback.
If you attempted to run the "Sunstone", you were unable to even start the program. The following are major updates reflected.
  • Reference to Com Add-in which does not allow cracking the code is removed so that you can view the code if you download the program
  • A worksheet("Reference") is not required. The holidays over the project in the worksheet "Reference" can be added using a dialog.
  • Add how to unlock .chm file (user guide)
    • -Now able to run the program from the new workbook. Original was to run in demo file which has all the worksheets created before program run.

18-Apr-2019
Product Excel Chart and graph
Excel Chart and graph is updated to reflect Jon Peltier's comments into. Program errors and bugs as indicated by Jon are cleared off. Now Demo file and a simple user guide are added in the download zip file.
Excel Chart and graph

9-Apr-2019
Data entry form- new article
Project people use Excel daily to manage project list(s) where project information is arranged in the tabular form.
The information might be dynamic in the course of project execution with a row of data very wide and requiring repeated horizontal scrolling. You may well consider using a data entry form to add, edit, find, and delete rows.
Excel offers a quite convenient and easy way to work with the project list using a data entry form that Excel generate automatically. This data entry form works with a normal range of data as well as a range designated a table.

27-March-2019
Rady for public service
Draft web pages are reviewed by specialized personnel and their feedback are reflected.

20-Mar-2019
Complete site master pages
Site master pages are complete to contain 61 articles to explain each individual application and five integrated package programs. 61 articles are available under the Article page and 5 integrated package programs are available under the Product page. All are downloadable for your direct use. Source codes of programs under article are available. In contrast, the source code of integrated package programs under Product page are made available upon your request and with your voluntary donation.