香港新浪網 MySinaBlog
Emily | 28th May 2006, 18:58 PM | General | (2043 Reads)

怎樣從字串裏提取想要的數字串

ExcelHome  問題

cells(1,1)="C:Documents and SettingsMatthewMy Documents測評周報20060508-20060514滿意度統計5.8-5.14"

選出  "滿意度統計5.8-5.14" 的 5.8-5.14

使用 vbscript.regexp 的例子

Function FilterStr(AnyVal As String)
    Dim RegEx
    Set RegEx = CreateObject("vbscript.regexp")
    RegEx.Global = True
    RegEx.Pattern = "[^d-.]+"
    FilterStr = RegEx.Replace(AnyVal, "")
    Set RegEx = Nothing
End Function
  
Sub Test()
Dim TempStr As String
TempArr = Split(Range("A1"), "")
TempStr = TempArr(UBound(TempArr))
MsgBox FilterStr(TempStr)
End Sub
 
mySinaBlog 不支援斜線,斜線現用全形\取代 ,請改回。
  

Emily | 28th May 2006, 12:20 PM | WB & WS | (1237 Reads)

2006 世界杯 Excel 電子表格

出色的 VBA 例子

An Excel workbook to enter scores for the 2006 FIFA World Cup in Germany. The current version is 1.06.

Details: http://www.parry.co.nz/downloads.html

函數 2006世界杯Excel電子表格


Emily | 26th May 2006, 21:26 PM | UserForm / Object | (1557 Reads)

Userform Chart using OWC

Microsoft Office Web Compoments  example by johnske posted in http://vbaexpress.com/forum/.

Picture

Details: Graph inside a userform


Emily | 26th May 2006, 12:25 PM | UserForm / Object | (897 Reads)

Userform 禁用 Alt+F4

用代碼中的 Unload 語句關閉窗口
Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> vbFormCode Then
        Cancel = True
    End If
End Sub


Emily | 26th May 2006, 11:14 AM | WB & WS, API / Add-in | (1574 Reads)

Prevent Excel Close

'---------------------------------------------------------------------------------------
' Module    : ThisWorkbook
' DateTime  : 26/5/2006 11:12
' Author    :
' Purpose   : Prevent Excel Close
'---------------------------------------------------------------------------------------
' Tested in WinXp + Office 2003


Option Explicit
Private Sub Workbook_Open()
On Error Resume Next
    'Activate the 1st worksheet using the workbooks worksheet index
    Worksheets(1).Activate
    'Or If you want to use the actual worksheet name
    'Worksheets("
End Sub
 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim Msg, Style, Title, Response
Dim MyFlag As Long, Ret As String
'Set ShutDown Constants
Const EWX_LOGOFF = 0
Const EWX_SHUTDOWN = 1
Const EWX_REBOOT = 2
Const EWX_FORCE = 4

' Define message.
Msg = "Do you want to continue ?" _
    & vbCr & vbCr & "You are about to exit the excel program." _
    & vbCr & vbCr & "You will need to Reboot Computer" _
    & vbCr & "to restore the program!"
Style = vbYesNoCancel + vbCritical + vbDefaultButton3    ' Define buttons.
Title = "Exiting Program"    ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
'Test the variable Response
Select Case Response
  Case vbYes
    'Save the file, Force Windows Closed
    Me.Save
'   Call Exit_Windows
    Ret = InputBox("Enter Password", "Password Required")
        If Ret = "testing" Then    ' 更改你的密碼
        Ret = InputBox("Exit Excel or Logoff User" _
        & vbCr & " Enter: E or L", "What Action")
        Else
        MsgBox "Invalid Password", vbCritical, "Wrong Password"
        Cancel = False
        Exit Sub
        End If
    If Ret = "E" Or Ret = "e" Then
        Application.Quit
    Else
        If
Ret = "L" Or Ret = "l" Then
            SetShutDownPrivilege 'Set the shutdown privilege - else reboot will fail
            ' Always execute a force shutdown if a shutdown is required

            MyFlag = EWX_LOGOFF  'LogOff
            ' Grab the shutdown privilege - else reboot will fail
            SetShutDownPrivilege
            'Do the required action
            Call ExitWindowsEx(MyFlag, 0)
        End If
    End If
  Case
vbNo
    Worksheets(1).Activate
    Cancel = True
  Case
vbCancel
    Cancel = True
  Case Else

  'Do Nothing
End Select

End Sub


 (閱讀全文)

Emily | 24th May 2006, 17:21 PM | General | (1507 Reads)

"#N/A" , "#value!" 在代碼中如何表示

Error number (Constant)   Literal error value     Converted error value
-----------------------------------------------------------------------

xlErrDiv0                   [#DIV/0!]               CVErr(xlErrDiv0)
xlErrNA                     [#N/A]                  CVErr(xlErrNA)
xlErrName                   [#NAME?]                CVErr(xlErrName)
xlErrNull                   [#NULL!]                CVErr(xlErrNull)
xlErrNum                    [#NUM!]                 CVErr(xlErrNum)
xlErrRef                    [#REF!]                 CVErr(xlErrRef)
xlErrValue                  [#VALUE!]
CVErr(xlErrValue)

 

 More Details: Error Trapping with Visual Basic for Applications


Emily | 21st May 2006, 09:42 AM | General | (1917 Reads)

Excel (中文版)  NumberString

函數是否一直存在但沒有解釋? 

NumberString(1234567890,1) = 一十二億三千四百五十六萬七千八百九十

NumberString(1234567890,2) = 壹拾貳億叁仟肆佰伍拾陸萬柒仟捌佰玖拾

NumberString(1234567890,3) = 一二三四五六七八九○


Emily | 13th May 2006, 23:49 PM | WB & WS | (1474 Reads)

說話的  Worksheet

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 13/5/2006 23:50
' Author    : Mpemba
' Orginal post: http://www.dailydoseofexcel.com/archives/2006/05/10/the-talking-worksheet/
' Purpose   : The Talking Worksheet

' Tested in Office 2003, Win Xp
' Set a reference to Microsoft Speech Library
'---------------------------------------------------------------------------------------

' Values for the Rate property range from -10 to 10
' Values for the Volume property range from 0 to 100
Sub Test()
    SaySomething "Hello", "Him", 10, 100
    SaySomething "-5", "Her", 5, 100
    SaySomething "Hello, I am Emily ", "None", 0, 100
End Sub

Sub SaySomething(Words As String, Person As String, Rate As Long, Volume As Long)
    Dim Voc As SpeechLib.SpVoice
    Set Voc = New SpVoice
    
    With Voc
        Debug.Print .GetVoices.Count
    
        If Person = "Him" Then
            Set .voice = .GetVoices.Item(0) 'LH Michael
        ElseIf Person = "Her" Then
            Set .voice = .GetVoices.Item(1) 'LH Michelle
        Else
            Set .voice = .GetVoices.Item(2) 'Microsoft Sam
        End If
        .Rate = Rate
        .Volume = Volume
        .Speak Words
    End With
End Sub

 '

 (閱讀全文)

Next