Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question on variables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question on variables
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("Someshee tnamehere") or set wscert = activesheet or ... msnyc07 wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question on variables
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. -- HTH, Barb Reinhardt "msnyc07" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question on variables
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! "Dave Peterson" wrote: 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("Someshee tnamehere") or set wscert = activesheet or ... msnyc07 wrote: 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 -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question on variables
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? "Dave Peterson" wrote: 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("Someshee tnamehere") or set wscert = activesheet or ... msnyc07 wrote: 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 -- Dave Peterson . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question on variables
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... "msnyc07" wrote: 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? "Dave Peterson" wrote: 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("Someshee tnamehere") or set wscert = activesheet or ... msnyc07 wrote: 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 -- Dave Peterson . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie question on variables
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. msnyc07 wrote: 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 CASE 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? "Dave Peterson" wrote: 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("Someshee tnamehere") or set wscert = activesheet or ... msnyc07 wrote: 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 -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real Newbie newbie question | New Users to Excel | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Newbie, Variables not retained in sub? | Excel Programming | |||
newbie ?: set variables, different shts, equal to each other | Excel Programming | |||
Newbie Macro Query - Clearing Variables and Assigning a Variable | Excel Programming |