Runtime Error Overflow

S

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Greetings,

I am receiving an Overflow error on the following line:

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

The line is within the script below:

Code: 
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:

Code: 
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
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Do you get the same error if you change all the Integer's to Long?
 
Upvote 0
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
 
Last edited:
Upvote 0
Heck, let me be post all the component parts of the sub. there are a few UDF's that are used in the sub.

Code: 
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
 
Upvote 0
When you get the error and you hover your mouse over bb, ii and Counter what values show for each?
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
So what results do you get if you change
Code: 
counter = 0 'reset counter
to
Code: 
counter = 1 'reset counter
 
Upvote 0
weird, I still cannot see why the counter malfunctions at the counter...why would it not be achieving a running sum?
 
Upvote 0
Silly one but what happens if you comment out
Code: 
counter = 0 'reset counter

be prepared to break the routine....

then restore it and try commenting out the
Code: 
:     counter = 0
part of

Code: 
Dim counter As Integer:     counter = 0
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Generate 1X2 combinations by percentage.
Replies
9
Views
643
motilulla
M
B
  • Solved
Add a formula in 28th row
2
Replies
11
Views
184
bdt
B
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
304
Patriot2879
P
R
  • Question Question
SUMIFS in VBA - return values on multiple columns dependent on a cell header
Replies
2
Views
510
ramona_manea
R
mehidy1437
  • Question Question
Final code required expert opinion
Replies
8
Views
915
mehidy1437
mehidy1437

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top