Variable Not Defined

[PSULionRP]

I am dynamically executing this function with a variable I want to calculate
within the Function. With this case, the last active row in an Excel
spreadsheet so that I can dynamically add a TOTAL Row.

The call looks like this...

GetLastActiveRow (0)

And the Function is defined as such...

Function GetLastActiveRow(pLastRow As Long)
Dim strAddress As String
Dim lngTotalRow As Long
Dim rngTable As Range
lngTotalRow = 1
Range("A2").Select
Set rngTable = ActiveCell.CurrentRegion
strAddress = rngTable.AddressLocal
lngTotalRow = CLng(Mid(strAddress, InStrRev(strAddress, "$") + 2))
lngTotalRow = pLastRow
End Function

When I return, I want to use the "pLastRow" variable and it is not allowing
me to do so.

GetLastActiveRow (0)
Dim myRange As String
Dim lngTotalRow As Long
lngTotalRow = pLastRow + 2
myRange = "A" & lngTotalRow
xl.Range(myRange).Select
xl.Selection = "TOTAL"
myRange = "D" & lngTotalRow
xl.Range(myRange).Select.Formula =
"=SUM(D3" & pLastRow & ")"


What do I have to do to pass this number back so that I can create my Excel
formula???

Thanks for your review and hopeful for a reply.

PSULionRP

 

[Jeff Boyce]

This sounds like an Excel question. This newsgroup supports MS Access, the
relational database.

Have you posted to an Excel newsgroup?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Daryl S]

I'm not sure if I'm reading this correctly. You have the function written to
allow a variable to be passed in, but not one to be passed out. In Access,
if you want to pass a value back from a function, then the function needs to
show the type of the variable passed back after the parentheses, while values
passed in go within the parentheses, like this:

Function GetLastActiveRow(pLastRow As Long) As Long

Then within the code, you set the result to return to the name of the
function, like this:
GetLastActiveRow = lngTotalRow

Finally, in your call to the function, you return the amount to a variable:
lngLastRow = GetLastActiveRow (0)

Then you can use this lngLastRow in your code.