Table Of Content

Õîðåâ Â. Ä. Ñàìîó÷èòåëü ïðîãðàììèðîâàíèÿ íà VBA â Microsoft Office Êèåâ“ÞÍÈÎД 2001 ББК32.973–01 Х79 УДК681.3.06 ХоревВ.Д. Х79 СамоучительпрограммированиянаVBAвMicrosoftOffice/.—К.: Юниор,2001.—320с.,ил. ISBN966–7323–17–Х КнигаадресованаширокомукругупользователейMicrosoftOffice,версий98,2000и XP, желающих автоматизировать свою работу при помощи Visual Basic for Application (VBA)иэффективныхприемовработывMicrosoftOffice. Даннаякнигаявляетсяпрактическимруководствомдляпользователейлюбойквали- фикации, желающих самостоятельнонаучиться программировать на VBA. Каждая глава книгипредлагаетпользователюмножествоработающих рецептовдля автоматизациисо- временныхзадачвразныхприложенияхMicrosoftOffice,априложениепредставляетсо- бойкраткийсправочникпоязыкуVBA,вкоторомтеоретическиесведенияоязыкеумело переплетенысмножествомпримеровипрактическихсоветов.Седьмаяглаваисоответст- вующий раздел девятой главы являются обзорно-практическими по объектной модели MicrosoftOfficeвцеломиотодельнопообъектныммоделямWord,Excel,AccessиOutlook. Достоинством книги является всестороннее рассмотрение механизма взаимосвязи приложений Microsoft Office на основе использования объектных библиотек соответст- вующихприложений. Поседняя глава содержит бесценный материал по методам программирования в Microsoft Outlook.Это малоизученная область программированияумело раскрытаавто- ромнапримересозданияготовогоприложениявMSExcel. ББК32.973–01 Всеназванияпрограммныхпродуктов,устройствитехнологий,описанныхвданнойкниге,являютсязареги- стрированнымиторговымимаркамисоответствующихфирм. ВсеправазащищенызаконодательствомУкраиныимеждународным законодательствомобавторскомправе. Никакаячастьэтойкниги,нивкакихцеляхнеможетбытьвоспроизведенавлюбойформеилюбымисредст- вами,будьтоэлектронныеилимеханические,включаяфотокопирование изаписьнамагнитныйносительили иныесредствакопированияилисохраненияинформациибезписьменногоразрешенияиздательства. ISBN966–7323–17–Х ©ООО“Юниор”,2001 Краткое оглавление ÏÐÅÄÈÑËÎÂÈÅ....................................................................................17 ÇÀ×ÅÌ ÝÒÀÊÍÈÃÀ........................................................................................17 ÑÒÀÐÈÍÍÛÉÑÏÎÑÎÁ:“ÄÅËÀÉ,ÊÀÊ ß!”.............................................................17 Î×ÅÌ ÝÒÀÊÍÈÃÀ........................................................................................18 ×ÒÎÏÎÒÐÅÁÓÅÒÑßÄËßÐÀÁÎÒÛÑ ÝÒÎÉÊÍÈÃÎÉ...................................................18 ÎÌÀÊÐÎÑÀÕÈÌÀÊÐÎÂÈÐÓÑÀÕ........................................................................18 ÃËÀÂÀ 1.EXCEL:CÎÇÄÀÍÈÅÏÐÀÉÑ-ËÈÑÒÀ............................................21 ×ÒÎÌÎÆÅÒÁÛÒÜÏÐÎÙÅÏÐßÌÎÓÃÎËÜÍÎÉÒÀÁËÈÖÛ?..........................................21 ÊÀÊÎÒÄÅËÈÒÜ ÂÍÓÒÐÅÍÍÞÞÈÍÔÎÐÌÀÖÈÞÎÒ ÂÍÅØÍÅÉÈÍÔÎÐÌÀÖÈÈ....................27 ÌÅÒÎÄÛÐÀÇÄÅËÅÍÈßÈÍÔÎÐÌÀÖÈÈÍÀ“ÂÍÓÒÐÅÍÍÞޔȓÂÍÅØÍÞÞ”....................30 VBA— ÝÒÎÎ×ÅÍÜÏÐÎÑÒÎ!...........................................................................31 ÏÅÐÂÎÅÇÍÀÊÎÌÑÒÂÎÑVISUALBASIC...............................................................32 ÃÅÍÅÐÀÖÈßÊËÈÅÍÒÑÊÎÃÎÏÐÀÉÑ-ËÈÑÒÀ ...........................................................38 ÃËÀÂÀ2.EXCEL:ÏÐÀÉÑ-ËÈÑÒÄËßÀÂÒÎÌÀÒÈ×ÅÑÊÎÃÎ ÑÎÑÒÀÂËÅÍÈßÇÀÊÀÇÀ........................................................................50 ÏÎÑÒÀÍÎÂÊÀÇÀÄÀ×È...................................................................................50 ÂÛÁÎÐÍÀÈÌÅÍÎÂÀÍÈßÈÇÑÏÈÑÊÀ..................................................................51 ÈÌÅÍÎÂÀÍÍÛÅÄÈÀÏÀÇÎÍÛ...........................................................................51 ÏÎÑÒÐÎÅÍÈÅÁËÀÍÊÀÇÀÊÀÇÀ..........................................................................53 ÐÅØÅÍÈÅÏÐÎÁËÅÌÛÑÎÂÌÅÑÒÈÌÎÑÒÈÂÛÁÈÐÀÅÌÛÕÓÑÒÐÎÉÑÒ ÊÎÌÏÜÞÒÅÐÀ..........58 ÂÛÏÈØÈÒÅÑ×ÅÒ,ÏÎÆÀËÓÉÑÒÀ…....................................................................59 ÏÎÑÒÀÍÎÂÊÀÇÀÄÀ×È...................................................................................60 ÃËÀÂÀ 3.ÏÎÂÅÐÊÀÃÀÐÌÎÍÈÈWORDÀËÃÅÁÐÎÉEXCEL...........................73 ÈÇÄÎÊÓÌÅÍÒÀWORDÂÒÀÁËÈÖÓEXCEL............................................................74 ÎÁÐÀÒÍÎÈÇÒÀÁËÈÖÛEXCEL  ÄÎÊÓÌÅÍÒWORD.................................................92 ÃËÀÂÀ4.ÏÐÀÊÒÈÊÓÌÏÐÎÃÐÀÌÌÈÐÎÂÀÍÈßÍÀVBAÄËßEXCELÈWORD102 ÑÓÌÌÀÏÐÎÏÈÑÜÞ....................................................................................102 ×ÀÑÒÎÒÍÛÉÑËÎÂÀÐÜ.................................................................................112 ÑÐÀÂÍÈÒÅËÜÍÛÉ ÀÍÀËÈÇÏÐÀÉÑ-ËÈÑÒÎÂ........................................................119 ÃËÀÂÀ 5.ACCESS:ÑÎÇÄÀÍÈÅÎÔÈÑÍÎÉÁÀÇÛÄÀÍÍÛÕ.......................131 ÑÎÇÄÀÍÈÅÁÀÇÛÄÀÍÍÛÕMSACCESS............................................................131 ÑÎÇÄÀÍÈÅÒÀÁËÈÖ....................................................................................131 ÂÂÎÄÄÀÍÍÛÕ Â ÁÀÇÓÄÀÍÍÛÕACCESS...........................................................139 ÑÎÇÄÀÍÈÅ ÝÊÐÀÍÍÛÕÔÎÐÌ ........................................................................139 ÓÑÎÂÅÐØÅÍÑÒÂÓÅÌÔÎÐÌÓÏÐÈÏÎÌÎÙÈVBA.................................................145 ÃËÀÂÀ 6.ACCESS:ÀÂÒÎÌÀÒÈÇÀÖÈßÎÔÈÑÍÎÉÁÀÇÛÄÀÍÍÛÕ..............149 ÀÂÒÎÌÀÒÈÇÀÖÈßÂÂÎÄÀÄÀÍÍÛÕ...................................................................149 ÀÂÒÎÌÀÒÈÇÀÖÈßÐÀÁÎÒÛÑÈÌÅÞÙÈÌÈÑßÄÀÍÍÛÌÈ.........................................154 ÃËÀÂÀ 7.ÂÌÈÐÅÎÁÚÅÊÒÎÂMSOFFICE..............................................167 4 Краткоеоглавление ÐÀÁÎÒÀÑÎÁÚÅÊÒÀÌÈMSWORD..................................................................167 ÐÀÁÎÒÀÑÎÁÚÅÊÒÀÌÈMSEXCEL..................................................................174 ÐÀÁÎÒÀÑÎÁÚÅÊÒÀÌÈMSACCESS................................................................182 ÃËÀÂÀ8.VBA-ÏÐÎÃÐÀÌÌÈÐÎÂÀÍÈÅ:ÏÐÈÌÅÐÛÈÈËËÞÑÒÐÀÖÈÈ.........190 ÏÐÎÃÐÀÌÌÈÐÎÂÀÍÈÅÎÁÚÅÊÒÎÂÈÍÒÅÃÐÈÐÎÂÀÍÍÎÉÑÐÅÄÛMSOFFICE..................191 ÔÎÐÌÀÒÈÐÎÂÀÍÈÅÎÁÚÅÊÒÎÂÏÐÈËÎÆÅÍÈÉMSOFFICE.....................................202 ÏÐÅÄÑÒÀÂËÅÍÈÅÄÀÍÍÛÕ Â MSOFFICE...........................................................210 ÃËÀÂÀ 9.ÏÐÎÃÐÀÌÌÈÐÎÂÀÍÈÅÂOUTLOOK:ÄÎÊÓÌÅÍÒÎÎÁÎÐÎÒÈ ÝËÅÊÒÐÎÍÍÀßÏÎ×ÒÀ........................................................................223 ÝËÅÊÒÐÎÍÍÀßÏÎ×ÒÀÂÑÎÂÐÅÌÅÍÍÎÌÎÔÈÑÅ..................................................223 ÏÎÑÒÀÍÎÂÊÀÇÀÄÀ×È.................................................................................223 ÎÁÚÅÊÒÍÀßÌÎÄÅËÜOUTLOOK2000..............................................................224 ÑÎÇÄÀÍÈÅÓ×ÅÒÍÛÕÇÀÏÈÑÅÉ.......................................................................231 ÑÎÇÄÀÍÈÅÏÐÈËÎÆÅÍÈßREGISTRATOR...........................................................234 ÏÐÈËÎÆÅÍÈÅ.VBA,ÊÀÊßÇÛÊÏÐÎÃÐÀÌÌÈÐÎÂÀÍÈß:ÄÀÍÍÛÅ, ÑÈÍÒÀÊÑÈÑ ÈÔÓÍÊÖÈÈ....................................................................262 ÄÀÍÍÛÅVBA:ÒÈÏÛÄÀÍÍÛÕ,ÏÅÐÅÌÅÍÍÛÅÈÊÎÍÑÒÀÍÒÛ ...................................262 ÑÈÍÒÀÊÑÈ×ÅÑÊÈÅÊÎÍÑÒÐÓÊÖÈÈßÇÛÊÀVBA...................................................274 ÎÏÅÐÀÒÎÐÛÈ ÂÑÒÐÎÅÍÍÛÅÔÓÍÊÖÈÈßÇÛÊÀVBA.............................................282 Оглавление ÏÐÅÄÈÑËÎÂÈÅ....................................................................................17 ÇÀ×ÅÌ ÝÒÀÊÍÈÃÀ........................................................................................17 ÑÒÀÐÈÍÍÛÉÑÏÎÑÎÁ:“ÄÅËÀÉ,ÊÀÊ ß!”.............................................................17 Î×ÅÌ ÝÒÀÊÍÈÃÀ........................................................................................18 ×ÒÎÏÎÒÐÅÁÓÅÒÑßÄËßÐÀÁÎÒÛÑ ÝÒÎÉÊÍÈÃÎÉ...................................................18 ÎÌÀÊÐÎÑÀÕÈÌÀÊÐÎÂÈÐÓÑÀÕ........................................................................18 Îïàñíû ëè ìàêðîñû?............................................................................18 Ãäå îíè íàõîäÿòñÿ?..............................................................................18 Íàäî ëèáîÿòüñÿ ìàêðîñîâ?..................................................................19 Óðîâíè áåçîïàñíîñòè âOffice 2000.......................................................19 ÃËÀÂÀ 1 EXCEL:CÎÇÄÀÍÈÅÏÐÀÉÑ-ËÈÑÒÀ..........................................................21 ×ÒÎÌÎÆÅÒÁÛÒÜÏÐÎÙÅÏÐßÌÎÓÃÎËÜÍÎÉÒÀÁËÈÖÛ?..........................................21 Òîâàðûè öåíû “â ñòîëáèê”...................................................................21 Êàê îòôîðìàòèðîâàòü ñòîëáåö äëÿ ââîäà äåíåæíûõçíà÷åíèé................22 Ñîçäàíèå çàãîëîâêà ðàçäåëà................................................................23 Ôîðìàòèðîâàíèå äèàïàçîíà ÿ÷ååê ïîîáðàçöó......................................23 ÊàêôîðìàòèðîâàòüðàçäåëïðèïîìîùèêîìàíäûÑïåöèàëüíàÿâñòàâêà............24 Óäàëåíèå èâñòàâêà ñòðîê.....................................................................25 Ìåõàíèçìîìãðóïïèðîâàíèÿ äàííûõ.....................................................25 Êàêãðóïïèðîâàòüäàííûå.....................................................................................25 Çàãîëîâîê ïðàéñ-ëèñòà.........................................................................26 ÊÀÊÎÒÄÅËÈÒÜ ÂÍÓÒÐÅÍÍÞÞÈÍÔÎÐÌÀÖÈÞÎÒ ÂÍÅØÍÅÉÈÍÔÎÐÌÀÖÈÈ....................27 Ïîñòàíîâêàçàäà÷è...............................................................................27 Èìåíîâàííûå ÿ÷åéêè............................................................................28 Èñïîëüçîâàíèå ôîðìóë è àâòîçàïîëíåíèå ÿ÷ååêôîðìóëàìè.................29 Óñëîâíîå ôîðìàòèðîâàíèå ÿ÷ååê..........................................................29 Êàêïðèìåíèòüê ÿ÷åéêàìóñëîâíîåôîðìàòèðîâàíèå..........................................29 ÌÅÒÎÄÛÐÀÇÄÅËÅÍÈßÈÍÔÎÐÌÀÖÈÈÍÀ“ÂÍÓÒÐÅÍÍÞޔȓÂÍÅØÍÞÞ”....................30 Ñâÿçûâàíèå äàííûõ â Excel...................................................................31 Íåäîñòàòêè ñâÿçûâàíèÿ ëèñòîâExcel....................................................31 VBA— ÝÒÎÎ×ÅÍÜÏÐÎÑÒÎ!...........................................................................31 ÏÅÐÂÎÅÇÍÀÊÎÌÑÒÂÎÑVISUALBASIC...............................................................32 Çàïóñê ðåäàêòîðà Visual Basic...............................................................32 Ñîçäàíèå ïðîöåäóðû VBA.....................................................................32 Ââîä èñõîäíîãîòåêñòà ïðîöåäóðû........................................................33 Âîçìîæíûå ïðîáëåìû è ìåòîäû èõ ðåøåíèÿ........................................34 Àíàëèç ñòàíäàðòíîé ïðîöåäóðûîáðàáîòêè ñîáûòèÿ— äâîéíîãî ùåë÷êà ìûøüþíà ÿ÷åéêåëèñòà Excel...............................................................35 Åùåîäíî ñîáûòèå— åùåîäèíìàëåíüêèéìàêðîñ................................36 Àâòîìàòè÷åñêîåâûäåëåíèåïîçèöèéâïðàéñå....................................................37 Àíàëèç ñòàíäàðòíîé ïðîöåäóðûîáðàáîòêè ñîáûòèÿ— àêòèâèçàöèÿ ðàáî÷åãîëèñòà Excel............................................................................38 ÃÅÍÅÐÀÖÈßÊËÈÅÍÒÑÊÎÃÎÏÐÀÉÑ-ËÈÑÒÀ ...........................................................38 6 Оглавление Êàê ïîìåñòèòü íàëèñò ýëåìåíò óïðàâëåíèÿ..........................................39 Îñîáåííîñòè èçìåíåíèÿ ñâîéñòâ ýëåìåíòîâ óïðàâëÿþùèõ ðàáî÷èì ëèñòîì.................................................................................................40 Ïðîñòåéøèéâàðèàíò ïðîöåäóðû ãåíåðàöèè ïðàéñà..............................40 Àíàëèçïðîöåäóðûãåíåðàöèèïðàéñà..................................................................41 Íåäîñòàòêè ïðîñòåéøåïðîöåäóðûãåíåðàöèèïðàéñà.........................................42 Óñîâåðøåíñòâîâàíèÿ ïðîöåäóðû ãåíåðàöèè ïðàéñà..............................42 Îãðàíè÷åíèåîáëàñòèêîïèðîâàíèÿ ÿ÷ååê............................................................42 Èçìåíåíèåøèðèíû ÿ÷ååêèñòîëáöîâ.................................................................43 Èçìåíåíèåèìåíèðàáî÷åãîëèñòà Excel..............................................................43 Òåñòèðîâàíèåðàáîòûíîâîéïðîöåäóðûãåíåðàöèèïðàéñà................................43 Êîïèðîâàíèåðåçóëüòàòîââû÷èñëåíèéïîôîðìóëàìèèõôîðìàòèðîâàíèÿ.......44 Èñêëþ÷åíèåïîâòîðåíèÿèìåíëèñòîâðàáî÷åéêíèãè Excel.................................44 Ïîëíîöåííûé âàðèàíò ïðîöåäóðû ãåíåðàöèè ïðàéñà.............................45 Íåäîñòàòêèíîâîãîâàðèàíòàïðîöåäóðûãåíåðàöèèïðàéñà................................47 Îêîí÷àòåëüíûéâàðèàíò ïðîöåäóðû ãåíåðàöèè ïðàéñà..........................47 ÃËÀÂÀ 2 EXCEL:ÏÐÀÉÑ-ËÈÑÒÄËßÀÂÒÎÌÀÒÈ×ÅÑÊÎÃÎ ÑÎÑÒÀÂËÅÍÈßÇÀÊÀÇÀ........................................................................50 ÏÎÑÒÀÍÎÂÊÀÇÀÄÀ×È...................................................................................50 ÂÛÁÎÐÍÀÈÌÅÍÎÂÀÍÈßÈÇÑÏÈÑÊÀ..................................................................51 ÈÌÅÍÎÂÀÍÍÛÅÄÈÀÏÀÇÎÍÛ...........................................................................51 Êàê ñîçäàòü èìåíîâàííûé äèàïàçîí......................................................52 ÏÎÑÒÐÎÅÍÈÅÁËÀÍÊÀÇÀÊÀÇÀ..........................................................................53 Êàê ñîçäàòü è íàñòðîèòü ýëåìåíò óïðàâëåíèÿ Ïîëåñî ñïèñêîì.............54 Îïèñàíèå ðàáîòû ýëåìåíòà óïðàâëåíèÿÏîëå ñî ñïèñêîì.....................55 Èñïîëüçîâàíèå ôóíêöèè ÈÍÄÅÊÑ().......................................................56 Ñîçäàíèå âáëàíêå çàêàçà “áåçâàðèàíòíûõ” ñòðîê.................................57 Ïîäñ÷åò ñóììû çàêàçà..........................................................................58 ÐÅØÅÍÈÅÏÐÎÁËÅÌÛÑÎÂÌÅÑÒÈÌÎÑÒÈÂÛÁÈÐÀÅÌÛÕÓÑÒÐÎÉÑÒ ÊÎÌÏÜÞÒÅÐÀ..........58 Ïîñòàíîâêàçàäà÷è...............................................................................58 Ñîçäàíèå ïðîöåäóðûÐàñêðñïèñîê2_Èçìåíåíèå()..................................58 ÂÛÏÈØÈÒÅÑ×ÅÒ,ÏÎÆÀËÓÉÑÒÀ…....................................................................59 ÏÎÑÒÀÍÎÂÊÀÇÀÄÀ×È...................................................................................60 Ýëåìåíò óïðàâëåíèÿ Êíîïêà.................................................................60 Ïåðâûé ñïîñîá ôîðìèðîâàíèÿ ñ÷åòà (èñïîëüçîâàíèå ìàêðîðåêîðäåðà).60 ÈñïîëüçîâàíèåôóíêöèèÑÅÃÎÄÍß()....................................................................61 Êàêçàïèñàòü ìàêðîñïðèïîìîùèìàêðîðåêîðäåðà............................................61 Àíàëèçêîäààâòîìàòè÷åñêèñîçäàííîãîìàêðîñà................................................62 Âòîðîé ñïîñîá ôîðìèðîâàíèÿ ñ÷åòà(ìàêðîñ ëèñòàExcel ðàáîòàåò ñ äîêóìåíòîìWord)................................................................................63 Ïîñòàíîâêàçàäà÷è...............................................................................................63 Ñîçäàíèåîáðàçöàñ÷åòàâWord..........................................................................64 Êàêâñòàâèòüïîëÿ âäîêóìåíòWord..................................................................64 Ñîçäàíèåìàêðîñàðàáî÷åãîëèñòàExceläëÿðàáîòûñäîêóìåíòîìWord...........65 ÊàêçàäàòüññûëêóíàáèáëèîòåêóîáúåêòîâWordâñðåäå MSExcel.................66 Àíàëèçïðîöåäóðûäëÿ ðàáîòûñäîêóìåíòîìWord..........................................67 ÑîçäàíèåòàáëèöûâäîêóìåíòåWordèååàâòîôîðìàòèðîâàíèå.................................68 Óñîâåðøåíñòâîâàíèÿìàêðîñàðàáî÷åãîëèñòàExceläëÿðàáîòûñäîêóìåíòîì Word.....................................................................................................................69 Ñîçäàíèåäèàëîãîâîãî îêíàïðèïîìîùèôóíêöèèMsgBox()...........................69 ÃËÀÂÀ 3 ÏÎÂÅÐÊÀÃÀÐÌÎÍÈÈWORDÀËÃÅÁÐÎÉEXCEL........................................73 Оглавление 7 ÊÎÅ-×ÒÎÎÑÎÂÐÅÌÅÍÍÎÌÄÎÊÓÌÅÍÒÎÎÁÎÐÎÒÅ..................................................73 ÈÇÄÎÊÓÌÅÍÒÀWORDÂÒÀÁËÈÖÓEXCEL............................................................74 Ïîñòàíîâêàçàäà÷è...............................................................................74 Ïîäãîòîâêàòàáëèöû äîêóìåíòîâ...........................................................74 Ïðèâÿçêà ìàêðîñà êêîìàíäåWord........................................................75 ÊàêçàìåíèòüêîìàíäóWord.................................................................................75 Ñîçäàåììàêðîñ-àðõèâàðèóñ Word-äîêóìåíòîâ.....................................77 ÊàêçàäàòüññûëêóíàáèáëèîòåêóîáúåêòîâExcelâñðåäåMSWord....................78 Îïèñàíèåðàáîòûìàêðîñà-àðõèâàðèóñà Word-äîêóìåíòîâ.................................78 ÀíàëèçêîäàäåéñòâóþùåéìîäåëèàðõèâàðèóñàWord-äîêóìåíòîââExcel.........79 Àâòîìàòè÷åñêîå îòîáðàæåíèåíàýêðàíåáèáëèîòåêè îáúåêòîâExcel..............80 Îñâîáîæäåíèåñèñòåìíîé ïàìÿòèêîìïüþòåðà................................................80 Óñîâåðøåíñòâîâàíèÿäåéñòâóþùåéìîäåëèäëÿñîçäàíèÿ ìàêðîñà- àðõèâàðèóñàWord-äîêóìåíòîâ.............................................................................81 ÀíàëèçïåðâîãîñëîâàWord-äîêóìåíòà............................................................81 Ìàêðîñ-àðõèâàðèóñ àíàëèçèðóåò ñîäåðæèìîå Word-äîêóìåíòà.............84 Ïîñòàíîâêàçàäà÷è...............................................................................................84 Ðåàëèçàöèÿìåõàíèçìààíàëèçàñîäåðæèìîãî Word-äîêóìåíòà..........................85 Àíàëèççíà÷åíèéñëîââäîêóìåíòåWord.............................................................86 Ïîñòàíîâêàçàäà÷è...........................................................................................86 Àíàëèçñëîâà Word-äîêóìåíòà..........................................................................87 ÏîñèìâîëüíûéàíàëèçñëîââäîêóìåíòåWord....................................................88 Ïîñòàíîâêàçàäà÷è...........................................................................................88 Ïðèìåíåíèåëîãè÷åñêîé îïåðàöèè Or(ëîãè÷åñêîå ÈËÈ).................................88 Ïðèìåíåíèåëîãè÷åñêîé îïåðàöèèAnd(ëîãè÷åñêîå È)....................................89 Óïðàâëåíèåîøèáêàìèâûïîëíåíèÿìàêðîñà.......................................................89 ÓñîâåðøåíñòâîâàíèÿàíàëèçàòîðàñîäåðæèìîãîWord-äîêóìåíòà......................92 Îïòèìèçàöèÿ êîäààíàëèçàòîðàWord-äîêóìåíòà.............................................92 ÎÁÐÀÒÍÎÈÇÒÀÁËÈÖÛEXCEL  ÄÎÊÓÌÅÍÒWORD.................................................92 Åùåîäíî “ñòðàøíîå” ñëîâî —OLE.......................................................93 Ïîñòàíîâêàçàäà÷è...............................................................................................93 Ïîäãîòîâêàòàáëèöû............................................................................................93 Ñâÿçûâàíèåòàáëèöûñäîêóìåíòîì.....................................................................95 Êàêñâÿçàòü ÿ÷åéêóðàáî÷åãîëèñòàExcelñòåêñòîâîéïîçèöèåéâäîêóìåíòå Word.................................................................................................................95 È ñíîâà VBA.........................................................................................96 Ïîñòàíîâêàçàäà÷è...............................................................................................96 Ìàêðîñ,êîïèðóþùèéòåêóùóþñòðîêóâñòðîêó2................................................96 Åùåïàðàìàêðîñîâ,íåîáõîäèìûõäëÿàâòîìàòè÷åñêîãî îòêðûòèÿèçàêðûòèÿ îêíàWord.............................................................................................................97 Ïîñòàíîâêàçàäà÷è...........................................................................................97 Îáðàáîò÷èêñîáûòèÿ ðàáî÷åãîëèñòàActivate...................................................98 Îáðàáîò÷èêñîáûòèÿ ðàáî÷åãîëèñòàDeactivate...............................................98 ÃËÀÂÀ 4 ÏÐÀÊÒÈÊÓÌÏÐÎÃÐÀÌÌÈÐÎÂÀÍÈßÍÀVBAÄËßEXCELÈWORD.............102 ÑÓÌÌÀÏÐÎÏÈÑÜÞ....................................................................................102 Ìîäóëè Visual Basic............................................................................102 Êàêäîáàâèòüêðàáî÷åéêíèãåExcelïðîãðàììíûéìîäóëü................................102 Ôóíêöèè............................................................................................103 Ïîñòàíîâêàçàäà÷è.............................................................................103 Êàêäîáàâèòüâìîäóëüïðîöåäóðó-ôóíêöèþ......................................................103 Ðàçðàáàòûâàåìèñõîäíûé òåêñòôóíêöèè Ñóììà_ïðîïèñüþ()...............104 ÈñïîëüçîâàíèåôóíêöèèFORMAT()äëÿïðåîáðàçîâàíèÿ÷èñëàâñòðîêó.........104 Àíàëèçòåêñòîâîéñòðîêè...................................................................................105 Èñïîëüçîâàíèå ôóíêöèè Ñóììà_ïðîïèñüþ()........................................110 Ñîçäàíèåïðîöåäóðû-îáðàáîò÷èêàñîáûòèÿChangeäëÿëèñòàðàáî÷åé êíèãèExcel.........................................................................................................110 8 Оглавление Çàùèòà îòâîçíèêíîâåíèÿêàñêàäíûõñîáûòèé..................................................111 ×ÀÑÒÎÒÍÛÉÑËÎÂÀÐÜ.................................................................................112 Ïîñòàíîâêàçàäà÷è.............................................................................112 Ðàçðàáàòûâàåìèñõîäíûé òåêñòìàêðîñà.............................................112 Èñïîëüçîâàíèå ìàññèâîâ...................................................................................112 Îáúÿâëåíèåêîíñòàíò.....................................................................................112 ÑîçäàíèåWord-äîêóìåíòîâèíîâûõàáçàöåââíèõïðèïîìîùèìåòîäàAdd...115 Óïîðÿäî÷èâàíèåýëåìåíòîâìàññèâàïîóáûâàíèþ............................................115 ÏðåîáðàçîâàíèåñòðîêWord-äîêóìåíòàâòàáëèöóïðèïîìîùèìåòîäà ConvertToTable...................................................................................................116 Àâòîôîðìàòòàáëèöû.........................................................................................116 Èñïîëüçîâàíèå ïðîöåäóðû×àñòîòíûé_ñëîâàðü...................................119 ÑÐÀÂÍÈÒÅËÜÍÛÉ ÀÍÀËÈÇÏÐÀÉÑ-ËÈÑÒÎÂ........................................................119 Âõîäÿùèå ïðàéñ-ëèñòû......................................................................120 Ïîñòàíîâêàçàäà÷è.............................................................................120 Ñâîäíûé ïðàéñ-ëèñò..........................................................................121 Ïîäêëþ÷åíèåàíàëèçèðóåìûõïðàéñ-ëèñòîâ......................................................121 Ïîäãîòîâêàðàáî÷åãîëèñòàäëÿñâîäíîãîïðàéñà..............................................122 Ñîçäàíèå ïðîöåäóðûÑâîäêà()............................................................123 Àâòîìàòè÷åñêîåçàêðûòèåôàéëàñ îòìåíîéñäåëàííûõâíåìèçìåíåíèé........125 Îñîáåííîñòè èñõîäíûõ äàííûõ èç ñðàâíèâàåìûõ ïðàéñ ëèñòîâ...........128 ÃËÀÂÀ 5 ACCESS:ÑÎÇÄÀÍÈÅÎÔÈÑÍÎÉÁÀÇÛÄÀÍÍÛÕ.....................................131 ÑÎÇÄÀÍÈÅÁÀÇÛÄÀÍÍÛÕMSACCESS............................................................131 ÑÎÇÄÀÍÈÅÒÀÁËÈÖ....................................................................................131 Âñïîìîãàòåëüíûåòàáëèöû−ñïðàâî÷íèêè.............................................132 Ñîçäàíèåñïðàâî÷íèêàêëèåíòîâôèðìû............................................................132 Êàêñîçäàòü òàáëèöóâðåæèìåêîíñòðóêòîðà.................................................132 Ñîçäàíèåñïðàâî÷íèêà îáðàùåíèéêêëèåíòàìôèðìû......................................134 Òàáëèöà “Êëèåíòû”.............................................................................134 Ïîäñòàíîâî÷íûåïîëÿ........................................................................................135 Êàêñîçäàòü ïîäñòàíîâî÷íûåïîëÿ ïðèïîìîùèÌàñòåðàïîäñòàíîâîê..........135 Òàáëèöà “Îïåðàöèè”...........................................................................137 Ñîçäàíèåñïðàâî÷íèêà òèïîâîïåðàöèé.............................................................137 Ñîçäàíèåòàáëèöûîïåðàöèé.............................................................................138 ÂÂÎÄÄÀÍÍÛÕ Â ÁÀÇÓÄÀÍÍÛÕACCESS...........................................................139 ÑÎÇÄÀÍÈÅ ÝÊÐÀÍÍÛÕÔÎÐÌ ........................................................................139 Ôîðìà “Âñåîïåðàöèè”.......................................................................139 Êàêñîçäàòü ýêðàííóþôîðìó ïðèïîìîùèÌàñòåðàôîðì................................139 Ââîääàííûõïðèïîìîùèôîðìû“Âñåîïåðàöèè”............................................140 Ïîä÷èíåííàÿ ôîðìà “Îïåðàöèè ïîêëèåíòó”.......................................140 Ôîðìà “Êëèåíòû”...............................................................................141 Ñîçäàíèåïðîñòîéôîðìû..................................................................................141 Äîáàâëåíèåïîä÷èíåííîéôîðìû......................................................................141 Êàêñîçäàòü ïîä÷èíåííóþôîðìóïðèïîìîùèÌàñòåðàïîä÷èíåííûõôîðì.....143 Êàêçàïðåòèòüèçìåíåíèåäàííûõ, îòîáðàæàåìûõôîðìîé................................144 ÓÑÎÂÅÐØÅÍÑÒÂÓÅÌÔÎÐÌÓÏÐÈÏÎÌÎÙÈVBA.................................................145 Àâòîìàòè÷åñêîå çàïîëíåíèå ïîëÿ Ñêèäêà............................................145 Ïîñòàíîâêàçàäà÷è.............................................................................................145 Êàêñîçäàòü ïðîöåäóðó îáðàáîòêèñîáûòèÿäëÿýëåìåíòà ýêðàííîéôîðìû.....146 À ó âàñ ñåãîäíÿ äåíü ðîæäåíèÿ!..........................................................147 Ïîñòàíîâêàçàäà÷è.............................................................................................147 Ñîçäàíèåîáðàáîò÷èêàñîáûòèÿCurrent............................................................147 ÈñïîëüçîâàíèåôóíêöèéMonth()èDay()........................................................147 ÃËÀÂÀ 6 Оглавление 9 ACCESS:ÀÂÒÎÌÀÒÈÇÀÖÈßÎÔÈÑÍÎÉÁÀÇÛÄÀÍÍÛÕ...........................149 ÀÂÒÎÌÀÒÈÇÀÖÈßÂÂÎÄÀÄÀÍÍÛÕ...................................................................149 Ïîñòàíîâêàçàäà÷è.............................................................................149 Ïîäãîòîâêà ðàáî÷åãî ëèñòà................................................................150 Ññûëêàíà áèáëèîòåêóîáúåêòîâ MS Access........................................151 Èñõîäíûé òåêñò ìàêðîñà,çàïèñûâàþùåãîîïåðàöèþâ áàçó äàííûõ Access...............................................................................................151 Àíàëèç ïðîöåäóðû Çàïèñü_îïåðàöèè..................................................152 Îáúåêòû MSAccess...........................................................................................152 Èñïîëüçîâàíèå îáúåêòàDoCmdäëÿ îòêðûòèÿ ôîðìû....................................153 Ñîçäàíèåíîâîéçàïèñèèïåðåõîäíàíåå......................................................153 ÀÂÒÎÌÀÒÈÇÀÖÈßÐÀÁÎÒÛÑÈÌÅÞÙÈÌÈÑßÄÀÍÍÛÌÈ.........................................154 Ñîçäàíèå ïðîãðàììíîãî ìîäóëÿ â ñîñòàâå áàçû äàííûõ......................154 Ññûëêè íàáèáëèîòåêè îáúåêòîâ: DAO èëè ADO?.................................156 Ôóíêöèÿ “Àíàëèçáàëàíñà ïîêëèåíòó”................................................156 ÏðîñìîòðçàïèñåéòàáëèöûAccessïðèïîìîùèöèêëàDo While…Loop............157 ÑîçäàíèåSQL-çàïðîñà......................................................................................158 ÈñïîëüçîâàíèåôóíêöèèMsgBox()äëÿîðãàíèçàöèèèíòåðôåñà, ïðåäîñòàâëÿþùåãîïîëüçîâàòåëþâûáîðî÷íîóïðàâëÿòüâûïîëíåíèåì ïðîãðàììû.........................................................................................................159 Ïðèíÿòèåðåøåíèÿ ïî ðåçóëüòàòàì àíàëèçà äàííûõ............................160 Èñïîëüçîâàíèå îáúåêòîâWordäëÿôîðìèðîâàíèÿïèñüìàèçäàííûõòàáëèöû Access................................................................................................................160 Îáúåêòû òèïà LetterContent............................................................................160 Ïîñòàíîâêàçàäà÷è.........................................................................................161 Àâòîìàòè÷åñêàÿ ãåíåðàöèÿ ïèñüìàêëèåíòó....................................................161 Ïðîãðàììíûé êîä ôóíêöèèÀíàëèç_áàëàíñà........................................163 ÃËÀÂÀ 7 ÂÌÈÐÅÎÁÚÅÊÒÎÂMSOFFICE............................................................167 ÐÀÁÎÒÀÑÎÁÚÅÊÒÀÌÈMSWORD..................................................................167 Ðàáîòà ñ îáúåêòàìè Word íàóðîâíå ïðèëîæåíèÿ èäîêóìåíòà.............167 ÎáúåêòApplication..............................................................................................167 ÑâîéñòâîCaption.................................................................................................. ............................................................................................................................. ............................................................................................................................. ............................................................................................................................. ............................................................................................................................. ............................................................................................................................. ..................................................................................................................... 167 ÑâîéñòâàActiveWindow,ActiveDocumentèActivePrinter..................................167 ÑåìåéñòâîCommandBars...............................................................................168 Ñâîéñòâà DisplayScrollBarsèDisplayStatusBar................................................168 Îáúåêò Options...............................................................................................168 Ñâîéñòâî AllowDragAndDrop:óïðàâëåíèåðåæèìîìïåðåòàñêèâàíèÿòåêñòàâðåæèìå Ïðàâêà.....................................................................................................................168 Ñâîéñòâî SaveInterval:çàäàíèåèíòåðâàëààâòîñîõðàíåíèÿ.......................................168 ÑâîéñòâîStatusBar.........................................................................................168 Ìåòîä Quit......................................................................................................168 ÑåìåéñòâîDocuments....................................................................................168 Ìåòîä Add...............................................................................................................169 ÌåòîäOpen.............................................................................................................169 ÌåòîäûCloseèActivate............................................................................................169 ÎáúåêòûActiveDocumentèThisDocument.......................................................169 ÌåòîäPrintOut:ïå÷àòüäîêóìåíòà.............................................................................169 Ìåòîä SendMail:îòïðàâêàäîêóìåíòàïîýëåêòðîííîéïî÷òå,êàêâëîæåíèå...............169 ÌåòîäClose.............................................................................................................169 Ìåòîäû SaveèSaveAs..............................................................................................170 ÎáúåêòRange:ðàáîòàñòåêñòîâûìñîäåðæèìûìäîêóìåíòà.............................170 10 Оглавление ÑâîéñòâîBold:âûäåëåíèåäèàïàçîíàïîëóæèðíûìíà÷åðòàíèåìåãî ñèìâîëîâ........................................................................................................170 ÑâîéñòâîInsertBefore:âñòàâêàñòðîêè............................................................170 Ïðîâåðêàïðàâîïèñàíèÿ.................................................................................170 Çàäàíèåâåðõíåãîêîëîíòèòóëà.......................................................................171 ÑåìåéñòâàParagraphs,Sentences,WordsèCharacters......................................171 ÑâîéñòâàFirstè Last.......................................................................................171 ÑâîéñòâàCount...............................................................................................171 ÑâîéñòâîAlignment.........................................................................................171 Ñîçäàíèåâîêðóãàáçàöàðàìêóçàäàííîãîñòèëÿ............................................171 Âñòàâêàòåêñòàïîñëåçàäàííîãîàáçàöà.........................................................171 Èçìåíåíèåôîíààáçàöà.................................................................................171 Èçìåíåíèåñòèëÿ àáçàöà................................................................................171 Èçìåíåíèåñòèëÿ àáçàöà................................................................................171 Ïåðåìåùåíèåïîòåêñòó.................................................................................172 ÌåòîäDelete..................................................................................................172 Ïðåîáðàçîâàíèåñèìâîëîâââåðõíèéðåãèñòð...............................................172 ÑåìåéñòâîStoryRanges..................................................................................172 Ðàáîòà ñî ñïèñêàìèè òàáëèöàìè äîêóìåíòà Word..............................172 Word-òàáëèöû....................................................................................................172 Óïðàâëåíèåãðàíèöàìè òàáëèö.......................................................................172 Óäàëåíèåñòîëáöàòàáëèöû............................................................................172 Èçìåíåíèåôîíàòàáëèöû..............................................................................173 Ñîçäàíèåòàáëèöû..........................................................................................173 Àâòîôîðìàòòàáëèöû......................................................................................173 Çàïèñüâ ÿ÷åéêèòàáëèöûäàííûõ...................................................................173 Èçìåíåíèåøèðèíû ñòîëáöîâòàáëèöû..........................................................173 Âûðàâíèâàíèåòåêñòàâòàáëèöå.....................................................................173 Äîáàâëåíèåçàãîëîâêàòàáëèöû.....................................................................173 Àâòîñóììèðîâàíèå ÿ÷ååêòàáëèöû.................................................................173 Ðàçáèåíèå ÿ÷ååêòàáëèöû..............................................................................174 Word-ñïèñêè.......................................................................................................174 Îáúåêò ListFormat...........................................................................................174 ÐÀÁÎÒÀÑÎÁÚÅÊÒÀÌÈMSEXCEL..................................................................174 Ðàáîòà ñ îáúåêòàìè Excel íà óðîâíå ïðèëîæåíèÿ, ðàáî÷åé êíèãè è ëèñòà.................................................................................................175 ÎáúåêòApplication..............................................................................................175 Ñâîéñòâà Workbooks,ActiveWorkbookèThisWorkbook....................................175 Ìåòîä SaveAs...........................................................................................................175 Ìåòîä Activate..........................................................................................................175 ÌåòîäClose.............................................................................................................175 ÌåòîäOpen.............................................................................................................175 ÑâîéñòâîActiveSheet......................................................................................175 ÑâîéñòâîName........................................................................................................176 ÑâîéñòâîDisplayScrollBars:ïðèìåð ñîêðûòèÿ ïîëîñïðîêðóòêè.....................176 ÑâîéñòâîStatusBar.........................................................................................176 Óâåëè÷èòüìàñøòàáàêòèâíîãî îêíà................................................................176 ÑâîéñòâîFixedDecimal....................................................................................176 ÌåòîäCalculate..............................................................................................176 ÑåìåéñòâîSheets...........................................................................................176 Ìåòîä Add...............................................................................................................176 Âñòàâêàëèñòàäèàãðàììû........................................................................................177 Ìåòîä Delete............................................................................................................177 ÑâîéñòâîVisible.......................................................................................................177 ÌåòîäMove.............................................................................................................177 ÌåòîäCopy..............................................................................................................177 Ìåòîä Quit......................................................................................................177 Ðàáîòà ñ ñîäåðæèìûìðàáî÷åãîëèñòà................................................177 ÎáúåêòRange.....................................................................................................177 Çàäàíèåäèàïàçîíà........................................................................................177 Ñâîéñòâî Offset..............................................................................................178 ÑâîéñòâîCells................................................................................................178

