0

I have code for writing a name and grade of a student.

When I close EXCEL and reopen it the code writes entries I have already written.

I need when I open Excel again, it will keep writing where I finished.

Private Sub cmdFirst_Click()
        
Dim intCode As Integer
Dim bytGrade As Byte
Static bytrow As Byte
Dim bytcount As Byte
Static bytmax As Byte
Static bytmin As Byte

    bytrow = 1
    
    intCode = Val(InputBox("", ""))
    
    bytGrade = Val(InputBox("", "))
    
    bytrow = bytrow + 1
     
        Cells(bytrow, "A") = intCode
        Cells(bytrow, "B") = bytGrade
    
   Loop
    
End Sub
Community
  • 1
  • 1
tzachi
  • 3
  • 1
  • 1
    [Find the last used row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen May 05 '21 at 16:43
  • 2
    For whole numbers use `Long` - there's really no need for other types. – Tim Williams May 05 '21 at 16:45
  • 2
    ^^ *especially* for anything that represents a row number. `Byte` (8 bits) will overflow at 256. `Integer` (16 bits) will overflow at 32,768. Meanwhile a `Long` (32 bits) will happily go well above a billion. Side note, That stray `Loop` token is invalid code. – Mathieu Guindon May 05 '21 at 17:11
  • I see ```Loop``` but nothing to start a Loop. Perhaps you mean to use something like ```Do Until bytrow = x```. Assuming you want to loop through rows as the variable name would suggest, I would agree with Tim and Mathieu to use Long types. Even if you don't need to loop all the way down to the bottom of the sheet, using Long is safe and a good habit to get into. I think there is neglible difference in memory use too these days. I would also recommend you delete unused variables to prevent confusion. This is generally considered good coding practice. – Andrew May 06 '21 at 02:31
  • Thank you for answers..How can i do it with "Do unntil..." ? I tried but it didn't work well. – tzachi May 06 '21 at 10:09

1 Answers1

0

While Static locals do keep their value between calls of the containing procedure scope, they're still runtime values that live in memory, not on disk: when execution ends, so does their life: the Static local is deallocated along with the VBA runtime itself, and everything starts over in the next session.

Code is stupid, it only does what it's told to. If you say "start at row 1" it's going to start at row 1 every time.

So we must replace bytrow = 1 with function calls that get you the row number you want to be writing to.

Something like this:

Dim currentRow As Long
currentRow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Offset(1).Row

Note that the Hungarian Notation prefixing is buying you exactly nothing here, and only serves to obscure the otherwise meaningful identifier names. Ctrl+i already gives you the type information for anything you want in VBA, and with Rubberduck you get this information by merely selecting the identifier in the editor.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Thank you. Is it possible to do it with "For next" loops? – tzachi May 06 '21 at 10:09
  • @tzachi finding the last used row? Sure is, but very inefficient compared to the above. – Mathieu Guindon May 06 '21 at 12:53
  • How can i do it and it will save the last used row ? I tried a lot and it doesnt work for me :( I know it's inefficient compared to your answer, but i'm trying to do the "for next" to get better with loops.. Thank a lot @Mathieu Guindon – tzachi May 06 '21 at 13:29
  • @tzachi you should learn `For...Next` loops in contexts that *require* a `For...Next` loop; this isn't it. In the meantime [this article](https://analystcave.com/vba-for-loop-vs-for-each-loop/) should put you ahead of everyone else learning about loops in VBA. – Mathieu Guindon May 06 '21 at 14:54