当我打开/关闭展开时，范围变量重置。 [英] Range variable resets when I open/close spread.

问题描述

我需要帮助存储范围，以便即使在打开/关闭文件后也能保持定义。

I need help storing a range so that it remains defined even after opening/closing the file.

 

I有一个 将提示用户输入初始范围的宏，然后根据输入构建不连续的范围。 我将公共变量设置为等于此计算范围，然后可用于以下函数。 这个宏有
一直在做我想要的一切，除了下面在我关闭文档后没有做任何事情，大概是因为没有定义var4。 我有什么办法可以在下次打开时保存var4吗？

I have a  macro that will prompt the user for an initial range, then build a non-contiguous range based on the input.  I set a public variable equal to this calculated range, which then is available for the function below.  The macro's have been doing everything I want, except the below doesn't do anything after I close the document, presumably because var4 isn't defined.  Is there any way I can save var4 for the next time it opens?

 

Private Sub Worksheet_Change（ByVal Target As Excel.Range）

  '在Col A的单元格中输入数据时，
 'var4.Select



'设置var4 = var

 

  On Error GoTo enditall

  Application.EnableEvents = False

 

  '如果Target.Cells.Column = 1那么  '替换为Intersect Line

  '如果相交并不是什么，那就是某种东西;（在范围内相交）

 如果不相交（目标，var4）则没有任何结果

   目标为
     如果.Value<> ""然后

        .Offset（0，-1）.Value =格式（现在，"mm / dd / yyyy hh：mm：ss AM / PM"）

     结束如果是
   结束与$
 结束如果

enditall：

  Application.EnableEvents = True

 

 



 

结束子

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  'when entering data in a cell in Col A
 'var4.Select

'Set var4 = var
 
  On Error GoTo enditall
  Application.EnableEvents = False
 
  'If Target.Cells.Column = 1 Then  'Replaced with Intersect Line
  'If Intersect Not Nothing then Is Something;(Intersects within range)
  If Not Intersect(Target, var4) Is Nothing Then
    With Target
      If .Value <> "" Then
        .Offset(0, -1).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
      End If
    End With
  End If
enditall:
  Application.EnableEvents = True
 
 

 
End Sub

推荐答案

问题：是有什么办法可以在下次打开时保存var4吗？答：如果您在下次运行程序时表示"打开"，是的，可以使用STATIC关键字。静态myVar As Variant

Question: Is there any way I can save var4 for the next time it opens? Answer: If you mean as 'open' as next time you run the procedure, yes it's possible using STATIC keyword. Static myVar As Variant

这篇关于当我打开/关闭展开时，范围变量重置。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！