ebook img

Самоучитель программирования на VBA в Microsoft Office PDF

314 Pages·2001·20.658 MB·Russian
by  Хорев В. Д.
#Computers #Software: Office software
Save to my drive
Quick download
Download

Download Самоучитель программирования на VBA в Microsoft Office PDF Free - Full Version

by Хорев В. Д.| 2001| 314 pages| 20.658| Russian

Download Самоучитель программирования на VBA в Microsoft Office by Хорев В. Д. in PDF format completely FREE. No registration required, no payment needed. Get instant access to this valuable resource on PDFdrive.to!

Free Download PDF

About Самоучитель программирования на VBA в Microsoft Office

No description available for this book.

Detailed Information

Author:Хорев В. Д.
Publication Year:2001
Pages:314
Language:Russian
File Size:20.658
Format:PDF
Price:FREE
Download Free PDF

Safe & Secure Download - No registration required

Why Choose PDFdrive for Your Free Самоучитель программирования на VBA в Microsoft Office Download?

  • 100% Free: No hidden fees or subscriptions required for one book every day.
  • No Registration: Immediate access is available without creating accounts for one book every day.
  • Safe and Secure: Clean downloads without malware or viruses
  • Multiple Formats: PDF, MOBI, Mpub,... optimized for all devices
  • Educational Resource: Supporting knowledge sharing and learning

