Excel Macros

[sbmahapatra]

Hi Experts,
I am new to VBA and currently assigned a Excel project which has got lots of VBA codes. The spreadsheet has got a Validate Button. On click on the button, the entire spreadsheet is validated. Here I have got many questions. Just to start with,
1. How does the VB code knows the cells (Rows, Cols)?
2. When clicked on the button, how does the code is executed? In other words, where do we specify which part of the code to execute in the Validate Button?
3. If there are errors, the cells get highlighted with didfferent colors. How does it happen?
4. How can I debug the code?

Understanding the above will give me a good start. Please try to help. Thank you alot in advance.

 

[Asala42]

- Close all other files.
- Right-click on the button and select "Assign Macro". It should show you the macro being used, as well as others available created by the previous user(s).
- Note the macro assigned. Hit 'Cancel'
- Hit Alt+F11. This should bring you to the VBA editor where this code was built.
- On the left hand side, you should have a "Project - VBAProject" section. One of these projects is your filename, containing sheets and modules. Checking modules first, look for the assigned macro (it likely starts with "sub macroname").

Once located, it's really just a matter of understanding the code. One thing you can do is to step through the macro. Click in the Sub Macroname() line and hit F8. Each time you hit F8 this will step through a line of code. You can switch back and forth between the editor and the xls file to see which lines of code are highlighting cells, etc.

 

[sbmahapatra]

Hi Asala42, Thank you for your reply. I am using Excel 2003. Is there any way I can disply the Line Numbers in the VBA Editor? Thank you again.

 

[shg]

Cursor position (line number and column) appears on the VBE's Standard toolbar (View > Toolbars ...)

 

[Asala42]

The line number should be referenced in the taskbar (under Standard, "ln 14, Col 1")

If your goal is to print though I'm not sure of a built in feature that will display the line number for all lines, at least without commenting them in yourself.

I believe there are some add-ins that can do this, but a simpler solution is to simply copy/paste the code into MS Word where you can more easily number lines (In 2010 it's an option under Page Layout - Line Numbers)

 

[sbmahapatra]

Hi Shg, Thank you for your reply. I went to View --> Tool Bars and checked the Standard is selected. Still I don't see the line numbers. Please let me know if I have to try it differently. Thank you.

 

[sbmahapatra]

Thank you Asala42.