ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

Visual Basic (Excel VB/VBA) Tutorial: How To Write Your First Program

Updated on June 23, 2012

This hub provides an introduction to Visual Basic, using embedded VBA capability found in Excel 2010. Let’s get started. We will need what is known as the Developer tab in Excel. Open up new Excel worksheet, which is given default name “Book1”. Immediately save it:

File name: VBA1

Save As Type: Excel macro-Enabled Workbook

Now we will write a simple and fun program. We will need to use the “Developer Tab”. In order to make it visible, do as follows:

1. File --> Options (click)

2. Customize Ribbon (click)

3. Check the “Developer” box and click “OK” at bottom.

4. You will now see “Developer” tab. Click it.

5. Section dealing with macros and VBA (Visual Basic) is now visible.

Next we place what is known as a command button. Do as follows: Click the “Insert” icon. Under “ActiveX Controls”, click the little box circled in red, see picture area labeled “A”. Then move your mouse down into the spreadsheet and click. A button will appear. Note the “Design Mode” icon is shaded (picture area “B”). We are in design mode, meaning we can edit the button without it activating anything. Once we get out of design mode, clicking on the button will start the program. Right click on the button and select “properties”. A properties window will open up, it will be kind of thin. You can widen it by placing mouse pointer over right edge and dragging right. ”. Note that if you click out on the spreadsheet, the properties window changes and is now showing properties for the spreadsheet. Re-select the button again to get its window back. See in the window that “caption” is set as “commandbutton1”. Click in the “commandbutton1” box to get a cursor, and change the caption name to “Press Me”. See that the button name has changed. In the properties box, click the “categorized” tab. Where you see “AutoSize” and “False”, edit to make it “True”. See that he button has sized itself to just fit the text. We will next put the button in a known position. In the properties box, edit the “Left” position to “396” and “Top” to “93”.

Close the properties box. Click on the “Design Mode” tab to get out of design mode, as we are done with the button. On the far upper left, click the “Visual Basic” tab. Nothing seen. As shown in picture section “C”, click the little “View Code” box and the grey area comes in white. In the drop down, select “CommandButton1” and the code appears as shown. There are only two lines of code, the name of the subroutine and the line “End Sub”. Place your cursor just to the left of “End Sub”, and hit enter 5 times. This will move it down and provide space for us to paste some code in. Now arrow key the cursor up 2 or 3 lines so it sits in between. Here is the code to paste in at the cursor:


Application.EnableCancelKey = xlDisabled 'prevents program from hanging up and giving error message

Dim wordstr As String, x As Single, y As Single, acell As String, iCount As Single 'Declare variables

Range("A1").Select 'start at cell A1 to get a reference position

For x = 1 To 5 'does 5 loops of message display

For y = 1 To 5 'picks the five words which make up the message

Select Case y

Case 1:

wordstr = "this" 'when y is 1, it displays "this"

acell = "G4" 'places it in cell G4

Case 2:

wordstr = "is" 'when y is 2, it displays "is"

acell = "I4" 'places it in cell I4

Case 3:

wordstr = "your" 'when y is 3, it displays "your"

acell = "K4" 'places it in cell K4

Case 4:

wordstr = "first" 'when y is 4, it displays "first"

acell = "K12" 'places it in cell K12

Case 5:

wordstr = "program" 'when y is 5, it displays "program"

acell = "G12" 'places it in cell G12

End Select

Range(acell).Select 'find the cell

ActiveCell.Value = wordstr 'place the word in it

Selection.ColumnWidth = 8 'set the column width to 8 to make sure the word fits

Application.Wait Now + TimeValue("00:00:01") 'one second delay

ActiveCell.Value = "" 'remove the word, clear the cell

Next y 'loop

Next x 'loop


Once you paste in the code, close the visual basic program editing box. Save your worksheet in order to keep the changes. Now hit the “Press Me” button and watch your first program run. It will loop 5 times then stop. I have added comments to the code to explain what each line does.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized. (Privacy Policy)
    CloudflareThis is used to quickly and efficiently deliver files such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisements has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)