Runtime Error Overflow

[SBF12345]

Greetings,

I am receiving an Overflow error on the following line:

centroids(ii, bb) = centroids(ii, bb) / counter

The line is within the script below:

Public Function ComputeCentroids(X As Variant, idx As Variant, K As Variant) As Variant

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  Function that calculates the new centroid mean for each feature X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Dim M As Integer:           M = UBound(X, 1) 'number of objects
Dim J As Integer:           J = UBound(X, 2) 'number of features
Dim ii As Integer:          ii = 1
Dim cc As Integer:          cc = 1
Dim bb As Integer:          bb = 1
Dim counter As Integer:     counter = 0
Dim centroids() As Variant: ReDim centroids(K, J) As Variant
Dim tempSum() As Variant


For ii = 1 To K 'For each centroid
    For bb = 1 To J 'for each feature
        counter = 0 'reset counter
        For cc = 1 To M 'for each observation
            If idx(cc) = ii Then 'for objects that belong to this centroid calc the sum
            centroids(ii, bb) = centroids(ii, bb) + X(cc, bb)
            counter = counter + 1
            End If
        Next cc
        centroids(ii, bb) = centroids(ii, bb) / counter 'divide sum by counter to get mean
    Next bb
Next ii


ComputeCentroids = centroids


End Function

the function is used in the sub below:

Public Sub KMeans()

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  This macro uses the functions in this module to produce clusters of X
'X related observations given their features                            X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


' get the initial centroid data 'initial centroid data can be K sets of observations
Dim InitCentrRange As String:       InitCentrRange = ActiveSheet.Range("C14").value
Dim InitialCentroids As Variant:    InitialCentroids = ActiveSheet.Range(InitCentrRange)


' get the number of max iterations
Dim MaxIt As Integer:               MaxIt = ActiveSheet.Range("C9").value


' get the features data
Dim DataSht As String:              DataSht = ActiveSheet.Range("C10").value
Dim DataRange As String:            DataRange = ActiveSheet.Range("C11").value
Dim X As Variant:                   X = Worksheets(DataSht).Range(DataRange)


' assign objects to centroids based on initial centroids
Dim my_idx As Variant:              my_idx = FindClosestCentroid(X, InitialCentroids)


Dim J As Integer:                   J = UBound(X, 2) ' number of features
Dim K As Integer:                   K = UBound(InitialCentroids, 1) 'number of clusters
Dim M As Integer:                   M = UBound(X, 1) 'number of objects


Dim centroids As Variant
Dim ii As Integer:                  ii = 1
For ii = 1 To MaxIt
    centroids = ComputeCentroids(X, my_idx, K)
    my_idx = FindClosestCentroid(X, centroids)
Next ii


'print the centroids
Dim outputCentroids As String:          outputCentroids = ActiveSheet.Range("C15").value
Range(outputCentroids).Resize(K, J).value = centroids


'print the cluster assignment next to the data
Dim ClusterOutputSht As String:     ClusterOutputSht = ActiveSheet.Range("C12").value
Dim ClusterOutputRange As String:   ClusterOutputRange = ActiveSheet.Range("C13").value
Worksheets(ClusterOutputSht).Range(ClusterOutputRange).Resize(M, 1).value = WorksheetFunction.Transpose(my_idx)


End Sub

the counter is an integer, and the centroid values are integers too. How can this line be written in excess of the function of the variant centroids when the component parts are all integers? any idea on how to troubleshoot this would be much appreciated

 

[MARK858]

Do you get the same error if you change all the Integer's to Long?

 

[SBF12345]

I changed all the component parts of the inputs into the centroids array to long, but didn't change the variant X, which defines the source data. Could the data format on the sheet be affecting this? the input data is numeric

also, the source data is a table that is 32 rows and 10 columns. All the numeric content is between 0 and 1 and to the hundreths decimal with the exception of a few blank cells

 

[SBF12345]

Heck, let me be post all the component parts of the sub. there are a few UDF's that are used in the sub.

Public Function EuclideanDistance(X As Variant, Y As Variant, num_obs As Integer) As Double

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  Function that calculates the euclidean distance between two vectors of size num_obs each    X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Dim ii As Integer: ii = 1
Dim RunningSumSqr As Double: RunningSumSqr = 0


For ii = 1 To num_obs
    RunningSumSqr = RunningSumSqr + (X(ii) - Y(ii)) ^ 2
Next ii


EuclideanDistance = Sqr(RunningSumSqr)


End Function


Public Function FindClosestCentroid(ByRef X As Variant, ByRef centroids As Variant) As Variant


'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  Function loops through all obervations and calculates the distance to all centroids X
'X  Function then returns an array with all the nearest centroids for each observation  X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Dim K As Integer:       K = UBound(centroids, 1)
Dim J As Integer:       J = UBound(centroids, 2)
Dim M As Integer:       M = UBound(X, 1)
Dim idx() As Variant:   ReDim idx(M) As Variant
Dim ii As Integer:      ii = 1
Dim cc As Integer:      cc = 1


