Cornelius’ answer is partially correct. His code gets the current instance and then makes a new instance. GetObject only ever gets the first instance, no matter how many instances are available. The question I believe is how can you get a specific instance from among many instances. For a VBA project, make two modules, one … Read more
First the good news. This code does what you want (please note the “line numbers”) Sub a() 10: On Error GoTo ErrorHandler 20: DivisionByZero = 1 / 0 30: Exit Sub ErrorHandler: 41: If Err.Number <> 0 Then 42: Msg = “Error # ” & Str(Err.Number) & ” was generated by ” _ & Err.Source … Read more
Write is a special statement designed to generate machine-readable files that are later consumed with Input. Use Print to avoid any fiddling with data. Print #2, “Start postion = “; StarRange Print #2, “End position = “; EndRange
I had forgotten to add a reference to “Microsoft ActiveX Data Objects 2.5 Library”: This reference is required for early binding. How to get to that reference: Tools > References > Check the checkbox in front of “Microsoft ActiveX Data Objects 2.5 Library” Other libraries that work include: Microsoft ActiveX Data Objects 2.6 Library Microsoft … Read more
I can give you the complete code in one go but that wouldn’t help you learn from it 😉 So let’s Break up your requests and then we will tackle them 1 by 1. This is gonna be a very long post so be patient 🙂 There are total 5 parts which will cover all … Read more
The VBA line-continuation character is an underscore _ if ( _ (something) _ or (somethingelse) _ )
Please see edits below! For Each edits also added below under Edit2 More edits about ForEach and Collections at Edit3 One last edit about ForEach and Collections at Edit4 A final note about iteration behavior at Edit5 Part of the subtlety of this odd behavior in the semantics of variant evaluation when used as a … Read more
http://www.mrexcel.com/td0097.html Dim WS as Worksheet Set WS = Sheets.Add You don’t have to know where it’s located, or what it’s name is, you just refer to it as WS. If you still want to do this the “old fashioned” way, try this: Sheets.Add.Name = “Test”
From the MSDN: You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist. If you use either Call … Read more
Try this Sub Sample() Dim test As Worksheet Sheets(1).Copy After:=Sheets(Sheets.Count) Set test = ActiveSheet test.Name = “copied sheet!” End Sub Looking back at this, a better approach would be Set test = Sheets(Sheets.Count) As correctly mentioned in the comments below, there are lot of things that needs to be considered when copying and renaming a … Read more