Verify range is square

[Greg Snidow]

Greetings. I am needing to do MMULT on a variable square range an unknown
number of times. I have it working, but I want to be able to verify the
range is square. From the below sub, I can not figure out a way to verify
that MatrixRange is actually square. Also, if InputBox("Power") is null, it
throws an error. I can't figure out how to deal with that either. Thank you.

Greg

**********************************************************
Sub SquareMatrix()

Dim OffsetCount As Integer
Dim Power As Long
Dim MatrixRange As Range
Dim ResultRange As Range
Dim Result()
Dim Matrix()

'Capture the number of times you want to multiply the matrix by itself
Power = InputBox("Power")
If Power < 2 Then
MsgBox ("You must populate a power of at least 2")
Exit Sub
End If


'Capture the number of rows in the matrix
OffsetCount = 0

For i = ActiveCell.Row To 100
If Cells(i, ActiveCell.Column).Value <> "" Then
OffsetCount = OffsetCount + 1
Else: GoTo StepOut
End If
Next i
StepOut:
OffsetCount = OffsetCount - 1

'Set the matrix range. Need to verify range is square.
Set MatrixRange = Range(ActiveCell.Address & ":" & _
ActiveCell.Offset(OffsetCount, OffsetCount).Address)

'Set the result range
Set ResultRange = Range(ActiveCell.Offset(OffsetCount + 2, 0).Address &
":" & _
ActiveCell.Offset(OffsetCount + OffsetCount + 2,
OffsetCount).Address)

'Set the value of the matrix array
Matrix = MatrixRange.Value

'Set the initial value of the result array
Result = Application.WorksheetFunction.MMult(Matrix, Matrix)

'If power is 2, keep result array as is and populate result range. If
power
'> 2, multiply result by power in a loop
Select Case True
Case Power = 2
ResultRange.Value = Result
Case Else
For i = 3 To Power Step 1
Result = Application.WorksheetFunction.MMult(Matrix, Result)
Next i
ResultRange.Value = Result
End Select

End Sub

 

[Rick Rothstein]

You can test if the range is square like this (where I am using R as the
stand in for your range)...

If R.Areas.Count > 1 Then
MsgBox "The range is not contiguous."
ElseIf R.Columns.Count = R.Rows.Count Then
MsgBox "The Range is square."
Else
MsgBox "The Range is rectangular, but not square."
End If

 

[Gary''s Student]

If MatrixRange.Rows.Count = MatrixRange.Columns.Count Then

 

[Rick Rothstein]

And for your InputBox question, try changing this line...

Power = InputBox("Power")

to this...

Power = Val(InputBox("Power"))

 

[Greg Snidow]

Thank you, I figured it was something simple.

 

[Dana DeLouis]

Thank you, I figured it was something simple.

Hi. Don't know if any ideas here would be of help:

Sub Demo()
Dim OffsetCount
Dim MatrixRange As Range
Dim ResultRange As Range
OffsetCount = 5

Range("B2").Select

Set MatrixRange = ActiveCell.Resize(OffsetCount, OffsetCount)
Set ResultRange = MatrixRange.Offset(OffsetCount + 2)
End Sub

For i = 3 To Power Step 1

As a side note... If your Power is a big number, there are ways to do
this a little faster. For example, if Power is 200, it can be done in 8
loops instead of 200.
= = = = = = =
Dana DeLouis