Matrix Operations With Text

[AALab]

I am working on a program that multiplies to matrices together, and then multiplies a combination of the inverses. I have that part done, but what I'm struggling with is how to still perform the operations if text is entered into one of the cells. If there is text, I still need to display the answer for that cell, but include whatever text is in the cell. So basically it would display as "text*(number)" if that makes sense. Thanks.

 

[Jerry Sullivan]

Hello and Welcome,

Perhaps you can use the IsNumeric() function to test each value as it is multiplied.

Hard to say the best way to incorporate this into your existing code without seeing it, but here is an example of the use of IsNumeric()

Function BiProduct(varX As Variant, dblY As Double) As Variant
    If IsNumeric(varX) Then
        BiProduct = varX * dblY
    Else
        BiProduct = varX & "*(" & dblY & ")"
    End If
End Function
 
Sub TEST()
    MsgBox BiProduct(3, 7) 'returns 21
    MsgBox BiProduct("three", 7) 'returns "three*(7)"
End Sub

 

[AALab]

So it's kind of long, but here is the code.

Global M(20, 100, 100) As Single
Global trs(10), tcs(10) As Single
Global Mtx(100, 100)
Global Mtr(20, 100, 100)
Global mtxtrsp(100, 100)
Global prtmtx As Single
Global Mp As String
Global Sp As String
Global Error As Single
Global Err As String
Global zz As Single
Global ii As Single
Global jj As Single


Function Start()
''''''''''''''''''''''''''''''''''''''''''
'' Finding number of active sheets
''''''''''''''''''''''''''''''''''''''''''
ts = Worksheets.Count
''''''''''''''''''''''''''''''''''''''''''
'' Looping Sheets
''''''''''''''''''''''''''''''''''''''''''
For K = 1 To ts
''''''''''''''''''''''''''''''''''''''''''
'' Select Sheet
''''''''''''''''''''''''''''''''''''''''''
Sheets("Sheet" & K).Select
''''''''''''''''''''''''''''''''''''''''''
'' Find size of sheet
''''''''''''''''''''''''''''''''''''''''''
trs(K) = ActiveSheet.UsedRange.Rows.Count
tcs(K) = ActiveSheet.UsedRange.Columns.Count

''''''''''''''''''''''''''''''''''''''''''
'' Read in from Excel
''''''''''''''''''''''''''''''''''''''''''
For i = 1 To trs(K)
For j = 1 To tcs(K)
If Worksheets("Sheet" & K).Cells(i, j) = "" Then
jj = j
ii = i
Error = 1
zz = K
GoTo 8
End If
M(K, i, j) = Worksheets("Sheet" & K).Cells(i, j).Value2
Mtr(K, j, i) = M(K, i, j)
Next j
Next i
Next K
''''''''''''''''''''''''''''''''''''''''''
'' Check to find any outliers
''''''''''''''''''''''''''''''''''''''''''
8

If Error = 1 Then
If tcs(zz) > jj Then
GoTo 10
End If
If trs(zz) > ii & tcs(zz) = jj Then
GoTo 9
End If
10 For f = 1 To trs(zz)
If Worksheets("Sheet" & zz).Cells(f, tcs(zz)) = "" Then

