VBA save a variable when criteria is met

[cblake843]

hi , A simple question I hope. I simply want to save a variable after it has met a criteria so it can be accessed later. The current code (below) overwrites it. I want cheese to be saved somewhere and not overwritten. The dict does not work either.




If y_max > y Then


cheese = y_max


Dim dict As Object
Dim i As Long, iMax As Long
Dim v As Variant


Set dict = CreateObject("Scripting.Dictionary")




dict.Add inputer, y_max


'Debug.Print dict.Items




End If


If Not IsNull(cheese) Then
Debug.Print "CHEESE" & cheese
End If

[rlv]

I see nothing in your code that overwrites anything at all. Cheese should have the same value at the end of your code as it had at the beginning.

[cblake843]

I see nothing in your code that overwrites anything at all. Cheese should have the same value at the end of your code as it had at the beginning.

Its part of a sub so gets overwritten

[rlv]

Declare cheese as a public variable at the top of your code module.



Option Explicit

Public cheese As Variant

Sub CBlake1()
Dim y_max, y

If y_max > y Then
cheese = y_max
End If

'
'
' Your code
'
'

If Not IsNull(cheese) Then
Debug.Print "CHEESE" & cheese
End If
End Sub

[cblake843]

Declare cheese as a public variable at the top of your code module.



Option Explicit

Public cheese As Variant

Sub CBlake1()
Dim y_max, y

If y_max > y Then
cheese = y_max
End If

'
'
' Your code
'
'

If Not IsNull(cheese) Then
Debug.Print "CHEESE" & cheese
End If
End Sub




Doesnt work 'invalid attritube in sub or function' !

Cheese is empty by the end of the sub , how can I simply save the value , surely possible

[rlv]

The public declaration


Public cheese As Variant

must be placed outside your subroutine at the top of the code module. Your "invalid attribute in Sub or Function" error indicates that you have placed the declaration inside your subroutine.