Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Syntax to use variables

0
Code_Goes_Here
Set importSheet = Sheets("Import") 'worksheet to copy data from Code_Goes_HereSet destinationSheet = Sheets("backup") 'worksheet to paste new data

Can someone please send me the correct syntax to use a variable in both the workbook and sheets argument
workbooks.("as variable").sheet("as variable") to make the above code example work? Please try not to laugh I'm just starting with VBA...Thanks
Answer
Discuss

Discussion

Hello Sam,
Welcome to the forum.
There are two ways to correctly set code tags in your posts here, of which I hope there will be many.
One is to copy the code to the clipboard, click on the Code icon, select "Code does here" and paste the code from the clipboard.
The other is to first paste the code to the text of your intended post, select it, and click on the Code icon last.
The second way doubles all carriage returns in the code, giving double spacing, that becomes visible only after you post. That's why I always use the first method which lets the code appear in the post just like the original although it inserts double lines before posting. (Don says that's exactly how he wants it.)
Variatus (rep: 4889) Jul 20, '21 at 7:18 pm
Thank you for the information I will comply with your request
ssammito (rep: 2) Jul 21, '21 at 6:04 am
Add to Discussion

Answers

0
Selected Answer

Variables can be set with a local, public or global scope, depending upon where they are set and how they are declared.

  • A variable declared with the Dim statement within a procedure is "local". It is recognized only within the procedure in which it is declared. VBA will delete it when the procedure has run to its end.
    • A variable declared within a procedure but using the Static statement has the same scope but retains its value between calls, even while the user may run other code in other workbooks.
  • A variable declared using the Dim or Private statement outside a procedure is of public scope, meaning it's available to all procedures within the same module.
    • Most programmers prefer to use the Private statement for such declarations and use the Dim statement exclusively for local variables.
  • A variable declared outside a procedure using the Public statement will be available to all procedures within the project in which it is declared.
    • Public and Private declarations should be placed at the top of a code module, before any procedures.

There is no way to declare a variable with an even larger scope, such as to let it be available throughout all VBA projects in all open workbooks, but there is a simple way for achieving that effect:-

Public Function MyName() As String
    MyName = "John Doe"
End Function

The above function can be called from any workbook, effectively making its value available everywhere. The function can be constructed to return a different value depending on which workbook it is called from, such as the value of Sheet1!A1, or the name of the person who created the workbook.

The location of the function further reduces or expands its scope. For example, it might be placed in a Personal workbook that loads when Excel is opened or it could be in another workbook that can be accessed using code in other workbooks that need it.

Discuss

Discussion

@ Variatus. Nice answer but I guess the accursed autocorrect prevented the first line from reading "local, public or global scope" 
John_Ru (rep: 6092) Jul 21, '21 at 8:27 am
@John You must have been reading my mind rather than my post. But I fixed it. Thanks for pointing out.
Variatus (rep: 4889) Jul 21, '21 at 9:23 am
Add to Discussion
0

Sam

In addition to the fine answer from Variatus, I suggest you see Don's tutorial Variables in Macros VBA for Excel - Practical Guide on this site.

For your requested example, the use of Set importSheet= suggests that importSheet itself has been declared as a variable (e.g. by Dim importSheet as Worksheet). Likewise for destinationSheet .

To declare your two variables (in a macro in one workbook) use the code below, but have another workbook open at the same time (say called ABC.xlsx) with a sheet called Sheet1 and a known value in cell G3 of that sheet. You can change the bits in bold below to suit your case, run the code and the message box will display that value

Sub Decl()

Dim WsName As String, WbkName As String

WbkName = "ABC.xlsx"
WsName = "Sheet1"

MsgBox Workbooks(WbkName).Sheets(WsName).Range("G3").Value

End Sub
Hope this helps
Discuss

Discussion

Thank you
ssammito (rep: 2) Jul 21, '21 at 9:02 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login