FMk decode...

Auto Naming Excel Sheets and Function Across Sheets

2009-11-28T16:43:00.006+08:00

Here's the scenario I faced. I was given an Excel file which

consists of 120 sheets of data in uniform template, that
any of them belong to only one of 3 main categories, and
under each category, any of them belongs to only one of 3 regions, and
I have to summarise them in a single "Summary" sheet.

Here what I've done. Because the Excel sheets are named somewhat arbitrary, I firstly need to rename them to a more consistent grouping.

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Dim nameln As Integer
Dim shname As String
.
nameln = Application.WorksheetFunction.Find(" ", Range("A1"), 13)
shname = Left(Range("A1"), nameln - 1)
ActiveSheet.Name = shname & " " & shname2
ActiveSheet.Next.Select
Application.Wait (Now + TimeValue("0:00:01"))
Range("I1").Value = ""
End Sub

After that, it is easier for me to use the SUM function across multiple sheets.
=sum('Sales Oil Reg1 Cntrct1:Sales Oil Reg3 Cntrct120'!F20)

Cell F20 in each sheet represents the total sales for each Contract, so in the above formula, it summarises all the sales from all contract from all region.

Here is another useful trick- because I made the auto naming to include its main category and region, I can do the function for only the sheets under each category (or region) by the following
=sum('Sales Oil*'!F20)

By the way, once you press Enter after typing in the formula, what Excel did was it analyse the shorthand formula and rewrite them if you were to do manually like =sum('Sales Oil Reg1 Cntrct1'!F20, 'Sales Oil Reg1 Cntrct2'!F20) and so on.

Access Form: Cancel Changes Button

2009-09-19T09:04:00.003+08:00

When you enter values in Access forms, and later decided to discard them, there is no ready button that can do such thing. The Cancel button as provided by Access form wizard just simply closes the form. As anybody familiar with Access, once you typed something in forms that are bound to tables, those values are automatically registered as new record.

Below is my workaround in creating a Cancel button that actually undo any changes and close the form. This is Access 2007 embedded macro set for the Cancel button.

Condition	Action	Arguments	Comment
	On Error	Next,
[Form].[Dirty]	RunCommand	Undo	Perform Undo only when form content is modified, else, skip
[MacroError]<>0	MsgBox	=[MacroError].[Description], Yes, None,	Spit out the error message from system
	Close	, , Prompt

Small Concept

2005-12-07T04:53:00.000+08:00

Designing an ALU model is a straightforward problem. Still, making the model works, requires a colossal amount of understanding in logic synthesis – that is how a block of several gates can be arranged to achieve a certain operation such as addition or subtraction.