Vba For Excel Serial Communication With Arduino Mega

One of my first jobs was to write a VBA script in Excel to connect to a PLC (programmable logic controller) via a serial port to read in data for further analysis.

-->

This topic describes how to use My.Computer.Ports to receive strings from the computer\'s serial ports in Visual Basic.

To receive strings from the serial port

1. Initialize the return string.

2. Determine which serial port should provide the strings. This example assumes it is COM1.

3. Use the My.Computer.Ports.OpenSerialPort method to obtain a reference to the port. For more information, see OpenSerialPort.

   The Try..Catch..Finally block allows the application to close the serial port even if it generates an exception. All code that manipulates the serial port should appear within this block.

4. Create a Do loop for reading lines of text until no more lines are available.

5. Use the ReadLine() method to read the next available line of text from the serial port.

6. Use an If statement to determine if the ReadLine() method returns Nothing (which means no more text is available). If it does return Nothing, exit the Do loop.

7. Add an Else block to the If statement to handle the case if the string is actually read. The block appends the string from the serial port to the return string.

8. Return the string.

Example

This code example is also available as an IntelliSense code snippet. In the code snippet picker, it is located in Connectivity and Networking. For more information, see Code Snippets.

Compiling the Code

This example assumes the computer is using COM1.

Robust Programming

This example assumes the computer is using COM1. For more flexibility, the code should allow the user to select the desired serial port from a list of available ports. For more information, see How to: Show Available Serial Ports.

This example uses a Try..Catch..Finally block to make sure that the application closes the port and to catch any timeout exceptions. For more information, see Try..Catch..Finally Statement.

See also

First of all, Why Arduino Mega 2560?

When cheaper boards are available, why go with Arduino Mega? The main reason behind this is the additional features that are inbuilt with this board. First feature is the large I/O system design with inbuilt 16 analog transducers and 54 digital transducers that supports with USART and other communication modes. Secondly, it has inbuilt RTC and other features like analog comparator, advanced timer, interrupt for controller wakeup mechanism to save more power and fast speed with 16 Mhz crystal clock to get 16 MIBS. It has more than 5 pins for Vcc and Gnd to connect other devices to Arduino Mega.

Other features include JTAG support for programming, debugging and troubleshooting. With large FLASH memory and SRAM, this board can handle large system program with ease. It is also compatible with the different type of boards like high-level signal (5V) or low-level signal (3.3V) with I/O ref pin.

Brownout and watchdog help to make the system more reliable and robust. It supports ICSP as well as USB microcontroller programming with PC.

The Arduino Mega 2560 is a replacement of the old Arduino Mega, and so in general reference, it will be called without the ‘2560’ extension. Due to the many numbers of pins, it is not usually used for common projects but you can find them in much more complex ones like Radon detectors, 3D printers, temperature sensing, IOT applications, real-time data monitoring applications etc.

Arduino Mega 2560 Specifications

Arduino Mega – Basic Features

Arduino Mega	Features
Microcontroller	AVR ATmega 2560 (8bit)
Power Supply	7-12V (Inbuilt Regulator for Controller)
Digital I/O Pins	54
Analog I/O Pins	16
Total Digital I/O	70 (Digital + Analog)
Clock Speed	16 MHz (Factory set to 1Mhz)
Flash Memory	128 KB
SRAM	8 KB
Communication	USB (Programming with ATmega 8), ICSP (programming), SPI, I2C and USART

Arduino Mega – Advanced Features

Arduino Mega	Advanced Features
Timer	2 (8bit) + 4 (16bit) = 6 timer
PWM	12 (2-16 bit)
ADC	16 (10 bit)
USART	4
Pin Change Interrupt	24

Arduino Mega 2560 is also packed with additional features like Analog Comparator, External Interrupt & Software Interrupt, Power Saving Mode, Inbuilt Temperature Sensor, RTC and more.

Arduino Mega Pinout

Power pins :

Arduino Mega	Power Pins
VIN	Supply voltage (7-12V)
GND	Ground
5V Supply	For External hardware device power supply
3.3V Supply	For External low voltage hardware device power supply

Arduino Mega Pin Diagram

Arduino Mega Pin Configuration

Controller Pins:

RESET: (Reset input) A low level on this pin for longer than the 4 clock cycle will generate a reset. Arduino Mega has inbuilt reset circuit with push button to reset system and this pin can be used by other devices to reset controller.

XTAL1,XTAL2: Crystal (16Mhz) is connected to supply clock for controller with 2 bypass capacitor to ground.

AREF: This pin is used, when we use Adc for analog to digital conversion with external reference voltage for conversion and don’t want to use internal 1.1V or 5v reference.

Digital pins (70):

Digital Pins: From 0-53(digital) and 0-15(analog) can be use as input or output for digital transducer and output devices by pinMode() for pin direction, digtalWrite() to write pin and digitalRead() to read pin status.

