你可以在Excel中给列列出变量名吗？ [英] Can you give columns in Excel a variable name?

问题描述

我有一个相当大的Excel电子表格，大约有4500行，35列。

我们的VBA代码以字母（也称为， A2 ， A3 ... A4500 ）修改字段值时...这是一个限制，因为我们无法移动列在我们的表格中，而不必更新我们所有的代码到该列已移动到的位置。

是否可以给列列一个变量名？

如果列A当前持有名字，可以将此列命名为 First_Name ，所以无论此列移动的位置如何，它保留 First_Name 引用？

然后在我们的VBA代码中，我们可以说名字而不是 A1 ？

谢谢！

解决方案

最简单的方法是对维（35）对应于两列的变量或枚举或列号。

  Option Explicit 
 
公共名称As String =A
 Public Const Age As String =C
 Public Const ID As String =F
 ... 
  

然后当引用列使用定义的名称ie。 Range（Name& 13）。

这样你只需要修改 public const ts ts when when when。。。。。。。。。。。。。。。。b b b

 公开枚举Col 
名称= 1 
 ID = 2 
年龄= 3 
结束枚举
 
 
 Sub Main（）
单元格（1，Col.ID）=ID HEADER
 End Sub 
  

I have a fairly large Excel spreadsheet, there are around 4500 rows with 35 columns.

Our VBA code references columns by their letter (aka A1, A2, A3 ... A4500) when modifying field values ... this is a limitation as we are unable to move columns around within our sheet without having to update all of our code to where that column has moved to.

Is it possible to give a column a variable name?

If column A currently holds First Name, is it possible to name this column First_Name so that regardless of where this column moves, it retains the First_Name reference?

Then in our VBA code we can say First Name instead of A1?

Thanks!

解决方案

The easiest way is to dimension (35) variables or Enums that correspond to the either columns or column numbers.

Option Explicit

Public Const Name As String = "A"
Public Const Age As String = "C"
Public Const ID As String = "F"
...

then when referring to the columns use the defined names ie. Range(Name & 13).

this way you only ever need to modify the public constants when the order of column changes.

if you reference columns by numbers consider using Enum

Public Enum Col
    Name = 1
    ID = 2
    Age = 3
End Enum


Sub Main()
    Cells(1, Col.ID) = "ID HEADER"
End Sub

这篇关于你可以在Excel中给列列出变量名吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！