Solved: Call to other sub within a sub

[Rejje]

Hi! What does it look like when one calls on another sub?

Example follows:

BUTTON 1: "SAVE FILE"

[vba]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[/vba]

I'd like to see something like this

BUTTON 2: SAVE FILE AND QUIT

[vba]Sub SaveFileAndQuit()

RUN SUB CALLED "SaveFile()"

ThisWorkbook.Close

End Sub
[/vba]

Possible?

[Bob Phillips]

As simple as

[vba]

Sub SaveFileAndQuit()

Call SaveFile

ThisWorkbook.Close

End Sub
[/vba]

[Rejje]

As simple as

[vba]

Sub SaveFileAndQuit()

Call SaveFile

ThisWorkbook.Close

End Sub
[/vba]

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:

[vba]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[/vba]

And then:

[vba]Sub SaveFileAndQuit()
Call SaveFile

If Workbooks.Count = 1 Then
Application.Quit
Else
ThisWorkbook.Close
End If

Set wb = Nothing

End Sub[/vba]

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]

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]

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

[vba]

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
[/vba]

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

[vba]

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
[/vba]

[Rejje]

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:

[vba]

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

[/vba]

[Norie]

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]

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??

[vba]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
[/vba]

NEWLY CREATED ARCIVE FILES WON'T BE CLOSED!

ONLY ARCHIVE FILES JUST HAS BEEN SAVED WILL BE CLOSED!

[Bob Phillips]

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]

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]

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.