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 and led pins int l1 = 13; int l2
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      ------>     Any Analog/Digital pin
Post a Comment
Read more

Arduino Tinkering : Controlling multiple LED with multiple potentiometers

HELLO THERE!!!!            How are you guys? I hope everyone is fine and well always. For this week's post, I would like to share with you guys a simple tinkering on Arduino, LED, and potentiometers. The objective of the experiment is to light up different amount of LED's with different intensity by controlling two potentiometers. Before starting, of course, we need to know what items are needed. The items needed are: 1. Arduino UNO   2. 4 to 5 LEDs 3. 2 Potentiometers The software used in this experiment is: 1. Arduino IDE. Now that all the hardware and software are obtained, let's go to the schematics of the circuit. The circuit is quite simple actually. The long legs of the LED's were connected to PWM pins while the shorter legs were connected to ground. The potentiometers however have three legs. The middle leg is connected to the analog pins (A0, A1) while the other two legs were connected to 5V and GND respectively. Don't w
Post a Comment
Read more