Strange behavior for my VBA code (HELP)

[VB_Lover]

Hi

I need some help in my VBA code in excel 2007

I wrote a code (a lot of calculations with arrays and ranges)

My code works with 'ButtonCommand_Click'

One trouble only

When I click on that button, the result appears Ok

When I click it again, the result is not Ok because of values of one
variable (they are zeros!!)

Now

When I click it again, it is Ok (the values of that variable is Ok i.e., not zeros!!)

and so on

First Click, Ok

Second Click, not Ok

Third Click, Ok,

Fourth Click, not Ok

and so forth based on the values of a certain variable (integer)

If the variable has a problem in intilization for example, it should not be Ok

any more Click later, but it would be Ok some times!!

Hope the problem is clear


Many thnaks for any suggestion
VB_Lover

[mdmackillop]

Without a sample workbook and your code, I don't see how we can assist.

[VB_Lover]

Thank you mdmackillop


This is it.. (attached)


the main Sub DO some operations then it calls another Sub named findUtilization. This Sub uses another function named getConnType.
From my testing, I think the problem comes from getConnType function since when I replaced its code with a simple statement like getConn = "something", it works well

As we said before, another click will be ok!!

the result appears on Cells P30 and down

[VBA]
Dim pkg, cls, Res, cList, mTable, ucPathTable As Range
Dim k, i, j, r, n, m, nConnMethods, totMethods, c, nClasses, col1, col2, ptr As Integer
Dim found As Boolean
Dim ucTable(3, 11), Util(7), mTarget, cName, pName, mPrime, c1, c2, c3, connType As String
Private Sub FindResult_Click()
Set cList = Range("A22:A28")
Set mTable = Range("B22:K28")
Set ucPathTable = Range("A307:K309")
Set pkg = Range("A2:A8")
Set cls = Range("B2:B8")
Set Res = Range("P30:P900")

For j = 1 To ucPathTable.Rows.Count
ucTable(j, 1) = ucPathTable.Cells(j, 1)
Next

For i = 1 To ucPathTable.Rows.Count
c = 2
For j = 2 To ucPathTable.Columns.Count
found = search(ucPathTable.Cells(i, j), i, 1)
If Not found Then ucTable(i, c) = ucPathTable.Cells(i, j): c = c + 1
Next 'j
Next 'i

findUtilization
End Sub

Sub findUtilization()
Res.Clear
ptr = 1
For i = 1 To cls.Rows.Count
cName = cls.Cells(i, 1)
pName = pkg.Cells(i, 1)
c = 0
For n = 1 To pkg.Rows.Count
If pkg.Cells(n, 1) = pName Then c = c + 1
Next
If c = 1 Then
Util(i) = 1
Else
ReDim connMatrix(c * mTable.Columns.Count, 3) As String
m = 1
For j = 1 To cList.Rows.Count
c1 = cList.Cells(j, 1)
If c1 = cName Then
nConnMethods = 0
totMethods = 0
For col1 = 1 To mTable.Columns.Count
mTarget = getMethodName(Range(mTable.Cells(j, 1), mTable.Cells(j, mTable.Columns.Count)), col1)
found = False
If mTarget <> "" Then totMethods = totMethods + 1
For k = 1 To cls.Rows.Count
c2 = cls.Cells(k, 1)
If c2 <> cName And pkg.Cells(k, 1) = pName Then
For r = 1 To cList.Rows.Count
c3 = cList.Cells(r, 1)
If c2 = c3 Then
For col2 = 1 To mTable.Columns.Count
mPrime = getMethodName(Range(mTable.Cells(r, 1), mTable.Cells(r, mTable.Columns.Count)), col2)
If mTarget <> "" And mPrime <> "" Then
connType = ""
For n = 1 To UBound(connMatrix, 1)
If (connMatrix(n, 1) = mTarget And connMatrix(n, 2) = mPrime) Or _
(connMatrix(n, 1) = mPrime And connMatrix(n, 2) = mTarget) _
Then connType = connMatrix(n, 3): Exit For
Next 'n
If connType <> "" Then
If connType = "direct" Or connType = "transitive" Then nConnMethods = nConnMethods + 1: found = True
Else
connType = getConnType(mTarget, mPrime)
If connType = "direct" Or connType = "transitive" Then nConnMethods = nConnMethods + 1: found = True
connMatrix(m, 1) = mTarget: connMatrix(m, 2) = mPrime: connMatrix(m, 3) = connType
m = m + 1
End If
If found Then Exit For
End If 'mTarget
Next 'col2
End If 'c2 =
If found = True Then Exit For
Next 'r
End If 'c2 <> cName..
If found = True Then Exit For
Next 'k
Next 'col1
Util(i) = nConnMethods / totMethods
Res.Cells(ptr) = Util(i): ptr = ptr + 1
End If 'c1=cName
Next 'j
End If 'if c=1
Next 'i
End Sub

Function getMethodName(list As Range, ByVal col As Integer) As String
getMethodName = list.Cells(1, col)
End Function

Function search(elem As String, ByVal r As Integer, flag As Byte) As Boolean
Dim exist As Boolean
Dim i As Integer
exist = False
If flag = 1 Then
For i = 2 To UBound(ucTable, 2)
If ucTable(r, i) <> "" And elem <> "" Then
If ucTable(r, i) = elem Then exist = True: Exit For
End If
Next
Else
For i = 1 To UBound(unionTable, 2)
If unionTable(r, i) <> "" And elem <> "" Then
If unionTable(r, i) = elem Then exist = True: Exit For
End If
Next
End If
search = exist
End Function
Function getConnType(ByVal m As String, ByVal mP As String) As String
Dim mRow, i, j, mCol, mPCol, a, b As Integer
Dim conn As String
Dim tmpRow As Range

