PDA

View Full Version : Gaining Familiarity with VBA



CivilHawkIC
09-07-2014, 07:58 PM
I want to create a VBA module that will draw 10 highlighted cells to the screen. The code should allow flexibility in placing the cells along a single row. I would like to constrain the placement of the cells so that the first cell is the farthest to the right on the screen and last cell is the farthest to the left. Using an array can I store this number/ attribute to each of the 10 cells so that the order will show up (1-10)? If I needed to provide a VBA module that uses a keystroke shortcut to show this how would I code this?

12240

CivilHawkIC
09-07-2014, 10:19 PM
I'm new to Visual Basic and have spent all day troubleshooting. I would greatly appreciate your expertise!! Truly.

Goal:

1) Randomly generate 10 different green cells within Range("A7:Y7")
2) Number those green cells 1 to 10, from right to left.
3) Set up an array that holds a randomly generated speed (from 1 to 5) for each green cell.

Module should be executable using a keystroke shortcut (not included here, but I made a macro that executes the module using keystrokes ctrl+g)

Here is what I've got :



Dim iColumn As Integer
Dim lowerBound As Integer
Dim upperBound As Integer
Dim lowerSpeed As Integer
Dim upperSpeed As Integer
Dim iVehicles() As Integer
Dim iSpeed() As Integer
Dim counter As Integer


lowerBound = 1
upperBound = 25
lowerSpeed = 1
upperSpeed = 5
Cells.Clear

Do Until counter = 10

iColumn = Int((upperBound - lowerBound + 1) * Rnd() + lowerBound)
'iSpeed(i) = Int((upperSpeed- lowerSpeed + 1) * Rnd() + lowerSpeed)
'Cells.Clear
Worksheets("sheet1").Cells(7, iColumn).Interior.Color = RGB(0, 300, 0)
'Worksheets("sheet1").Cells(7, iColumn).Value = i
For Each c In Worksheets("Sheet1").Range("A7:Y7").Cells
If c.Interior.Color = RGB(0, 300, 0) Then
counter = counter + 1
End If
Next

Loop


j = 10

For i = 25 To 1
If ActiveSheet.Cells(7, i).Interior.Color = RGB(0, 300, 0) Then
Cells(7, i).Value = j
j = j - 1
Else
i = i - 1
End If
Next






End Sub

SamT
09-07-2014, 11:46 PM
Because this request sounds like either game development OR student homework, I am replying as if it was a (our more restrictive) student homework request.

The Array "iSpeed" first dimension holds a cell index number of the Range("A7:Y7") and the second dimension holds a speed value.

Dim iSpeed(10, 2) As Integer

'Note use of Dots in With clauses
With Sheets("sheet1").Range("A7:Y7")
.Cells.Clear
For i = 1 To 10
Set iColumn value as written above
With .Cells(iColumn)
.Interior.Color + as written above
End With
Next i

j = 10
For i = 25 To 1 Step -1
If .Cells(i) are colored green
Cells(i).Value = j
Randomize
iSpeed(j, 1) = i
iSpeed(j, 2) = iSpeed VALUE as written above

Decrement j here
End If
Next i
End With

Note: I left typos and inconsequential logic errors in case you are student. I apologize If you are a game developer.