Replies

Okay – after much cogitation (that’s what I call it when I want to increase my hourly rate!), I think we can use the following UDF (also in file attached) for straight-line depreciation over non-integer years (like a 40% sl rate, representing a 2.5 year depreciation term). Required arguments are all as for previous version of the function, except that “Year” is no longer coerced to an Integer value. It was easier to use years than depreciation rate, but if the rate is more available (or more intuitive) it is simple enough to amend the function appropriately

Function SLDepr2(Purchases As Range, ThisCell As Range, Years As Single) As Currency

Dim CurrCol As Integer
Dim PurRow As Integer
Dim StrtCol As Integer
Dim FullYr As Integer
Dim PartYr As Single

FullYr = Int(Years)
PartYr = Years - FullYr
PurRow = Purchases.Row
CurrCol = ThisCell.Column
StrtCol = Application.WorksheetFunction.Max(1, CurrCol + 1 - FullYr)
SLDepr2 = Application.WorksheetFunction.Sum(Range(Cells(PurRow, StrtCol), Cells(PurRow, CurrCol)))
If CurrCol - FullYr > 1 Then
   If IsNumeric(Cells(PurRow, CurrCol - FullYr).Value) Then
      SLDepr2 = SLDepr2 + Cells(PurRow, CurrCol - FullYr).Value * PartYr
   End If
End If
SLDepr2 = SLDepr2 / Years
End Function

Thanks for you kind comments – I had a bit of an advantage, having done something similar to this for a client – but in that case I inserted about a million (rough estimate) hidden columns so that I could create a sum that totalled the correct number of years using a “choose” function without trying to reference a cell to the left of column A (small excerpt attached)….

Actually, I had that SUM formula in the function initially, but it wasn’t working, and I put in the looping structure as it is now. In doing that I noticed that I had mis-identified the current cell as the “ActiveCell” (as you note, it is only the active cell when the function is entered), and that correction probably would’ve made the SUM finction operate properly. Consider the from – to loop as the veriforma appendix of this function!

As for your question of how to deal with part years (if the depreciation rate should be 30%, representing S/L over 3 & 1/3 years, for instance): In this case, I would imagine that the depreciation amounts would be (assuming $100 initial cost) $30, $30, 30, 10. I would use a similar function that ‘looked back’ at the purchasing history on a cell by cell basis and added a full year of depreciation for all the full years and a part year for the stub year. It is easier to visualize than explain – I will post a function in the next little while.

Anybody who can shed some light on how a function can access the cell it is entereed in (other than by a direct reference in the arguments supplied to the function, which strikes me as a real kludge) PLEASE chime in…

Simon – this one works:

I have created a custom function in VBA that requires three arguments:
1) The range that includes the purchases for the asset class you are dealing with (should be entered as an absolute address (ie $C$9:$m$9);
2) The cell that the function is located in, and;
3) The cell that contains the number of years that the assets are depreciated over (should also be an absolute address). Note that I have changed the approach from a depreciation RATE to YEARS, since the function uses the number of years to be depreciated to determine how many years of purchase history should be included in the numerator of the depreciation calculation.

I think the operation of the function is pretty self-evident, but there are a couple of possible improvements:

– use the “SUM” function to determine the total assets to be included in the numerator of the calculation – I started to do it this way, then got tired of fooling around with cell offset formulas, and decided to do it as shown – I suspect that the SUM function would execute faster.
– eliminate the reference to the cell the function is in (actually, it just has to be to the column it is in). I don’t know how (or if) you can return the range where the function is being invoked – if anyone can shed some light on it, I would appreciate it.

the function listing (also in the attached s/sheet) is:

Option Explicit

Function SLDepr(Purchases As Range, ThisCell As Range, Years As Integer) As Currency


Dim CurrCol As Integer
Dim PurRow As Integer
Dim StrtCol As Integer
Dim ctr As Integer

PurRow = Purchases.Row
CurrCol = ThisCell.Column
StrtCol = Application.WorksheetFunction.Max(1, CurrCol + 1 - Years)
For ctr = StrtCol To CurrCol
   If IsNumeric(Cells(PurRow, ctr)) Then
      SLDepr = SLDepr + Cells(PurRow, ctr).Value
   End If
Next ctr
SLDepr = SLDepr / Years
End Function

In cell B2 enter the percentage growth you are planning for, and enter the annual contribution you are going to make.
Leave row 4 blank and enter the following formula in cell B5. Copy the formula aas many rows down as you like – you could also place meaningful labels in column A – like ‘growth,’ ‘contribution,’ and the year.

=B4 * (1 + $B$2) + $B$3

It will be easier to understand in a month if you name the cells B2 and B3, and ‘apply’ names to the formulas in B5:B?

Arcturus:

I have three suggestions:

1) If all you want to do is hide the rows with a blank in a specified column, I would suggest the following VBA code:

