Saving a value from a variable -- I'm missing something obvious!

[reyreyreyes]

Hi all

I'm sorry if this is a newbie question (I suspect it is), but I can't find a specific answer to my issue.

Here's what I'm trying to do: make a basic app to record fastest lap times on a computer game. There's a textbox that shows the current fastest lap, and a button saying 'Update Lap Time'. When you press the button to update, you enter a new time in the input box, which then populates the Lap Time box if it is faster. The issue I have is the variable resets, and the lap time has gone. I'm sure I've done this very thing before but I can't figure out what is obviously very simple!

Here's my code:


' UpdateLapAus is the button name that brings up the inputbox
' FastestLapAus is the textbox that contains the fastest lap time



Private Sub UpdateLapAus_Click()

Static str As String
Dim msg As String

str = InputBox("Please state the new fastest lap in 00:00:00 format", "Fastest Lap Updater")

If FastestLapAus = "" Then
FastestLapAus = str

ElseIf str <> "" And str >= FastestLapAus Then
msg = "You have entered a fastest lap that is slower or equal to the existing time. Do better!"
MsgBox msg

End If

End Sub


Thanks in advance.

[Yongle]

I have put some notes in that may help you understand what you were missing.



Private Sub UpdateLapAus_Click()


Static FastestLapAus As String 'You had not declared this variable
Static str As String
Dim msg As String 'can delete this by putting text inside " " after MsgBox (see below examples)


str = InputBox("Please state the new fastest lap in 00:00:00 format", "Fastest Lap Updater")


If FastestLapAus = "" Then
FastestLapAus = str
End If


If str <> "" And str >= FastestLapAus Then
'note that message box text is now entered inside quote marks after MsgBox
MsgBox "You have entered a fastest lap that is slower or equal to the existing time. Do better!"
Else 'new line here
FastestLapAus = str 'new line here - THIS LINE IS CRUCIAL TO CARRY FWD FASTEST LAP TIME
MsgBox "NEW FAST LAP !!!" & vbNewLine & FastestLapAus 'new line - shout that it is a fastest lap
End If


End Sub

[reyreyreyes]

Thanks Yongle. I was glad to see you'd replied as I'd read a couple of your helpful threads yesterday!

When I am home, I will give it a go. A question though, why do I need to declare the 'FastestLapAus' with it being a textbox control name?

Also, why is this new line so important?

Else 'new line here
FastestLapAus = str 'new line here - THIS LINE IS CRUCIAL TO CARRY FWD FASTEST LAP TIME

Thanks for being newbie friendly!

[Yongle]

Why do I need to declare the 'FastestLapAus' with it being a textbox control name?
In my code FastestLapAus is not a textbox control name, it is a variable which keeps the value of the fastest laps until next time macro is run. Where was that value being held in your code? Answer – it was not held anywhere
Code will often work without declaring variables – this code is simple and maybe it will still work without declaring. Try it.
Creating code is a habit, and we all tend to copy what we did yesterday. So, today I am lazy and do not declare variables because the code is simple, but tomorrow with more difficult code, the macro may not work. And then I will ask - why does this not work? Answer – because I was lazy yesterday.
I think (others may disagree), it is good habit to declare variables because it forces you to think about what type of value is being held and then you understand better if a macro does not work. Also it makes a big difference in Excel - Excel allocates a different amount of memory to each type of variable. If you do not declare a variable, and the workbook contains many rows or there are lots of calculations, then Excel can run out of memory. And then it crashes. And then you waste a lot of time!


Why is this new line so important?
FastestLapAus = str - your code did not work without it, and now it does. This is the reason why you started this thread.

I hope my explanations will help you when you have future problems.
Yon

[reyreyreyes]

I hope my explanations will help you when you have future problems.
Yon

Thanks for your confidence Yon "when you have future problems", not IF... Ha ha! You're right, I will.

Thanks for the explanation. I get the variable declaration bit, which is why I used the 'msg' variable for the msgbox even though I didn't need it -- trying to form a habit!

I see what you mean about the code not working for the new line on the 'Else' statement too, but functionally speaking, why doe sthat new line matter? Why can't it be on the same? Or am I over-analysing that?

Again, thanks for your feedback -- very, very, very helpful.

[Yongle]

Yes you are over-analysing. The new line is just make things pretty. Delete it if you want.
You now have a solution to your original thread. So please go to Thread Tools and mark this thread as SOLVED

thanks ... until next time!!

[reyreyreyes]

Next time has come early Yon!

I've tried the code but it isn't working. I've just stepped through it and even when the text box (FastestLapAus) is blank, it carries on to the first msgbox line and tells me my fastest lap is slower and therefore no update occurs in the FastestLapAus box even though VBA seems to be executing it based on:


If FastestLapAus = "" Then

being true.

Thoughts?

[Yongle]

(Like I said, when you have future problems...) ha ha
I have checked and all is working here, I have added a new message box and it shows what is input, what previous fastest lap was etc.
Are you remembering to enter everything in the format 05:00:00? If you enter 5:00:00 then the code will not work?
(your original code).

Anyway, just in case there is something wrong above (I have not checked) here is a copy off my test file which runs ok.




Private Sub UpdateLapAus_Click()


Static FastestLapAus As String 'new static here - this needs to be remembered at end of macro
Static str As String
Dim msg As String 'can delete this by putting text inside " " after MsgBox (see below examples)
MsgBox "FastestLapAus at onset:" & FastestLapAus


str = InputBox("Please state the new fastest lap in 00:00:00 format", "Fastest Lap Updater")
MsgBox "INPUT TIME =" & str & " PREVIOUS FAST TIME = " & FastestLapAus
If FastestLapAus = "" Then
FastestLapAus = str
End If


If str <> "" And str >= FastestLapAus Then
'note that message box text is now entered inside quote marks after MsgBox
MsgBox "You have entered a fastest lap that is slower or equal to the existing time. Do better!"
Else 'new line here
FastestLapAus = str 'new line here
MsgBox "NEW FAST LAP !!!" & vbNewLine & FastestLapAus 'new line - shout that it is a fastest lap
End If


End Sub

[reyreyreyes]

Yon, I'm beginning to wonder if you're toying with me!

I've just tried replacing all of my code for that sub, and here's the journey I go through:

When 'UpdateLapAus' button is pressed:


Msgbox: "FastestLapAus at onset:"
<I press ok>
Input box comes up asking for lap time. I enter 01:30:00
<I press ok>
msgbox "INPUT TIME = ... etc."
<I press ok>
msgbox "You have entered a fastest lap that is slower..."
<I press ok>

No lap time shows in the textbox.

That is, without interruption, what happens when I press my button.

:-/

[Yongle]

Help please from anyone reading this thread.
Please try the code in post#8
It runs perfectly for me every time, and so I cannot understand why it does not run for reyreyreyes
thank you