0402 is doable but hard without something to keep the alignment perfect.
The smallest i've done with a DIY method holding my stencil is 0603
The way I do it is to take some blank PCBs (minimum 4. ideally 6-8) and double sided tape them to some rigid cardboard to create a box around where the PCB to be paste'ed can 'drop' in. Then I tape the stencil to the top of the thing so i can lift it up and it will drop down in the same location. You do have to keep an eye on it, even doing it this way its location drifts a bit and this will be worse for 0402.
I usually use the microscope to check it between PCBs and nudge as needed and you will definitely need to keep checking it for 0402.
The key is, when you're doing the actual pasting, you need to stop the stencil sliding as you move the paste squeegee across the pcb.
One option is just pressing down hard so friction locks it and another is to put a few tiny dots of double sided tape between the stencil and some of the blank PCBs. You have to overpower them to lift up the stencil but if they are small that is usually ok. But you have to keep them well away from the main PCB area because they are adding a gap between stencil and PCB.
Also you need to be using the right size solder paste balls so you can paste through tiny apatures. T4 or T5 for example. I think my stuff is T4.
Also the temp of the solder paste will affect your results abit, i find it works better when the paste is colder than room temp but YMMV.
When it gets hot/runny it tends to produce more smudged transfers. That isn't always a problem for 2 pad devices as they tend to fix themselves with a little paste alignment error, but it can cause bridges for ICs.
Oh, and if you get it to work (and produce good results) but then for some reason it stops working properly, the cause is usually that the under side of the stencil needs to be fully cleaned with IPA and dried. Solder has come through to the other side of the stencil and is now preventing it from being totally flat against the PCB.
There is some art/skill to it, if you've never done it before then doing 0402 and getting good results will be challenging.
If you don't mind lots of rejects, and having to clean them and try again, then give it a try.
Just be aware that solder paste makes a mess and it gets everywhere, including under your fingernails and it contains lead.
So you want to control the area and make sure cleanup is easy. I would highly recommend always wearing gloves to avoid having to clean paste out from under your fingernails.

The pasting itself isn't that messy but the cleanup is for any PCBs you ruined and need to clean and try again.
The squeegee doesnt need to be super special, a debit/credit/library/airpoints card works ok as long as it's new and has a totally smooth edge.