Formula Array (II)

When I drill down the formula array, I found that it has reduced much the necessity of creating VBA function.  As you know, all the Excel functions are optimized internally; for the same functional effect, Excel built-in functions have much less overhead than VBA function.  Besides, formula array adds even more capacity to built-in functions.  I would like to quote below 2 examples:

1. If function which adds “or” / “+” and “and” / “*” operators

2. Vlookup function: columns summation

Are they marvelous?  I cannot imagine how powerful the formula array is.  I only know that there are many rooms to exploit.  At the same time, it may lead to re-thinking of Excel function usage.

Formula Array (I) – A powerful techniques in Excel

It has been many years I have not had any significant advancement with my Excel skills.  I become more focusing on template design in problem solving (algorithm).  Out of which is a technique in calculating the available inventory of 1 materials, after meeting the demand within the lead time, for other usage beyond any plan.  To be more elaborate, I quote the following example in bullet point:

1. The lead time of RM-1 is 10 weeks, that is, place order to supplier on week 1, you will get the materials received on week 11
2. Now it is week 1; the inventory balance is 15,000 in the factory, every week from now to week 11, there are scheduled demand (production schedule) and supply (goods received).  Assume that there is no unmet demand within the coming 10 weeks
3. A sudden order (order-X) comes and it requires RM-1.  The question is how many RM-1 could be available for this sudden order or how many in each week (Week 1 to 11), after the scheduled demand & supply, could be available for each week production of this sudden order.  As you know, if we place additional order to the supplier of RM-1 now, we can only get the goods received on Week 11.  If we only make use of the Week 11 receipt of RM-1, we may not be able to meet the customer’s urgency requirement.  At best, we need to utilize the available RM-1 without affecting the confirmed demand.
4. To resolve this, conceptually, I use a retroactive calculation of RM-1 from Week 10 back to Week 1.
   1. On Week 10, if the supply is 850 and the demand is 700; therefore, we can allocate 150 to order-X; however, if the supply is 630 and the demand is 890, gap is 260, then we need to reserve the inventory balance of week 9 to close the gap of week 10 (260)
   2. Assume that there is no demand and supply from week 1 to week 9, in short, we need to reserve 260 out of 1,500 from the week 1 inventory.  Therefore, 1,240 can be allocated to order-X on Week 1
   3. By all means, the real world situation should be more complicated.  There should be demand and supply from Week 1 to Week 9
   4. Then assume that on Week 9, the demand is 1,000 and the supply is only 830, the gap is therefore, 170; together with the gap of week 10 – 260; the culminated gap is 430; it needs the inventory of week 8 to close the gap of Week 9 & 10.
   5. Assume that there is no demand and supply from Week 1 to Week 8, the gap of 430 will required the appropriation of Week 1 inventory; therefore, 430 out of the 1,500 of week 1 inventory is reserved and only 1,070 is available in Week 1 for order-X immediate consumption

When I figured out the above mechanism, I was feeling excited and included this in one of my Excel training course as well as the planning mechanism of my existing company which took even longer period in the application development.

Then, few weeks ago, I was stuck up with the thinking if it was really necessary to write a VBA function to solve a problem.  I tried to google any alternative.  I came to realize that there exists a wonderful functional tools of formula array in Excel.  Take a look of the following screen dump.  I need to sum the products of column c and column d for those cells with column b contains “Dick” in the cell content.  This is the solution after I digest the online teaching of formula array.  Bravo!  Otherwise, I need to write a VBA function with the loop to examine each cell content in column b in order to do the sum of products of column c & column d.

What does it mean to me?

1. Sum can be sum(c5:c12*d5:d12) which is equivalent to sumproduct
2. It performs loop function which is looping each cell in B5:B12 to check for “Dick” though it is quite wired for the function of Find to contain this loop function!
3. Same loop capability functions in If.  Awesome!
4. However, there are more to explore: some functions, like vlookup, index, have limitation in doing formula array.  It requires more effort to sort out the limitation
5. Above all, it also needs to dig out how each other function could encapsulate the formula array function!

It goes without saying that if you are not doing data model in Excel, you will not share my exhilaration.  If you are not believing Kaizen (there is always room for improvement), you will not share the happiness of knowing that Kaizen is working all the times and everywhere.  Unless you believe it, you will not keep thinking that there is always a better solution!

If it is embarrassingly obvious, it must be right! (my embarrassment)

I learn the above saying in TOC fiction – “Isn’t it obvious?”.  Usually, I use it as an advise to my peers.  However, when it comes to a situation which is embarrassing myself, it is bitter.  Anyway, it is not difficult to accept it provided the lesson is really helpful.

I used to trumpet that I am good at Excel but this year, I came across 3 functions which I overlooked completely while they are very useful at all.