Application:

Output devices : Relay, LED, buzzer, LCD and others.

Input devices: digital thermistor, push button, ultrasonic sensor, joystick and others

Example:

• Output low signal on Arduino mega board

pinMode(0,OUTPUT);

digitalWrite(0,LOW);

• Input read signal on Arduino mega board

pinMode(0,INPUT);

digitalRead(0);

Analog Pins (16):

Analog pins: From 0-15(analog) can be used as analog input pin for adc, if not used than it work as normal digital pin. It can be used by pinMode() for pin direction, analogRead() to read pin status and get digital value for analog signal, care must be taken for internal or external reference voltage selection and Aref pin.

Application :

Input devices: Ntc thermistor, sensors (like ldr, irled and humidity) and others

Example :

• INPUT analog signal on Arduino mega board

pinMode(0,INPUT);

analogRead(0);

Alternative Pins Function:

SPI Pins:

These pins are used for serial communication with SPI protocol for communication between 2 or more devices. SPI enable bit must be set to start communication with other devices.

Application:

Programming AVR controller, communication with others peripheral like LCD and SD card with four-line communication at high speed.

I2C Pins:

Digital pin 20 for SDA and 21 for SCK (Speed 400khz) to enable two wire communication with others devices. Function used are wire.begin() to start I2C conversion, with wire.Read() to read i2c data and wire.Write() to write i2c data.

Application:

Output devices : LCD and communication between multiple devices with two wire.

Input devices : rtc and others.

Example:

I2c master read data from slave

Wire.begin();

Wire.requestFrom(2, 1); //1byte data

Wire.Read();

PWM Pins:

Digital pin 2-13 can be used as PWM output with analogWrite() to write pwm value from 0-255.It’s alternative of DAC for low cost system to get analog signal at output by using filter.

Application:

Output devices: speed control of motor, light dimmer, pid for efficient control system.

Example:

• OUTPUT analog signal on Arduino mega board

pinMode(0,OUTPUT);

analogWrite(0,255);

USART Pins :

This pin is used for serial usart communication with pc or other system for data sharing and logging. It is used with serialBegin() to set baud rate setting and start communication with serial.Println() to print array of char on other device output.

Application:

Two controller communication, pc and controller communication, debugging with usart by serial monitor.

Windows 2000 vdi. Example:

Serial.begin(9600);

Serial.Println(“hello”);

Pinchange Interrupt Pins:

This pin is used for pin change interrupt. Enable bit of pinchange interrupt must be set with global interrupt enable.

Application :

Rotary encoder, push button based interrupt and others.

Example :

pinMode(0, OUTPUT);

pinMode(1, INPUT_PULLUP);

attachInterrupt(digitalPinToInterrupt(1), LOW, CHANGE);

Hardware Interrupt Pins :

Digital pin 18 – 21,2,3 hardware interrupt is used for interrupt services. Hardware interrupt must be enabled with global interrupt enable to get interrupt from other devices.

Application:

Push button for ISR program, wake up controller with external devices, sensors like ultrasonic and others.

Example:

pinMode(0, OUTPUT);

pinMode(1, INPUT_PULLUP);

attachInterrupt(digitalPinToInterrupt(1), LOW, LOW);

Arduino Mega Schematic Components:

DC Jack Power Supply :

External Supply for Arduino Mega from range 7-12 volt is given with this port. Arduino Mega R3 has a voltage regulator for 5v and 3.3v supply for Arduino controller and sensor supply.

AVR 2560 :

This is the main controller used to program and run task for the system. This is the brain of the system to control all other devices on board.

ATmega8 :

This controller is used for communication between the main controller and other devices. This controller is programmed for USB communication and serial programming features.

ICSP 1 (ATmega8) and 2 (AVR 2560):

It has features of programming using serial bus with AVR programmer using SPI communication. AVR 2560 is programmed to run the system and ATmega 8 is programmed for serial communication and programming.

Reset :

It has reset circuit with capacitor, button and resistor to reset the controller. A push button is used to get 4 cycle low signal on reset pin to get the controller in reset mode.

Crystal :

It has a crystal circuit with two capacitors and one 16 Mhz crystal for xtal pins 1 and 2 interfacing with avr 2560.

I2C :

It has features of I2C (two wire communication) with an external pull-up resistor.

USART :

It has TXD and RXD pin for serial communication with LED indicator.

Some Simple Programs to try on Arduino Mega 2560

Program 1: Blinking LED (Digital pin)

Program 2: Led Light Dimmer (PWM):

Program 3: Analog Read Voltage (Analog pin with USART) :

You may also try your own logic and implement programs for Arduino mega with basic C and Arduino function.

...'>Vba For Excel Serial Communication With Arduino Mega(24.02.2020)