Order of Calculation

[Guest]

I am doing a worksheet that draws data from Access. I need to separate this
data based on a any of 8 dates being within the report month. I pull the data
into one sheet, then on a separate (monthly) sheet I am writing a function
which will write in the name of the client that has one (or many) of the
dates in the report month.

I have several problems.

Excel 2000 is starting to calculate from the bottom of the worksheet, not
the top and there appears to be no switch to tell it to start from the upper
left hand corner (nothing at the upper left sides needs anything down and
right of the calling cell). This, of course means that I have to know the
exact number of clients that month and only populate that many rows with the
function calls. Not very good planning and it also gives me a reverse
alphabetical listing of the clients. I have tried several ways to get around
this, but none works.

I can't write to another cell from a VB function.

Setting a global doesn't work since the Workbook_Calculate sub is not always
called - but I finally figured out a way around that problem for the first
sheet, having gotten to the next month, yet.

Thanks for any assistance.

John H W

 

[Tim Williams]

What does your function look like?

Tim

 

[Guest]

Tim - Here is the functions
In each Row starting at row 3:
Col A =GetName("1/1/2005")
Col B =IF(A3>"", GetJASID(A3),"")
Col C through O =IF(A3>"", GetDate(C2),"") Note: I have not included GetDate
below

--------------------
Option Explicit
Dim gnCurrentRow As Integer
Dim gbLastRowHit As Boolean, gbRunning As Boolean

Public Function GetName(dtReportMonth As Date) As String
Dim strCell As String, nRow As Integer, nCurrentRow As Integer
Dim strTemp As String
Dim bFound As Boolean, strRow As String
Dim strReport As String, vTemp As Variant, strName As String

If Not gbRunning Then
gnCurrentRow = 3
gbLastRowHit = False
gbRunning = True
Else
gnCurrentRow = gnCurrentRow + 1
End If
If gbLastRowHit Then
GetName = ""
gbRunning = False
Exit Function
End If

nRow = gnCurrentRow
bFound = False

With Worksheets("Data")
strRow = CStr(nRow)
strCell = "A" + strRow
strName = .Range(strCell)
If IsEmpty(strName) Then
gbLastRowHit = True
GetName = ""
Exit Function
End If
While Not bFound
strCell = "C" + strRow + ":L" + strRow
For Each vTemp In .Range(strCell)
If vTemp.Value = Empty Then

ElseIf DateInMonth(CDate(vTemp), dtReportMonth) Then
bFound = True
Exit For
End If
Next
If bFound Then
gnCurrentRow = nRow
GetName = strName
Else
nRow = nRow + 1
strRow = CStr(nRow)
strCell = "A" + strRow
strName = .Range(strCell)
If IsEmpty(strName) Then
gbLastRowHit = True
gnCurrentRow = nRow
GetName = ""
Exit Function
End If
End If
Wend
End With

End Function

Public Function GetJASID(ByVal Name As String) As String
Dim nRow As Integer, bFound As Boolean, strRow As String
Dim strCell As String, strName As String

nRow = 3
bFound = False

With Worksheets("Data")
While Not bFound
strRow = CStr(nRow)
strCell = "A" + strRow
strName = .Range(strCell)
If strName = Name Then
bFound = True
Else
nRow = nRow + 1
End If
Wend

If bFound Then
strCell = "B" + strRow
GetJASID = .Range(strCell)
Else
GetJASID = ""
End If
End With

End Function

Public Function DateInMonth(ByVal dtIntro As Date, ByVal dtMonth As Date) As
Boolean
Dim nMonth As Integer, nYear As Integer
Dim nIntroMonth As Integer, nIntroYear As Integer

nMonth = Month(dtMonth)
nYear = Year(dtMonth)
nIntroMonth = Month(dtIntro)
nIntroYear = Year(dtIntro)
If nMonth = nIntroMonth And nYear = nIntroYear Then
DateInMonth = True
Else
DateInMonth = False
End If

End Function

-------------------------------

 

[Tim Williams]

Your function "GetName" seems to be the problem. It's not really a good
idea to structure a UDF like this - it would be better to place this kind of
calculation in a sub called from the worksheet_change event or similar.

Tim