Solved: I think I need an Array

[TrippyTom]

General Idea: working with TABLES in VBA

Specifics: I want to write a function that returns which row # the selection starts at and how many rows were selected. For instance, if I select rows 3 and 4 in a table, it would return [3,2]. If I select rows 7-10, it would return [7,4].

Sounds simple enough, but I'm still very much a noob when it comes to arrays in VBA. Do I even need an array for that kind of thing, or am I thinking too much into this?

[TrippyTom]

Here's my code so far. (don't laugh) :devil2:

Sub RowsUp()
On Error Resume Next

Dim numRows As Long ' number of rows
Dim numSelectedRows As Long ' number of selected rows
Dim oTable As Table
Dim I As Long, J As Long
'array to store which rows numbers are selected
'PPT doesn't allow selected rows to be discontinuous (i.e. 1st and 3rd but not 2nd) - it has to be continuous (i.e. 1-3)
Dim whichRows() As String
ReDim whichRows(1)
If ActiveWindow.Selection.ShapeRange.HasTable Then
Set oTable = ActiveWindow.Selection.ShapeRange.Table
With oTable
numRows = .Rows.Count
numCols = .Columns.Count

'1) check to make sure selection does not include any cell in first row; if it does, stop the macro
For J = 1 To numCols
If .Cell(1, J).Selected Then Exit Sub
Next J


'2) I need a function to return: which row# the selection starts at, and how many rows were selected
' i.e. (select rows 3 & 4, function returns [3,2]
For I = 1 To numRows
For J = 1 To numCols
If .Cell(I, J).Selected Then
'get row numbers that are selected and write to an array (whichRows)
End If
Next J
Next I


'3) Then I would use this return value to cut the row above the selection
' get contents of row above selection and cut it (i.e. rows 3&4 selected, cut row 2 to clipboard)

'4) go to the last row of selection (i.e. 3&4 become 2&3, so go to row 4)

'5) insert row above

'6) paste original cut row (paste from clipboard)

End With
End If
End Sub

[Andy Pope]

So it looks like you have to check every cell for the selected property.
So how about this function.


Function TableRowSelection(MyTable As Table) As Variant

Dim lngRow As Long
Dim lngCol As Long
Dim lngMinRow As Long
Dim lngMaxRow As Long
Dim vntStore(1) As Variant

On Error GoTo ErrTableRowSelection

lngMaxRow = 0
lngMinRow = MyTable.Rows.Count + 1

For lngRow = 1 To MyTable.Rows.Count
For lngCol = 1 To MyTable.Columns.Count
If MyTable.Cell(lngRow, lngCol).Selected Then
If lngRow > lngMaxRow Then lngMaxRow = lngRow
If lngRow < lngMinRow Then lngMinRow = lngRow
Exit For
End If
Next
Next

If lngMaxRow > 0 Then
vntStore(0) = lngMinRow
vntStore(1) = lngMaxRow - lngMinRow + 1
End If

ErrTableRowSelection:
TableRowSelection = vntStore
Exit Function

End Function


This is how you would use the function

Sub x()

Dim vntTableSelection As Variant

vntTableSelection = TableRowSelection(ActiveWindow.Selection.ShapeRange.Table)
MsgBox "Start Row=" & vntTableSelection(0) & vbLf & "Rows=" & vntTableSelection(1)

vntTableSelection = TableColSelection(ActiveWindow.Selection.ShapeRange.Table)
MsgBox "Start Column=" & vntTableSelection(0) & vbLf & "Columns=" & vntTableSelection(1)

End Sub


And the equivalent for columns, just in case

Function TableColSelection(MyTable As Table) As Variant

Dim lngRow As Long
Dim lngCol As Long
Dim lngMinCol As Long
Dim lngMaxCol As Long
Dim vntStore(1) As Variant

On Error GoTo ErrTableColSelection

lngMaxCol = 0
lngMinCol = MyTable.Columns.Count + 1

For lngCol = 1 To MyTable.Columns.Count
For lngRow = 1 To MyTable.Rows.Count
If MyTable.Cell(lngRow, lngCol).Selected Then
If lngCol > lngMaxCol Then lngMaxCol = lngCol
If lngCol < lngMinCol Then lngMinCol = lngCol
Exit For
End If
Next
Next

If lngMaxCol > 0 Then
vntStore(0) = lngMinCol
vntStore(1) = lngMaxCol - lngMinCol + 1
End If

ErrTableColSelection:
TableColSelection = vntStore
Exit Function

End Function

[TrippyTom]

Thanks very much, Andy! :D
This will be very useful.

[TrippyTom]

Thanks very much, Andy! :D
This will be very useful.

[TrippyTom]

Thanks very much, Andy! :D
This will be very useful. Now

[TrippyTom]

Thanks very much, Andy! :D
This will be very useful. Now to

[TrippyTom]

Thanks very much, Andy! :D
This will be very useful. Now to go

[TrippyTom]

arg, double post. how do i delete this one?

[TrippyTom]

Thanks very much, Andy! :)

Could you explain the array a little for me? I'm a bit dense when it comes to them. Or maybe show me a link that explains them and their proper uses in detail. Thanks again.

[Andy Pope]

This may help
http://msdn2.microsoft.com/en-us/library/aa164778(office.10).aspx

Image an array as a matrix of variables.

for your example

vntStore(0) and vntStore(1) could have been individual variables named vntStore0 and vtStore1. This is a single dimension array.

You can have multi dimensions with arrays, such as vntStore(0,0)
This would be very similar to the rows and columns in a worksheet or table.

By the way thanks for the many thanks :)

[Bob Phillips]

By the way thanks for the many thanks :)

:rotlaugh:

[burtburt]

This is my first post but it seems like this is overkill. Why not use selection.row and selection.rows.count?

[burtburt]

crap. just noticed this is the ppt thread. Sorry. I was thinking excel.