Solved: Help ...Passing Arguments

[Joker12]

I'd like to pass the argument: Workbooks(1).WorkSheets("Display #2"")

I'd like to assign the argument as follows but I know that won't work.

[VBA] ws = Workbooks(1).WorkSheets("Display #2"")

MyLastCell = LastCell(ws).Address(False,False)
[/VBA]
This function returns the last cell on a worksheet that I have implemented.

The function:

[VBA] Function Lastcell(ws as ???) as Range
Code
With ws
LastRow% = .cells.Find( more code
more code
End Function
[/VBA]
How do I define the argument so that I can Pass it???
The reason is I would like to use the function over different workbooks and sheets.

Any Help would be appreciated

Thanks

[Joker12]

I think I have figured this out. It seems to work.
Is it the correct way of doing it???


[VBA]MyLastCell = LastCell(Workbooks(1).WorkSheets("Display#2") ).Address(False,False) [/VBA]


[VBA]
Function Lastcell(ws as VARIANT) as Range
Code
With ws
LastRow% = .cells.Find( more code
more code
End Function
[/VBA]

[Ken Puls]

Hi Joker,

I would think that your variant should be substituted with Worksheet.

One problem I see, though, that I don't know what you've set MyLastCell to. If it's a string, then you'll need to return the address from the function. If it's a range, then you'll need to use the Set statment to use the function.

Here's a reusable function that I've adapated from some I use frequently. It will get you the last cell on any worksheet you specify. I've also included an example of how to use it with your sheet name.

[vba]Function GetLastCell(wks As Worksheet) As Range
Set GetLastCell = Intersect(wks.Cells.Find(What:="*", After:=wks.Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow, _
wks.Cells.Find(What:="*", After:=wks.Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).EntireColumn)
End Function
Sub test()
Dim rngLastCell As Range
Set rngLastCell = GetLastCell(Workbooks(1).Worksheets("Display#2"))
MsgBox rngLastCell.Address
End Sub[/vba]

Hope it helps,

[Joker12]

Thanks, I replaced "variant" with "worksheet" and my function seems to work the same way. Interesting that I can use either one. Learn something every day.

Joker12

[Ken Puls]

Actually, that's totally expected.

If you do not declare a type for a variable, it automatically sets it up as variant. Variants take the first data type assigned to them.

So stepping through the following will give you a data type of Variant/String:

[vba]Sub test()
Dim testvar
testvar = ActiveCell.Text

End Sub[/vba]

likewise, these are effectively the same
[vba]Dim testvar
Dim testvar as Variant[/vba]

The danger comes when you pass the wrong thing to your variant data type. Say a worksheet to something that should be a string. It will take it, and error further along in the code. The issue there is that it can be very hard to figure out why it's erroring at that point. Variants also take more memory than other data types.

For those reasons alone, and others as well, it's best to be as specific as possible.

HTH,

[Zack Barresse]

I'm going to pick on Ken a little here .. ( )

.. you pass the wrong thing to your variant data type.

Thing? You mean Argument?

.. a worksheet to something that should be a string. It will take it, and error further along in the code.

Something like that will error when you try to set the variable, providing the data type does not accept the current argument status .. not further along in the code.

[Ken Puls]

I'm going to pick on Ken a little here .. ( )

Thing? You mean Argument?

Yep. You didn't get that?

Something like that will error when you try to set the variable, providing the data type does not accept the current argument status .. not further along in the code.

You misread what I posted, I think... Look at the following:

[vba]Sub Bad()
Dim wsName As Variant
Set wsName = Worksheets(1)

'Errors here as msgbox cannot take a worksheet
MsgBox wsName
End Sub

Sub Bad2()
Dim wsName As String

'Errors here as string cannot take a worksheet
Set wsName = Worksheets(1)
MsgBox wsName
End Sub

Sub Good()
Dim wsName As String
wsName = Worksheets(1).Name
MsgBox wsName
End Sub[/vba]

Obviously neither of the bad procedures will work, but I'd rather have mine error out as shown in Bad2. In Bad, the msgbox could be miles down the code, leaving you scratching your head as to what's going on. Everyone who has ever coded has run into a situation where everything looks right but doesn't work because of something dumb, we all know that.

Personally, I'd rather have it gag on trying to assign the value or whatever to the variable, as that gives me a really good indication immediately of what went wrong. It also makes sure things will work better later.

That make more sense?

[Bob Phillips]

If you do not declare a type for a variable, it automatically sets it up as variant. Variants take the first data type assigned to them.

Not quite true, they take whatever data type is assigned to them. Consider this code

[VBA]
Dim myVar

myVar = "abc"
myVar = 17
[/VBA]

myVar starts as type Variant/Empty, it changes to Variant/String when the value "abc" is assigned to it, then becomes Variant/Integer when the value 17 is assigned to it. It is precisely because of the data typing looseness that it is a good idea to avoid variants wherever possible.

[TonyJollans]

Thing? You mean Argument?

Technically, I believe, parameters are passed and arguments received - but the terminology is not consistently used anywhere.

[Ken Puls]

LOL!

See Zack, look what you turned this into!

Bob, good points. Avoiding Variants as much as possible, I actually did not think if that, but now that you mention it, yes that makes complete sense. I would say that it doesn't change the problems in the code I posted, though, but rather somewhat reinforces my point on scratching your head... especially for a newer coder.

Tony, I've never been a terminology major. I think I'll stick with "Thing". It covers all the bases!

Thanks guys! (yeah, you too, Zack )

[Zack Barresse]

I've never had so much fun stirring a pot before.

[Ken Puls]

I just knew that you were going to want the last word, too!