Re: start programing

bruceceng@xxxxxxxxx wrote:
A good way to write your first program would be in VBA (Visual Basic
for Applications). I say this because chances are you already have it
on your computer since it comes with Microsoft Office. To start a
program open up Microsoft Excel and goto Tools->Macro->Visual Basic
Editor (or hit Alt+F11). This should open the Visual Basic Editor.

In the editor goto Insert->Module. In the module text area that is
created type: "Sub start" hit enter.

Visual Basic automatically will create an "End Sub". In between these
two you can write the code of your program.

Note: If your program will not run because "Macros are disabled" goto
Excel: Tools->Options->Security and turn the macro security down a bit.

For example if you were to write:

Sub start()
yourName = InputBox("Enter your name please")
yourAge = InputBox("Enter your age please")
MsgBox "Hi " & yourName & ", you will be " & (yourAge + 2) & " in two
years."
If (age > 1000) Then
MsgBox "I seriously doubt that you are older than 1000."
End If
End Sub

you will have a simple program which can be run by clicking on the play
arrow at the top of the editor.

In this programming interface you can also interact directly with the
Excel worksheet. For example, enter the code:

Sub start()
For index=1 to 10
Cells(index,1) = index*10
Next
End Sub

Run this program and then Activate the main Excel page and see what is
outputed. You will find that other types of input and output are
possible. Try copying in the following code and run to see what happens
(take a look at the Excel sheet)

Sub start()

'declare the variables (define what type they are)
Dim currentSheet As Worksheet
Dim newChart As ChartObject
Dim newShape As Shape

'set a reference to the first excel worksheet (part of the
Worksheets collection)
Set currentSheet = Worksheets(1)

'create a new chart (corners are (0,0) and (500,500) and set
'newChart' to reference this
Set newChart = currentSheet.ChartObjects.Add(0, 0, 500, 500)

'let the processing occur (otherwise the next step might fail)
DoEvents

'create a new shape and set newShape to reference this
Set newShape = newChart.Chart.Shapes.AddShape(msoShapeOval, 100,
100, 30, 30)

'make the new circle red
newShape.Fill.ForeColor.RGB = RGB(255, 0, 0)

End Sub


In this program try moving the circle by adding the following code
before the End Sub:

For i = 1 to 1000
newShape.left = i/10
DoEvents
Next


You can try reading keyboard input by using the following code:

Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As
Integer
Sub start()
MsgBox "Press Q to quit"
Do
If GetAsyncKeyState(Asc("Q")) Then
MsgBox "Q was pressed"
End
End If
Loop
End Sub

The above programs can get you started with some of the basics of
programming: Input, Output, Loops, and Conditional Statements. Try
typing the programs out your self instead of just coping and pasting.
You will find the the Visual Basic Editor is helpful in showing what
different events are associated with different objects (if this doesn;t
make sense, just try typing the programs and you will see what I mean).
Also attempt to figure out what each line in the program is doing.

And don't forget the Macro Recorder. It's a great teacher of VBA
syntax.

For example, say you have entered 3 in cell A1 and want your
program to set the font attributes, borders and color of the cell.
Sure, you can look these up in the Help, but if you turn on the
Macro Recorder, select the cell, set Bold, heavy border outline
and yellow, then stop the recorder and look at the code, you'll
see:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/27/2006 by mensanator
'

'
Range("A1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

Now you know exactly how to do these operations in VBA.

What if I wanted Italic instead of Bold? A very good guess
would be change

Selection.Font.Bold = True

to

Selection.Font.Italic = True

Note that yellow is color index 6. What is the index for other
colors? Here's where VBA really comes in handy. You can
have VBA read the value of a cell and set the color accordingly.

But to do that, we have to make a slight change that you can't
do using the recorder. If your selection is an entire range of cells,
we have to write complicated loops to walk through all the cells.
But VBA has an easy way to do that and works especially well
if your selection is not contiguous.

1] make a block of cells filled in with numbers 1 to 31.

2] turn on recorder

3] select the block of cells

4] change the color to yellow

5] stop the recorder

This produces

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/27/2006 by mensanator'

'
Range("D7:D37").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

First problem - the range is hard coded into the program.
We would like to be able to use any range, so change it to:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/27/2006 by mensanator'

'
For Each cell in Selection 'change hard coded range
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Next 'add for loop terminator
End Sub

This will automatically walk through every cell you selected
without your having to figure out what all the cell addresses are.

Second problem - as coded every cell will be changed to yellow.
We want the color to be based on the cell's value, so instead
of saying With Selection.Interior, which changes all cells, we say
With cell.Interior so that each individual cell gets changed.

And to get the color index from the cell, we read the cell's value,
so the final macro looks like:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/27/2006 by mensanator
'

'
For Each cell In Selection
With cell.Interior 'change from all cells to individual cell
.ColorIndex = cell.Value 'how to read the contents of a cell
.Pattern = xlSolid
End With
Next
End Sub

Now you have a handy reference table to see what the index
numbers are for various colors. Even if you're not setting the
color to the cell's value, this technique allows you to
conditionally apply attributes:

If cell.Value<0 then
cell.Font.Bold = True

.

Relevant Pages

  • RE: Find and Delete Row from Range
    ... If r.Value = "Haack" Then ... End Sub ... cell relative to the Range, rather than relative to the worksheet. ... If I used the Macro Recorder to right click on the cell containing the name, ...
    (microsoft.public.excel.programming)
  • Re: Range inside Array not working
    ... With macro recorder I generated this code ... Sub Macro6() ... My objetive is to replace the array values with my named range, ... In cell A1 I write value 1 ...
    (microsoft.public.excel.programming)
  • Re: ?? Auto go to link source
    ... (Just tinkered around with the macro recorder) ... Sub JumpToLinkCell() ... Select a cell with a link formula to another sheet in the same ... cell in the linked sheet. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: How to select a listed item?
    ... Editor ... Sub A_in_A1 ... Then....Switch to Excel ... I would like to create a macro shortcut to change the value of cell A1, ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Revert array formula using code
    ... Alt+F11 to open VB editor. ... Sub Clear_Array ... I have a large worksheet with hundreds of array formula on it. ... " for each cell but it didnt work. ...
    (microsoft.public.excel.programming)