Microsoft Visual Basic Help...

3 posts in this topic

Hi all,

I'm trying to update an old document generation tool that someone has built in excel via Visual Basic code. Whilst I have experience in PLC & HMI programming, this is new to me. However, I have managed to crack the passwords with help via excel and to the point where it's not letting me "save as" with a custom message. I've found the message, and believe I've found the code preventing me from saving...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox arrLanguage(30, 2)
End Sub

Can someone please talk me through it (every day is a school day) and then, how can I bypass.

Disclaimer - there is absolutely nothing exciting past this document, so no ethical misconduct haha, it's a isolation placard generator that has masses of admin (which will save me a heap of time) that needs updating.

This thread may grow....

Thanks in advance.

Share this post

Link to post
Share on other sites

Worked it out. Please ignore above. However, the below "unhide all tabs", if anyone could assist me with what this code means by steps:


Private Sub UnHideAllTabs()
    Dim arrSheet
    ReDim arrSheet(Sheets.Count)
    For iIndex = 1 To Sheets.Count
         arrSheet(iIndex) = Sheets(iIndex).Name
    For iIndex = 1 To UBound(arrSheet)
        Worksheets(arrSheet(iIndex)).Visible = True
End Sub

Share this post

Link to post
Share on other sites

What did you find out about your first question? I was able to make that MsgBox call work, but not meaningfully.


As for your current question:

Dim declares a variable. Since it has no data type in the declaration, it's a "variant", which is inefficient but works. It allocates enough memory to hold any data in any format. Since they're using it to store sheet names, I would probably have declared it as a String

ReDim converts the variable arrSheet to an array the same length as the number of sheets in the workbook. In other words, there's an element in the array for each sheet.



Edited to clarify a perhaps extraneous detail:

The ReDim converts the variable to an array with its top index equal to the number of sheets in the workbook. I don't remember (and can't find quickly) how VB handles arrays, whether the lowest index is 0 or 1. If arrays start with 0, then the array will have an unused element in this case since the numbering of the sheets (see below) starts with 1.





Are you familiar with For-Next loops? In a nutshell, the loop will execute once for each value of the index, "iIndex" in your case. The "For" statement sets up the limits of the loop (first and last values, the "Next" statement tells it to go back to the top of the loop after incrementing the index vaviable.

The first For-Next loop steps through each sheet one by one and stores its name in the arrSheet array. Each worksheet in the workbook has an index number, which is the order in which it appears in the tab bar at the bottom.

The second For-Next loop steps through the arrSheet array and marks each sheet as visible.

Edited by Joe E.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now