Skip to main content

Intro To Macro: Lets move the cell pointer!!!

HELLO THERE!!!

       How are you,guys? I hope everyone is fine and good always. This week I will introduce you guys to a new programming sort of feature I found when I was using Excel quiet some time ago. The feature actually simplifies your job when you have a lot of data in your Excel file. The feature is known as Excel Macro. There are many applications on how to use the macro that you can find on the net. For this post, I am going to show you guys on how to move the cell selector(the darkbox thing) to left,right,up and down.

Firstly, what you need is an Excel form. Next, you need to turn on developer tab. On how to do this, please do as instructed as in the link shown. http://www.addintools.com/documents/excel/how-to-add-developer-tab.html 

The developer tab of Excel



Next let's add some button. If you see, there are two types of buttons, one is forms button and another one is activex button. Choose the activex button and draw 4 buttons on your form.
Choose the activeX button not the form control

 Next, let's change the name of the buttons. The buttons default name is Command button. To change the name, right click on the button, go to the command button object and click edit.
Changing the name of the button
 You can also change the properties of the button by right clicking the button and choose properties. A properties pane will pop up. You can change many things such as button size and color in the properties name.
Properties pane

Double click one of the button, to open the visual basic editor as shown in the picture below. Now we can code the program that we want here.
Visual basic editor



There are two ways actually to code a macro, one is recording (i'll explain this in the other post) and another one is coding them. My personal opinion is to code the macro as it gives more flexibility of compared to recording them. Ok, now for the coding part:

1. This is the first thing you'll see in the editor when you double click one of the button

Private Sub Up_Click()

End Sub

It is quiet similar to Arduino style of coding, where the Private Sub Up_Click() is like a void loop in the Arduino. Any code is written between the sub and End Sub. Now the code we want to add is this:


Selection.Offset(-1, 0).Select

this one line code will move the code pointer up. If you change the -1 to 1, the cell pointer will move down. What about left and right? Simple just switch the 1 and 0 and you can move left and right. To move right:
Selection.Offset(0, 1).Select
 
if you change the 1 to -1, the pointer will turn left. Here is a demo video of the code in action.




You can try by changing the values of 1's and 0's to other value such as 2 or 5 and see what happens .Looks like that's all from me guys this week,see you guys next week and Happy Tinkering!!!






















Comments

Post a Comment

Popular posts from this blog

Arduino and Multi Function Shield: Pots,LEDs and buzzers

HELLO THERE!!!!!    How are you guys? I hope everyone is fine and in good mood always. This week, I continue my tinkerings with the Multi Function Shield. In this experiment, I used the potentiometer in the shield to control the LEDs in the shield. Without further wasting time, let's jump in straight to the experiment!! HARDWARE 1.ARDUINO UNO  2.MULTI-FUNCTION SHIELD SOFTWARE: 1. ARDUINO IDE CIRCUIT ASSEMBLY    The circuit assembly is fairly simple. Just attach the multi function shield on top of Arduino properly. Please attach the shield properly as failure to do so would cause the shield  not to function as intended. CODING THE ARDUINO: Let's refer to the multi-function shield image below: Referring to the image of the shield, it can be seen that the potentiometer (blue object near buttons) is connected to pin A0 (analog 0)     int pot = A0; //declaring the pot ...
Post a Comment
Read more

Arduino Beginner Experiments: Arduino and Light Blocking Sensor

HELLO THERE!!!     How are you guys? I hope everyone is fine and in a good mood always. This week, I would like to share with you guys my experiment on Arduino and Light Blocking Sensor. The objective of this experiment is to receive feedback from the sensor ( Digital and Analog) What is Light Blocking Sensor?      A linear hall sensor is a type of sensor which responds when the sensor is blocked out from light. The sensor is different for LDR's as LDR's responds to the amount of light it received while the light blocking sensor responds if the light is blocked out or not from the sensor.It can be used for both digital and analog measurements. HARDWARE: 1.Arduino UNO 2. Light Blocking Sensor SOFTWARE: 1.Arduino IDE CIRCUIT ASSEMBLY   Let's refer to the image of the blocking sensor below: The Connection of the Sensor to the Arduino is shown below: SENSOR  ----->       ARDUINO Signal   ...
Post a Comment
Read more

Arduino and Keyes KY-017 Mercury Tilt Sensor

HELLO THERE!!!     How are you guys? I hope everyone is fine and good as always. I am back in tinkering after a few months of hiatus( busy stuff :) ). So, today i would like to share with you guys on KY-017, which is a Keyes Mercury Tilt Sensor. KY-017 Sensor KY-017      The picture above shows the KY-017 Sensor from Keyes. It has 3 pins, A ground, A vcc, and A signal pin. If you look closely, there is a mercury ball inside the bulb like object. The mercury ball acts like a switch for this sensor. KY-017 Working Procedure: 1. When the sensor is not tilted, the mercury ball will be at the bottom of the bulb. This will complete the circuit and turns on the LED of the sensor. The output that is sent through the signal pin will be LOW. 2.When the sensor is tilted, the mercury ball will be at the top of the bulb(near at the sharper end). This will complete break circuit and turns off the LED of the sensor. The output that is sent through t...
Post a Comment
Read more