Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 14846|回复: 24

[分享] 【VBA冷门方法整理&不完全研究】

[复制链接]
发表于 2021-10-30 16:02 | 显示全部楼层 |阅读模式
    几年下来整理出几个比较冷门(或者个人认为比较冷门)的VBA处理方法,由于基础不扎实,所以也不区分是方法还是函数或者属性之类,有些术语表达可能也不准确,也没有针对某个方法的深入研究,都是通过实例来说明其用途,举例都比较简单,看下就明白,请见谅。

由于没有深入研究,甚至有些理解都可能是错误的,比如最后的自定义函数问题,心里没底,所以,在此发贴的目的是起到抛砖引玉的作用。

另:这里所指的“方法”,基本上都是指一句代码或一个函数或一个方法,甚至是一个格式设置,而不是通过某段代码的精妙组合,来完成特定的任务。

目录如下:
1、Choose函数用法
2、CallByName字符串表示变量名
3、字符串表示窗体控件
4、单元格自定义格式的条件判断(不是条件格式)
5、Application.Goto跳转
6、FSO简单生成TXT文件
7、NoteText添加/删除/修改批注
8、复制工作表区域到其他工作表的同一个位置
9、Name As移动文件(改变文件路径)
10、跟踪引用单元格提取方法
11、指定列显示在最左边和指定行显示在最下行
12、自定义函数可以返回值,也可以赋值

从2楼开始每层楼为1个方法的示例。
今后如有新发现,继续添加。

评分

参与人数 1学分 +2 收起 理由
AmoKat + 2 学习了

查看全部评分

 楼主| 发表于 2021-10-30 16:03 | 显示全部楼层
本帖最后由 大灰狼1976 于 2021-10-30 16:05 编辑

1、Choose()
    Choose()用得很少,但能灵活运用的话能减少很多逻辑判断或多余的语句,参数可以是变量或者单元格区域甚至其他任何对象(没有考证)。
举例如下:
    有3个数据源,分布状态没有规律,比如数据1放在当前工作表的A1:A10区域,数据2放在sheets(2)的C20:C30,数据3放在sheets(2)的D15:D78区域,然后通过输入的某个参数来选择源数据区进行处理,这种情况下,一般处理方法是用IF语句或SELECT语句通过判断输入条件来选择源数据区域,但是用Choose()则更为方便,如:

set rngOri = Choose(2, activesheet.[a1:a10], sheets(2).[C20:C30], sheets(2).[D15:D78])

通过参数2来选择括号内的第二个单元格对象,然后赋值给rngOri变量进行处理就可以了。
注:其实后来想想用数组也能达到类似的效果,就当知道一下吧。


ChooseExample.zip

16.17 KB, 下载次数: 66

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:06 | 显示全部楼层
2、CallByName
CallByName可以用字符串表示变量名,可以赋值,也可以取值。
    看到过有些求助者询问,能否使用字符串作为名称来操作某个变量,先不说是否实用以及是否有此必要,仅从可能性角度来说,是可以实现的。话不多说,CallByName功能比较多,我没有深入研究,仅举个简单示例如下。

CallByNameExample.zip

15.07 KB, 下载次数: 58

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:07 | 显示全部楼层
3、字符串表示窗体控件
在用户窗体内,可以通过类似me("TextBox1")的形式来处理窗体上的控件(当然不仅限于TEXTBOX),括号内的字符串可以是变量,可以对其赋值,也可以取值。

me("TextBox1")=123
debug.print me("TextBox1")


Str2NameExample.zip

16.94 KB, 下载次数: 45

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:08 | 显示全部楼层
4、单元格自定义格式条件判断(不是条件格式)
这个不算太冷门,但是使用情况很少,可以通过自定义格式来设置显示结果,且有简单的判断和着色功能,甚至可以使显示的内容跟单元格的实际内容毫无关联,起到迷惑敌人或欺负小白的作用。
注:由于自定义格式的设置方法与工作表函数TEXT的区段条件判断类似,所以顺便在附件里也一并说明了一下。

FormatExample.zip

8.85 KB, 下载次数: 52

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:08 | 显示全部楼层
5、Application.Goto(这个不算太冷门)
Application.Goto可以跳转到任何打开工作簿的任意位置,不需要提前激活目标工作簿或目标工作表,极为方便。
注意:无法跳转到未打开的工作簿。

参数scroll可以设置是否使目标单元格显示在最左上位置(true,false)
附件内有说明。

AppGotoExample.zip

22.38 KB, 下载次数: 59

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:09 | 显示全部楼层
6、FSO简单生成TXT文件
    格式:Set FSO = CreateObject("scripting.filesystemobject")
            FSO.createtextfile("path&filefullname").write string
    可以一次性写入字符串(带换行也可以)到txt文件并保存。
    附件内有说明。

更新:
可以一次写入,也可以一次读取,参考以下代码:

Sub Example()
Dim FSO As Object, s$, s1$, arr, i&, j&, sFile As Object
For i = 65 To 90
  s = s & Chr(10)
  For j = 1 To 8
    If j = 1 Then s1 = "" Else s1 = vbTab
    s = s & s1 & Chr(i) & j
  Next j
Next i
Set FSO = CreateObject("scripting.filesystemobject")
FSO.createtextfile(ThisWorkbook.Path & "\ABC.txt").write Mid(s, 2)
s = FSO.opentextfile(ThisWorkbook.Path & "\ABC.txt").readall
MsgBox s
End Sub

FSOCreateTxtExample.zip

16.08 KB, 下载次数: 48

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:10 | 显示全部楼层
7、NoteText添加/删除/修改批注
格式:range.notetext string
  好处1:不管目标单元格当前有无设置批注均不影响,有批注时修改内容,无批注时添加批注
  好处2:设置添加字符串为""时,则为删除批注,同一个语句实现所有批注操作
  缺点:不能添加图片
附件内有说明。

NoteTextExample.zip

14.6 KB, 下载次数: 47

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:11 | 显示全部楼层
8、复制工作表区域到其他工作表的同一个位置
    格式:Sheets(Array(1, 2, 3)).FillAcrossSheets Sheets(1).[a1]
    如果想把一个固定的页面布局(比如某个表头)一次性写入多个工作表,且相对位置与源工作表相同,单元格格式和内容不变,就可以考虑使用此方法。如果是整行或整列时,也会把行距或列宽一起拷贝过去,跟复制粘贴功能差不多,只是不用循环每个工作表并确定粘贴单元格位置了,只要在Array()数组中确定工作表序号就行了(可以跳号),注意必须包含源数据工作表序号。
附件内有说明。

FillAcrossSheetsExample.zip

16.84 KB, 下载次数: 61

回复

使用道具 举报

 楼主| 发表于 2021-10-30 16:12 | 显示全部楼层
9、Name As移动文件(改变文件路径)
    格式:Name path1 As path2
    简单移动文件或文件夹,只要设置path就可以了,不需要FSO,配合DIR和MKDIR可以很方便的完成文件转移工作。
附件内有说明。

NameAsMoveFileExample.zip

13.46 KB, 下载次数: 53

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 01:25 , Processed in 0.309572 second(s), 7 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表