'for each observation find closest centroid


For ii = 1 To M
    Dim Dist_min As Double:     Dist_min = 1000000
    Dim Dist As Double:         Dist = 0
    
    'for each K calculate the distance
    
    For cc = 1 To K
        Dist = EuclideanDistance(Application.Index(X, ii, 0), Application.Index(centroids, cc, 0), J)
        If Dist < Dist_min Then
            idx(ii) = cc
            Dist_min = Dist
        End If
    Next cc
Next ii


FindClosestCentroid = idx()


End Function


Public Function ComputeCentroids(X As Variant, idx As Variant, K As Variant) As Variant


'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  Function that calculates the new centroid mean for each feature X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Dim M As Integer:           M = UBound(X, 1) 'number of objects
Dim J As Integer:           J = UBound(X, 2) 'number of features
Dim ii As Integer:          ii = 1
Dim cc As Integer:          cc = 1
Dim bb As Integer:          bb = 1
Dim counter As Integer:     counter = 0
Dim centroids() As Variant: ReDim centroids(K, J) As Variant
Dim tempSum() As Variant


For ii = 1 To K 'For each centroid
    For bb = 1 To J 'for each feature
        counter = 0 'reset counter
        For cc = 1 To M 'for each observation
            If idx(cc) = ii Then 'for objects that belong to this centroid calc the sum
            centroids(ii, bb) = centroids(ii, bb) + X(cc, bb)
            counter = counter + 1
            End If
        Next cc
        centroids(ii, bb) = centroids(ii, bb) / counter 'divide sum by counter to get mean
    Next bb
Next ii


ComputeCentroids = centroids


End Function


Public Sub KMeans()


'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  This macro uses the functions in this module to produce clusters of X
'X related observations given their features                            X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


' get the initial centroid data 'initial centroid data can be K sets of observations
Dim InitCentrRange As String:       InitCentrRange = ActiveSheet.Range("C14").value
Dim InitialCentroids As Variant:    InitialCentroids = ActiveSheet.Range(InitCentrRange)


' get the number of max iterations
Dim MaxIt As Integer:               MaxIt = ActiveSheet.Range("C9").value


' get the features data
Dim DataSht As String:              DataSht = ActiveSheet.Range("C10").value
Dim DataRange As String:            DataRange = ActiveSheet.Range("C11").value
Dim X As Variant:                   X = Worksheets(DataSht).Range(DataRange)


' assign objects to centroids based on initial centroids
Dim my_idx As Variant:              my_idx = FindClosestCentroid(X, InitialCentroids)


Dim J As Integer:                   J = UBound(X, 2) ' number of features
Dim K As Integer:                   K = UBound(InitialCentroids, 1) 'number of clusters
Dim M As Integer:                   M = UBound(X, 1) 'number of objects


Dim centroids As Variant
Dim ii As Integer:                  ii = 1
For ii = 1 To MaxIt
    centroids = ComputeCentroids(X, my_idx, K)
    my_idx = FindClosestCentroid(X, centroids)
Next ii


'print the centroids
Dim outputCentroids As String:          outputCentroids = ActiveSheet.Range("C15").value
Range(outputCentroids).Resize(K, J).value = centroids


'print the cluster assignment next to the data
Dim ClusterOutputSht As String:     ClusterOutputSht = ActiveSheet.Range("C12").value
Dim ClusterOutputRange As String:   ClusterOutputRange = ActiveSheet.Range("C13").value
Worksheets(ClusterOutputSht).Range(ClusterOutputRange).Resize(M, 1).value = WorksheetFunction.Transpose(my_idx)


End Sub

 

[MARK858]

When you get the error and you hover your mouse over bb, ii and Counter what values show for each?

 

[SBF12345]

1 1 and 0...can't divide by zero...I don't understand why it would not have achieved a non-zero value, to reach this point it should have run through the number of observations...sorry for all the edits...but when I hover the mouse of cc just above the error line I am seeing 34, so the "for cc next cc" loop has run through the entire set of observations within the first feature, therefore it must have run the line counter = counter +1 that number of times.

 

[MARK858]

So what results do you get if you change

counter = 0 'reset counter

to

counter = 1 'reset counter

 

[SBF12345]

the code just ran, but the output is definitely problematic...i'll step through it

 

[SBF12345]

weird, I still cannot see why the counter malfunctions at the counter...why would it not be achieving a running sum?

 

[MARK858]

Silly one but what happens if you comment out

counter = 0 'reset counter

be prepared to break the routine....

then restore it and try commenting out the

:     counter = 0

part of

Dim counter As Integer:     counter = 0