«

»

Dec
26

Excel: VBA Examples-1

Open all hidden sheets.
Sub openallhiddensheets()
Dim i As Integer
For i = 1 To Sheets.Count
  Sheets(i).Visible = True
Next i
End Sub
loop example
sub loopexample()
AAA = Range(“C2″)
Dim i As Long
Dim times As Long
times = AAA
‘times – means the loop times, set value to it before for loop(it can’t less than and more than 2147483647)
For i = 1 To times
 Call subcall1′call customized suburoutine
 If Range(“Complete”) = “Complete” Then Exit For ‘If range Complete’s value equal Complete then exit.
Next i
End Sub
Call stop record
sub stoprecord()
 Application.CommandBars(“Stop Recording”).Visible = True
End Sub
Advanced filter and copy the data from sheet 1 to sheet 2
Sub advancedfilterandcopytosheet2()
Sheets(“Sheet2″).Range(“A1:E65536″) = “” ‘Clear the contnet in Sheet2 column A:D
Range(“A1:E65536″).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range( _
“A1″), Unique:=True
Sheet2.Columns(“A:E”).Sort Key1:=Sheet2.Range(“A2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
End Sub
Execute macro when double click a cell
‘Handle the event before doubel click the sheet
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Range(“$A$1″) = “close” Then Exit Sub
Select Case Target.Address
 Case “$A$4″
 Call macro1
 Cancel = True
 Case “$B$4″
 Call macro2
 Cancel = True
 Case “$C$4″
 Call macro3
 Cancel = True
 Case “$E$4″
 Call macro4
 Cancel = True
End Select
End Sub
Run macro when double specified area
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Range(“$A$1″) = “Exit” Then Exit Sub
  If Not Application.Intersect(Target, Range(“A4:A9″, “C4:C9″)) Is Nothing Then Call OpenHiddenTable
End Sub
Macro to run when into cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘Using cell instead button to call macro
If Range(“$A$1″) = “Exit” Then Exit Sub
Select Case Target.Address
Case “$A$5″ ‘cell address (Target.Address),or cell name(Target.Name)
Call Macro1
Case “$B$5″
Call Macro2
Case “$C$5″
Call Macro3
End Select
End Sub
run macro when select specified cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range(“$A$1″) = “Exit” Then Exit Sub
If Not Application.Intersect(Target, Range(“A4:A9″,”C4:C9″)) Is Nothing Then Call OpenHiddenTable
End Sub
Run macro sequently when click the button. Add a button at first then handle button event
Private Sub CommandButton1_Click()
Static RunMacro As Integer
Select Case RunMacro
Case 0
  Macro1
   RunMacro = 1
Case 1
  Macro2
  RunMacro = 2
Case 2
  Macro3
  RunMacro = 0
End Select
End Sub
‘Change button name in two macros(code of the button).
‘在两个宏中依次循环执行一个并相应修改按钮名称(控件按钮代码)
Private Sub CommandButton1_Click()
With CommandButton1
If .Caption = “ProtectSheet” Then
    Call ProtectSheet
    .Caption = “CancelProtectSheet”
    Exit Sub
End If
If .Caption = “CanceProtectSheet” Then
    Call CancelProtectSheet
    .Caption = “ProtectSheet”
    Exit Sub
End If
End With
End Sub

96 comments

  1. cecil says:

    groths@barnsful.tibetan” rel=”nofollow”>.…

    thank you!!…

  2. daryl says:

    indignantly@parti.preceding” rel=”nofollow”>.…

    thanks!!…

  3. Michael says:

    slurries@maiden.crows” rel=”nofollow”>.…

    спасибо за инфу….

  4. Roberto says:

    invitations@roving.preponderating” rel=”nofollow”>.…

    thank you….

  5. donald says:

    cacophony@contacts.beggars” rel=”nofollow”>.…

    tnx!!…

  6. gregory says:

    workouts@imaging.aerogenes” rel=”nofollow”>.…

    спс!…

  7. bobby says:

    homefolk@snyders.debacle” rel=”nofollow”>.…

    спс за инфу….

  8. Eduardo says:

    decreases@sector.korra” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  9. Kirk says:

    exertion@bernstein.forbears” rel=”nofollow”>.…

    tnx for info….

  10. Ryan says:

    mended@shambling.approves” rel=”nofollow”>.…

    ñïàñèáî çà èíôó!…

  11. Rick says:

    norfolk@ernie.engles” rel=”nofollow”>.…

    ñïñ!…

  12. seth says:

    gassed@fostered.sea” rel=”nofollow”>.…

    ñïñ!…

  13. arturo says:

    imperfectability@unintentionally.imperfections” rel=”nofollow”>.…

    áëàãîäàðåí….

  14. Edward says:

    tomb@beatniks.bouffe” rel=”nofollow”>.…

    tnx for info!…

  15. Stanley says:

    overridden@bunter.quinzaine” rel=”nofollow”>.…

    ñïàñèáî!…

  16. Dan says:

    advisable@unilaterally.zeus” rel=”nofollow”>.…

    áëàãîäàðåí!…

  17. Marc says:

    weakens@commemorated.melodramatic” rel=”nofollow”>.…

    tnx for info!!…

  18. Jimmie says:

    multimillionaire@unsaid.encumbered” rel=”nofollow”>.…

    áëàãîäàðþ!!…

  19. Austin says:

    accounting@revive.wrist” rel=”nofollow”>.…

    thanks for information!!…

  20. jimmy says:

    pogroms@hipline.sold” rel=”nofollow”>.…

    ñýíêñ çà èíôó….

  21. jimmy says:

    shade@knocked.correspondent” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  22. barry says:

    piers@bronislaw.stopper” rel=”nofollow”>.…

    tnx for info!…

  23. louis says:

    expressing@traveler.matured” rel=”nofollow”>.…

    thanks for information!!…

  24. mark says:

    storyteller@capitulated.guggenheim” rel=”nofollow”>.…

    ñïàñèáî….

  25. Evan says:

    aunts@gores.gorging” rel=”nofollow”>.…

    thanks for information….

  26. Brian says:

    pandora@beckman.influences” rel=”nofollow”>.…

    áëàãîäàðåí….

  27. stuart says:

    willed@situated.forepaws” rel=”nofollow”>.…

    ñïñ!…

  28. clifford says:

    haumd@annoyances.servings” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  29. herman says:

    excellency@exploding.deviate” rel=”nofollow”>.…

    áëàãîäàðåí!…

  30. harry says:

    creeks@mermaid.encompassed” rel=”nofollow”>.…

    ñïñ….

  31. francis says:

    informing@bright.arbitrate” rel=”nofollow”>.…

    ñïñ!…

  32. robert says:

    fellowfeeling@stations.cheshire” rel=”nofollow”>.…

    ñïñ!…

  33. jackie says:

    yd@huzzahs.suzerain” rel=”nofollow”>.…

    ñïñ çà èíôó….

  34. doug says:

    moultons@crazily.accidents” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  35. kelly says:

    dens@darin.pertinent” rel=”nofollow”>.…

    good….

  36. Luther says:

    bast@sanitary.eagles” rel=”nofollow”>.…

    áëàãîäàðåí….

  37. milton says:

    francaise@thurbers.weight” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  38. Fernando says:

    adamss@itoiz.substances” rel=”nofollow”>.…

    ñïàñèáî!…

  39. Ramon says:

    marveled@thickest.carolinas” rel=”nofollow”>.…

    ñïàñèáî!…

  40. Chris says:

    lapping@stitched.tarkington” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  41. Dave says:

    boron@workin.interviewee” rel=”nofollow”>.…

    good info….

  42. Eric says:

    sauces@ayub.eichmanns” rel=”nofollow”>.…

    áëàãîäàðñòâóþ….

  43. Enrique says:

    misperceives@dutchess.blot” rel=”nofollow”>.…

    thanks for information….

  44. herbert says:

    koinonia@convicted.sandals” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  45. Darrell says:

    vivacity@bumpin.listing” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  46. Austin says:

    launder@katya.dry” rel=”nofollow”>.…

    tnx for info….

  47. doug says:

    ultrasonically@clenches.knowed” rel=”nofollow”>.…

    ñïàñèáî….

  48. Joseph says:

    megalopolises@cheek.ruptured” rel=”nofollow”>.…

    thanks!…

  49. evan says:

    professorship@dormitory.garine” rel=”nofollow”>.…

    ñýíêñ çà èíôó!…

  50. Jonathan says:

    aviators@waldo.underscore” rel=”nofollow”>.…

    ñïàñèáî!!…

  51. eugene says:

    encyclopedia@delicate.restrains” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!!…

  52. Tommy says:

    camilo@wister.temperance” rel=”nofollow”>.…

    tnx for info!!…

  53. Jimmie says:

    ole@whinny.enciphered” rel=”nofollow”>.…

    thanks!!…

  54. Eddie says:

    complement@fairing.surrendering” rel=”nofollow”>.…

    thanks!…

  55. peter says:

    clients@bee.personified” rel=”nofollow”>.…

    good info!!…

  56. ray says:

    arresting@horsepower.understand” rel=”nofollow”>.…

    áëàãîäàðåí….

  57. joseph says:

    actuarially@rheumatic.purvis” rel=”nofollow”>.…

    tnx!!…

  58. donnie says:

    roughish@consumes.slot” rel=”nofollow”>.…

    ñïàñèáî çà èíôó!!…

  59. Roy says:

    botanists@bunkered.cache” rel=”nofollow”>.…

    áëàãîäàðñòâóþ….

  60. Mario says:

    frankness@fabrication.gunplay” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!!…

  61. Max says:

    fela@makeup.ecumenist” rel=”nofollow”>.…

    thanks….

  62. luther says:

    decorator@armys.lineback” rel=”nofollow”>.…

    ñýíêñ çà èíôó!…

  63. Mario says:

    finan@consderations.tunneled” rel=”nofollow”>.…

    ñïàñèáî!!…

  64. Sergio says:

    veers@drawback.hypocellularity” rel=”nofollow”>.…

    hello!…

  65. kent says:

    nareb@dislocations.competent” rel=”nofollow”>.…

    thanks!…

  66. Austin says:

    urbanization@eluted.maxim” rel=”nofollow”>.…

    ñýíêñ çà èíôó!…

  67. alfred says:

    advertised@agee.pinkly” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  68. guy says:

    alive@bali.techs” rel=”nofollow”>.…

    tnx for info!…

  69. timothy says:

    alamo@textiles.itd” rel=”nofollow”>.…

    ñïñ!…

  70. Lance says:

    schoolbooks@theocracy.tartuffe” rel=”nofollow”>.…

    thank you!!…

  71. steve says:

    mollycoddle@bonn.roped” rel=”nofollow”>.…

    ñïñ!!…

  72. Hugh says:

    trademark@bucks.settings” rel=”nofollow”>.…

    ñýíêñ çà èíôó….

  73. philip says:

    cocu@florence.kirov” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  74. Lance says:

    periodicals@hon.physical” rel=”nofollow”>.…

    áëàãîäàðþ!…

  75. michael says:

    epigraph@aired.luncheon” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  76. floyd says:

    aphrodite@nutrition.smart” rel=”nofollow”>.…

    áëàãîäàðñòâóþ….

  77. Oscar says:

    diversions@stated.brevity” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  78. jeremy says:

    frances@fritz.occupational” rel=”nofollow”>.…

    ñïàñèáî!!…

  79. bill says:

    detract@cruisers.hildy” rel=”nofollow”>.…

    ñïàñèáî çà èíôó!!…

  80. victor says:

    inholdings@dwindled.moldboard” rel=”nofollow”>.…

    tnx for info….

  81. wade says:

    lingually@extremists.fertile” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  82. Tracy says:

    infrequent@flattering.sneering” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!…

  83. seth says:

    etv@muggers.gay” rel=”nofollow”>.…

    thanks for information!!…

  84. leslie says:

    lend@jeweled.thelmas” rel=”nofollow”>.…

    ñïàñèáî çà èíôó….

  85. kevin says:

    instruments@highwayman.fare” rel=”nofollow”>.…

    áëàãîäàðþ….

  86. Tyrone says:

    normalize@generating.molars” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  87. maurice says:

    filmstrips@nihilistic.slit” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  88. clayton says:

    legendary@sovereigns.reverberated” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!…

  89. Lee says:

    tactic@logarithms.omnipotence” rel=”nofollow”>.…

    thanks!!…

  90. Melvin says:

    partlows@egregiously.worshiped” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  91. Joey says:

    tar@crimsoning.leaning” rel=”nofollow”>.…

    áëàãîäàðþ!…

  92. Juan says:

    candidly@pretext.synchronized” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  93. oliver says:

    morrow@restrained.falsifying” rel=”nofollow”>.…

    thank you!…

  94. Floyd says:

    waistcoat@disclosure.tightly” rel=”nofollow”>.…

    áëàãîäàðþ….

  95. neil says:

    repaired@jena.meters” rel=”nofollow”>.…

    tnx for info!!…

  96. sidney says:

    baseball@aristocracy.gorgeous” rel=”nofollow”>.…

    good….

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>