VBA Hyperlink

Would you like to create a hyperlink to your favourite website on a form using VBA? Thought so!
Open a new project in VBA and insert a new Userform.
Add a label to the form and change the Caption to the URL of your website.

e.g. http://www.afralisp.com

Change the colour of the label to Blue - Underlined.

e.g. http://www.afralisp.com

Now add the following coding to the click event of the label :

'<- Coding Starts Here

Private Sub Label1_Click()

Dim nFile As Integer

'Write a temp file URL

nFile = FreeFile

Open "\TEMP.URL" For Output As #nFile

Print #nFile, "[InternetShortcut]"

Print #nFile, "URL=http://www.afralisp.com"

Close #nFile

'Launch the browser

Shell "rundll32.exe shdocvw.dll,OpenURL " & "\temp.url", vbNormalFocus

'Delete the temp file

Kill "\TEMP.URL"

End Sub

' Coding Ends Here

Voila, you have a hyperlink on your form.

Control Template

Ever get tired of making an exit button over and over? Do you use the same set up for multi page (or any control) over and over? Save time, save effort. Make a control template!

1) Start with a New VBA Project in AutoCAD (or any VBA enabled application)
2) Add a UserForm to the project
3) Draw a command button on the form
4) If the properties window is not visible, click properties on the View Menu
5) Make sure that the properties for the command button are being listed in the properties window, then change the following items:
6) Caption = Exit
7) Font = Arial (example, use your favorite font)
8) From the file menu choose close and return to AutoCAD (or whatever application you are setting up).
9) Close AutoCad, when prompted to save the changes to Global1 say "yes" (save it under any name, you will open it right back up).
10) Restart AutoCAD and load the dvb file saved in step 7.
11) Drag the command button you customized in step 5 onto the controls tab of the Toolbox.

Command Line VBA

Did you know that you can run VBA statements from the command line?
Try this:

From AutoCAD's Command line

1. VBASTMT <enter>
2. ThisDrawing.PurgeAll <enter>

CD Rom Drive

Let VB determine if the CD Rom drive contains media.

To quickly determine if the CD Rom drive contains media, use the Scripting Runtime library's IsReady property for the Drive object.
For CD Rom drives, this property returns True only if the drive contains the appropriate media. To take advantage of this handy property, add a Reference to Microsoft Scripting Runtime library (scrrun.dll). Next, create a Drive variable based on the CD Rom drive, and test the IsReady property, as shown below :

Dim FSO As FileSystemObject
Dim CDDrive As Drive

Set FSO = New FileSystemObject
Set CDDrive = FSO.GetDrive("E:")
If CDDrive.IsReady Then
     MsgBox CDDrive.VolumeName
Else
     MsgBox "Please enter a CD."
End If

Set CDDrive = Nothing
Set FSO = Nothing 

Type-Declaration Characters

In addition to declaring a variable as its explicit data type,VB also lets you declare certain data types using a single type-declaration character.
For example, instead of using :

Dim MyString As String

you can use:

Dim MyString$

