cannot use public var vba

Question

i am wrting a vba code where i will set some public var to use in many subs vars declared in userform code like this

public xxxx as string

sub vars()
xxxx = "test"
end sub

Private Sub CommandButton1_Click()
' test button 
MsgBox ("pub var is " & xxxx)
End Sub

when i click in test buton the msg box is blank looks like it cant see the pub var aney ideas where is the problem (even if i set public xxxx as srtring in the code of a module or thisworkbook i get the same result) thanks

What is probably happening is, you run the vars sub and it saves "test" to xxxx, then the script stops running. Once the script stops, all your variables disappear - nothing is saved. You would need to call vars within the CommandButton1_Click sub - if you really need to save a value between runs of the script, either save it in a cell (if using excel) or in a table (in Access). — Acantud
– Acantud, Commented Jul 10, 2014 at 17:30
1. Where did you call subroutine 'vars'? 2. where is xxxx defined? your code will work if you define properly, set the value, then reference. — Wayne G. Dunn
– Wayne G. Dunn, Commented Jul 10, 2014 at 17:36
thanks for the comments. @Acantud yes i need to call xxxx each time i need it (i am using excel and xxxx will define the last empty row in a column and i will use this var in many place so i dont wont to declare it each time i'll use). — Khaled_Jamel
– Khaled_Jamel, Commented Jul 11, 2014 at 7:40
@Wayne G.Dunn : subroutine vars is coded in main userform code and xxxx is defined on the same place just inside vars sub and just in the begining of userform code i declared a public xxxx. — Khaled_Jamel
– Khaled_Jamel, Commented Jul 11, 2014 at 7:40

djikay · Accepted Answer · 2014-07-10 17:31:36Z

2

It looks obvious to me, so I may have misunderstood the question.

You're not calling vars() anywhere, so the string xxxx is initialised to its default value (blank). If you change your code to call vars() as follows:

Private Sub CommandButton1_Click()
  ' call vars() to set the string value
  vars

  ' test button
  MsgBox ("pub var is " & xxxx)
End Sub

then it will work as you expect.

In my test, even if I call vars() first on its own and then your button_click as you posted it, the value of xxxx is set correctly. Obviously, this value won't survive closing Excel and restarting it. In that case, you'll need to save its value externally somewhere and reload it when Excel is restarted.

answered Jul 10, 2014 at 17:31

djikay

10.7k9 gold badges44 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Khaled_Jamel Over a year ago

thanks i will use this method, call the sub vars in each subroutine where i need xxxx var.

Collectives™ on Stack Overflow

cannot use public var vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related