View Full Version : Solved: Call to other sub within a sub
Rejje
11-26-2010, 04:32 PM
Hi! What does it look like when one calls on another sub?
Example follows:
BUTTON 1: "SAVE FILE"
Sub SaveFile()
Dim wb As Workbook
Set wb = ActiveWorkbook
If Range("V_10300") = True Then
If UCase(wb.FullName) = UCase(Range("V_50100").Value) Then
ActiveWorkbook.SaveAs Range("V_50200").Value
Workbooks.Open wb.FullName
wb.Save
Else
wb.Save
End If
Else
MsgBox ("Can't save - valid name isn't found!")
Range("EXP_1010").Select
End If
Set wb = Nothing
End Sub
I'd like to see something like this
BUTTON 2: SAVE FILE AND QUIT
Sub SaveFileAndQuit()
RUN SUB CALLED "SaveFile()"
ThisWorkbook.Close
End Sub
Possible?
Bob Phillips
11-26-2010, 04:38 PM
As simple as
Sub SaveFileAndQuit()
Call SaveFile
ThisWorkbook.Close
End Sub
Rejje
11-26-2010, 05:53 PM
As simple as
Sub SaveFileAndQuit()
Call SaveFile
ThisWorkbook.Close
End Sub
I see - very simple! Vba really is easier than Chinese!
However I have found that in this particular example "Call SaveFile" poses a problem.
See again:
Sub SaveFile()
Dim wb As Workbook
Set wb = ActiveWorkbook
If Range("V_10300") = True Then
If UCase(wb.FullName) = UCase(Range("V_50100").Value) Then
MsgBox (UCase(Range("KUNDPOST_NAMN").Value) & " will be created!")
ActiveWorkbook.SaveAs Range("V_50200").Value
Workbooks.Open wb.FullName
wb.Save
Else
wb.Save
End If
Else
MsgBox (UCase("Not a valid name!"))
Range("EXP_1010").Select
End If
Set wb = Nothing
End Sub
And then:
Sub SaveFileAndQuit()
Call SaveFile
If Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close
End If
Set wb = Nothing
End Sub
Thing is that in case SaveFile() renames ActiveWorkbook then SaveFileAndQuit() won't recognize ThisWorkbook.Close as the workbook to close it seems. Am I right? Well, it's a problem wheter I'm right or not anyway...
Norie
11-26-2010, 08:18 PM
What are you actually trying to do?
The problem might be with the structure/logic/flow of your code.
I think you might want to consider the difference between ThisWorkbook and ActiveWorkbook.
ThisWorkbook usually refers to the workbook the code is in, whereas ActiveWorkbook is the workbook that VBA/Excel considers to be active.
It's really not clear what your code is doing.
What workbook(s) do you want to save as/open etc?
If you are getting unexpected results, then it could be because...
Actually I don't know what the cause might be exactly the code's too confusing to try and decipher this late, for me anyway.:)
Bob Phillips
11-27-2010, 03:22 AM
I mentioned to you earlier that you cannot rely on a workbook reference when you save that workbook as another name and try to reopen the original.
So this code
Sub SaveFile()
Dim wb As Workbook
Set wb = ActiveWorkbook
If Range("V_10300") = True Then
If UCase(wb.FullName) = UCase(Range("V_50100").Value) Then
MsgBox (UCase(Range("KUNDPOST_NAMN").Value) & " will be created!")
ActiveWorkbook.SaveAs Range("V_50200").Value
Workbooks.Open wb.FullName
wb.Save
Else
wb.Save
End If
Else
MsgBox (UCase("Not a valid name!"))
Range("EXP_1010").Select
End If
Set wb = Nothing
End Sub
looks highly suspect to me.
As it happens, you don't have to save it under another name, you can save a copy, then the original is still open
Sub SaveFile()
Dim wb As Workbook
Set wb = ActiveWorkbook
If Range("V_10300") = True Then
If UCase(wb.FullName) = UCase(Range("V_50100").Value) Then
MsgBox (UCase(Range("KUNDPOST_NAMN").Value) & " will be created!")
ActiveWorkbook.SaveCopyAs Range("V_50200").Value
End If
wb.Save
Else
MsgBox (UCase("Not a valid name!"))
Range("EXP_1010").Select
End If
Set wb = Nothing
End Sub
Rejje
11-27-2010, 08:51 AM
No - that is not want I want to happen. The code's first checking if ActiveWorkbook = Range("V_50100").Value which is a write-protected template.
IS SO: Save as Range("V_50200").Value then close the write-protected template and open file Range("V_50200").Value so that one can continue working with file Range("V_50200").Value instead of template.
IF NOT: Save as usual.
IT IS VERY IMPORTANT THAT THIS WILL ALWAYS HAPPEN WHEN SAVING!
But this poses a problem with above descibed "Call SaveFile". Maybe there's a way to reset Active workbook something like:
Sub SaveFileAndQuit()
Call SaveFile
"RESET ActiveWorkbook"
Dim wb As Workbook
Set wb = ActiveWorkbook
If Workbooks.Count = 1 Then
Application.Quit
Else
wb.Close
End If
Set wb = Nothing
End Sub
Norie
11-28-2010, 11:17 AM
Why don't you actually use a template?
If you used a template you could automatically create a new workbook based on it without actually changing the original template.
Then you could do whatever you need with that new workbook.
Opening/saving/closing workbooks like you seem to be trying to do in the 1st code is just going to cause confusion.:)
Rejje
11-28-2010, 12:17 PM
But that's exactly what I'm doing! See above SaveFile.
The problem is that If I Call SaveFile from within SaveFileAndQuit the latter won't know if ActiveWorkbook has made the transaction from TEMPLATE TO NEWLY CREATED ARCHIVE FILE or ARCHIVE FILE JUST HAS BEEN SAVED. Then below won't happen correctly when TEMPLATE TO NEWLY CREATED ARCHIVE FILE has happened in SaveFile but will happen correctly if vice versa.
This does pose a problem - I've tried it! And, the thing is that in case one just replaces Call SaveFile with the code from Sub SaveFile() it works perfectly. Or am I just crazy??
Sub SaveFileAndQuit()
Dim wb As Workbook
Set wb = ActiveWorkbook
Call SaveFile
' ActiveWorkbook setting can be either Template or Archive file when inhereted from SaveFile it seems
If Workbooks.Count = 1 Then
Application.Quit
Else
wb.Close
End If
Set wb = Nothing
End Sub
NEWLY CREATED ARCIVE FILES WON'T BE CLOSED!
ONLY ARCHIVE FILES JUST HAS BEEN SAVED WILL BE CLOSED!
Bob Phillips
11-28-2010, 01:10 PM
As I have said, I am sure that your problem is in the variable wb, when you save the Activeworkbook as another name you cannot rely on what the value of that varibale now is so you should reinitilaise it. I am sur I am being slow, but I am still not clear enough on what you are to suggest the how.
Rejje
11-28-2010, 01:17 PM
Lol yes! And if you say so I will just drop it and paste the SaveFile code into SaveFileAndQuit. I feel this will save us all a lot of time as well...
Thanks!
Bob Phillips
11-28-2010, 01:35 PM
It is not using a variable, that is good practice, but rather relying on that variable to remain pristine after you have abused (:)) it. You have to be aware that object variabes can change due to system interventions.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.