In this article a certain number of trade
names are given (Excel®, VBA® etc) to avoid putting ® everywhere I make
a point of saying how much I respect their authors and how much I thank them.
What we want to do
To make dialogue a micro controller with a PC, one can build an owner application in
Visual-BASIC, Delphi, C… it is long and one is never safe from a bug.
And then there is VBA Visual-BASIC-for-Application the programming language of
Excel.
It is then enough to write macro of some lines
to be simplified the life considerably.
The exposed example shows:
-
To transfer the contents from a cell towards the microcontroller. In this example, the transmitted
cell is A3 (column
A line 3) thus it is necessary that there is something in A3.
- Posting of this cell on the display
associated with the microcontroller.
- To transfer a message from the microcontroller towards Excel.
- Posting of this message in a cell of B3 reception.
We will consider that the cell sent can
contain characters as many as the buffer can receive, here is 32. (What we
defined in our program microcontroller)
We turn over a word of 20 characters, if it
makes less than 20, it is supplemented by spaces chr(32) . It is obvious that by incrementing
the variables Colonne and Line, the transfer can relate to a complete
array; but that exceeds the goal of our matter.
For you familiarise with VBA it exists a very good course, clearly,
pragmatic which will make it possible the electronics specialist to very
quickly control this language. Run which is quite simply formidable of
simplicity and facility. I make a point of thanking Mr. Cherbé who authorised me to quote it in this article. http:/cherbe.free.fr/
To communicate by the port series, we use a add on: Mscomm32.ocx
which is not installed with the various versions of Excel and that Mr Bill it is not nice!
One can get it either while going on the site
of Mr. Cherbé (see program XL_RS232) or while following www.martin2k.co.uk/vb6/vb6download4.php Or
the mscomm.ocx from Mcselec but I did’nt test. This OCX must be placed in the
c:windowssystem32 repertory. Program XL_RS232
copies it automatically but does not install it, to see further.
Construction of the macro .
In Excel you will have to activate the tool
bar Visual-BASIC
It is composed of 3 icons: an inkstand,
onetools box, a gauge and a square.
- To click on tools box - to take a command button, it takes shape
with the left button of the mouse and fleet on the grid Excel, it is called commandButton1 if you click above a screen VB appears.
On the left in top, the window of project, in
lower part the window of property, on the right the window of code which posts
the first sub.
VB and VBA authorise the implicit
declaration of variable what is aberrant, dangerous, stupid!
Also as of the opening of the program it is
necessary to write:
Option Explicit`to write this order before the first sub, in all the sheets, it wants to say that
the variables must be declared for this module, sheets.
--------------------------------
Private Sub CommandButton1_Click()
End Sub
Then it is necessary to ask VBA to add a userform
in the page of writing of code to click on the insertion index then userform.
To return on the Excel grid, click again the button to add:
Option Explicit
Private Sub CommandButton1_Click()
UserForm1.Show `to
show the userform
End Sub
In the properties of the button, modify Caption “to launch the transfer”
To click on userform1
in the window of project.
To open tools box @ if the É is not present to install Mscomm32.ocx (see higher)
to click on the index tools click on
additional controls and to seek “Microsoft communication control, version…” To
notch the box, the telephon must be posted.
To slip the telephone into the userform, to add 3 buttons and two textbox and two label
The userForm
contains
3 buttons: To send; to open Comm1; To close Comm1 icon:
2 labels à icon (ab|)
2 windows: Envoy; Receipt à icon (A)
1 Telephone
In the properties of Userform one will be able to change the colour, the size, the
title…
The property name
of the Open buttons Comm1 and Close Comm1 will be changed respectively into Opencomm1 and Closecomm1
their title (Caption) in the same way.
The caption
of commandButton1 will be changed of “To
send File” one will not change the name
The object communication has a certain number of properties; it should be checked
in the window of properties that those are well configured as follows:
CommPortthe
number of the serial port of the PC (ie=1)
DTREnableData
Transmission Ready Enable must be “true” to authorise the communication.
EOFEnable End
Of File Enable we thus do not use for our applications “false”
HandshakingDefines
the communications protocol used, 0=pas of protocol
InBufferSizeSize
of entry 1024 per default
InputLenDefines and
return the number of natures read in the buffer of reception by the property Input.
Si=0 reads the totality of the
buffer
InputmodeDefines
or return the type of data received by
the Input property. In text mode
NullDiscardDetermine
if the idle characters are transferred from the port towards the buffer of
reception.
OutBufferSize defines the size of the buffer of transmission in bytes.
ParityReplace defines the character returns which replaces a
nonvalid character in the flow of data in
the event of error of parity.
Rthresholdnot
used must be left to 0
RTSEnableAsk
to emit , false here
SettingsAdjustment
of the port series per default 9600 baud, No Parity, 1 bit of stop, word of 8
bits
Sthresholdnot
used must be left to 0
We quote these properties because the
assistance of VBA does not indicate
anything for the option telecommunication.
Result:
Material and microcontroller
used
The microcontroller used is ATmega32 assembled on
module XAMEGA32,
the power supply is
provided by a module PS200, these modules are available at www.sidena.com.
Bond:
In the event of problem with BASIC – BASCOM visit my site:
MsgBox ("attention, le
port série n'est pas ouvert")
End If
' setting with 0 of
the buffer -------------------------
MSComm1.InBufferCount = 0
CommandButton1.Caption =
"bien reçu"
End Sub
--------------------------------
Here it is all for Excel!
BASIC-BASCOM
' ------Jean-Pierre Duval
26-01-2006----------------- 'test la liaison série avec Excel 'utilisation
d'un module Xamega32 SIDENA '---------------------------------------------------------- $regfile="M32def.dat"' the used chip $crystal= 11052000 '----[LCD
configuration---- REMarks if not used]----- Config Lcdmode = Port' config4 bit Config Lcdpin = Pin , Db4 =Portd.4 , Db5 =Portd.5 , Db6 =Portd.6 , Db7 =Portd.7 , E =Portd.3 , Rs =Portd.2 ConfigLcd= 20 * 2 'i2C bus for the XAmega32 module from Sidena Config Sda =Portb.0 Config Scl =Portb.1 ConfigPortb.3 =Output
Led AliasPortb.3 '--RS232 configuration -------------------------------- $baud= 9600 Config Serialin = Buffered , Size = 32 Config Serialout = Buffered , Size = 32 EnableInterrupts '---sub andfunctions---------------------------------- DeclareSub Rsread()'read the data from the excel file DeclareSub Rswrite() ' ----variables ---------------------------------------- Dim Jbyte AsByte Dim Bytereceived(30)AsByte Dim Send AsString* 20 ' --chek of the module ok---------------------------- For Jbyte = 1 To 3'blinking of the led Set Led Waitms 200 Reset Led Waitms 200 Next Reset Led '---------------------------------------------------------
Main: Cls Locate 2 , 1 :Lcd" wait"'time to run Excel Etc.... Wait 5 Locate 2 , 1 :Lcd"lecture" Call Rsread
Locate 2 , 1 :Lcd"transfert:" Call Rswrite Waitms 500' Very important because you need to let the buffer flushes 'before the End End '------rswrite----RS232-------------------------- Sub Rswrite() Local Kbyte AsByte, Lbyte AsByte
Send ="ABCDEFGHIJK"
Kbyte =Len(send)
Kbyte = 20 - Kbyte For Lbyte = 1 To Kbyte'filling the string with space until len=20 Send = Send +" " Next Print Send' envoi par RS232 EndSub '------rsread----RS232------------------------ Sub Rsread() Local Rs_index AsByte
Jbyte = 0
Rs_index = 1' array starts to
1 in Basic-Bascom Locate 1 , 1 :Lcd"" Do IfIscharwaiting()= 1 Then'if 1 char is
waiting in the buffer Jbyte =Inkey()'read the ascii value of the character If Jbyte = 3 Then'Chr(3)=End of
Text ExitDo EndIf LcdChr(jbyte); Bytereceived(rs_index)= Jbyte'filling of the array Rs_index = Rs_index + 1 EndIf Loop EndSub