Sub Compress()
Dim TestArea As Range
Dim TestValue As Currency
Set TestArea = Selection
Application.ScreenUpdating = False
For Each Cell In TestArea
   If IsNumeric(Cell.Value) = True Then
      TestValue = Selection.Value
      If TestValue  0 Then
         Cell.EntireRow.AutoFit
      Else
         Cell.RowHeight = 0
      End If
   Else
      If Cell.Value = "" Then
         Cell.RowHeight = 0
      End If
   End If
Next Cell
Application.ScreenUpdating = True
End Sub

Select the column you are using to test whether the row should be hidden, then run the macro.

2) For the volume of data you have, you might also investigate whether a pivot table provides a better report format. you might have to manipulate the source data a bit to fill the blank values in some rows (or use tags like the division # rather than division, since the division field is left blank in several rows). I have filled in some random values for ‘regular hours’ as an example and attached a pivot table to your sample. In part, this will depend on how you are obtaining your data, particularly the budget and variance calculations, and at what level of detail they are calculated.

3) If you have to trim details out of your s/sheet to meet the 100K limit, you might try ‘zipping’ the file with any one of the zip compression utilities. The attached file has been zipped – if you cannot open it let me know, and I will send it as a self-extractor.

Dean

Dick:

So – I’ve finally finished the macro you asked for (rather than the s/sheet I told you you needed – hey, I’m a consultant: ya sell what ya got!). If my luck holds,you have already finished with your dinner, but it was an interesting way for me to learn a couple of things! In the s/sheet you can enter the number of participants, the number of cocktail and dinner hosts, press the button (still imaginatively labelled ‘button 1’ and it will generate a list of the guests assigned to each host, by number. I have arbitrarily decided that if you have X cocktail hosts and Y dinner hosts, they will be the first X participants and the last Y participants, respectively.

In truth, I still think this is a less-than-optimal solution, since you now have to generate a concordance that will tell Nancy Adams that she is participant number 52, and that host number 3 is Bill Smith and host 198 is Drew Jones, etc. But hey, it was interesting to work on!

There is a limit to the number of participants that can be accomodated: in order to use the “RANK” s/sheet function I had to dump a list of random numbers (one per participant) into the files, and in order to get the function to work it had to be a horizontal list, so you can’t have more participants than the number of columns in the s/sheet, ie – no more than 256 people involved. I am going to be sending out a separate query to the group here about both those limitations, but I think this conversation is pretty stale, so I will start a new thread!

Cheers – I hope the dinner goes well,

Dean

Okay – I’ll try that ‘attachment’ thing again….

Hi Dick (and 200 of his closest personal friends):

As it sits now you can “use” a variable number of hosts by only putting the required number in the “locations” ranges and leaving the rows below blank. It will probably be a little clearer after you look at the s/sheet.

I am giving some thought to how you might just put in the required variables and have some VBA code to spit out the guests (by number) per location (also by number) but I am having a tough time with how you would identify that guest (or perhaps “participant”) number 123 was a host, but #124 was not. At this point, it is a conceptual problem: I haven’t even begun to think about how to code it!

As a practical matter – isn’t it going to be just about as big a problem to identify to everyone involved that “participant 45” is actually Kathleen Cardno, and “host 16” is Doug Banks, etc, as to type in the names. I presume that somebody has a list of names somewhere that could be cut/pasted into place….

Hi Dick

I have attached a revised s/sheet. It determines the number of “names” of cocktail and dinner hosts and tests whether the each name in the list of guests is included in the list of hosts. If the particular name is a ‘host’ for that portion of the evening it assigns a negative number to it rather than a random number. Since the ordinals are determined from largest to smallest, it forces the ordinal to be in a “tie for last place.” The number of ordinals in last place are not assigned a location.

To use the S/sheet, place the “names” of guests in the sheet in the range from B31 and downwards for however many rows you need. Note that there should be no blank rows before the end of the block of names, and that the names have to be in the range named “Guests.” I can appreciate that it will be a pain to actually use full names, so you could do it by assigning arbitrary numbers, from 1 through the number of participants, and just filling the range with those numbers.

In the ‘host’ locations ranges (F4:G24 and I4:J24) you fill in the host names for cocktails and dinner – and they must match the names in the ‘guest’ list exactly (numbers make that easier!). It is the match to the name in the ‘host’ ranges that identifies them in the guest range as hosts and forces the random value to be negative. Again, clear like mud, I’m sure.

To get the pivot table to show the list of guests you either click once on the “cocktail location” or “dinner location” buttons on the pivot table and then on the yellow “+” on the Pivot Table toolbar (I am using XL 2000, but I remember the toolbar coming up automatically in XL 97 when a Pivot Table is activated – I can’t remember if it popped up in XL v. 5) to show the guests or on the “-” to hide them. Alternatively, if you want to show the guests for one specific host, click once on the host name in the pivot table, and then on the “+” or “-” buttons on the PT Toolbar.

PS – I am doubtful that a hardware and tech support guy would have 200 friends – are you sure you’re not putting us on!?!

Dick – I think the S/sheet will work for you as it is. The names of the Cocktail and Dinner hosts were arbitrary (you can see my limited imagination by the fact that I couldn’t come up with other names). If you simply replace the host names shown with the real ones you have, you will generate the lists of guest for each host appropriately.

I didn’t put in anything to force host to be included at their own function – in fact, the sheet logic implies that hosts are distinct from guests – they do all the work and don’t even get invited to the party!

Since the host for -say- a cocktail location will also be a participant in the dinner, but NOT in the cocktails (ie – they will not be a guest at anyone else’s cocktail location) I would ‘force’ the random number for cocktail assignments for all of the hosts to be less than zero. This will put them all at the back end of the list of ordinals – then set the last “N” ordinals (where N = number of hosts) to a dummy host since you don’t need to assign those people to a host – all those folks will already be at their own functions. You then do the same thing for the dinner random numbers and hosts.

It is possible to automate this process if required, although there may be some manual interventions required. I will post a revised S/sheet in a little while.

Drop me a note if this is all clear like mud!

Has your user saved his/her files in their “XLSTART” directory? Excel will open all files in that directory on start up.

Okay – so where was the file the last time? Maybe second time lucky?

I think the attached file will do what you want – obviously you will have to input the real guest names and cocktail / dinner locations to replace the sample ones I used.

Each guest is assigned two random numbers, one for the cocktail location, one for the dinner location – other wise you wouldn’t get any mingling of the groups. The “Rank()” function is used to determine the ordinal number of each guest’s random number among the set of all random numbers assigned. A look-up table then associates the ordinal number with a cocktail location, by assigning the 10 lowest ordinals to the first location, the next ten to the second location, and so on. The same procedure is used to assign dinner locations.

It would be possible to list the guests in order by location by sorting or filtering the guest list by location, but it was just as easy to use the two sets of data as the base for a pivot table. The two pivot tables are on sheet 2 – highlighting either the “Cocktail Location” or “Dinner Location” buttons and clicking the “+” sign or “-” sign on the Pivot Table toolbar will allow you to toggle back and forth between a summary view (lists number of guests per location) and a detail view (lists the guests by location).

Hope that helps!

“Oops – unless things have changed in Excel 2000 (and that is what you are using), I’m afraid that in 97 you can still access very hidden sheets via the Worksheets collection. The only thing very hidden does for you is that it stops users unhiding from menus.”

Interesting. I am using XL 2000, and when I run:

Sub HideProtect()
Dim Sh As Object
‘ Worksheets(“hidden”).Visible = xlVeryHidden
For Each Sh In Worksheets
Sh.Select
ActiveSheet.Protect password:=”Deaner”
Next Sh
ActiveWorkbook.Protect password:=”Deaner”
End Sub

It operates as expected – the sheets and the workbook become protected. When I remove the comment indicator from the ‘xlveryhidden’ line and run it again with sheets and workbook unprotected I get a “Select method of Worksheet class failed” error message on the “Sh.Select’ line

This is exactly the behaviour when I run the macro as shown (with the xlveryhidden line commented out) but I have previously ‘xlveryhidden’ a sheet through the immediate window. I had assumed that the worksheets collection was affected by including a ‘very hidden’ object.

When I try to run

Sub UnHide()
Dim Sh As Object
For Each Sh In Worksheets
Sh.Visible = True
Next Sh
End Sub

After I have ‘veryhidden’ a worksheet and protected the workbook, I also get an error “Unable to set the Visible Property of the Worksheet Class” at the “sh.visible” line, although this macro works when the book is unprotected.

I think I was confused because I was protecting the w/book, as well as “veryhiding” a sheet within it.

A couple of comments:

First, you have to leave at least one sheet visible in the workbook. If you are hiding sheets manually the option to hide the last visible sheet is ‘grayed out’ (IIRC) but if you are doing it by looping though the “worksheets” collection you will get an error (subscript out of range, I think – it happened to me recently)

Second, if hide the worksheet(s) then ‘protect’ the workbook with a password then the user cannot unhide the sheets (the ‘unhide’ option is grayed out).

Third, if you are protecting the workbook through VBA and you don’t want the user to be able to open the module to find the password (the VBA command to protect the workbook is:
ActiveWorkbook.protect password:=Password, Structure:=True, Windows:=False <- note that it contains the password!)

you can do what I had to do recently – place the password in a named range on one of the hidden sheets, then access it by:

Dim Pword as String
Pword = [pwcell].value
ActiveSheet.protect password:=Pword

where pwcell is the named range that contains the password.

So long as the sheet is hidden the password is hidden (and I used xlveryhidden elsewhere in the routine to make sure it was).

Finally – if your workbook contains hidden worksheets you have to know the name to access it, you cannot loop through the Worksheets collection and select them one at a time.