VBA学习过程中遇到的问题（主要针对Excel）

①怎样在一个单元格中动态的显示当前的时间？
 用函数“=NOW()”的话只会是打开Excel工作薄时的时间，而不会动态更新。在某个论坛看到一个方法，是在固定的时间间隔调用一次SendKey “{F9}”来刷新工作薄，我按着做了，确实是动态显示了，我设定一秒一刷新，但是问题在我打开VBE的时候出来了，F9在VBE中是设置断点的快捷键，工作薄中的函数在打开的时候执行，然后每隔一秒就循环调用一次，所以在VBE中就一直会要插入断点，很郁闷。然后修改了那个方法，调用F9键来刷新完全没有必要。
 在WorkBook的Open事件中添加代码：

Private   Sub  Workbook_Open()
Application.OnTime Now  +   TimeValue ( " 00:00:01 " ),  " GetTime "
End Sub

然后在新的模块中添加两个相互调用的子过程：

Sub  GetTime()
Application.OnTime Now  +   TimeValue ( " 00:00:01 " ),  " Refresh "             '这句是主要的
End Sub
Sub  Refresh()
Range( " B73 " ).Value  =   Format (Now(),  " hh:mm:ss " )
Call  GetTime
End Sub

Ok，这样就不存在快捷键冲突的问题了，而且实现了想要的效果。

②How to creat a new workbook ?

Sub  CreatWorkBook(strName  As   String  ,strTitle  as   String  ,strSubject  as   String )
Set  newBook  =  Workbooks.Add
     With  newBook
    .Title  =  strTitle
    .Subject  =  strSubject
    .SaveAs Filename: = strName
     End   With
End Sub

of course you can make  "strTitle" and "strSubject"  as optional arguments,but the Filename argument must be met  , it must be a absolute file path with the workbook's filename.

③Using "ByRef" or "ByVal"
I'm a beginner in Visual Basic Application, I know the Function has a return value, but what I should do if I want more than one return value from a Sub procedure ? By declaring the argument as ByRef you can change the argument's value .This keyword means that the Visual Basic passes the argument by reference.
It is something like a pointer or the out keyword in C#, maybe I'm totally wrong.

④Quit problem again !
The same problem I met in my previous little project show up ,again, this time I'm not going to use the way I've  already  used . Set workbook's Saved property to True or set the application's DisplayAlerts property to False ,job done.

⑤Interested one.

Private   Declare   Sub  sleepp  Lib   " kernel32.dll "   Alias   " Sleep "  ( ByVal  dwMilliseconds  As   Long )
Sub  TestGridlineColor()
     '  Change grid color using hexidecimal values.
    ActiveWindow.GridlineColor  =   & HFF      '  Red
     '  Wait 1 second.
    sleepp  5000
    ActiveWindow.GridlineColor  =   & HFF00    '  Green
    ActiveWindow.Zoom  =   50
   sleepp  5000
    ActiveWindow.GridlineColor  =   & HFF0000  '  Blue
    ActiveWindow.Zoom  =   80
    sleepp  5000
     '  Restore the default.
    ActiveWindow.GridlineColorIndex  =  xlColorIndexAutomatic
    ActiveWindow.Zoom  =   100
End Sub

⑥All FaceId

' 显示所有face和对应的faceid
Sub  ShowFaceIDs()
     Dim  NewToolbar  As  CommandBar
     Dim  NewButton  As  CommandBarButton
     Dim  i  As   Integer , IDStart  As   Integer , IDStop  As   Integer
    
'    Delete existing FaceIds toolbar if it exists
     On   Error   Resume   Next
    Application.CommandBars( " FaceIds " ).Delete
     On   Error   GoTo   0
    
'    Add an empty toolbar
     Set  NewToolbar  =  Application.CommandBars.Add _
        (name: = " FaceIds " , temporary: = True )
    NewToolbar.Visible  =   True
    
'    Change the following values to see different FaceIDs
    IDStart  =   1
    IDStop  =   800
    
     For  i  =  IDStart  To  IDStop
         Set  NewButton  =  NewToolbar.Controls.Add _
            (Type: = msoControlButton, ID: = 2950 )
        NewButton.FaceId  =  i
        NewButton.Caption  =   " FaceID =  "   &  i
     Next  i
    NewToolbar.Width  =   600
End Sub

 

这当中有一些没有Face的，不知道什么原因啊。可能机器性能不好吧，企图显示1000或以上的Face时就会特别特别特别的慢。800个就够用了，常用的Face也就那么几个。

⑦ 用类型说明符声明变量的方式
&是Long的类型说明符
是dim ...as ...的简化写法
不过这种声明方式不直观，还是不推荐
常用类型说明符
% integer
& long
! single
# Double
$ string
@ currency

转载于:https://www.cnblogs.com/Jinspet/archive/2008/11/20/1337350.html