Silfuos Thoughts

A subject that is often covered with overuse of casting and converting by many developers , due to lazy – fast programming . There are many approaches on the specific subject . Fastest one I discovered on the web is

DateAdd(dd,0,datediff(dd,0,  foodate ))

It is the least resource intensive . And to extend this technique you can use it in a whole bunch of other cases

Such us round to :

Whole minute    DATEADD(minute, DATEDIFF(minute, 0, startdate)0)

Whole Hour    DATEADD(hour, DATEDIFF(hour, 0, startdate), 0)

etc…

(Proof of concept is Here and Here )

An ordinary day usually doesn’t start with the coffee maker  refusing to work , and your primary External Disk –call it disk1 from now on ( the one that you always carry with you saving your job) broken . Good thing I always have it synced with a backup disk –the one that ia call a life-saver (disk2 for the story )  ) .

So I started facing the situation . Ok , disk1  has no bad sectors , over a million files on it , and its benchmarks started to be very low . Defragmentation ok , checked it on another machine ok , format does not seem to do anything . Let’s start low level formatting it .

My office machine is a pretty good one ( for my standards ) . A win 7 64bit , with two 500gb disks , 8 Gb Ram and I Core I5 CPU . OK , grab  disk 2 ( backup was only 2 days old ) let’s do some benchmark test to check its status . Benchmark OK , defragmentation as low as 2% files all OK . As a had a time windows not needing anything , started to defrag it to see if I can zero it . A phone call asking me some feature that had to be tested locally , took my thought away from that ( along with the everyday end user support ) . Started local sql server , Internet information manager , set up a small project started debugging , two hours and a hanged debugger later , saw that  process explorer was using more tha 5 GB of ram . What was that . switching to the low level formatter window taught me that you can’t revive a  disk that has intelligence on its own , and gave me no option than stopping the low level format ( the sea tools from Seagate informed me already the the disk has errors and it was on warranty ) . Explorer still on 5 GB ram . Defragmentation still working , stopped it . No difference . The feature I refered earlier was already ftp’d on its correct location as it was built and free of errors ( I hope ) . so nothing currently working , let’s stop the process ( my mistake ) . Explorer restarted , disconnected disk 2 , reconnected it , where the heck are my files ????@#%^@$%^@$ .

Hopefully there was nothing serious that a usual check disk couldn’t handle ( to be sure I started recovering  a third disk I used in the whole process of moving files and I was sure that it was formatted and never used again , so if something went wrong , I could get some important stuff out of it ) .

Conclusion . Always keep a second copy of your important data ( and in different geo places ) . Always think that the worst case scenario is not as far as you hope . Maybe it’s time for an external SSD ( but cost is high for serious capacities ).

( Still don’t have a clue why the filesystem had trouble handling over 2 hundred thousand files ( am I missing something ??) under my xampp directory – disk 2 ( the that for now I promoted to my disk 1 for now ) had also trouble giving enough performance writing to it but it was something that I could live with it . Xampp was used mainly for debugging joomla installations that was already backed up from original sites by this wonderfull module ( Akeeba ) and I deleted and set up a new one

Phone Ring ……… Phone Ring ……… The CID informed me that it was my good friend Timos on the other end .

Timos : Hey Nickos , Good morning can you tell me if there is an easy way to get the page number of a cell ??? I want to summarize a column based on page breaks

Me: Hmmmmm . Interesting problem . Let me get back to you in a while.

I must admit . that was a thing i could easily name , an interesting problem . Until that time the page number in excel for me was something that existed in the sphere of print settings and i had never involved it in and calculation inside the sheet .

That was surely an interesting problem !!!

Did some search and found some interesting stuff in forum posts .

The best one i found was mentioned in ( http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/8ca260935d83f46a )

Here is the solution transformed in VBA Function you can easily insert into Excel VBA Editor .

Public Function getpagenumber(CurrentCell As Range) As String

 Dim VPC As Integer, HPC As Integer

Dim VerticalPageBreak As VPageBreak, HorizontalPageBreak As HPageBreak

Dim NumPage As Integer

 If ActiveSheet.PageSetup.Order = xlDownThenOver Then

  HPC = ActiveSheet.HPageBreaks.Count + 1

  VPC = 1

Else

  VPC = ActiveSheet.VPageBreaks.Count + 1

  HPC = 1

End If

NumPage = 1

For Each VerticalPageBreak In ActiveSheet.VPageBreaks

  If VerticalPageBreak.Location.Column > CurrentCell.Cells.Column Then Exit For

  NumPage = NumPage + HPC

Next VerticalPageBreak

For Each HorizontalPageBreak In ActiveSheet.HPageBreaks

  If HorizontalPageBreak.Location.Row > CurrentCell.Cells.Row Then Exit For

  NumPage = NumPage + VPC

Next HorizontalPageBreak

getpagenumber = NumPage

 End Function