Convert VB code to VBA

picklefactory

Active Member
Joined
Jan 28, 2005
Messages
412
Morning folks (At least it is where I am). Hoping for some guidance please.
Not sure if this is the best idea or even possible, but here goes. Sorry if its a ramble and a long post, but there's information here that is beyond my meager experience and skills, but may well be perfectly clear and helpful to you wizardly experts.
I have a snazzy electrical relay that is controllable through a USB via PC with an output of simply on or off. It is supplied with some bespoke software to control it by simple duration periods of on/off that is of no use for my purpose, as that is really just for basic electrical formats.
However, it also comes with advanced instructions that it can be controlled via a Windows VB form and a very basic example form (And code) is also supplied as a starting point for programmers, that functions perfectly and triggers the relay as expected. I'm hoping to be able to create an Excel WB and user form to set detailed calendar times over a 7 day period, to trigger the relay, which sounds perfectly feasible in my head..... but I may just be a poor, disillusioned soul. The WB would need to run 24/7 and continually loop the UDF over the week. There may be better options to do this, but I know nothing about VB and creating forms to run via Windows. I think I know enough VBA to create an Excel WB and user form to set the timings, but first I need to be able to convert the VB code to something that will run in Excel and trigger the relay, if that is indeed possible at all.
Although the sample form supplied is quite basic, even that is more complex than I require. The sample Windows VB form has 6 functions, I only need 3 (There are various other relays available with many more outputs than mine). I need to initialise the form (Probably only once), I need to switch on Output 1 and switch off Output 1. The code for the sample Windows form is enclosed here in it's entirety. I gather the majority is simple creating the form (It was created in MS Visual Studio, apparently), but I don't wish to assume, so I'm posting the lot. The bits (I think) I'm interested in converting to VBA are highlighted in green.
My initial intention, just to test the entire notion, is to create a simple 3 cmdbutton userform, with 'Initialise', 'Output 1 On' and 'Output 1 Off' if I can get the code converted and make that test form work. If I can get that far, then expanding it to work from set days/times I think I can manage later.
I have also enclosed the programming instructions from the manual, as I think that would be meaningful for you guys. The other question I have, is would there be any variation on the code between 32 and 64 bit operating systems. I think this may well end up on a 32 bit system on a spare pc, but I will be testing on my pc which is 64 bit.
Any advice gratefully accepted, I won't be able to test anything now till Monday, but hoping to maybe learn something over the weekend or at least have some direction.
Thanks folks

