Use variables defined in sub in my function

**niko79542** · 02-11-2013, 11:47 AM

Hi,

When I write a function, do I have to redefine my variables? What If i want to use a variable that I defined in my sub?

For example:

Sub test()

Dim Wa as Worksheet
Set Wa = Worksheets(2)

End sub

How do I use Wa in my function without redefining it?

**JosephP** · 02-11-2013, 11:55 AM

pass it to the function as an argument

**Norie** · 02-11-2013, 11:57 AM

You could either declare Wa publicly.

Dim Wa As Worksheet

Sub test()
Dim x As String

   Set Wa = Worksheets(2)


   x = MyFunction()

End Sub

Function MyFunction() As String
       MyFunction = Wa.Name
End Function

Or, which is probably much better, pass it as an argument to the function.

Sub test()
Dim Wa as Worksheet
Dim x As String
   Set Wa = Worksheets(2)

    x = MyFunction(Wa)

End Sub

Function MyFunction(ws As Worksheet) As String

       MyFunction = ws.Name

End Function

**RHCPgergo** · 02-11-2013, 11:57 AM

Hi niko,

not sure what you mean. You don't have to redefine your variable, but you can assign different values to it later on. After you assigned the value "Worksheets(2)" to your variable, you can use it instead of it. Like this:

 Wa.Range("A1").Value

is the same as

Worksheets(2).Range("A1").Value

If your want to change the value, you just make another "Set" statement. For example

 Set Wa = WorkSheets("Sheet1")

edit: never mind, I completely misunderstood.

Use variables defined in sub in my function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Use variables defined in sub in my function

Re: Use variables defined in sub in my function

Re: Use variables defined in sub in my function

Re: Use variables defined in sub in my function

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions