Re: Passing excel objects to subroutines as parameters

On Apr 14, 6:16 pm, RoyVidar <roy_vidarNOS...@xxxxxxxx> wrote:
"henryonyeagb...@xxxxxxxxxxx" <henryonyeagb...@xxxxxxxxxxx> wrote in
message <1176570214.225121.30...@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>:





I have the following procedure MoveGrandTotalLabel code below which
finds a specified cell value on an excel spread*** and then formats
the found cell and inserts text next to the found cell. The sub is
passed excel objects from have that have been intiated in the main
subroutine as shown below. The problem is that when i step through
the code as soon i get to the CellAddress = Cells.Find statement the
code simply exits the sub routine and proceeds with the next
statement in the main sub. When i copy and paste this exact same code
into the main sub it works with no problems at all. The cell value to
be searched is clearly on the excel spread***. The procedure
MoveGrandTotalLabel is in a different module to the
PublishDataInExcel procedure. Any suggestions on why this is
happening and possible solutions welcome

Public Sub PublishDataInExcel(dbrs As DAO.Recordset, dbfld As
DAO.Field, SheetName As String, firstdate As Date, lastdate As Date)
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Work***
Dim xlrng As Excel.Range

MoveGrandTotalLabel xlApp, xlWb, xlWs, xlrng, firstdate, lastdate

Sub MoveGrandTotalLabel(App As Excel.Application, Wb As
Excel.Workbook, Ws As Excel.Work***, rng As Excel.Range, firstdate
As Date, lastdate As Date)
Dim CellAddress As String
Dim Findstring As String
Findstring = "Grand Total"
rng.Value = Ws.name
Set Ws = Wb.Active***
rng.Value = Wb.name
CellAddress = Cells.Find(What:="Grand Total",
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set rng = Ws.Range(CellAddress) '.Address
If Not rng Is Nothing Then
FirstAddress = rng ' .Address 'ActiveCell.Address
Do
rng.Cut
rng.End(xlToLeft).Select
Ws.Paste
Set rng = Ws.Columns("A:A")
rng.EntireColumn.AutoFit
Set rng =ActiveCell.FindNext(rng)
Loop While Not rng.Value Is Nothing And ActiveCell.Address <>
FirstAddress
End If
Set rng = rng.Offset(0, 1).Range("A1").Select
rng.Value = dbrs.RecordCount & " assets Aquired in the selected
period " & firstdate & " - " & lastdate
End Sub

The problem, is that Cells (and also ActiveCell), are Excel objects
that need to be "anchored" in the relevant Excel objects, else they
will create sideffects like only working the first time, RT 1004
_method blah blah failed, keeping an instance of Excel left in memory
etc...

Without any testing, I would guess you could prefix Cells with either
your *** or application object, and activecell with your application
object.

CellAddress = xlApp.Cells.Find(What:="Grand Total", _
After:=xlApp.ActiveCell, LookIn:=xlValues, _
...

for more info, check out for instance thishttp://support.microsoft.com/default.aspx?kbid=178510

--
Roy-Vidar- Hide quoted text -

- Show quoted text -

Thank you very much for this tip it helped me rectify problem coding.
My problem now is when I step through the code and get to the
following line

Set rng =App.ActiveCell.FindNext(rng)

There is only one occurence of "grand total" in the spread*** so the
code should see this and follow the next line to bring the loop to a
stop and then proceed to the next lines which place text next to the
findstring. However this is not the case as the code immediately exits
the procedure before executing the remaining lines. How can i get the
code to check that there are no more occurences of the findstring
"grand total" and cleanly exit the loop display the text and then exit
the procedure. Thanks for your assistance greatly appreciated.

.