how to call a function, and send across a worksheet object

**aaronkoh** · 12-27-2012, 03:34 AM

Hi, i would like to find out how to send across a worksheet object when calling a function.

My codes are as follows

___________________________________________
Private Sub EditUserCommandButton_Click()

Dim ws As worksheet

Set ws = ThisWorkbook.Sheets("LogInDataBase")

lastline (ws)
____________________________________________

Public Function lastline(ByVal ws As worksheet) As Integer

End Function
____________________________________________

when i run the program, it stops on the line in which i call the function, and displays run time error 438. object doesnt support this property or method

also, can i find out how to initialize the value of the following string

hello"" my name is xxx

string = "hello""my name is xxx"

the additional quotes within my string is causing a problem. how do i tell the editor that the quotes are part of my string.

Thank you

**OllieB** · 12-27-2012, 03:57 AM

Your function is defined as returning an integer so the statement

Please Login or Register  to view this content.

is not permissible as there is no place to store the returned integer value. Use either of the two possibilities below

Please Login or Register  to view this content.

With regards to the quotes question, I am not really sure what you are trying to achieve. If you want to use quotes in your text then using Chr$(34) is easier than using the " character itself

**JosephP** · 12-27-2012, 04:09 AM

you can also remove the parentheses

Please Login or Register  to view this content.

if you are not interested in the return value

**ag273n** · 12-27-2012, 04:12 AM

Hi aaronkoh,
about this part

Originally Posted by aaronkoh

when i run the program, it stops on the line in which i call the function, and displays run time error 438. object doesnt support this property or method

also, can i find out how to initialize the value of the following string

hello"" my name is xxx

string = "hello""my name is xxx"

the additional quotes within my string is causing a problem. how do i tell the editor that the quotes are part of my string.

Thank you

if you want the name "xxx" replaceable

then you do this:
-----------------------------------------

Sub greeting()
dim greetstring, namestring as string
greetstring = "Hello, My name is "
namestring = "aaronkoh"
msgbox (greetstring & namestring)
end sub

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

the qoutations (aka speechmarks) - "" - are only required if you're refering to the actual words you want to appear. the words have to be inside the speechmarks
*try and run the macro i have above and have fun with it

**aaronkoh** · 12-27-2012, 04:33 AM

Hi, i have tried making the following, but it still gives back the same error.

what i am trying to do is to pass the worksheet object over to a function. the function will then work with reference to the data on this worksheet, and finally return an integer.

Private Sub EditUserCommandButton_Click()

Dim ws As Worksheet
Dim result As Integer

Set ws = ThisWorkbook.Sheets("LogInDataBase")

result = lastline(ws)

End Sub

**JosephP** · 12-27-2012, 04:37 AM

please add code tags to your post

**aaronkoh** · 12-27-2012, 04:43 AM

Hi, the code worked. thanks. the reason is i forgot to change the function variable type back from string to integer.

Thank you all.

Also, for the second problem, what i am trying to achieveis to allow for the initializing of a string.

the string content, however is to include characters such as ""

e.g. string = "this string attempts to include symbols such as "" within the string"