Some More Basics

Hi guys,
here are some basics which will help you always while writing the macro
1) To declare the workbook:
Dim wb1 As Workbook
Dim ws1 As Worksheet

2) To set the workbook to be used in program :

Set wb1 = Workbooks("Book1.xls")
Set ws1 = wb1.Sheets(1)

now, "ws1" will correspond to first sheet of "Book1.xls"
2) To write a value in a cell:
if i want to write "Hello excel" in cell B3 then just set the workbook as tole in 1 and 2

ws1.Cells( 3,2 ) = "Hello Excel"

here 3 --> row and 2--> column i.e. B in this case.

3) to take the value of the cell for operations (for eg : compare value with some constant):

Dim abc as integer
abc = ws1.Cells( 3,2 ).Value

This will assign the content of cell "B3" to variable "abc"
Next, I will give some sample code snippets to do magic with excel.
As always , please let me know anything you would like to do with excel @ kashiwar.h@gmail.com
Thanks,
Himanshu

Creating Button on Excel and attaching it with macro

Hi guys,

This is how you can create the button on excel and associate it with the macro you have wrote

For Excel 2003

step 1: Go to view

step 2: In Excel 2003 and in earlier versions of Excel, click Option Button on the Forms toolbar, and then draw the outline of the button on the sheet.

For Excel 2007

Step 1:Click the Microsoft Office Button, and then click Excel Options.

step 2: Click the Customize category, select All Commands in the Choose commands from list, select Option Button (form control), and then click OK.

Following Steps are similar in both versions

Step 3: Select any cell in the worksheet.

Step 4:In versions of Excel that are for Microsoft Windows, right-click the option button, and then click Assign Macro.

Step 5:In versions of Excel that are for Macintosh, press COMMAND while you click or press CTRL while you click, depending on your operating system version. Then, click Assign Macro.

Step 6: In the Assign Macro dialog box, click the name of the macro that you want to assign to the option button, and then click OK.

Now that we have learnt how to create a macro and assign it to a button on excel from next blog I will start giving small tips in the form of code snippets.

Let me know if there is anything specific that you want me address @ kashiwar.h@gmail.com

Take care,

Himanshu

Get Started

Hi there its me again,
I will now tell you how to start with the basic commands to create macros.

Step 1 : open an empty worksheet.
step 2: press "Alt+F11" or (Tools-->Macro --> visual basic editor)
step 3: In the VB editor select Insert--> Module
step 4: this is your interface where we can start writing codes (known as a macro).
step 5: the macro should have " Sub () " in the beginning and "End Sub" in the end.
this is how a basic structure of macro is created.
to run the macro you eith press "F5" or "Run" Button on the editor.

In next Tip I will talk about creating a small button on the spreadsheet itself and asscoiate it with the written macro.

Hey People

Hi Guys,
This is Himanshu Kashiwar. I am an Engineering Managment grad student in Syracuse University,NY. I am in my second year now.

During my summer internship with JP Morgan Chase I started developing some macros to help in process automation.I found this world very fascinating and thought I would share whatever I know about it with the world.

I will share small small code snippets and help examples in order for people to use them as guidence. Also, if you have any questions please do share it with me @ kashiwar.h@gmail.com I would try to solve it and share it with others on this blog itself.

I hope this excercise is benificial to all of us.

Thanks,
Himanshu