Code:
Public Class Form1
    Inherits System.Windows.Forms.Form

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Region]#Region[/URL]  " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents InitialiseMinibee As System.Windows.Forms.Button
    Friend WithEvents output1 As System.Windows.Forms.Button
    Friend WithEvents output2 As System.Windows.Forms.Button
    Friend WithEvents output3 As System.Windows.Forms.Button
    Friend WithEvents alloff As System.Windows.Forms.Button
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents op1to4 As System.Windows.Forms.Button
    Friend WithEvents Label2 As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.InitialiseMinibee = New System.Windows.Forms.Button
        Me.output1 = New System.Windows.Forms.Button
        Me.output2 = New System.Windows.Forms.Button
        Me.output3 = New System.Windows.Forms.Button
        Me.alloff = New System.Windows.Forms.Button
        Me.Label1 = New System.Windows.Forms.Label
        Me.GroupBox1 = New System.Windows.Forms.GroupBox
        Me.op1to4 = New System.Windows.Forms.Button
        Me.Label2 = New System.Windows.Forms.Label
        Me.SuspendLayout()
        '
        'InitialiseMinibee
        '
        Me.InitialiseMinibee.Location = New System.Drawing.Point(80, 24)
        Me.InitialiseMinibee.Name = "InitialiseMinibee"
        Me.InitialiseMinibee.Size = New System.Drawing.Size(112, 23)
        Me.InitialiseMinibee.TabIndex = 0
        Me.InitialiseMinibee.Text = "Initialise Mini-Bee"
        '
        'output1
        '
        Me.output1.Location = New System.Drawing.Point(88, 144)
        Me.output1.Name = "output1"
        Me.output1.Size = New System.Drawing.Size(104, 23)
        Me.output1.TabIndex = 1
        Me.output1.Text = "Output 1 On"
        '
        'output2
        '
        Me.output2.Location = New System.Drawing.Point(88, 184)
        Me.output2.Name = "output2"
        Me.output2.Size = New System.Drawing.Size(104, 23)
        Me.output2.TabIndex = 2
        Me.output2.Text = "Output 2 On"
        '
        'output3
        '
        Me.output3.Location = New System.Drawing.Point(88, 224)
        Me.output3.Name = "output3"
        Me.output3.Size = New System.Drawing.Size(104, 23)
        Me.output3.TabIndex = 3
        Me.output3.Text = " Output 3 On"
        '
        'alloff
        '
        Me.alloff.Location = New System.Drawing.Point(88, 320)
        Me.alloff.Name = "alloff"
        Me.alloff.Size = New System.Drawing.Size(104, 23)
        Me.alloff.TabIndex = 4
        Me.alloff.Text = "All Outputs Off"
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(64, 56)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(176, 32)
        Me.Label1.TabIndex = 5
        Me.Label1.Text = "Initialise Mini-Bee before using any of the output buttons below"
        '
        'GroupBox1
        '
        Me.GroupBox1.Location = New System.Drawing.Point(56, 120)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(176, 240)
        Me.GroupBox1.TabIndex = 6
        Me.GroupBox1.TabStop = False
        Me.GroupBox1.Text = "Outputs"
        '
        'op1to4
        '
        Me.op1to4.Location = New System.Drawing.Point(92, 272)
        Me.op1to4.Name = "op1to4"
        Me.op1to4.Size = New System.Drawing.Size(104, 23)
        Me.op1to4.TabIndex = 7
        Me.op1to4.Text = "Outputs 1 to 4 On"
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(112, 376)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(56, 16)
        Me.Label2.TabIndex = 8
        Me.Label2.Text = "Ver 3.2"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(280, 398)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.op1to4)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.alloff)
        Me.Controls.Add(Me.output3)
        Me.Controls.Add(Me.output2)
        Me.Controls.Add(Me.output1)
        Me.Controls.Add(Me.InitialiseMinibee)
        Me.Controls.Add(Me.GroupBox1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  Region

[COLOR="#00FF00"]    Declare Function InitMbee Lib "mb.dll" () As Boolean
    Declare Function SetOutputs Lib "mb.dll" (ByVal outputs As Integer) As Boolean


    Private Sub InitialiseMinibee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InitialiseMinibee.Click
        InitMbee()          ' initialise the Mini-Bee USB comms
    End Sub

    Private Sub output1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles output1.Click
        SetOutputs(1)       ' bit 1 is on to turn on output 1
    End Sub[/COLOR]

    Private Sub output2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles output2.Click
        SetOutputs(2)       ' bit 2 is on to turn on output 2
    End Sub

    Private Sub output3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles output3.Click
        SetOutputs(4)       ' bit 3 is on to turn on output 3
    End Sub

    Private Sub op1to4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles op1to4.Click
        SetOutputs(15)       ' set outputs 1 to 4 inclusive ON
    End Sub

[COLOR="#00FF00"]    Private Sub alloff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles alloff.Click
        SetOutputs(0)       ' set all outputs off
    End Sub[/COLOR]
End Class

Programming instructions direct from the manual

Provided with the MiniBee relay is a DLL (dynamic link library) called
“mb.dll”. This encapsulates the functions used in communicating with
the relay across the USB interface into a few simple functions easily
understood and used in custom software. Although the DLL was written in „C‟ it can
be used (called) by programs written in a number of popular languages including
BASIC (visual BASIC etc..).



6.1 Writing Visual Basic Programs for the relay
The DLL provides a general-purpose interface that greatly simplifies the task
of writing programs for a USB device. It can be tricky manipulating the USB comms
into sending and receiving messages to and from a device, which can easily be
plugged and unplugged at any time. The DLL eliminates all of these headaches by
simplifying the task into two library functions.
InitMbee() and SetOutputs(outputs)



InitMbee () is called somewhere near the start of your program and takes care
of all of the USB comms initialisation and prepares the MiniBee for receiving
messages.



SetOutputs(outputs) can then be called at any time during your program to set
the output pattern of on's and off's. The parameter Outputs is simply a 32 bit integer
value where bit0 corresponds to output 1, bit 1 to output2, etc... Where a logic value
of 1 turns the output on and a value of 0 turns it off. For example the statement below
would turn on the first three outputs (ie the first three relays)...
SetOutputs(7)
The only other thing that a VB program must do is to declare the functions
that it is going to use within the DLL and the name of the DLL itself. This must be
done at the start of your program or at least before any references to the two functions
are made. The following is an program excerpt showing how this is done...



Declare Function InitMbee Lib "mb.dll" () As Boolean
Declare Function SetOutputs Lib "mb.dll" (ByVal Outputs As Integer) As Boolean



The first declaration states that the function BeeInit has no parameters, is found in
mb.dll and returns a boolean value. The second states that SetOutputs has one integer
parameter passed by value rather than reference, is found in mb.dll and also returns a
boolean value. It should be noted that the ….Lib "mb.dll"…. part lets the program
know where to find the mb.dll file. When written like this it assumes that since there
is no path information that the bee.dll file can be found in the windows system
directory c:\windows\system32 If you like you can copy the file mb.dll on the
installation disk to the system32 directory and the above statement will work
perfectly. Alternatively you can copy the file to some other location and give that
location in the declaration as below...


Declare Function InitMbee Lib "c:\library\mb.dll" () As Boolean
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,127,008
Messages
5,622,140
Members
415,880
Latest member
Bruce0203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top