View Full Version : Strange behavior for my VBA code (HELP)
VB_Lover
01-15-2012, 04:06 AM
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
01-15-2012, 05:07 AM
Without a sample workbook and your code, I don't see how we can assist.
VB_Lover
01-15-2012, 09:49 AM
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
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
mdmackillop
01-15-2012, 10:36 AM
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.
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
Paul_Hossler
01-15-2012, 11:12 AM
As a (very) small FYI, when variables are Dim-ed like this
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 will treat pkg, cls, .... as Variant.
Only ucPathTable will be explictly typed as a Range
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
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
01-15-2012, 11:43 AM
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!
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
mdmackillop
01-15-2012, 02:00 PM
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
01-15-2012, 08:38 PM
Alternately, you could explicitly initialize the values of your global variables each time the Sub is run.
VB_Lover
01-16-2012, 04:45 AM
I found it
An IF-statement should be added in the first nested for-loop as below (red colored).
Thank you for your participation :)
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
VB_Lover
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.