# Solved: Passing Variables in VBA



## Carlos_Mendoza (Nov 12, 2011)

I have read a million tutorials and still can not wrap my head around it....What I want to do is create a base template something like this:

```
Public Sub Template()
    Sheets("varStar").Select
    Sheets("varStar").Copy
    Range("A37").Interior.ColorIndex = xlNone
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\My Documents\Book5.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
```
And be able to declare varStar in several modules, but still have the value of varStar pass to my Sub Template. The "Main" module as I will call it (the one that will call Template) will be called from a UserForm within Excel. So only one module will be passing variables to Template at a time.


----------



## Keebellah (Mar 27, 2008)

You can pass a variable to another macro as a parameter

You have your Public Sub Template()

Now you want to call another macro and want it to use the same varaiable

The other Sub OtherOne(varStar as String)

Inside this Sub you do not have to Dimension this variable again.

Paste the code below in the VBAProject and run Testing


```
Sub Testing()
Dim varStar as string, myAnswer as String

myAnswer=InputBox("Say something","",Default:="")

call Other(myAnswer)

End sub

Sub Other(varStar as String)

Msgbox varStar

End Sub
```


----------



## Carlos_Mendoza (Nov 12, 2011)

If I am reading your code properly, it looks like I would just need to call my other procedure and add in the parenthesis at the end the variable that I want to pass to it. For examples sake let's say in a seperate module I declared varHouse, then to pass varHouse to my Template module I would just need to code it as:

```
Public Sub Template(varHouse as String)
    Sheets("varHouse").Select
    Sheets("varHJouse").Copy
    Range("A37").Interior.ColorIndex = xlNone
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\My Documents\Book5.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
End Sub
```
Am I following you?


----------



## Keebellah (Mar 27, 2008)

Yes if you have another macro that 'sends' the varGouse value to this macro

Once again, see my sample code Testing I posted earlier


----------



## Carlos_Mendoza (Nov 12, 2011)

How would I take my Template Sub

```
Public Sub Template()
   Sheets("varStar").Select
    Sheets("varStar").Copy
    Range("A37").Interior.ColorIndex = xlNone
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\My Documents\Book5.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
```
And check to see if the worksheet in here exists


----------



## Keebellah (Mar 27, 2008)

You should start by adding Dim statements for all you variables.
If you're passing a variable the receiving parameter must be of the same type, then set it to Varian too.
Try and let me know, otherwise post the Excel file with the code, it's easier than just looking at small parts, I don't get the whole picture


----------



## Carlos_Mendoza (Nov 12, 2011)

```
Public Sub Master()
Dim varStar
Dim varStars

varStar = Array("Precipitation", "Wind_Speed")

For each varStar in varStars

call Template (varStar)

Next varStar

End Sub

Public Sub Template(varStar)
Dim WB As Excel.WorkBook

Set WB = Workbooks.OpenFileName=:("FileName to Open")
   Sheets("varStar").Select
    Sheets("varStar").Copy
    Range("A37").Interior.ColorIndex = xlNone
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\My Documents\Book5.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

End Sub
```
This is the code I am attempting to run, I want to somehow say that if varStar does not exist in the workbook that is opened, that is okay, just ignore it and keep processing.


----------



## Keebellah (Mar 27, 2008)

The code

Since you don't say anyhting about the filename I did it this way.


```
Option Explicit

Public Sub Master()
Dim varStar(), i As Integer
Dim strPath As String, fileName As String
strPath = ActiveWorkbook.Path
fileName = "The name you want.xls"
If Len(Dir(strPath & "\" & fileName)) = True Then
    varStar = Array("Precipitation", "Wind_Speed")
    For i = LBound(varStar) To UBound(varStar)
        Call Template(strPath & "\" & fileName, varStar(i))
    Next i
End If
End Sub

Public Sub Template(fullFilename As String, thisSheet As Variant)
    Dim WB As Excel.Workbook
    Set WB = Workbooks.Open(fullFilename)
    Sheets(thisSheet).Select
    Sheets(thisSheet).Copy
    Range("A37").Interior.ColorIndex = xlNone
    ActiveWorkbook.SaveAs fileName:= _
    "C:\Documents and Settings\My Documents\" & ActiveWorkbook.Name, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    ActiveWorkbook.Close True
End Sub
```
It's bedtime here, Good-night


----------



## Zack Barresse (Jul 25, 2004)

```
function WSEXISTS(wksName as string, optional WKB as workbook) as boolean
    if wkb is nothing then
        if activeworkbook is nothing then exit function
        set wkb = activeworkbook
    end if
    on error resume next
    wsexists = cbool(wkb.worksheets(wksname).name <> "")
    on error goto 0
end function
```
Here is a pretty fast function for checking whether a worksheet exists or not. You can call in another routine, an example looks like this...


```
Dim WB as workbook, WS as worksheet, sWSName as string
swsname = "Sheet1"
set wb = thisworkbook
if wsexists(swsname, wb) = true then
    set ws = wb.worksheets(swsname)
else
    set ws = wb.worksheets.add(after:=wb.worksheets(wb.worksheets.count))
    let ws.name = swsname
end if
```
HTH


----------



## Carlos_Mendoza (Nov 12, 2011)

Zack -- question about the Function you provided....

Could I set swsname = varStar and that way it will search my array of possible workbook names to determine if they exists

```
Dim WB as workbook, WS as worksheet, sWSName as string
swsname = (varStar)
set wb = thisworkbook
if wsexists(swsname, wb) = true then
    set ws = wb.worksheets(swsname)
else
    set ws = wb.worksheets.add(after:=wb.worksheets(wb.worksheets.count))
    let ws.name = swsname
```


----------

