1

How do I declare a - private - class variable, when a new sheet is created using the tab. It has to be done automatically. I presume it is a good idea to declare it by using the Private Sub Workbook_NewSheet(ByVal Sh As Object) -event from the Wookbook object

    Sub Workbook_NewSheet(ByVal Sh As Object)
        Dim sh.privateVariableOfSheet As Integer
        Declare New sh.privateVariableOfSheet2 As Integer
    End Sub

Both above 'declarations' fails of course!

And for the completeness, how to refer to this variable from an ordinary module.

Improve this question
8
  • what type of Variable/Class you want to get ? What type of events ? Commented Mar 23, 2017 at 7:38
  • @Shai Rado: It is all in the code example: when a new sheeet is initiated / created then the Workbook-NewSheet routine is triggered (the event). And I want to declare a variable (type Range to refer to the second last selection of the sheet - for later use) in this new sheet WITHOUT using the VBAeditor. Commented Mar 23, 2017 at 8:19
  • 1
    You can't do that without automating the VBE to add code to the worksheet. It would be easier to copy an existing sheet that already has the variable if you can. I'm not really clear why you need to do this. Commented Mar 23, 2017 at 8:37
  • @Rony. I need to keep track of the last selected cell - not the present one - for EACH of the many sheets I have at a certain time and I thought that a variable à la ' oldSelectedCell' would be the way of doing it. Commented Mar 23, 2017 at 8:55
  • 2
    Because you're trying to add a property to an object at runtime, which is not normal. ;) Commented Mar 23, 2017 at 9:13

3 Answers 3

Reset to default
1

Another method is to use the CustomProperties collection of the Worksheet. For example:

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Sh.CustomProperties.Add Name:=Sh.Name, Value:=99

End Sub

You can then use it later in a sub-routine:

Option Explicit

Sub Test()
    Dim var As Variant

    var = ThisWorkbook.Worksheets("Sheet13").CustomProperties(1)

    MsgBox var

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You're quite busy. I go for this one. BUT great to see your other treatease using classes.
1

Let take some class - clsFoo - which has a single property of type Range with a getter and setter:

Private m_rngSomewhere As Range

Public Property Get SomeRange() As Range
    Set SomeRange = m_rngSomewhere
End Property

Public Property Set SomeRange(rng As Range)
    Set m_rngSomewhere = rng
End Property

Now, in the Workbook code module you have:

  • a Public variable which we will set as a Dictionary
  • a Sub to instantiate the Dictionary - could be called from Workbook_Open or something
  • an event handler for Workbook_NewSheet

The event handler creates a new instance of clsFoo and sets its property as a Range from the new Worksheet, and then adds that to the dictionary (and checks if it was already there for some new reason).

Code in Workbook module:

Option Explicit

Public SheetFooDic As Object

Public Sub InitialiseSheetFooDic()
    Set SheetFooDic = CreateObject("Scripting.Dictionary")
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim rng As Range
    Dim cls As clsFoo
    If Not SheetFooDic.Exists(Sh) Then
        Set rng = Sh.Range("A1")
        Set cls = New clsFoo
        Set cls.SomeRange = rng
        SheetFooDic.Add Sh, cls
    End If
End Sub

This leaves you needing to simply use some Worksheet object as a key into the Dictionary in order to retrieve the Range you stored when the Worksheet was created. You can refer to the public Dictionary like this:

ThisWorkbook.SheetFooDic(ThisWorkbook.Worksheets("Sheet2")).SomeRange.Address

And get:

$A$1
Improve this answer

2 Comments

Nice solution, maybe the one I'll use. Though I thought it could be done with less 'work' (as I wrote a kind of Dim/Declare ...)
Hope it helps. Regarding the 'amount of work' - personally to me, it seems simpler than injecting some code into a worksheet. You would need to keep that 'hard-coded' code somewhere and maintain it over time.
0

You can store the address of the last selected cell as a string. So, asking for an object variable might be misleading. Either way, the easiest way is to declare a public variable (for example PrevCell As String, or As Range if you prefer or need the value) in each of your worksheets' code and set that variable in each sheet's Selection_Change event procedure. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PrevCell = Target.Cells(1)     ' or .Address
End Sub

Since you may find it useful to avoid recording selections of multiple cells my above procedure just records the address of the first cell which avoid errors that often crop up when a copy/paste action was performed on a sheet.

Now, when you insert a new sheet, don't use the Add method. Instead copy an existing sheet and clean it up the way you want. In this way the new sheet will already have the variable and the code that sets it.

I can't imagine your having need of the previous cell in any circumstance other than when the sheet is activated, but if my imagination is insufficient in this case, you might declare a global array with an element for each sheet, using the sheets' CodeName property for identification. This array would be set by the Selection_Change event procedure as demonstrated above, but when a sheet which isn't known to the array tries to register its latest selection it must be a new sheet and the array is extended to include it. The code to do so is inherited from other sheets by the same method described above.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.