Order of Calculation

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

What does your function look like?

Tim

--
Tim Williams
Palo Alto, CA


"John H W" <[email protected]> wrote in message
news:[email protected]...
> 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 - 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" wrote:

> What does your function look like?
>
> Tim
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "John H W" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

--
Tim Williams
Palo Alto, CA


"John H W" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > What does your function look like?
> >
> > Tim
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "John H W" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >