Trying to use global variable

  • In workbook module I have this code, where I declared Global variable "a"

    Option Explicit
    Public a
    Private Sub Workbook_Open()
       a = ActiveCell.Value
    MsgBox a
    End Sub

    In sheet1 module I have this code where I'm trying to use declared variable "a"

    Public Sub Worksheet_SelectionChange(ByVal Target As Range)
    a = Target.Value
    End Sub
    Public Sub Worksheet_Change(ByVal Target As Range)
       If Not Intersect(Target, Me.Range("A2")) Is Nothing Then Me.Range("A3").Value = a
    End Sub

    but code in sheet1 module do not considers "a" as the variable declared in workbook module.

    Please tell what I'm doing wrong and how to do right

  • Re: Trying to use global variable

    Quote from StephenR;751257

    Why are you assigning a value to 'a' twice - the active cell and the target. In the Change event it will assume the value assigned in the SelectionChange event.

    because I try To make code that if I change value In A2, A3 will get value that A2 had before. For that I need to save A2 value in "a" before it changes when A2 is selected, but if A2 SelectionChange event doesn't occur, because A2 was already selected when file was opened, then I need to save A2 value on event

  • Re: Trying to use global variable

    You cannot declare a Public variable in a Workbook/Worksheet/Class or Userform module. These are are transient objects that may, or may not (OK, so the ThisWorkBook module isn't going to disappear, it is still basically a class module... ), exist at any particular time.

    Move the declaration to a standard code module.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!