n00bie question

[rudejohn]

(a) I'm new to VBA programming (mostly Java background) and I was wondering if there was a good "tutorial" starting out. The context of my work: I'll be doing text manipulation, mostly. I will be pasting worksheets in from other workbooks and I'd like to create Macros that will manipulate the data.

(b) Specifically, I want to go through columns and make changes. For example... go through column A and remove all semicolons. Go through column B and append the word "foo" to the cell contents. Merge the contents (text strings) of two cells.

Sorry for such a beginner's question... I did searches but most of these posts are so specific that they didn't help a whole lot...

Thanks in advance,

Rudejohn

************
RudeJohn
************

 

[xlbo]

Best way to start is to utilise the MAcro Recorder

Tools>Macros>Record New Macro


This records your movements, mouse clicks and keyboard entries - it then translates these into VBA. Whilst it ususally generates excess code, it should give you a good idea of the objects and methods you will need to look into

To get the VBA object model for excel, goto the VBE and press F2 - you can then search about in there to find properties / methods of objects

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[dmodele]

removing semicolons is easy!...

1. select the column or columns that contain the semicolons
2. do a "find and replace"... choose the option that looks for "any part of a cell" rather than "whole cells"
3. search for a ";" and replace with nothing "".

you can record it as a macro and then look at the code to see how to build this into VBA.

merging fields is easy too.. there is a function called "concatenate". if you want to join the contents of column a and b into column c type:

concatenate(a1,b1) in the cell "c1"... use the f(x) button and select it from the function wizard and excel will help you with the exact syntax. once you get it, copy it down all the columns you need. once again, you can record it as a macro and see how excel codes it to vba. btw, you could also do stuff like "concatenate(a1&"foo"). Im not sure of the syntax off the top of my head, but the function wizard thing does let you enter text, not just references to cells. once you see how it builds it, you dont have to use the wizard if you dont like it

in general... push the f(x) button or choose "function" from the menu and learn how it works. excel is actually amazingly flexible in what it can do to text in cells using all of its functions (most of which you will intuitively know from knowing other languages). GOOD LUCK!

 

[terzaghi]

The only thing to know in VBA is how you can refer data from the progra:

use the statment worksheet("NAME").cells(row,column).value

i.e worksheet("Sheet1").cells(1,1).value is cell A1.
you can

a=worksheet("Sheet1").cells(1,1).value

to gather a data and

worksheet("Sheet1").cells(1,1).value =a

to set a.

then you have conditional loops while .. wend and so on like in java.

good luck!
A