Free Books Similar to Самоучитель программирования на VBA в Microsoft Office

Frequently Asked Questions

Is it really free to download Самоучитель программирования на VBA в Microsoft Office PDF?

Yes, on https://PDFdrive.to you can download Самоучитель программирования на VBA в Microsoft Office by Хорев В. Д. completely free. We don't require any payment, subscription, or registration to access this PDF file. For 3 books every day.

How can I read Самоучитель программирования на VBA в Microsoft Office on my mobile device?

After downloading Самоучитель программирования на VBA в Microsoft Office PDF, you can open it with any PDF reader app on your phone or tablet. We recommend using Adobe Acrobat Reader, Apple Books, or Google Play Books for the best reading experience.

Is this the full version of Самоучитель программирования на VBA в Microsoft Office?

Yes, this is the complete PDF version of Самоучитель программирования на VBA в Microsoft Office by Хорев В. Д.. You will be able to read the entire content as in the printed version without missing any pages.

Is it legal to download Самоучитель программирования на VBA в Microsoft Office PDF for free?

https://PDFdrive.to provides links to free educational resources available online. We do not store any files on our servers. Please be aware of copyright laws in your country before downloading.

The materials shared are intended for research, educational, and personal use in accordance with fair use principles.

We use cookies

We use cookies to ensure you get the best browsing experience on our website. By clicking "Accept Cookies", you agree that we can store cookies on your device in accordance with our Terms and Privacy.