Else
If Worksheets("Sheet" & zz).Cells(f + 1, tcs(zz)) = "" Then
Err = "(" & f & "," & tcs(zz) & ")" & " Sheet " & zz
If f < trs(zz) Then
GoTo 9
End If
Sheets("Sheet" & zz).Select
MsgBox "There is an Error in cell " & Err
GoTo 3
End If
End If
Next f
9 For g = 1 To tcs(zz)
If Worksheets("Sheet" & zz).Cells(trs(zz), g) = "" Then
Else
If Worksheets("Sheet" & zz).Cells(trs(zz), g + 1) = "" Then
Err = "(" & trs(zz) & "," & g & ")" & " Sheet " & zz
Sheets("Sheet" & zz).Select
MsgBox "There is an Error in cell " & Err
GoTo 3
End If
End If
Next g
End If
''''''''''''''''''''''''''''''''''''''''''
'' Perform Matrix Multiplication
''''''''''''''''''''''''''''''''''''''''''
For a = 1 To trs(1)
For b = 1 To trs(1)
For c = 1 To trs(1)
Mtx(a, b) = Mtx(a, b) + M(1, a, c) * M(2, c, b)
Next c
Next b
Next a
''''''''''''''''''''''''''''''''''''''''''
'' Perform Matrix Multiplication With Transpose
''''''''''''''''''''''''''''''''''''''''''
For a = 1 To trs(1)
For b = 1 To trs(1)
For c = 1 To tcs(2)
mtxtrsp(a, b) = mtxtrsp(a, b) + M(2, a, c) * Mtr(1, c, b)
Next c
Next b
Next a
''''''''''''''''''''''''''''''''''''''''''
'' Write Everything to a Text File
''''''''''''''''''''''''''''''''''''''''''
Output = "C:\Users\Grant\Documents\Work\MatxFile.txt"
Open Output For Output As #1
''''''''''''''''''''''''''''''''''''''''''
'' Printing [M] With Formatting
''''''''''''''''''''''''''''''''''''''''''
Print #1, ""
Print #1, "M Matrix"
For d = 1 To trs(1)
MMp = ""
For e = 1 To tcs(1)
Sp = " "
Mp = M(1, d, e)
If Mp < 0 Then
Sp = " "
If Mp < -9 Then
Sp = " "
If Mp < -99 Then
Sp = " "
If Mp < -999 Then
Sp = " "
End If
End If
End If
End If
If Mp > 9 Then
Sp = " "
If Mp > 99 Then
Sp = " "
If Mp > 999 Then
Sp = " "
If Mp > 9999 Then
Sp = " "
End If
End If
End If
End If
MMp = MMp & Sp & Mp
Next e
Print #1, MMp
Next d
''''''''''''''''''''''''''''''''''''''''''
'' Printing [N] With Formatting
''''''''''''''''''''''''''''''''''''''''''
Print #1, ""
Print #1, "N Matrix"
For d = 1 To trs(2)
MMp = ""
For e = 1 To tcs(2)
Sp = " "
Mp = M(2, d, e)
If Mp < 0 Then
Sp = " "
If Mp < -9 Then
Sp = " "
If Mp < -99 Then
Sp = " "
If Mp < -999 Then
Sp = " "
End If
End If
End If
End If
If Mp > 9 Then
Sp = " "
If Mp > 99 Then
Sp = " "
If Mp > 999 Then
Sp = " "
If Mp > 9999 Then
Sp = " "
End If
End If
End If
End If
MMp = MMp & Sp & Mp
Next e
Print #1, MMp
Next d
''''''''''''''''''''''''''''''''''''''''''
'' Printing [M]^T With Formatting
''''''''''''''''''''''''''''''''''''''''''
Print #1, ""
Print #1, "[M]^T"
For d = 1 To tcs(1)
MMp = ""
For e = 1 To trs(1)
Sp = " "
Mp = Mtr(1, d, e)
If Mp < 0 Then
Sp = " "
If Mp < -9 Then
Sp = " "
If Mp < -99 Then
Sp = " "
If Mp < -999 Then
Sp = " "
End If
End If
End If
End If
If Mp > 9 Then
Sp = " "
If Mp > 99 Then
Sp = " "
If Mp > 999 Then
Sp = " "
If Mp > 9999 Then
Sp = " "
End If
End If
End If
End If
MMp = MMp & Sp & Mp
Next e
Print #1, MMp
Next d
''''''''''''''''''''''''''''''''''''''''''
'' Printing [N]^T With Formatting
''''''''''''''''''''''''''''''''''''''''''
Print #1, ""
Print #1, "[N]^T"
For d = 1 To tcs(2)
MMp = ""
For e = 1 To trs(2)
Sp = " "
Mp = Mtr(2, d, e)
If Mp < 0 Then
Sp = " "
If Mp < -9 Then
Sp = " "
If Mp < -99 Then
Sp = " "
If Mp < -999 Then
Sp = " "
End If
End If
End If
End If
If Mp > 9 Then
Sp = " "
If Mp > 99 Then
Sp = " "
If Mp > 999 Then
Sp = " "
If Mp > 9999 Then
Sp = " "
End If
End If
End If
End If
MMp = MMp & Sp & Mp
Next e
Print #1, MMp
Next d
''''''''''''''''''''''''''''''''''''''''''
'' Printing [M]*[N] With Formatting
''''''''''''''''''''''''''''''''''''''''''
Print #1, ""
Print #1, "[M]*[N]"
''''''''''''''''''''''''''''''''''''''''''
'' Error Check #2
''''''''''''''''''''''''''''''''''''''''''
If tcs(1) = trs(2) Then
Else
Print #1, "Check your Matrix sizes"
GoTo 4
End If

