Excel/Macros

Advertisement


Question
Hey tom,



 Simply you are genius, I appreciate your kindness and your knowledge. Can you tell me one thing more that is the structure or learning style of macros because I am a developer and I don't know how to learn macros. what is the exact way of learning macros?



Again thanking you and kind regards

Ashish

Answer
Ashish,

VBA is pretty close to basis

Sub Countto10()
Dim i As Long, j As Long
For i = 1 To 10
 j = Int(Rnd * 100 + 1)
 if j < 70 then
   MsgBox "Count is " & i & " random number is " & j
 End if
Next i
End Sub

So this demonstrates using a loop and using a condition statement.

It does nothing with excel - but just demonstrates basic structure using loops and conditional constructs which are basic to most programming languages.

If I wanted to loop through the range A1:A10 and sequentially display the contents of that cell in a message box I could do

Sub Loop10cells()
Dim rng as Range, cell as Range
set rng = ActiveWorksheet.Range("A1:A10")
for each cell in rng
  cell.value = Int(rnd() * 600 + 1)
Next
End Sub

This shows creating a rng reference (a range object) to refer to a specific range of cells on the activeworksheet.

Then looping through those cells and placing a random integer in the cell.


this is a reference to John Walkenbach site.  He is one of the Key authors on Excel books.  He write his programming book for each version of excel and his Excel Bible and many of his other books.  You would do well to buy one of his programming books say from Amazon.  If you don't want to spend a lot of money, look at one that is for several excel versions ago.  95 percent of the information will be applicable even in the latest excel version.  If you want to work with the ribbon, then you should get a book no later than Excel 2007 when the ribbon was introduced.  

The URL is:  http://www.spreadsheetpage.com/

but buying a book is not the reason I picked his site.  He has excellent VBA and just Excel tips on his site which can show you great code examples for doing a lot of routine and non-routine stuff.  

On the above page, look for Excel hints. Look at the entries below "What Else Is Here?"

There are tips and downloads and the letter from David Hager have some really advanced tips.

So for me, the exact way of learning macros is to look at other peoples macros when you know what the macro is supposed to do.  I personally learned Excel VBA by using Reed Jacobson's introductory book.  It is pretty much a tutorial - so you could look up his book.  But I polished my skills helping people solve problems on the old Microsoft Excel newsgroups (no longer exist) and then I could read other people's solutions as well.  Doing is the best way to learn.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.