Download "Débuter avec VBA Excel et la programmation"

"videoThumbnail Débuter avec VBA Excel et la programmation

Créer un site web de A à Z #2 Créer la maquette graphique du site (Adobe XD)

Créer un site web de A à Z #2 Créer la maquette graphique du site (Adobe XD)

Channel: Elio - GeeZMoKeZ

Manipuler les cellules en VBA avec l’objet Range [#22 FORMATION EXCEL VBA COMPLETE]

Manipuler les cellules en VBA avec l’objet Range [#22 FORMATION EXCEL VBA COMPLETE]

Channel: Excel Formation

Día 28 - Consultar en Access desde Excel con Macros, capacitación excel, vba

Día 28 - Consultar en Access desde Excel con Macros, capacitación excel, vba

Channel: El Automatizador

Tout sur les procédures Function en VBA [#07 FORMATION EXCEL VBA COMPLETE]

Tout sur les procédures Function en VBA [#07 FORMATION EXCEL VBA COMPLETE]

Channel: Excel Formation

Les variables en VBA Excel, premiers pas en développement

Les variables en VBA Excel, premiers pas en développement

Channel: Formations Excel, Access et bien d'autres

C'est quoi VBA ? [#01 FORMATION VBA EXCEL COMPLETE]

C'est quoi VBA ? [#01 FORMATION VBA EXCEL COMPLETE]

Channel: Excel Formation

Día 19 - Macro en Excel paso a paso con condicionales: capacitación excel, macros en excel,

Día 19 - Macro en Excel paso a paso con condicionales: capacitación excel, macros en excel,

Channel: El Automatizador

How did the evolution of complex life on Earth begin? - The Gene Code, Episode 1 - BBC Four

How did the evolution of complex life on Earth begin? - The Gene Code, Episode 1 - BBC Four

Día 27 - Insertar en Access desde Excel con Macros en Excel, capacitación excel, vba

Día 27 - Insertar en Access desde Excel con Macros en Excel, capacitación excel, vba

Channel: El Automatizador

Día 29 - Actualizar en Access desde Excel con Macros, capacitación excel, vba

Día 29 - Actualizar en Access desde Excel con Macros, capacitación excel, vba

Channel: El Automatizador

Formation VBA Excel

didacticiel VBA Excel

Visual Basic

programmation

programmation Excel

Code

Langage

Objets

propriétés

méthodes

macros

vba

macros excel

automatiser

programmer

applications

débuter programmation

développement

macros vba

with

range

cells

apprendrevba

vbaexcel

formationexcel

programmationexcel

00:00:01

this video is intended to take your

00:00:03

first steps with the

00:00:05

java programming language excel vba for visual

00:00:09

basic applications or for applications

00:00:11

this is why in red the vb has for

00:00:14

word for excel for powerpoint or

00:00:17

even for access the vba language is an

00:00:20

oriented language object this means that

00:00:24

we use

00:00:25

programming objects which designate for example

00:00:28

a cell in an Excel workbook or

00:00:30

rows of columns to which

00:00:32

properties and methods are associated.

00:00:35

The properties allow us to

00:00:37

personalize the objects for example a

00:00:40

cell we can with its

00:00:42

properties customize the color of a

00:00:46

cell as well as the color of the text or

00:00:48

the size of the text etc.

00:00:49

the methods allow you to carry

00:00:52

out actions in power such

00:00:54

as inserting cells inserting

00:00:56

lines deleted from cells etc

00:00:58

etc

00:00:59

the simplest method to learn

00:01:03

the bb1 is still to ask

00:01:06

excellent to create a macro years we

00:01:09

record and we will see that each

00:01:11

macro is associated with a code

00:01:14

the first thing to do is to display

00:01:16

on your workspace

00:01:18

the developers ribbon to do this you

00:01:21

pass you go through the file menu

00:01:23

the option section is in the

00:01:26

dialog box that appears or customize

00:01:30

the ribbon by asking to display the

00:01:33

developers ribbon in the piggy bank all

00:01:36

you have to do is validate and you

00:01:37

notice that in this developers ribbon

00:01:39

you have all the functionalities

00:01:41

in particular to manage the

00:01:43

automatic hands but also to access the

00:01:46

associated visual basic code so we are

00:01:50

going to record a macro which

00:01:51

consists of putting a cell of a

00:01:54

certain color in bold with a background

00:01:57

to do this we preselected a

00:02:00

cell and we click on record

00:02:03

a macro as we know how to do there is

00:02:05

also dedicated support on the

00:02:08

site www.grandbaie.mu which explains

00:02:10

how to create and manage automatic mac pro

00:02:12

in the excellent let's name 7

00:02:15

macro as always without emphasis test space

00:02:20

mf for formatting test we

00:02:23

record just to place ourselves and not

00:02:25

for the model because it is a test

00:02:28

also we do not saturate and

00:02:31

when we click on ok

00:02:33

as you notice excel is in the

00:02:34

process of we record each

00:02:36

excellent action worth to transcribe to the

00:02:38

vba code we said that we wanted to

00:02:41

make the cell a large one and then

00:02:45

change the color of the text

00:02:48

and then associate a background to the cell

00:02:52

with a different color

00:02:54

we could say for example yellow

00:02:57

that's it

00:02:58

so right away we have to stop

00:03:01

the recording of the macro we

00:03:02

will go back to the developers bench and

00:03:05

we click on the stop

00:03:06

recording button for the actions to

00:03:09

come and before heading towards the code

00:03:11

we will associate a button with this macro

00:03:14

so that it is more

00:03:17

meaningful and easier to access to

00:03:20

do this and this is always what

00:03:22

the support on the bondage point fr site reminds us

00:03:24

we will go through the option file

00:03:28

again we will add our mother-of-

00:03:31

pearl buttons or in the

00:03:32

quick access bar that is to say next to the

00:03:34

functionalities to save undone

00:03:36

or even redo

00:03:37

so we choose the

00:03:41

quick access toolbar category in the middle we

00:03:44

choose the macro category and in

00:03:46

the list of macros you notice that

00:03:49

your mac os appears then these

00:03:51

are old ones that we can

00:03:53

delete we have to do and we are going to

00:03:56

add the macro that we have just created by

00:03:58

clicking on add

00:03:59

and then to personalize it

00:04:02

we are going to modify its button by

00:04:03

clicking on modify and it associate

00:04:05

a very simple image like this one

00:04:07

we do hockey and a second time

00:04:10

ok to invite it the other dialog box

00:04:11

and you see that your button

00:04:13

appears here

00:04:14

cost 6 we select a cell

00:04:16

even a range of cells and that our

00:04:18

little idiots on your mackerel sheep

00:04:19

you noticed that all our

00:04:21

settings save me apply in

00:04:24

Africa so to return to an original formatting

00:04:28

we will take the formatting

00:04:30

from a cell which has not been

00:04:32

applied to all the cells that

00:04:34

we have modified now accessing

00:04:36

the vba codes we have the

00:04:39

developers ribbon we are going to click on the

00:04:41

visual basic button which is here and you

00:04:44

notice that you switch to

00:04:46

the visual basic code editor for

00:04:49

excel a priori it is empty in reality

00:04:52

your code is always saved in

00:04:54

a module so we deploy

00:04:56

the display of the module in the

00:04:58

projects window and we click on modules

00:05:01

1 and here is the code of your mac row you

00:05:05

noticed that there is the name of your

00:05:08

micro it is a function so the standard in pro

00:05:11

in object oriented programming is

00:05:13

that there are always parentheses because

00:05:14

it can reach parameters here

00:05:15

there are none so we open and close the

00:05:17

parentheses the code of a vba macros

00:05:20

was taken between the terminals of the

00:05:23

procedure sub and ncube here is all the

00:05:26

code everything that is preceded by glass

00:05:28

is a comment it does not act on the

00:05:31

code moreover we could very well

00:05:32

delete it then we will

00:05:35

comment on the lines which are

00:05:37

below you noticed here the presence of

00:05:42

what we call an object the

00:05:44

selection object selects selection it

00:05:47

designates as its name indicated the

00:05:49

active selection at the time of executing

00:05:52

migros this means the

00:05:53

selected cell or the cells

00:05:55

selected for each object as we

00:05:57

said earlier, a property is associated with it

00:05:59

so here we have the

00:06:02

font property of the selection object which

00:06:05

designates the font of healthy cells

00:06:08

to operate except that the font is

00:06:11

too generic a term what do we do with the

00:06:13

font is this that we modify its

00:06:15

size its color its aspect large and

00:06:18

so on and so we are going in

00:06:20

the sequence to use a property

00:06:22

derived from bold of the property both make

00:06:26

the bolt selection object for bold and

00:06:30

we made it that all our values

00:06:32

it can be either true thirst rise

00:06:35

or roux or falls a saft property

00:06:40

is a value or the rule has a value

00:06:42

so writing selection point

00:06:45

font point bold equals hole this means

00:06:46

that we put the selection in bold

00:06:49

after noticing the presence of certain

00:06:53

lines like these instructions grouped

00:06:58

in a web block and we've weave

00:07:01

selection point funds in fact we

00:07:04

might even be surprised that the

00:07:06

first line is not part of this

00:07:08

block with a block where it allows us to

00:07:10

group an object a property for

00:07:13

list all the properties of the derivatives

00:07:15

which follow

00:07:17

rather than repeating on each line

00:07:19

selection point fountain color selection

00:07:22

for the background of points in time scheid

00:07:24

here what the vb ans code does so

00:07:28

it is to apply a background color to

00:07:33

the cell at the selection it uses a

00:07:37

property derived from the property cast

00:07:39

of the celestial object which is called such

00:07:41

colloquy tm color with a value it is

00:07:45

very difficult to understand know that

00:07:46

commodity bea if you want to know more

00:07:49

about a property or an object you

00:07:52

select the keyword and you press

00:07:54

the f1 key on the keyboard

00:07:56

so that

00:07:58

you will be redirected to the

00:07:59

online contextual help as you

00:08:02

see which will allow you to

00:08:05

find information on the property

00:08:07

and examples of use so it is

00:08:10

a method like any other to

00:08:12

find out more when for example you

00:08:15

retrieve a code which is written

00:08:17

automatic macro permit

00:08:18

in this case here this property does

00:08:21

not interest us too much we will

00:08:23

prefer a property which is called

00:08:25

color which is used with an

00:08:28

rgb function for red green in blue

00:08:31

it is the French equivalent of rgb red

00:08:33

green blue to which we will give

00:08:36

color component values

00:08:41

to define the background color of the

00:08:43

cell imagine that we want that the

00:08:45

background color of the purple silk cell

00:08:47

for example well we

00:08:51

can use software like paint

00:08:53

or photoshop passed through a

00:08:56

color manager in order to

00:08:59

position the color on the one

00:09:02

we want taken to see

00:09:04

that for example for a rape is

00:09:05

as we rent the 143.30 931 suits us well

00:09:11

143 39 31 and well in the components

00:09:15

of the mgb

00:09:18

you see that when you have the

00:09:20

parenthesis vba expects the three values

00:09:23

of the component we have ten years

00:09:25

43.39 1.131 very well if we test this

00:09:31

code that we return to the

00:09:33

excel sheet and that we click on our

00:09:36

mackerel button you noticed that

00:09:39

we know how to apply the color well

00:09:41

then the color that we applied

00:09:43

here is in fact the color of the font

00:09:46

and not the color background since

00:09:49

it is the font property of the

00:09:51

selection object and you see that in fact the

00:09:54

color of the font passes have violated the

00:09:57

property which is below

00:09:59

is a property which you can

00:10:01

find with the f12 key on the keyboard which

00:10:04

allows us to say to influence the

00:10:07

density of colors more or less are

00:10:09

more or less clear we have not

00:10:11

adjusted it does not interest us so

00:10:13

we are going to delete this line to

00:10:15

see that after having recorded

00:10:20

when they count on our

00:10:23

mackerel button stood exactly the same

00:10:25

result in fact we can therefore purify our

00:10:28

power our code sorry know that

00:10:32

to be able to save the pimps

00:10:35

associated with a workbook you must

00:10:36

save it in xls m6 format us in

00:10:41

classic format all your taxes are

00:10:42

lost for security reasons here

00:10:45

to continue to purify our body of

00:10:48

your remarks and that as a result

00:10:49

of having grouped here halak the

00:10:52

with instruction is no longer entirely interesting

00:10:54

since there is only one

00:10:55

interior island left just at the on it we

00:10:58

have this isolated mine so what we

00:10:59

're going to do is this we're going to

00:11:01

take the end of the code so we can

00:11:03

group them and from the instruction it we

00:11:05

learn to code a clean way and

00:11:08

we save and if we go back

00:11:11

to our sheet to test a hitch

00:11:14

the result is the same except that the code

00:11:16

is cleaner

00:11:17

if we move to the block below you

00:11:21

see that the property interior interior is associated with the selection object

00:11:25

for the front of the

00:11:28

cell quite simply the pattern

00:11:31

designates the pattern fill we have

00:11:34

not defined

00:11:35

here it is solid that means it is

00:11:37

normal and an automatic color

00:11:39

but at no time have we designated

00:11:42

patterns all this comes from the fact that

00:11:44

when we go through the

00:11:46

fill button of the ribbon excellent it is

00:11:50

possible to define a pattern so

00:11:52

excellent when they register you

00:11:54

register the fact that you have a

00:11:56

background color and you have not

00:11:58

chosen patterns so it describes but

00:12:00

we do not need so

00:12:02

we can delete here you see

00:12:06

that hills it still uses the

00:12:09

property and vba excel or let's say when

00:12:11

the color property of the

00:12:14

interior property of the selection object to

00:12:16

assign a background color to the

00:12:19

cell in the same way we could

00:12:21

choose a background color with the

00:12:25

rgb function that is to say the three

00:12:27

color components then as we

00:12:29

have a text color which is

00:12:30

violence we could choose a

00:12:32

background color which would be for example

00:12:34

we had yellow here

00:12:36

well we would have to choose another

00:12:38

color with paint this which would

00:12:40

bring out the purple we could

00:12:42

choose for example a light blue like

00:12:44

this you see so it's a

00:12:46

possibility it won't be very pretty but

00:12:48

hey it's for manipulations for

00:12:49

example if we take wine 200 200 it

00:12:54

seems suitable

00:12:55

we're going to try that and we're going to test

00:12:57

so we come back to the corner to send

00:12:59

that we make small modifications

00:13:01

like these 20 and then to nadi 200 and

00:13:05

then two hundred for the three

00:13:08

components red green blue we

00:13:10

record we returns to the

00:13:13

excel sheet and disputes what the modifications are,

00:13:17

see that there the errors of the

00:13:18

modifications are clear since we have

00:13:19

preserved the color of the text on

00:13:21

the other hand we really changed the

00:13:24

false color faithfully to what we took

00:13:27

from the editor paint colors

00:13:29

so little by little we learn at the same

00:13:31

time the properties and objects of

00:13:34

vba and we modify some values to

00:13:38

see in real time thanks to our button

00:13:40

and the direct impacts on the

00:13:42

selections the cells and it's like

00:13:44

everything at the moment

00:13:46

this property we do not need

00:13:47

and the father that we know concerns

00:13:49

once again the pattern so that

00:13:52

means what we can delete it

00:13:54

suddenly if we delete francs the weave block

00:13:57

no longer has much interest since it is used to

00:13:59

group the instructions of the

00:14:02

properties listed for an object

00:14:04

one under the other in a more fun way

00:14:06

in this case here suddenly the

00:14:08

weave block and too since there is no

00:14:11

enumerated list so we have all

00:14:13

grouped together on one and moore so

00:14:16

that the code is even simpler and

00:14:19

cleaner and little by little starting from

00:14:22

the code returned by the

00:14:23

rent tax ape that suddenly by doing tests

00:14:26

we obtain a code which is extremely

00:14:28

simple and which strictly carries out the three operations

00:14:30

that we have defined with

00:14:31

the macro we will still test to

00:14:33

ensure that everything is functional again

00:14:36

otherwise clicking on the button in macau

00:14:38

you see that although the code is

00:14:40

extremely refined by compared to

00:14:42

what it was, well we obtain

00:14:44

exactly exactly the same

00:14:46

results

00:14:47

so that's it, it's really very

00:14:50

interesting for the method which consists

00:14:52

of getting straight into the code and

00:14:56

blissfully but gently to learn the

00:14:59

programming objects the properties

00:15:01

which actually allow us to create a

00:15:03

little bit of soul at the front through the speaker

00:15:05

what we are going to do is that we are going to create

00:15:07

a bb1 macro from scratch by hand,

00:15:11

that is to say without going through by

00:15:12

automatic recording

00:15:13

to do this the simplest we will

00:15:16

retrieve the code from our microphone and then

00:15:19

we will the pass is below you

00:15:21

noticed two things the first is

00:15:24

that yesterday a separation line which

00:15:26

clearly shows that the code a procedure and

00:15:29

writings between the sub and extra terminals

00:15:31

and the second is that we have

00:15:33

macon angles which have well no that

00:15:35

's impossible so we have to modify it

00:15:38

here what we are going to do is is for

00:15:40

example changing the name is bad

00:15:43

for formatting for example and we

00:15:45

could say black what we are going to do

00:15:46

is that we would like for example

00:15:48

this time a white text on a

00:15:52

black background so a register you see that

00:15:56

the very important keywords

00:15:57

always appear in blue in

00:16:00

the quad bb1 editor and we therefore said

00:16:03

that we wanted we are going to say a white text

00:16:05

on a black background so the white as

00:16:08

you know you just have to

00:16:10

select it for example here the three

00:16:12

components at the maximum so that means

00:16:14

three times 255 and conversely

00:16:16

black is the three values at 0 so

00:16:19

if we want to assign the

00:16:25

white color to the texts point font selection that

00:16:28

makes the font look good we said the

00:16:31

three values should being set at 255

00:16:33

we are writing this time

00:16:35

and manually the code

00:16:37

the other procedure very well and we

00:16:40

said that we wanted our

00:16:42

funds to appear what we note so we

00:16:45

write the three components as

00:16:47

we 'we indicated earlier

00:16:48

this time we have our

00:16:51

procedure but the button that we

00:16:52

created were attached to the hand

00:16:54

east coast sea and not one but this month so

00:16:56

how to test it and well we

00:16:58

can attest directly from

00:17:00

the editor to do this we will

00:17:02

preselect a page of cells

00:17:03

like this one we will return to

00:17:05

the editor and you see that in water

00:17:07

first you click in the banks of

00:17:09

the procedure requires executed so that

00:17:11

ribéry excels the know and you will click

00:17:14

on the execution button which is here

00:17:15

you leave a priori nothing happens

00:17:17

why because it goes very quickly on the

00:17:19

other hand if you come back here on

00:17:22

your sheet see that the mac os and

00:17:25

executed well on the selection by

00:17:27

assigning porthole to the text on a

00:17:30

black background exactly as we defined it

00:17:31

now imagine that we

00:17:35

want to assign to these cells a

00:17:39

border of white color

00:17:41

well we do not know what is the

00:17:43

property of the selection object which

00:17:47

allows to create a border

00:17:49

we could go through the

00:17:51

object or property explorer having

00:17:54

an even simpler method you know it just

00:17:56

needs to create a macro which

00:17:58

records us doing it this is what

00:18:00

we are going to do we are going to

00:18:01

select a cell

00:18:04

in the developers ribbon we click

00:18:06

on record a macro we will

00:18:08

call it borders and we will click on

00:18:11

ok the goal of the game is to be able to

00:18:15

apply a border so here let's

00:18:19

choose the most ribbon welcomes a

00:18:21

border like this then obviously black

00:18:25

on black that either not very well

00:18:27

what is the principle but you see that

00:18:28

we have the border which appears

00:18:29

here I should not have clicked in

00:18:31

this cell since excellent we have

00:18:34

saved but it does not matter we

00:18:35

just want to recover the code of the

00:18:37

border of the ngo the developers we

00:18:40

click on stop recording and

00:18:41

we return to our friend visual

00:18:43

basic and you noticed that we have

00:18:45

a new procedure edges du rhône where

00:18:47

the balm or who knows well written in

00:18:48

vba excel language under the previous ones

00:18:50

the quad looks very long for just

00:18:54

applying a border in passing the

00:18:56

gnaws h very select here it is the

00:18:58

selection that I made involuntarily

00:19:00

at the end we delete it in fact why

00:19:03

these long because excellent handles

00:19:05

the borders one by one that is to say the

00:19:08

border from left to right etc etc

00:19:10

the property is therefore the

00:19:12

borders property to which we will define what

00:19:15

is the trait others take care here

00:19:18

outré left outré from above the one

00:19:20

below the one on the right

00:19:21

so we're going to take inspiration from that and then we

00:19:27

're going to take it like these by

00:19:29

copying it and we're going to integrate it into our

00:19:31

code o the colon so it's very

00:19:36

interesting sent to learn

00:19:38

once again it's very very practical the

00:19:42

principle here is to say that we

00:19:46

want them to treat the entire

00:19:48

border once it is possible with

00:19:50

the object of the borders property by

00:19:52

pardon it is simply enough not to

00:19:55

indicate anything between the parentheses and

00:19:57

the bays with those including that

00:20:00

we treat the four corners of the

00:20:02

border here we still have this

00:20:05

property which does not interest us for

00:20:07

the contrast of the color on the other hand

00:20:09

property derived from the borders property

00:20:12

of the lines times selection object

00:20:15

here you have seen the value it

00:20:19

simply means that we opted for

00:20:21

a continuous line because we could have

00:20:22

chosen a very good shot and his

00:20:25

thoughts the f1 key on the keyboard on the

00:20:27

property the list to know how it is

00:20:29

used that they are the values that

00:20:31

their indexes by default zero

00:20:33

and then a very interesting property

00:20:36

weitz on the value excellent medium that

00:20:40

means

00:20:41

the thickness of the border line

00:20:43

thick enough to translate literally we

00:20:46

would for example make a

00:20:48

border color which is white

00:20:49

so rather than color index can we

00:20:51

use the property color to adjust

00:20:55

the color of a border this is what

00:20:57

we are going to see we are going to try like these we

00:21:00

save and then we are going to test

00:21:03

our code on another selection let

00:21:08

's return to the editor places us

00:21:11

between the benches of the procedure and

00:21:13

we click on read and you

00:21:16

notice in fact that we have indeed

00:21:19

our result on a black background had

00:21:22

white text and a white border of

00:21:23

separation if everything else here were

00:21:27

of a certain color we would

00:21:30

still see at best the white border as

00:21:33

you do notice here

00:21:34

except that in fact here we are going to do it again

00:21:37

we have replaced the background color

00:21:40

of a selection here it is much

00:21:43

clearer

00:21:45

we have once again learned

00:21:48

something new

00:21:49

thanks to the automatic macro of Excel

00:21:53

didn't know how to create bourdieu we

00:21:55

asked ixelles to register us

00:21:57

while we don't fear her with the

00:22:00

home ribbon we got the

00:22:03

code and we adapted it so it's

00:22:05

very simple when you see this macro

00:22:08

only exists under form of code is not

00:22:11

in the form of buttons well it is

00:22:12

not because we have not created

00:22:14

automatically we cannot create a

00:22:16

button the procedure is exactly the

00:22:18

same

00:22:19

we return to our excel sheet

00:22:22

we go back by file then options

00:22:24

is again we go to the

00:22:27

quick access toolbar we choose

00:22:29

the mackerels in the list workers

00:22:30

which has the same border these do

00:22:32

not interest us there is another macro

00:22:34

written by hand that we add to

00:22:36

which we go associate a bench

00:22:38

just like this one for example

00:22:40

and then we validate and if we test

00:22:42

normally then we will be able to

00:22:45

execute our code that

00:22:47

's perfect you see

00:22:49

directly from the button on the

00:22:51

quick access bar it's really very

00:22:52

interesting it is written by hand and

00:22:54

we attached a button so

00:22:56

to purge our work well these

00:22:59

procedures that we used

00:23:01

as an example so that excellent

00:23:03

shows us can be deleted since it

00:23:05

was deleted from the suddenly it will no

00:23:06

longer appear in the list of macros which are

00:23:08

available to associate them with

00:23:12

buttons finally and to finish with

00:23:15

this smooth presentation of vba

00:23:17

excel

00:23:18

we would like to know what are the

00:23:20

objects or the object use excel to

00:23:23

precisely designate a cell a

00:23:24

range of cells because in this case

00:23:26

it is a question of selection that means

00:23:29

once the cells are already

00:23:31

selected but if we want to

00:23:32

make modifications with the code on

00:23:34

precise series a precise target range

00:23:36

what is it it is good once again

00:23:38

we are going to ask with that to

00:23:40

show us that is to say that it will

00:23:43

record the fact that a selection

00:23:44

we a cell precisely

00:23:47

so we click on the ribbon

00:23:48

expand we click on record

00:23:51

a macro we choose lambda for

00:23:54

this one shot in this workbook we which

00:23:56

counts on integer and the goal of the game is

00:23:58

to click on a particular cell

00:24:01

and we immediately stop

00:24:03

the recording when we

00:24:05

return to the code you see which has been

00:24:07

created the procedure cell

00:24:10

always with these parentheses between the

00:24:12

south and the vein sub the comments here

00:24:15

it deserves to be personalized

00:24:18

because not very interesting at this stage

00:24:21

yes that the object which allows you to designate

00:24:23

a particular cell in vba excel

00:24:26

is the object orange orange associated here with

00:24:31

a method method which does an action

00:24:33

this is the ex it means

00:24:35

select the cell like the object

00:24:38

red the selection object allows you

00:24:41

to affect the cells of a sheet

00:24:44

so they have the same properties

00:24:45

methods this means that I can very

00:24:48

well set the font and the background of the

00:24:51

cell through the orange object

00:24:53

look what is interesting we

00:24:56

are going to copy this part of the code and

00:24:59

we are going to put a dot to call

00:25:01

the properties methods and the missed

00:25:02

that a drop-down list is triggered

00:25:04

listing for the object in question all

00:25:07

the properties and methods that are

00:25:09

available so it is very interesting

00:25:11

for yours is easy when they

00:25:14

are preceded by a green icon this

00:25:15

is 'a method therefore an action

00:25:16

when they are pressed preceded by an

00:25:18

icon hungary it is a property therefore

00:25:20

a value and we will personalized

00:25:22

we celebrate customize the font

00:25:24

doubt the pile for the first three

00:25:27

letters you see that font and well

00:25:28

available is - so we

00:25:30

can personalize its cut its

00:25:33

color always the same color it's

00:25:35

always the same no problem and here we are

00:25:38

going for example to put red so

00:25:42

as the three components are red

00:25:44

green blue well we just have to force

00:25:46

on the red

00:25:48

you see that there also you have a

00:25:49

real time tooltip which is the

00:25:51

component of eleven yellow at the maximum and

00:25:53

then nothing for the others so we

00:25:55

should get red

00:25:56

what we are going to do is we are going to test

00:25:59

that it is for the police it is still necessary

00:26:01

to have entered a text leave say so

00:26:04

we are going to enter a text like these

00:26:07

cellulite select moreover

00:26:08

it is not necessary since the code

00:26:10

does it places us against the bands of

00:26:12

the procedure we execute either again

00:26:14

there is 11.5 from editors let's return

00:26:18

to the sheet and you see that we

00:26:20

have written our text in red

00:26:21

after having selected the cu through

00:26:24

the orange object

00:26:26

we can in the same way at this

00:26:28

moment look take a code which

00:26:30

works for the selection object and

00:26:35

assigned it to Rennes like this so the

00:26:40

0-0 Sédunois we will see what it is

00:26:44

we do f5 and we have put

00:26:47

the background of the cell in black which is

00:26:50

interesting with the orange object it is

00:26:53

that we can designate more than one

00:26:56

cell leberon designate a range of

00:26:57

cells exactly as we do

00:26:59

in the excellent functions

00:27:01

i.e. the references of the cells

00:27:03

separated by two poles imagine for

00:27:06

example that you wanted to assign these

00:27:08

settings to this range of cells

00:27:10

which simply goes from jets 10

00:27:12

to h 11

00:27:14

and in the code it is

00:27:17

simply enough to indicate that the

00:27:20

cells concerned are in fact those

00:27:24

of the range of cells g10 to h 11

00:27:28

and we replace it like this in the

00:27:31

illustration code we do f5 and when

00:27:34

we return to the sheet we

00:27:35

have assigned personalize the

00:27:39

properties of the cells of the range of

00:27:41

cells designated by the wedge object we

00:27:43

begin to progress and see a little

00:27:46

more clearly as for the control of objects

00:27:48

with the blissful excellent know that

00:27:51

there is also a very

00:27:53

particular object to designate a cell

00:27:55

according to its row index and two

00:27:57

columns this is the object cells cells course

00:28:01

cells which when the 'we open the

00:28:03

parenthesis we will not ask for the

00:28:06

letter of the column and row number

00:28:07

but the number of the row and the number

00:28:09

two columns imagine for example that

00:28:11

we want to modify and property of

00:28:17

the cell its 10

00:28:18

therefore row 10 and the column it is

00:28:22

therefore the 3com column the cells object

00:28:25

designates cells

00:28:26

just like orange and selection it has the

00:28:29

same properties as the other two

00:28:32

which means that we can very well

00:28:34

associate the error messages with it is

00:28:35

completely normal because the berrics she sees

00:28:38

that the code is not finished while

00:28:40

we go elsewhere it is normal we

00:28:42

are doing it at the foot we are therefore going

00:28:43

to take what we had done for

00:28:45

object shift the colleague like this and then

00:28:48

while we are doing we are going to duplicate this

00:28:50

line and we are also going to modify

00:28:55

the interior of the cell in the same way by copying

00:28:59

echo in the code

00:29:00

so we said that the scene they

00:29:03

say is this one we agree

00:29:05

for the moment it is not not affected

00:29:07

if we execute it what is it

00:29:10

you see that the result is the same

00:29:13

but with the object that is therefore a

00:29:15

vba excel object allowing to control

00:29:18

a cell in particular orange cones

00:29:21

but in two different ways

00:29:23

here it is necessary know that to

00:29:25

personalize cells it is not

00:29:27

necessary to select it first

00:29:28

so we can remove this idea

00:29:31

and if we want to make it clean the most

00:29:34

correct would be to group its

00:29:37

instructions in a quiz block so as

00:29:40

not to repeat objects from each time.

00:29:42

programming for example here so there we

00:29:44

will see an error message this is normal

00:29:46

we will group under the orange weave instruction

00:29:49

like these the properties that are

00:29:53

left to us is not necessary in

00:29:55

fact as long as it is a matter to repeat

00:29:58

things which can be optimized as

00:30:01

is the case here do not forget to

00:30:02

close the doors of the procedure

00:30:05

notice that if you do not respect

00:30:07

the case and well ribery that corrects it for you

00:30:09

in real time and to this content

00:30:12

we must doing the same thing for the

00:30:14

two minutes fuchs is more structured

00:30:16

we see more clearly and you see that

00:30:18

suddenly we have a code which in addition to

00:30:22

being clear is completely understandable

00:30:23

and readable

00:30:25

so we delete that it's interesting and

00:30:27

important to have this

00:30:30

programming rigor when you code

00:30:33

whatever the language there are the

00:30:35

instructions proposed by vba excel in

00:30:37

this case hendry will not respect

00:30:39

the case when we validate and bea does not

00:30:42

correct we will make a small and last

00:30:44

test

00:30:45

we will resume with the ribbon welcomes

00:30:48

a neutral format to

00:30:50

apply it to all of these

00:30:52

cells we therefore return to the starting state

00:30:53

and we will again execute

00:30:56

our code by f5 for example and that In

00:30:59

fact, you see that everything has been

00:31:01

reflected so we have modified

00:31:03

our code by structuring it so

00:31:06

what we must remember here is that

00:31:07

automatic macros allow us to

00:31:09

discover programming objects

00:31:11

with their method properties.

00:31:13

inspire we recover them we read modify

00:31:16

and suddenly we learn and we are able

00:31:18

to code things which have

00:31:21

influences direction of infel ducasse and

00:31:23

rexel and

00:31:26

condemnation instructions like the weave m

00:31:27

louise blocks which allow to properly structure

00:31:29

the club of billiard group is rather than

00:31:31

repeating the heavy instructions which

00:31:34

in the end multiplies the lines of code and

00:31:37

makes it much less visible

00:31:39

here you can find even

00:31:42

more information the paper support

00:31:44

on the site www.google.cn do for

00:31:47

getting started with vba excel and programming

Description:

Apprendre à programmer en Visual Basic sous Excel pour créer des applications professionnelles très puissantes et répondre aux besoins d'entreprise. Le VBA Excel est un langage orienté Objets qui permet de manipuler tous les éléments d'un tableau, d'une feuille ou d'un classeur Excel. Ainsi cette formation propose de découvrir quels sont les objets de programmation fondamentaux sous Excel ainsi que les propriétés et méthodes pour tirer partie de toute la puissance d'Excel. Entraînez vous avec la version complète sur le site : https://www.bonbache.fr/debuter-avec-vba-excel-et-la-programmation-118.html

Preparing download options

Popular

HD video

Only sound

All

* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."

** — Link intended for online playback in specialized players

Questions about downloading video

How can I download "Débuter avec VBA Excel et la programmation" video?

http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.
The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.
UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.
UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.

Which format of "Débuter avec VBA Excel et la programmation" video should I choose?

The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.

Why does my computer freeze when loading a "Débuter avec VBA Excel et la programmation" video?

The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.

How can I download "Débuter avec VBA Excel et la programmation" video to my phone?

You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.

How can I download an audio track (music) to MP3 "Débuter avec VBA Excel et la programmation"?

The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.

How can I save a frame from a video "Débuter avec VBA Excel et la programmation"?

This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.

What's the price of all this stuff?

It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.