Newbie question on variables

  • Thread starter Thread starter msnyc07
  • Start date Start date
M

msnyc07

I had a coder write me a VBA which was rife with errors and he disappeared on
me so I've spent the last few days somehow managed to slog through and make
fixes (don't know VBA or coding)

However I am stuck on my last fix.

Essentially there are a # of functions within a larger one

I need to set a variable inside an if/then statement and store it in a cell
on the WS to access in a later function.

Based on how he wrote to cells I did this

wsCert.Cells(targetCertRowNumber, 300).Value = "Name"

which worked inside other functions

However I get this error

424

Object Required

Is there something I need to do inside that Function to allow me to use the
variable 'targetCertRowNumber'?

As I said I was able to write to cells like that in other functions.

Thanks in advance, hopefully I provided enough basic information
 
I don't think it's the targetcerrownumber variable.

I think it's the wsCert variable. I don't think it has been set to a worksheet
(yet).

You could verify this by adding this before the offending line of code:

msgbox targetcertrownumber 'just to check to see what it is!
if wscert is nothing then
msgbox "wscert hasn't been set"
else
msgbox "wscert is ok and points to: " & wscert.name
end if

=======
Someplace in your code, you'll need a line like:

set wscert = workbooks("someworkbook.xls").worksheets("Somesheetnamehere")
or
set wscert = activesheet
or ...
 
When I see object required, it makes me think that wsCert is not defined.
Try this

if wscert is nothing then
debug.print "wsCert not defined"
end if

debug.print targetCertRowNumber

You will also get an error if TargetCertRowNumber is zero but I'm not sure
which one it might be.
 
Not in front of my computer but that sounds right somehow. There are two
worksheets being worked on and I know the name is called here and there. I'll
see how he did it on the other functions, I'll report back, thanks!
 
Hmm of wsCert is not defined

HOWEVER when the code jumps to the next function it is because that is where
the other Cells are written

How can I pass a variable from the function where it is not defined to the
one it is?

In the one it is not I need to add a variable inside

If CASE1
CurrentVariable=X
Else
CurrentVariable=Y

so I want do do a

If CASE1
CurrentVariable=X
New Variable = A
Else
New Variable = B
CurrentVariable=Y

I tried doing just that but it didn't work so I am assuming i need to
declare that somewhere too?
 
Right ok I tested that by adding a msgbox in the function where I named the
new variable i.e.

NewVariable = "U"
MsgBox New Variable

and then the same message box in the function that is called next

So I get
"U"
""

meaning that variable is not stored anywhere

Can I make it? Obviously the one that is otherwise created is carried over
since that is it's purpose anyway...
 
One way is to pass it is like this:

Option Explicit
Sub testme01()
dim mySheet as worksheet
dim res as boolean
set mysheet = thisworkbook.worksheets("sheet1")

res = myfunc(wks:=mysheet)
if res = false then
msgbox "Failed"
else
msgbox "worked"
end if
End sub

Function myFunc(wks as worksheet) as boolean
if wks is nothing theng
msgbox "it's nothing"
myfunc = false
else
msgbox wks.name
wks.range("A1").value = "hi there"
myfunc = true
end if
end function
==========
Another way to make the variable visible to any other procedure in the
workbook's project:

In a General module:
Public wks as worksheet

And it you set it in one procedure (and never destroy it elsewhere), then it'll
be visible in all other procedures.
 
Back
Top