excel - Change Sub to Function then Call in vba -

i have password generator sub, want change function , use in macro generate column starting on b2 each cell after unique password.

the thing delete b1 header cell.

thanks

i have sub:

sub randompassword()  dim integer    = 1 8     if mod 2 = 0         strpassword = chr(int((122 - 48 + 1) * rnd + 48)) & strpassword     else         strpassword = int((9 * rnd) + 1) & strpassword     end if  next  msgbox strpassword end sub 

my atempt , turning in function:

function randompassword(strpassword string) string  dim integer   = 1 8     if mod 2 = 0         strpassword = chr(int((122 - 48 + 1) * rnd + 48)) & strpassword     else         strpassword = int((9 * rnd) + 1) & strpassword     end if  next end function 

my calling it:

sub qqq()      dim rng range     dim lastrow long      sheets("sheet1")         lastrow = .range("b" & .rows.count).end(xlup).row     end      each rng in sheets("sheet1").range("b2:b" & lastrow)         rng.value = randompassword(rng.value)     next end sub 

you need assign value of strpassword variable function randompassword

function randompassword(byval strpassword string) string     dim integer      = 1 8         if mod 2 = 0             strpassword = chr(int((122 - 48 + 1) * rnd + 48)) & strpassword         else             strpassword = int((9 * rnd) + 1) & strpassword         end if     next      randompassword = strpassword end function 

also in below procedure getting last used row in column b , overwriting them random password. feel need last used row of column a instead.

sub qqq()      dim rng range     dim lastrow long      sheets("sheet1")         lastrow = .range("a" & .rows.count).end(xlup).row     end      each rng in sheets("sheet1").range("b2:b" & lastrow)         rng.value = randompassword(rng.value)     next end sub