1. ceiling:  say, a number: 456, we need to round up to a multiple of 30, it is just ceiling(456,30) = 480; I am so stupid to write as ceiling(456/30,1) * 30.  I learn it from an attendant to my Excel course
2. sumproduct: it can add condition to the series of data to sum up, sumproduct(data1, data2, – -( )); the final part of – -() is for setting condition
3. copyfromrecordset: there exist a method for a range in Excel/VBA so that it can write the data from a recordset to a worksheet starting from the cell specified, like worksheets(“xyz”).range(“a1”).copyfromrecordset CustomerSet; CustomerSet is a recordset; in the past, I use a common module for writing a set of data to a worksheet(self-developed, around 40 lines of command in Excel/VBA).  I happen to learn it from my co-worker.  He is smart!

Well, I am happy that I can learn from my peers and my staff.  I am more than happy that I can overcome my self-centric to take in new knowledge.  Thanks

Production Materials Plan

Few months ago, I have developed a template of Production Materials Plan for calculating which order and what quantity can be produced based on the materials available in warehouse in MS Excel.  The outline of it can be viewed in a blog entry of the following:

Middle Layer–The element that can only be done in Excel

The architecture is to overview a timeframe of 8 weeks in order to proper plan the order commitment to customers together with the planning of operators.  The major usage is to determine what product & quantity is for production scheduling.  Also, the issuance of shop order based on this result could help lock the materials to a particular shop order.  Although it has alleviated the workload of the original staff in materials planning, it could not provide perfect solution in production schedule.  The model assumes that all materials for a particular week production is in the warehouse at the beginning of the week.  However, if the materials is only available on, says, Tuesday and we must do the production this week, we need to make sure what products and how many should be scheduled before Tuesday and what should be scheduled after Tuesday.

I must recognize that I am complacent to certain extent.  I must have know this critical part when designing the model.  Luckily since there is some personnel change, I happen to talk to the supervisor to exploit if there is any room of improvement in the planning cycle.  I was told of this weakness of the model.  I come to realize few issues with the people including myself:

• Why don’t they tell me in advance?  There must be some communication barrier among us.  My failure to break the communication boundary is one of it.  I am not friendly enough.
• How can they tolerate with this deficiency?  Maybe since the improvement is regarded as great compared to the previous situation, they do not want to be so demanding in this regard.
• Actually, the solution to this remaining deficiency is easy.  It is only a matter of application.  Why don’t they figure it out by themselves?  Are they too busy?
• Or the model is not so great that any effort to change may not bring sufficient benefit.
• Or it is Dick Lam model that nobody dare to badmouth it overtly!

Anyway, the solution is simple as I thought.  Once the weekly production materials plan is done, then take out the 1st week no short quantity as the demand for a production materials plan by day.  Instead of putting week1 to week8, there should be day1 to day7.  Therefore, what products and how many quantity could be produced for day1 and so to day7.

Middle Layer–The element that can only be done in Excel

If you are not builder of algorithm or Model in Excel, you won’t be able to realize what I am talking about.

I receive quite a lot of teasing from IT professional when they are looking at what I have done in Excel/VBA, those include the one I hired to succeed my work in my last supply chain project with the bottle manufacturer (I know it by my speculation).

Currently, I am doing another supply chain related module – Determine what and how many to produce based on the available materials.  To this end, the following factors are to consider:

1. Priority of the products/customer order, which product/order should precede (the Priority Column of the below screen shot)
2. Arbitrarily set the quantity to produce once the ordered quantity cannot be produced while releasing some materials to other equally important product/order (the Demand column of the below screen shot)

Exhibit-1

There should be hundreds of products/orders to produce. 

Usually, IT people will just think about a 1-click solution after re-setting the above 2 factors with several rounds of iteration, i.e. click on a button and run the program behind and see the effect; however, it is a black box operation.

With Excel and its User-defined functions, the solution can be something more straightforward.  Many cells are interlinked in different worksheets.  The following data should be ready in the same Excel file

1. On hand balance of the materials
2. BOM of each product

Then, the middle layer of this model is as follows:

Exhibit-2

The quantity allocated is determined by the available quantity of the item with the minimum quantity balance and the BOM quantity * Demand, whichever is the less.  Look at RM-9 of Product-A, the available quantity is 800 which can only produce 1,600 pieces of Product-A.  The No Short quantity of quantity is therefore 1,600 which is also shown in Exhibit-1.

The No Short in Exhibit-1 is equivalent to No Short in Exhibit-2.  The materials usage/reserved is transparent to the user.  Above all, when the user is going to change the value of Priority and Demand in Exhibit-1, the result is returned immediately (of course, it depends on how fast you computer is).

My reader, if you are interested in this kind of solution, drop me a note and see how I can support.

MS Excel: ScreenUpdating is not reliable

Be reminded that the following command in Excel/VBA is not reliable and sometimes, it will stop Excel from functioning. 

Application.ScreenUpdating = false

Almost all Excel tips and tricks in faster performance tell that to speed up, the VBA code should write the following commands:

1. Application.Calculation = xlCalculationManual             ‘This will suppress the automatic calculation on any change of any cell content
2. Application.ScreenUpdating = false                          ‘This will save the time in updating screen on any change of the screen display

