VB初试

最新推荐文章于 2022-03-19 15:09:17 发布

xiaobei_cug

最新推荐文章于 2022-03-19 15:09:17 发布

阅读量314

点赞数

CC 4.0 BY-SA版权

分类专栏：笔记整理学习笔记文章标签：函数 excel string

本文链接：https://blog.youkuaiyun.com/xiaobei_cug/article/details/78914929

笔记整理同时被 2 个专栏收录

18 篇文章

订阅专栏

学习笔记

12 篇文章

订阅专栏

上次在知乎上看到一个帖子，如何起名字，有个答案印象深刻，决定动手试一试，采用excel，蛮好玩的。
示意图

A、B两列是从诗经中截取的，C、D两列随机取AB两列的1-2个字组合而成。
两个控件绑定的宏函数如下：

Sub two_word()
Dim first_name As String
Dim second_name As String
Dim temp As String
Dim temp2 As Integer
Dim i As Integer
For i = 1 To 24

   first_name = Mid(Sheets(1).Cells(i, 1), Application.RandBetween(1, Len(Sheets(1).Cells(i, 1))), 1)
   second_name = Mid(Sheets(1).Cells(i, 2), Application.RandBetween(1, Len(Sheets(1).Cells(i, 2))), 1)
   'first_name = StrConv(first_name, vbFromUnicode)
   'second_name = StrConv(second_name, vbFromUnicode)
   Sheets(1).Cells(i, 3) = first_name & second_name
'MsgBox first_name, vbOKOnly
'MsgBox second_name, vbOKOnly
Next i
End Sub

Sub three_word()
Dim first_name As String
Dim second_name As String
Dim temp As String
Dim temp2 As Integer
Dim i As Integer
For i = 1 To 24

   first_name = Mid(Sheets(1).Cells(i, 1), Application.RandBetween(1, Len(Sheets(1).Cells(i, 1))), 2)
   second_name = Mid(Sheets(1).Cells(i, 2), Application.RandBetween(1, Len(Sheets(1).Cells(i, 2))), 1)
   'first_name = StrConv(first_name, vbFromUnicode)
   'second_name = StrConv(second_name, vbFromUnicode)
   Sheets(1).Cells(i, 4) = first_name & second_name
'MsgBox first_name, vbOKOnly
'MsgBox second_name, vbOKOnly
Next i
End Sub

遇到的坑：
1、Midb函数将中文转化为ascii码了，导致输出的名字全是乱码，应该使用mid函数，关于excel的编码转化，可以参考，主要使用StrConv函数：http://blog.chinaunix.net/uid-7374279-id-3934228.html
2、RandBetween是excel前台sheet页可调用的函数，在VBA中应写成：Application.RandBetween调用