Constant with range value

[YasserKhalil]

I have a code and in a line there is a constant
<code style="white-space: nowrap;"><code>
</code></code>

Private Const MAX_USES As Long = 1000

I'd like to know is it possible to link the value 1000 to a cell say F1 instead of typing 1000
I tried but failed....

 

[YasserKhalil]

This is the full code
I need your help

Option Explicit 

Private Const MAX_USES As Long = 1000 

Private Sub Workbook_Open() 

    Dim lNumberOfUses As Long 

    On Error Resume Next 

    lNumberOfUses = Evaluate("NumberOfUses") 

    If Err.Number = 13 Then 
        Me.Names.Add "NumberOfUses", 1, False 
        Me.Save 
        Exit Sub 
    End If 

    Me.Names.Add "NumberOfUses", Evaluate("NumberOfUses") + 1, False 
    Me.Save 

    If Evaluate("NumberOfUses") > MAX_USES Then 
        Call NotifyUser 
        Call Kill_Myself 
    End If 
     
End Sub 


Private Sub NotifyUser() 

    Dim sVbsFile As String 
     
    sVbsFile = Environ("Temp") & "\VBS_MSG.vbs" 
     
    Open sVbsFile For Output As #1 
        Print #1, "Dim Wb" 
        Print #1, "On Error Resume Next" 
        Print #1, _ 
        "set wb=Getobject(" & Chr(34) & Me.FullName & Chr(34) & ")" 
        Print #1, _ 
        "MSG= ""YYYYYYYY."" & vbnewline & vbnewline" 
        Print #1, _ 
        "MSG= msg & ""YYYYYYYY !""" 
        Print #1, "Do" 
        Print #1, "Loop until wb.name=""""" 
        Print #1, "WScript.Echo MSG" 
    Close #1 
     
    Call Shell("WScript.exe " & sVbsFile) 

End Sub 

Private Sub Kill_Myself() 
     
    With Me 
        .Saved = True 
        .ChangeFileAccess xlReadOnly 
        Kill .FullName 
        .Close False 
    End With 
     
End Sub

 

[xenou]

I'd like to know is it possible to link the value 1000 to a cell say F1 instead of typing 1000
I tried but failed....

It is not possible with VBA constants.

For this, it would be appropriate to name the range F1, then use the named range in your code. Let's say you name Cell F1 NumberOfUses. You can then use it in your code:

Msgbox Range("NumberOfUses").Value
Or
Msgbox [NumberOfUses]

 

[YasserKhalil]

I tried your solution but there is an error when reopening the workbook..
I have edited the code in this line to be :

Private Const MAX_USES As Long = Range("NumberOfUses").Value

The error is Constant expression required

 

[Rick Rothstein]

You cannot assign anything to a constant other than a constant value... that means you cannot use any objects (functions, methods, properties, etc.) that can change value in any way. The Range property can be changed by changing its argument, so it is not a candidate for assigning to a constant. The suggestion by 'xenou' was to name your range with the name you wanted to give your constant and then just wrap that in the Range property housing at the time you needed it. Or... just use a regular variable instead of trying to use the constant.

 

[YasserKhalil]

You cannot assign anything to a constant other than a constant value... that means you cannot use any objects (functions, methods, properties, etc.) that can change value in any way. The Range property can be changed by changing its argument, so it is not a candidate for assigning to a constant. The suggestion by 'xenou' was to name your range with the name you wanted to give your constant and then just wrap that in the Range property housing at the time you needed it. Or... just use a regular variable instead of trying to use the constant.

Can you edit the code for me, please?
I tried more and more, but I failed...

 

[mikerickson]

If you are currently useing the constant MAX_USES, you could replace that with a function.

Function MAX_USES() As Long
    MAX_USES = Val(CStr(Sheet1.Range("F1").Value))
    If MAX_USES <= 0 Then MAX_USES = 1000
End Function

The existing code won't need to be changed at all.

 

[Rick Rothstein]

I think you should take the easy way out and just use a variable. Instead of this...

Private Const MAX_USES As Long = 1000

just to this...

Dim MAX_USES As Long
MAX_USES = Range("F1").Value

 

[YasserKhalil]

Compile error
Invalid outside procedure

This code for deleting the file after opening a number of times...the file will be deleted after that number.
I want to link this number to a cell value>>>>

 

[Ruddles]

Places those lines immediately after Private Sub Workbook_Open() .