Here's a complete list of the data-types and their corresponding characters :

		String ($)
		Integer (%)
		Long (&)
		Single (!)
		Double (#)
		Currency (@)

Of course, you should use these characters with caution, as they do reduce your code's readability. 

Add Controls to a VB Control Array at Run-time.

As you probably know, a control array lets you create controls that share the same name and events. They also use fewer resources than do the same number of controls not part of a control array. Often, you may want to add a control, such as a button, to a control array at runtime. To do so, you use the Load statement, which takes the following syntax :

Load object(index)

where object is the name of the control array, and index is the index number of the new control you want to add. In order to add controls to a control array at runtime, however, you must have at least one control already in the array, (with it's index property set-most likely to 0). VB only allows 32,767 controls in an array.

For example, suppose you have a form with a button control array named cmdBtn. On the button's Click event, you want to add another button to the form. To illustrate, open a new project and add a command button to the default form. In the Properties Window, enter 0 for the control's Index. When you do, VB transforms the button into a control array. Now, add the following code to the form:

Private Sub cmdBtn_Click(Index As Integer)
Dim btn As CommandButton
Dim iIndex As Integer
iIndex = cmdBtn.Count
If iIndex <= 32767 Then
    Load cmdBtn(iIndex)
    Set btn = cmdBtn(iIndex)
    With btn
        .Top = cmdBtn(iIndex - 1).Top + 620
        .Caption = "Command" & iIndex + 1
        .Visible = True
    End With
    Set btn = Nothing
End If
End Sub

When you run the form, and click the button, the procedure adds a new button to the form.

Listbox - Deleting Items.

To delete selected items from a multiselect listbox, loop backwards through the items, then remove those where the Selected property tests True. For example, suppose you have a list box with item1, item2, item3, item4, item5. You've set the control's MultiSelect property to Extended or Simple and have selected item1, item3, and item5. To remove them from the listbox use code similar to the
following:

Private Sub cmdDeleteListItems_Click()
Dim i As Integer

For i = List1.ListCount - 1 To 0 Step -1
    If List1.Selected(i) Then List1.RemoveItem i
Next i

End Sub

Extending the Timer Control.

As you know, the Timer control provides a great way to schedule events in a VB project. When you enable the control, it fires off its Timer event every n milliseconds, as determined by the TimeInterval property. However, the TimeInterval property only accepts numbers up to 65,535, or just over one minute. As a result, you may have wondered how to use this control for periods longer than that.

To do so, use a form, or project level, variable to keep track of how many times the Timer event fires. Then, in the Timer event, re-enable the control if enough time hasn't passed. For example, consider the code below that we attached to a standard form.

Option Explicit
Dim iElapsedMin As Integer
Const cMax_Min As Integer = 2

Private Sub Form_Load()
Timer1.Enabled = True
iElapsedMin = 1
End Sub

Private Sub Timer1_Timer()
lblText.Visible = (iElapsedMin = cMax_Min)
Timer1.Enabled = (iElapsedMin < cMax_Min)
iElapsedMin = iElapsedMin + 1
End Sub

Create an Excel object in VB.

First, assign a new reference to your VB app, "Microsoft Excel 8.0 Object Library" - Excel8.olb

    Dim objExcel As New Excel.Application

    '-- Display Excel Application
    objExcel.Visible = True

    '-- Add new Workbook
    objExcel.Workbooks.Add

    '-- Set Text
    objExcel.ActiveCell(1, 1) = "Row 1 Col 1"
    objExcel.ActiveCell(1, 3) = "Row 1 Col 3"

    '-- Release the object variable
    Set objExcel = Nothing

Be sure to check out the Object Browser in VB for more properties/methods
of the Excel object.

Performing the Windows shutdown operation

Ever wondered how programs that you install automatically perform the Windows shutdown operation? Well, it's actually a simple API call to do this. Add the following API Declares and Constants to a BAS Module:

Declare Function ExitWindowsEx& Lib "user32" (ByVal uFlags&, ByVal
wReserved&)

Global Const EWX_FORCE = 4 'constants needed for exiting Windows
Global Const EWX_LOGOFF = 0
Global Const EWX_REBOOT = 2
Global Const EWX_SHUTDOWN = 1

Then you Shutdown/reboot/logoff windows with the following call:

lresult = ExitWindowsEx(EWX_SHUTDOWN, 0&) 'shut down the computer

Note: Replace the first parameter of the ExitWindowsEx function call with the appropriate CONSTANT.

A new Format function.

VB 5 has the Format command that almost works the same as Print.
The difference is that Format shortens the output string length if all the format characters are not used. To work around this I wrote a Public Function called FormatNum.

Public Function FormatNum(MyNumber As Double, FormatStr As String)

'This Function returns number formated as a string with the desired
'minimum number of characters
'MyNumber - Use CDbl(MyNumber) in the function call to prevent type
'mismatch error.
FormatNum = Format(MyNumber, FormatStr)
If Len(FormatNum) < Len(FormatStr) Then FormatNum = Space(Len(FormatStr) _
- Len(FormatNum)) & FormatNum
End Function

Use this function like this:

Print #FileNumber, FormatNum(CDbl(MyVariable), " #### ")

Highlight Text.

When you display a form, it's nice to highlight the text in a particular text box
so that the user does not have to delete the existing text before typing in his
new text string. Here's the coding :

**************************************************************************
Private Sub UserForm_Initialize()

	UserForm1.TextBox1.SetFocus
    	'set focus to the textbox

   	UserForm1.TextBox1.SelStart = 0
	'start at the first character

    	UserForm1.TextBox1.SelLength = Len(UserForm1.TextBox1.Text)
	'highlight the text string

End Sub
*********************************************************************

Double Click a List Box.

How do you create a double-click event for a list box?
Easy. From the Double-click event procedure of the list box, simply call the click event procedure of the OK button :

*******************************************************************
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
	CommandButton1_Click
End Sub
*******************************************************************
Private Sub CommandButton1_Click()
	'Your coding goes here..............	
End Sub
******************************************************************** 

Format Numbers.

Here's a quick tip that allows you to set the number of leading zeros, or to round to a certain decimal point. Let's say you want to print '1.234000' in '001.234' format. To do this, use :

	format(1.234000,"000.######")