mRow = 0
For i = 1 To UBound(ucTable, 1)
If search(m, i, 1) And search(mP, i, 1) Then
For j = 2 To UBound(ucTable, 2)
If ucTable(i, j) <> "" Then
If ucTable(i, j) = m Then
mCol = j
ElseIf ucTable(i, j) = mP Then
mPCol = j
End If
End If
Next
mRow = i: Exit For
End If
Next
If mRow = 0 Then
getConnType = "none"
Exit Function
Else
conn = ""
a = 0
b = 0
For j = 2 To ucPathTable.Columns.Count - 1
If (ucPathTable.Cells(mRow, j) = m And ucPathTable.Cells(mRow, j + 1) = mP) Or (ucPathTable.Cells(mRow, j) = mP And ucPathTable.Cells(mRow, j + 1) = m) Then
conn = "direct"
Exit For
End If
Next
If conn = "direct" Then
getConnType = "direct"
Exit Function
Else
For j = 2 To ucPathTable.Columns.Count
If ucPathTable.Cells(mRow, j) = m Then a = j: Exit For
Next
For j = 2 To ucPathTable.Columns.Count
If ucPathTable.Cells(mRow, j) = mP Then b = j: Exit For
Next
For j = a + 1 To b - 1
For i = 2 To mCol - 1
If ucPathTable.Cells(mRow, j) = ucTable(mRow, i) Then conn = "none": Exit For
Next
If conn = "none" Then Exit For
Next
End If
End If
If conn = "none" Then
getConnType = "none"
ElseIf Abs(mCol - mPCol) = 1 Then
getConnType = "none"
Else
getConnType = "transitive"
End If
End Function

[/VBA]

[mdmackillop]

A lot to check with no comments!
As no values are changing, other than the "results", I guess some variables are retaining values. Adding this may assist in tracking these down.
[VBA]findUtilization
Cells(1, 13) = "k": Cells(1, 14) = k
Cells(2, 13) = "i": Cells(2, 14) = i
Cells(3, 13) = "j": Cells(3, 14) = j
Cells(4, 13) = "r": Cells(4, 14) = r
Cells(5, 13) = "n": Cells(5, 14) = n
Cells(6, 13) = "m": Cells(6, 14) = m
Cells(7, 13) = "nConnMethods": Cells(7, 14) = nConnMethods
Cells(8, 13) = "totMethods": Cells(8, 14) = totMethods
Cells(9, 13) = "c": Cells(9, 14) = c
Cells(10, 13) = "col1": Cells(10, 14) = col1
Cells(11, 13) = "col2": Cells(11, 14) = col2
Cells(12, 13) = "ptr": Cells(12, 14) = ptr[/VBA]

[Paul_Hossler]

As a (very) small FYI, when variables are Dim-ed like this

[VBA]
Dim pkg, cls, Res, cList, mTable, ucPathTable As Range
Dim k, i, j, r, n, m, nConnMethods, totMethods, c, nClasses, col1, col2, ptr As Integer
[/VBA]

VBA will treat pkg, cls, .... as Variant.

Only ucPathTable will be explictly typed as a Range

[VBA]
Sub VarDemo()
Dim pkg, cls, Res, cList, mTable, ucPathTable As Range
Dim k, i, j, r, n, m, nConnMethods, totMethods, c, nClasses, col1, col2, ptr As Integer

pkg = 1234
MsgBox VarType(pkg)

pkg = "1234"
MsgBox VarType(pkg)

Set pkg = ActiveSheet
MsgBox VarType(pkg)

End Sub
[/VBA]

Some languages will make pkg, etc. Ranges since they're on the same Dim,but VBA does not

However, the code will still work, but the usual recommendation is to explictly Dim variables for performance unless there's a reason not to

Paul

[VB_Lover]

Thank you Paul

I changed it as below but still the same problem

However, no need to understand my code in detail

No need for that ..the behavior of that code is really strange..

'Cilck' is ok and the other 'Click' is not then Ok then not!!
the same code and the same variables and the same intilization!


[VBA]
Dim pkg As Range
Dim cls As Range
Dim Res As Range
Dim cList As Range
Dim mTable As Range
Dim ucPathTable As Range
Dim tmpRow As Range

Dim mRow As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim mCol As Integer
Dim mPCol As Integer
Dim a As Integer
Dim b As Integer
Dim k As Integer
Dim i As Integer
Dim j As Integer
Dim r As Integer
Dim n As Integer
Dim m As Integer
Dim nConnMethods As Integer
Dim totMethods As Integer
Dim c As Integer
Dim nClasses As Integer
Dim col1 As Integer
Dim col2 As Integer
Dim ptr As Integer

Dim exist As Boolean
Dim found As Boolean

Dim ucTable(3, 11) As String
Dim Util(7) As String
Dim mTarget As String
Dim cName As String
Dim pName As String
Dim mPrime As String
Dim c1 As String
Dim c2 As String
Dim c3 As String
Dim connType As String
Dim conn As String
[/VBA]

[mdmackillop]

Because your variables are Global, they are retaining values, at least 2 of which change on each running. Try dimming these within a routine and passing the values if required.

[mikerickson]

Alternately, you could explicitly initialize the values of your global variables each time the Sub is run.

[VB_Lover]

I found it

An IF-statement should be added in the first nested for-loop as below (red colored).

Thank you for your participation

[VBA]
For i = 1 To ucPathTable.Rows.Count
c = 2
For j = 2 To ucPathTable.Columns.Count
If ucPathTable.Cells(i, j) <> "" Then
found = search(ucPathTable.Cells(i, j), i, 1)
If Not found Then ucTable(i, c) = ucPathTable.Cells(i, j): c = c + 1
End If
Next 'j
Next 'i
[/VBA]


VB_Lover