Microsoft Office Tutorials and References
In Depth Information
Choosing the Scope and Lifetime of Your Constants
Press the Enter key, which will cause Excel to place a set of parentheses after the Test6
macro name, and also will create the End Sub statement. Your macro so far will look
In the empty line between Sub Test6() and End Sub , type Dim myString As
String and press Enter.
Now is the time to define the myString variable, by telling VBA that it shall be
equal to the value in cell A1, which is the word Hello you entered in Step 2. To do
that, type the following line of code into your macro and press Enter: myString =
With your String variable defined, try entering its defined text into a few cells, starting
with cell B3. If you combine the variable with a space and the word “World”, you can
programmatically enter the text “Hello World” into B3. To do that, type this line of code
into your macro and press Enter: Range(“B3”).Value = myString & “ World!”
10. Just for fun, repeat the variable’s text three times in succession, which would be
HelloHelloHello, and tell VBA to enter that into cell B4. For the next line in your macro,
type Range(“B4”).Value = myString & myString & myString and press Enter.
As a third and final entry, show the text Hello and Goodbye in cell B5 by typing
this last line of code into your macro: Range(“B5”).Value = myString & “ and
Goodbye” . At this point, your macro is completed, and it will look like this:
Dim myString As String
myString = Range(“A1”).Value
Range(“B3”).Value = myString & “ World!”
Range(“B4”).Value = myString & myString & myString
Range(“B5”).Value = myString & “ and Goodbye”
12. Press Alt+Q to return to your worksheet.
13. Watch your new macro in action. Press
Alt+F8 to display the Macro dialog box.
14. Select the Test6 macro name in the large
window as shown in Figure 6-6, and click the