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

 

[SBF12345]

for both it returns the overflow error. I am fairly certain the zero denominator value is the source of the error...in what other ways could the line

counter = counter + 1

be malfunctioning?

when I add watch I have a cc value of 34 and a counter value of 0...hmmm

 

[Fluff]

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

If cc is 34 when you get the error I suspect that this line

If idx(cc) = ii Then

Is always returning False & so the counter never changes

 

[MARK858]

Well as earlier you can't start from 0 as you are dividing by it as you know.

The counter = 0 in the loop I can't see should be there and I would think that the other counter should be set as 1 but can't test as I don't have your workbook (and don't really want to try and set up one).

EDIT: Forget the above I think Fluff is right (should have refreshed before posting ).

 

[SBF12345]

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

the function above returns the idx() array. why does the author include the "0" in the index method "Application.Index(X, ii, 0), Application.Index(centroids, cc, 0), J)"

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

 

[MARK858]

See remarks under Array Form

https://support.office.com/en-us/article/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd