Is this possible?

[Don Wells]

Running Office 2003

I have a module which I use solely as a temporary cache (Temp_Cache) containing 26 variant variables, A through Z.

In attempting to clear all of these variables I quickly found that the following approach doesn't work.

Code: Select all

Public Sub Clear_Cache()

Dim ctr As Long

  For ctr = 65 To 90
    Temp_Cache.Chr(ctr) = ""
  Next ctr

End Sub

Is there an approach that doesn't require 26 lines similar to:

Code: Select all

Temp_Cache.A = ""

I realize that this problem would disappear if Temp_Cache consisted of an array. However my question remains of academic interest.

[HansV]

No, you can't do that.

[Don Wells]

Thank you Hans.

[Jan Karel Pieterse]

You could move the a-z module's code to a class module instead. Then you can have a single variable holding an instance of the class. If you set that instance variable to nothing the class (and its a-z properties) instance is zapped.

[Don Wells]

You could move the a-z module's code to a class module instead. Then you can have a single variable holding an instance of the class. If you set that instance variable to nothing the class (and its a-z properties) instance is zapped.

Thank you Jan Karel

In attempt to fully understand your advice I created a class module named TC. The complete code follows.

Code: Select all

Public A As Variant
Public B As Variant

Option Explicit

In a standard module I placed the following code

Code: Select all

Option Explicit


Public Sub Test()

      Dim TMP As TC

      Dim mva As Variant
      Dim mvb As Variant

10      Set TMP = TC
        
20      TMP.A = "Variable A"
30      TMP.B = "Variable B"
        
40      mva = TMP.A
50      mvb = TMP.B
        
60      Set TMP = Nothing
End Sub

This code throws an error at line 10, yet without it I get the error "Object variable or With block variable not set"

Can you advise me on my error?

[HansV]

Change 10 to

Code: Select all

    Set TMP = New TC

[Don Wells]

Thank you Hans
    I should have known that, based on previous instructions from Jam Karel on initializing userforms. However I erred in writing some code and have been getting away with the following combination of commands.

Code: Select all

    Dim uf As frmBasic
                |
         Other Code
                |
    Set uf = frmBasic

    Can you suggest why this code worked?

[HansV]

Userforms are a special type of object. Unless you want to open multiple copies of a userform simultaneously, you don't need to use the keyword New.

[Don Wells]

Thank you Hans

It's a poor day when ou don't learn something. It's been a great day or me.