Can it be a UDF (User Defined Function using VBA)?

Solved

Posted on 2011-05-09

Good afternoon!

I am helping a friend build up a gnatt chart but am struggling.

As it is supposed to run for several years, to limit the use of columns, I am using one cell per week and try using "REPT" with blanks (" ") and bars ("¦") to show the number of days worked. The blanks are there to align the bars either to the right or to the left depending on whether the first part of the week is worked, or the second part only.

I use networkdays which allow for public holidays. However, with the long formula I came up with, I have not managed to show the public holidays themselves as blanks as the formula was getting so long.

I was wondering whether by some clever trick the formula could be reduced dramatically AND allow the public holidays to show up!

I am asking much, I know.

I find the date formulas extremely hard to handle most probably because I do not use them often enough.

I would appreciate any help.Thanks!

Danèle

PS I am adding a short version of the gnatt chart with the bars shown as little rectangels to make it easier to see the number of days. example-gnatt.xls

I am helping a friend build up a gnatt chart but am struggling.

As it is supposed to run for several years, to limit the use of columns, I am using one cell per week and try using "REPT" with blanks (" ") and bars ("¦") to show the number of days worked. The blanks are there to align the bars either to the right or to the left depending on whether the first part of the week is worked, or the second part only.

I use networkdays which allow for public holidays. However, with the long formula I came up with, I have not managed to show the public holidays themselves as blanks as the formula was getting so long.

I was wondering whether by some clever trick the formula could be reduced dramatically AND allow the public holidays to show up!

I am asking much, I know.

I find the date formulas extremely hard to handle most probably because I do not use them often enough.

I would appreciate any help.Thanks!

Danèle

PS I am adding a short version of the gnatt chart with the bars shown as little rectangels to make it easier to see the number of days. example-gnatt.xls

8 Comments

Havent worked it to Put a space for Holidays

=IFERROR(REPT(" ",MAX($B2,F$1)-F$1)&REPT("

NETWORKDAYS needs the Analysis Toolpak (by the looks of your formula, you already have this)

IFERROR needs Excel 2007 or higher.

For Excel 2003 and below use

=IF(ISERROR(REPT(" ",MAX($B2,F$1)-F$1)&REPT("

```
Option Explicit
Function MyGnatt(ByVal StartDate As Date, ByVal EndDate As Date, ByVal MondayDate As Date, ByVal Holidays As Variant) As String
Const NoDay = "N"
Const YesDay = "Y"
Dim MyHol As Collection
Dim i As Variant
Dim j As Variant
Set MyHol = New Collection
'''''''''''''''''
'Fill Holidays Collection
'Excel Range
If Not IsMissing(Holidays) Then
On Error Resume Next
If IsObject(Holidays) Then
For Each i In Holidays
MyHol.Add i.Value, Format(i.Value, "DD/MM/YYYY")
Next i
'Array
ElseIf IsArray(Holidays) Then
For i = LBound(Holidays) To UBound(Holidays)
MyHol.Add Holidays(i), Format(Holidays(i), "DD/MM/YYYY")
Next i
End If
On Error GoTo 0
End If
On Error Resume Next
'Loop through this week
For i = MondayDate To MondayDate + 6
'Try and get The Holiday of i
'If it doesn't exist, it will error
j = MyHol.Item(Format(i, "DD/MM/YYYY"))
'Check:
'Date is within Range
'Date is not weekend
'Date is not Holiday
If i >= StartDate And _
i <= EndDate And _
WorksheetFunction.Weekday(i, 2) < 6 And _
IsEmpty(j) Then
MyGnatt = MyGnatt & YesDay
Else
MyGnatt = MyGnatt & NoDay
End If
j = Nothing
Err.Clear
Next i
End Function
```

Sample Attached

example-gnatt.xls

The UDF is perfect. I just tweaked so that only the week days appeared ( Monday + 6 became Monday + 4). The formula was also quite intersting... but the UDF is so much simpler and cleaner.

I changed the Y and N to bars and spaces but they do not have the same width so was wondering

( wishes, wishes) whether the "Y" a block bar using Alt+219 (that is what I used i the UDF) formatted in a colour by choice, and the "N" , the same block bar but this time formatted either as white, or with no colours ( I am not sure that exists), so that the spacing can be consistent through all the cells, and the alignment perfect?

Many thanks!

The use of a UDF is much easier to implement and easier to use that the long formula which was also provided, though it did not allow for the holidays but seriously reduced in length the formula I had come up with.

Many thanks!

Danièle

Yes it can be done, with some un-preferable changes

This will cancel it from being a formula and make it a Method (Needing to be re-run with every change)

From what i understand you want a string of | in different colours based on whether its a working day.

i.e.

YYNYYNN

Y's in Black

N's in White (invisible)

This can only be done on a Cell basis, not a character by character basis

If both the bars and the spaces were of equal width, then the entries would all be consistent and the alignment would be perfect.

I have no idea how one can determine the width in pixels of a character, then assign a space of a given number of pixels to achieve this, but I suspect that would definitely have to be done with VBA.

If the rept characters do not align correctly, the lines will be "messy".

I have read a few blogs were people used the rept to that effect but I cannot find the actual method to remain consistent width wise.

I thought keeping the same characters but different colours, but as you mentioned, that is impossible. Any other possible way to retain the same width for whatever entries of N or Y?

Thanks!

Danièle

Each font would have a different size.

If you were to use a mono-spaced font (like Consolas or Lucida Console) they would be in perfect alignment.

Mono-spaced fonts have all letters and characters the exact same width in pixels.

So the space is the same as the pipe bar is the same as the letter N

By clicking you are agreeing to Experts Exchange's Terms of Use.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**13** Experts available now in Live!