VBA案例：去空数据和合并多列，延申问题，变量传递和跨sheet引用

1 问题1

原始需求：

表上有很多列，需要先对每列去掉空的数据，然后把多列合并为1列

对应方案

• 写了下面的代码
• 需要点开，宏，挨个宏执行。

• 代码思路
• 每个功能单独写成1个过程（写成单独的函数更好?）, 去空行的，合并列的，删除其他的
• 为了避免EXCEL表过大的效率问题，限制了选择了行数，列数，否则很慢，效率很低。带来的问题是需要手动修改行数和列数参数。对不会改VBA代码的人是个问题。
• 没有界面化，按钮化

Private m
Private n


Sub 去空()
m = 99
n = 9
For j = 1 To n
For i = Cells(m, j).End(xlUp).Row To 1 Step -1
    If Cells(i, j) = "" Then
       Cells(i, j).Delete
    End If
Next
Next

End Sub



Sub 合并列()

For j = 2 To n
a = Cells(m, 1).End(xlUp).Row
For i = 1 To Cells(m, j).End(xlUp).Row
    If i <= Cells(m, j).End(xlUp).Row Then
        Cells(a + i, 1) = Cells(i, j)
    End If
Next
Next

End Sub


Sub 打扫战场()
   Range(Cells(1, 2), Cells(m, n)).Delete
   MsgBox "done"
End Sub

2 问题1+：

要考虑，计算过程中，和计算结果是否会越界！！

• 第1段代码，逻辑上没问题，但是还是可能会出错
• 问题会出现在哪儿呢------ 要考虑，计算过程中，和计算结果是否会越界！！
• 所以要设置足够大得 m 行数 ! 或者设置好足够大得 output得行数
• 理论上保险可以设置成，整个行列矩阵包含单元格的数量~~代码里改为199了

1. 虽然代码刚开始声明了 m=99, n=9
2. 但并不是，原始数据，99行，9列之内就不会有问题
3. 比如，当实际得数据为 15行，列为9，就会出问题，因为 15*9=135 会超过99，也就是计算后得结果，会越过99行得边际，会导致错误！

• 另外，有点冗余代码，暂时不用循环时判断，筛选值。

Private m
Private n


Sub 去空()
m = 199
n = 9
For j = 1 To n
For i = Cells(m, j).End(xlUp).Row To 1 Step -1
    If Cells(i, j) = "" Then
       Cells(i, j).Delete
    End If
Next
Next

End Sub



Sub 合并列()

For j = 2 To n
a = Cells(m, 1).End(xlUp).Row
Debug.Print "a=" & a
For i = 1 To Cells(m, j).End(xlUp).Row
        Cells(a + i, 1) = Cells(i, j)

'这里是多余得，除非要判断，筛选 哪些数据要筛进去
'    If i <= Cells(m, j).End(xlUp).Row Then
'        Cells(a + i, 1) = Cells(i, j)
'    End If

Next
Next

End Sub


Sub 打扫战场()
   Range(Cells(1, 2), Cells(m, n)).Delete
   MsgBox "done"
End Sub

问题3

想把前面的这份代码扩展下，问了不少问题，下面来一个个解决和学习

第一段代码，各种问题

Public m As Integer
Public n As Integer
Private path1 As Object
Private path2 As Object


Sub para1()

Set path1 = ThisWorkbook.Worksheets("data")
Set path2 = ThisWorkbook.Worksheets("setting")

m = path2.Cells(3, 2).Value
n = path2.Cells(4, 2).Value

End Sub



Sub 去重()
Call para1
For j = 1 To n
    For i = path1.Cells(m, j).End(xlUp).Row To 1 Step -1
        If path1.Cells(i, j) = "" Then
           path1.Cells(i, j).Delete
        End If
    Next
Next

End Sub

Sub 合并列()
Call para1
For j = 2 To n
a = path1.Cells(m, 1).End(xlUp).Row
    For i = 1 To path1.Cells(m, j).End(xlUp).Row
        If i <= path1.Cells(m, j).End(xlUp).Row Then
            path1.Cells(a + i, 1) = path1.Cells(i, j)
        End If
    Next
Next

End Sub