For d = 1 To trs(1)
MMp = ""
For e = 1 To tcs(2)
Sp = " "
Mp = Mtx(d, e)
If Mp < 0 Then
Sp = " "
If Mp < -9 Then
Sp = " "
If Mp < -99 Then
Sp = " "
If Mp < -999 Then
Sp = " "
End If
End If
End If
End If
If Mp > 9 Then
Sp = " "
If Mp > 99 Then
Sp = " "
If Mp > 999 Then
Sp = " "
If Mp > 9999 Then
Sp = " "
End If
End If
End If
Else
End If
MMp = MMp & Sp & Mp
If tcs(2) > trs(1) Then
If e = trs(1) Then
Z = trs(1) - tcs(2)
For Z = 1 To Z
MMp = MMp & Sp & Mp
Next Z
End If
End If
Next e
Print #1, MMp
Next d
4
''''''''''''''''''''''''''''''''''''''''''
'' Printing [N]*[M]^T With Formatting
''''''''''''''''''''''''''''''''''''''''''
Print #1, ""
Print #1, "[N]*[M]^T"
''''''''''''''''''''''''''''''''''''''''''
'' Error Check
''''''''''''''''''''''''''''''''''''''''''
If tcs(1) = tcs(2) Then
Else
Print #1, "Check your Matrix sizes"
GoTo 3
End If
For d = 1 To trs(2)
MMp = ""
For e = 1 To trs(1)
Sp = " "
Mp = mtxtrsp(d, e)
If Mp < 0 Then
Sp = " "
If Mp < -9 Then
Sp = " "
If Mp < -99 Then
Sp = " "
If Mp < -999 Then
Sp = " "
End If
End If
End If
End If
If Mp > 9 Then
Sp = " "
If Mp > 99 Then
Sp = " "
If Mp > 999 Then
Sp = " "
If Mp > 9999 Then
Sp = " "
End If
End If
End If
End If
MMp = MMp & Sp & Mp
Next e
Print #1, MMp
Next d
3
Close #1
End Function

It reads in two matrices from excel. The matrix in sheet one is M, the one in sheet two is N. It finds: M*N, the transpose of both matrices, and then N*M^T. What I need it to do is to run even if there is say a letter entered into a cell in one of the matrices. So in the text file it's printing to, the entire equation will appear for any affected cells. I'm new to vba, so I'm sure there is a better way to code what I'm trying to do, but I just need to get the last requirement for now.

Thanks.

 

[Jerry Sullivan]

I'm new to vba, so I'm sure there is a better way to code what I'm trying to do, but I just need to get the last requirement for now.

There are better ways to approach this, but as you request I'll focus on minimum changes to your existing code that might make it work the way you want for text exceptions.

First, in your Dim statements that declare your arrays, use Variant instead of Single data type for any arrays that might hold text values.

Next add this function to your code module to handle your text exceptions:

Function BiProduct(ByVal varX As Variant, ByVal varY As Variant) As Variant
    If IsNumeric(varX) And IsNumeric(varY) Then
        BiProduct = varX * varY
    Else
        BiProduct = "(" & varX & "*" & varY & ")"
    End If
End Function

Lastly, revise your matrix multiplication and transpose statements to read:

Mtx(a, b) = BiProduct(M(1, a, c), M(2, c, b))
 
mtxtrsp(a, b) = BiProduct(M(2, a, c), M(1, c, b))

When you have a chance to revisit the code as a whole consider:
1. Using dynamic arrays sized using Redim to match the data range in the worksheet.
2. Declaring the arrays locally instead of globaly and using Erase at the end of the procedure to release memory.

Good luck!

 

[AALab]

This may be a dumb question, but where does the function go? (Like I said, I'm new to vba, so sorry). Thanks a lot for your help though.

 

[Jerry Sullivan]

No worries.

Just paste the BiProduct Function code below your Start Function in the same module.

 

[AALab]

Okay, it's not allowing me to put it under the start function. It says it needs an end of function for the first function. Is there something special I have to do to embed it? Also, any ideas on how to set the spacing when I print the matrices? I was thinking just an if statement. If there is text, then spacing equals the correct number of spaces, and then goes on. Would this be appropriate? Thanks again for your help.

 

[Jerry Sullivan]

Each Procedure (a Function or Subroutine) is a separate block of code within a Module. So you should paste the new function below the End Function statement for your Start Function.

I'd suggest you try pasting this code in a single module in a completely different workbook. It will allow you to change the var1 and var2 values and see how the function is called and returns a value.

Sub MySubRoutine()
    Dim var1 As Variant, var2 As Variant
    var1 = 3
    var2 = "Hello"
    MsgBox BiProduct(var1, var2)
End Sub
 
Function BiProduct(ByVal varX As Variant, ByVal varY As Variant) As Variant
    If IsNumeric(varX) And IsNumeric(varY) Then
        BiProduct = varX * varY
    Else
        BiProduct = "(" & varX & "*" & varY & ")"
    End If
End Function

Also, any ideas on how to set the spacing when I print the matrices? I was thinking just an if statement. If there is text, then spacing equals the correct number of spaces, and then goes on. Would this be appropriate? Thanks again for your help.

Is there a reason you are printing this to a text file? If you want it formatted, that will be much easier if you write your values to a separate worksheet that is formatted for printing from Excel.

 

[AALab]

Okay, I'll try that. The reason for the text file is that one of the requirements is to go directly to a text file. Even for not knowing vba much, I do know that printing to a text file is the long/hard way. I'll play around with the code and see how it works and try and adapt it to my code. Thanks a lot.