The first one is right.  The second is partially right.  If you only have small amount of screen updating, it works well.  However, if it is many, your Excel will run in error mode, particularly, when the VBA will create a number of worksheets with plenty of formulas inside.  I tried to duplicate 54 worksheets from a template full of formula and the Excel suddenly enter to error mode which I need to cancel the Excel application in order to let the computer work properly.

A new perspective of MS Excel application

In addition to the consultant job of bottle company, I am also helping a friend of mine (a button factory) to build a manufacturing order tracking system.  Actually, the factory is a small size operation with only 350 workforce.  The whole workflow is done by several key staff including the BOM, routing, shop order and subcontracting monitoring as well as the packing order generation.  I often tease him if these people disappear a day, his factory will do the same.  To cultivate a tree with low hanging fruit, I apply Excel to build the whole operation system and the order tracking function with SQL server as the backend.  To my surprise, the people there are very helpful, particularly, Ricky.  He is almost the heart of the new system who provides me all engineering information as well as the operation flow.  It is fantastic that if you expect you can re-engineer or turnaround a messy situation.  The following is sample of a series of shop order originated from a sales order.  All the process and the related document together with the engineering chart is generated from a click in Excel.

http://www.box.net/shared/8zdegq38e2

Because of the recent 2 projects, I am inspired with the following idea of Excel application:

1. We can transform Excel spreadsheet as pages of Internet Explorer.  It is even more powerful that the cell can accept formula and it is easy to format the spreadsheet
2. The output is portable by just copy and paste to other workbook
3. We can program the event of workbook_open to make it a auto-run program and put it in the Scheduler of the control panel of Windows so that it can auto-generate reports and send it out to recipients via SMTP.  Each report/worksheets with VBA code behind can be an independent module.  We can use the loop of “For each worksheet”
4. We can make the worksheet/report more action-trigger by putting more chart/drawing, like the one I mention above.
5. …..

This blog entry may be a little bit technical.  If you are interested, drop me a note and let’s do more discussion.

Another bug with Combobox in MS. Excel

The combobox may not function well in the spreadsheet environment (not Form) when freeze pane is set. The symptom is when you click the reverse triangle of the combobox to expand the dropdown, it will not respond. However, when the freeze pane is release, everything goes back to normal. Strange enough that it only happens occasionally. There is no pattern of this bug and it cannot be replicated systematically.

Excel bug in ComboBox with linkedcell property

Thank you for the RGP assignment of developing various systems in Excel for the client.  I encounter quite a lots of bug with Excel.  To name a significant one, it is linkedcell property of all Excel form control.  You would not be aware of it if you do not use ComboBox/ListBox in worksheet.

For a ComboxBox control, namely, “xcomb_name”, you can create a dropdown list with restricted choices for the user.  By assigning a particular cell address in the property of linkedcell, any choice selected will be shown on the linkedcell, for example, you create a list of the following from B4 to C7 in a worksheet:

Code	Name
87025	Dick
87046	Lisa
87087	Dickson

In the ComboBox, you fill in “B5:C7” in the listfillrange with columnhead set to true, and the linkedcell set to G1.  For any option you select in this ComboBox, the value will be replicated to cell G1.  There is nothing awry if you do not protect the worksheet.

When you protect the worksheet, you will probably set G1 as allow-edit range under the command “Review –> Allow Users to Edit Range”.  In principles, since G1 is set to allow-edit range, even the worksheet is protected, when you choose an option in the ComboBox, the result should be replicated to G1.  However, Excel will prompt you that you are trying edit a range which is protected.  That mean no value can be placed to cell G1.

To skip around this bug, all you have to do is as follows (you need to write some VBA code):

1. Create the gotfocus event in VBA environment for this object, the event will be automatically named as xcomb_name_gotfocus
2. write the program code as follows:

• Activesheet.Unprotect password:=”dicklam128”                          ‘dicklam128 is the password assumed

3.   Create the lostfocus event in VBA environment for this object, the event will be automatically named as xcomb_name_lostfocus and write the following code:

• Activesheet.Protect password:=”dicklam128”                      ‘dicklam128 is the password assumed

Then the choice you selected in the ComboBox can be replicated to cell G1.

For any question, please feel free to leave me a message.

General Ledger in Excel(enhanced version)

Thanks to my existing job; I got the chance of refreshing and sharpening my techniques in Excel, including VBA techniques.  Above all, I find a way of putting drop down list box (multi-columns of course) in the spreadsheet.  Usually, people are used to applying validation list; however, it is only single column and it is easy to be overwritten by a copy and paste.

I have put this new techniques in the enhanced version of General Ledger.  You can try to download the file from the following link:

http://www.box.net/shared/vd63fhjisc

Be noted that the new techniques is put under the column of Account code in Voucher entry worksheet.  I also fix some minor bugs to improve the performance of this Spreadsheet General Ledger system.