Sub 打扫战场()
Call para1
'  path1.Range(Cells(1, 2), Cells(m, n)).Select  '只有切到data才生效？
'  Sheets("data").Range(Cells(1, 2), Cells(m, n)).Select
'   Sheets("data").Cells(6, 6).Interior.ColorIndex = 30  '这个可以，证明cells()好使，问题出在range上
'   Sheets("data").Range("d8").Interior.ColorIndex = 40  '这个可以，证明range()好使，问题出在range(cells,cells)上
'      Sheets("data").Range("a6:c8").Interior.ColorIndex = 40 '这个可以，证明range()好使，
'   Sheets("data").Range("a6", "c8").Interior.ColorIndex = 45 '这个可以，证明range()好使，
'      Sheets("data").Range(Cells(3, 3), Cells(4, 4)).Interior.ColorIndex = 45 '果然这个得问题，但是这样才好带参数啊！
    
    For j = 2 To n
        Sheets("data").Range(Columns(j)).Interior.ColorIndex = 48
    
    Next
    

   
  MsgBox "done"
End Sub




Sub test1()

ThisWorkbook.Worksheets("output").Cells(1, 1).Interior.ColorIndex = 15
End Sub

出现了很多问题，挨个解决

3.1 奇怪的sub

Public m As Integer
Public n As Integer
Private path1 As Object
Private path2 As Object

Sub para1()

Set path1 = ThisWorkbook.Worksheets("data")
Set path2 = ThisWorkbook.Worksheets("setting")

m = path2.Cells(3, 2).Value
n = path2.Cells(4, 2).Value

End Sub

• 为什么要写这个奇怪的代码
• 是因为，首先需求是

1. 需要在几个过程，代码块之间传递数据
2. 这些公用的数据，需要声明为 public 或 private级

• 然后遇到的问题是

1. 因为变量的定义，必须在过程或者函数中，比如写在第一个过程中
2. 但是如果第1个过程不被执行，直接执行第2个过程，则无法找到对应的参数值！
3. 所以暂时解决办法，我想的是  写1个单独的参数 sub ，每个 其他需要用到她们的都先执行一下 参数那个sub

• 应该有更好的解决办法

1. 比如改成调用函数？
2. 比如修改为常数变量？声明的时候同时赋值？Const pi As Single=3.14
3. 比如写成，调用函数的参数这样？之后试试

总结: 变量想在多个程序间调用的问题

• 先声明好变量类型

1. 不是初学者之后，一定要先声明变量，加option explicit，不加这个遇到变量赋值问题不会报错。
2. 基本型变量：  integer string double float date
3. 通用性变量    variant ，默认就是通用变量，可以随时改变变量的类型
4. 数组变量       dim arr1() as string
5. 对象变量，比如 dictionary  worksheet 等等   dim dict1 as object
6. 特殊问题;   dim i , j as integer 只有j 是int 而i 是 variant

• 变量如果想在多个程序之间调用，首先要声明为 非局部变量。

1. 具体在VBA里
2. private可以本模块之间互相调用，私有变量/模块级变量
3. public可以多模块之间调用，公共变量
4. static 静态变量
5. dim 常见定义
6. 全局变量是在整个程序中都可以使用，一般一直拥有内存
7. 而局部变量一般定义在函数里，只能在函数中使用，函数开始就分配内存空间，函数结束就释放内存空间。

• 变量的作用范围
• 变量的传递方式
• 变量的调用，需要声明为非局部变量，且需要调用变量所在的代码，

• 

3.2  这段奇怪的测试代码

Sub 打扫战场()
Call para1
'  path1.Range(Cells(1, 2), Cells(m, n)).Select  '只有切到data才生效？
'  Sheets("data").Range(Cells(1, 2), Cells(m, n)).Select
'   Sheets("data").Cells(6, 6).Interior.ColorIndex = 30  '这个可以，证明cells()好使，问题出在range上
'   Sheets("data").Range("d8").Interior.ColorIndex = 40  '这个可以，证明range()好使，问题出在range(cells,cells)上
'      Sheets("data").Range("a6:c8").Interior.ColorIndex = 40 '这个可以，证明range()好使，
'   Sheets("data").Range("a6", "c8").Interior.ColorIndex = 45 '这个可以，证明range()好使，
'      Sheets("data").Range(Cells(3, 3), Cells(4, 4)).Interior.ColorIndex = 45 '果然这个得问题，但是这样才好带参数啊！
    
    For j = 2 To n
        Sheets("data").Range(Columns(j)).Interior.ColorIndex = 48
    
    Next
    
  MsgBox "done"
End Sub
 

• 这里发生一个比较奇怪的问题，这段代码
• 如果在当前sheet就可以成功执行
• 如果不在当前sheet就执行报错，我也是疯了，不知道原因~，正在一个个排查

但是我写段测试数据，却是可以跨sheet执行的，不报错

Sub test1()

ThisWorkbook.Worksheets("output").Cells(1, 1).Interior.ColorIndex = 25
End Sub
 

3.3 

另外，为啥我只是，想用些传递参数

不都在代码里写，而是读外面的配置，就会遇到这么多问题呢？

修改后的代码