Unreliable results - why?

[Herseem]

Hi,

I’ve set up a small spreadsheet that uses a macro to read in each record of a (potentially) huge file, does some analysis per record, and then updates some summary fields before going on to the next record. Or at least it is supposed to.

In order to summarise the analysis of each record it reads, I’m using circular references to accumulate values. The reason for this is so that the master spreadsheet that reads the records and displays the results is small, and can easily switch from file to file. But for some reason, when I try to accumulate the values just derived into the existing value for an individual record, the value becomes corrupted, often incrementing the values many times over in comparison with the value of the accumulation that should be taking place. I’ve updated the spreadsheet with a long delay for each record so you can see what is happening. One file is the main spreadsheet, the other file is a subset of sample data. I’ve also tried separating out the calculated cell from the cell it’s circularly referring to in a bid to correct the problem. I know this kind of thing can work in Excel, I’ve done it before, and in quite an old version as well. Can anybody explain why this is happening, and what I can do about it?

Thanks.
NOTE: This file will need to be changed to .csv before using it, as .csv is not an allowed file type for uploading:
BPLSEshort.xls

To run the macro, just click the button which will invoke the file picker:
Finanalysis-v02.ods
file2.ods

Mike

(Edit: attempted to improve file display -AK)

[doug]

The problem is unclear; the code appears to work as it appears to have been designed. There are some nits, for example, where you define valhist(0 to 20) as Single, consider valhist(0 to 21) as Currency, especially where you pass a var() value which is a Double. Likewise, the Input function will do a lot of the work manually performed if you offer up a variable for every column (and skip the column names).

I cannot see any ‘corruption’. As best I can tell, you consider undesirable that the list keeps updating like a tickertape. But this is because you are Looping through the csv and updating the current row each time as the first value in the valhist array, so it makes perfect sense. Or at least it is obvious the reason why it functions in that way. The array is fully updated for every single line in the csv. The purpose of the project is unclear to me, so I cannot suggest any more.

Here is the code as revised slightly, if the corruption is more obscure and corrected by the revisions:

Option VBASupport 1
Option Explicit

Private Sub CommandButton1_Click()

Dim myFile As String, textline As String, compos, x as integer, valhist(0 to 21) as Currency
Dim sDate As String, vOpen As Currency, vHigh As Currency, vLow As Currency, vClose As Currency, vVol As Currency, vaClose As Currency
Dim sDateCol As String, vOpenCol As String, vHighCol As String, vLowCol As String, vCloseCol As String, vVolCol As String, vaCloseCol As String

myFile = "{file path and name}.csv"

ActiveSheet.EnableCalculation = True

Open myFile For Input As #1
    Input #1; sDateCol, vOpenCol, vHighCol, vLowCol, vCloseCol, vVolCol, vaCloseCol
    Do Until EOF(1)
 
        Input #1; sDate, vOpen, vHigh, vLow, vClose, vVol, vaClose
        Range("A3").Value= sDate
        valhist(0)=Round(vaClose, 2)
        
        for x = 20 to 1 step -1
        	valhist(x)=valhist(x-1)
        next x
        Range("G5").Value=Round(vaClose, 2)
        Range("H24").Value=Round(valhist(0), 2)
        Range("H23").Value=Round(valhist(1), 2)
        Range("H22").Value=Round(valhist(2), 2)
        Range("H21").Value=Round(valhist(3), 2)
        Range("H20").Value=Round(valhist(4), 2)
        Range("H19").Value=Round(valhist(5), 2)
        Range("H18").Value=Round(valhist(6), 2)
        Range("H17").Value=Round(valhist(7), 2)
        Range("H16").Value=Round(valhist(8), 2)
        Range("H15").Value=Round(valhist(9), 2)
        Range("H14").Value=Round(valhist(10), 2)
        Range("H13").Value=ROund(valhist(11), 2)
        Range("H12").Value=Round(valhist(12), 2)
        Range("H11").Value=Round(valhist(13), 2)
        Range("H10").Value=Round(valhist(14), 2)
        Range("H9").Value=Round(valhist(15), 2)
        Range("H8").Value=Round(valhist(16), 2)
        Range("H7").Value=Round(valhist(17), 2)
        Range("H6").Value=Round(valhist(18), 2)
        Range("H5").Value= Round(valhist(19), 2)
        Range("H4").Value= Round(valhist(20), 2)
        wait 2
    Loop
Close #1
End Sub