Bookmark and Share




Visual Basic 2010 / SQL - Scripting (2020)
visual basic 2010




BohGillDo


Visual Basic Script

VBA Macros

An implementation of VBA is built into all MS Office applications. It shares the same core VB language but has different available objects in each application. Word has an ActiveDocument object and Excel has an ActiveSheet object. It runs within a host rather than as a standalone application with VBA because it has not native compiler. But we can create a script, hidden within the document file, and so on.

Let's launch MS Word or any other Office 2010 application, then click the Developer tab and choose the Visual Basic ribbon to launch Visual Basic Editor.


DeveloperTabVB

Developer tab is not visible by default. So, we need to activate it. File => Options => Customize Ribbon => Developer check box.


CustomizeRibbon

After we choose the Visual Basic ribbon icon, we get the Visual Basic Editor.


VisualBasicEditor

In the editor, type in code something like this. Then, run it.


FirstVBAScript

Then, we get:


FirstVBARun




Word Macro

Bookmarks can be inserted into a Word document. It indicates the position at which a macro should insert content.

  1. Open a new document in Word. Then, type lines of text and Bookmark to add a bookmark by using Insert. Name it "MyBookmark".
  2. Open Visual Basic Editor (Developer => Visual Basic).
  3. Click Insert => Module to open Editor.
  4. Add code and run as in the picture below.

BookmarkMacro

BookmarkMacroWord




Excel Macro

We can insert values into cells of an Excel spreadsheet by a macro that uses a loop to move through a range of cells.

  1. Open a worksheet of Excel and then click Developer, Visual Basic to launch the Visual Basic Editor.
  2. In the Editor, click Insert, Module to open the Editor window.
  3. Write code into the Code Editor. Then, Select any cell in the worksheet. Then, click the Run. We will set blue month names appear in cells down the current column, starting at the selected cell.

    ExcelMacro

    ExcelMacroRun





Excel Macro - Automatic Run/Shortcut/ActiveX Control

Excel macros can be run automatically when a Worksheet gets loaded.

  • In the Project Window, right-click on the ThisWorkbook icon and choose View Code from the context menu.

    ThisWorkbook

  • From the dropdown list at the top of the Code Editor, select the Workbook item then add this code: 
    MsgBox("Workbook opened at " + Str(Time))

    Workbook_Open

    We will see the message whenever WorkSheet gets loaded.


    Workbook_Open_Message

  • To make a shortcut key, we should add the Macro name and then hit Create button.


    WorkbookOpenMacro

    Then, add a shortcut key letter to the Ctrl+ statement in the Options dialog.


    WorkbookOpenMacroShort

  • To use ActiveX Control, Developer tab => Insert => ActiveX button control. Then click an empty cell to place a Button control there.

  • Double-click the Button control to open the Code Editor in its Click event-handler. Then, add the call statement.

    Private Sub CommandButton1_Click()
Call AddMonthNames
End Sub

    ActiveXButtonClick

  • So, when we click the ActiveX button "Add Month Names", we get another column of month.


    ExcelTwoColumns





VBScript

Visual Basic Script (VBScript) - This is a derivative of Visual Basic. It is the default language for Active Server Pages (ASP). It can be used in Windows scripting and client-side web page scripting for IE. Although it resembles VB in syntax, it is a separate language and it is executed by vbscript.dll as opposed to the VB runtime. ASP and VBScript should not be confused with ASP.NET which uses the .NET Framework for compiled web pages. VBScript is a scripting language and it shares the same core VB language. Scripts written in VBScript are interpreted by a script engine that processes the instructions to execute the script. The script engine can be invoked either from within the Windows GUI or at a Command Prompt, or IE.

  • Open any text editor and type in the following code:

    MsgBox "Hello, my first VBScript!", vbExclamation

  • Name this file Hello.vbs and save it.

  • Double-click the file to run. Then it will be executed by vbscript.dll (Windows Based Script Host) and we'll see:


    VBScriptRun

  • How about VBScript inside html file:

    <html>

<head>

<title>VBScript in Internet Explorer</title>

<script type = "text/vbscript">

Sub myBtn_OnClick
myForm.myMsg.value = "Hello, my first VBScript!"
End Sub

</script>

</head>

<body>

<h3>Click the Button to see a message appear.</h3>

<form  name = "myForm">
<input type = "text"   name = "myMsg" size = "50">
<br>
<input type = "button" name = "myBtn" value = "Go">
</form>


</body>

</html>

  • Open the file, MyFirstVSScript.html, using IE and click the button to run the script.


    VBScriptIE