Home

Run-time error 9 after copying a macro to a new project in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-877563%22%20slang%3D%22en-US%22%3ERun-time%20error%209%20after%20copying%20a%20macro%20to%20a%20new%20project%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877563%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20very%20simple%20macro%20as%20below%20to%20run%20a%20UserForm%20(formHygiene)%20in%20VBA.%20It%20works%20fine%20in%20one%20file.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20451px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133657i052D2AC991B59881%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture1.JPG%22%20title%3D%22Capture1.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20wanted%20to%20use%20it%20for%20other%20Excel%20file%20and%20copied%20the%20macro%20and%20the%20UserForm%20to%20other%20(by%20dragging%20them%20into%20a%20new%20VBA%20Project%20in%20VBA).%20Then%20when%20I%20ran%20the%20macro%20in%20the%20new%20Excel%2C%20I%20am%20getting%20a%20%22Run-time%20error%209%3A%20Subscript%20out%20of%20range%22.%20When%20I%20tried%20to%20debug%2C%20it%20points%20to%20the%20line%20%22formHygiene.Show%22%20in%20the%20macro.%20So%20I%20think%20the%20error%20can%20be%20due%20to%20the%20new%20file%20not%20seeing%20the%20UserForm%20(formHygiene)%20somehow.%20But%20the%20UserForm%20is%20shown%20for%20the%20new%20one%2C%20as%20you%20see%20in%20the%20screenshot%20below.%20Can%20anyone%20tell%20me%20why%20I%20am%20getting%20this%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20393px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133659iF60188FF0588EEDD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture2.JPG%22%20title%3D%22Capture2.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EDavid%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-877563%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878524%22%20slang%3D%22en-US%22%3ERe%3A%20Run-time%20error%209%20after%20copying%20a%20macro%20to%20a%20new%20project%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415908%22%20target%3D%22_blank%22%3E%40seolac%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20to%20reproduce%20that%2C%20but%20I%20didn't%20see%20any%20error!%3C%2FP%3E%3CP%3EIf%20the%20new%20workbook%20has%20a%20different%20structure%2C%20so%20it's%20possible%20that%20the%20code%20in%20the%20UserForm%20refers%20to%20something%20missing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20watch%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DAC3GgPOalLM%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Evideo%3C%2FA%3E%20to%20learn%20more%20about%20the%20Run-time%20error%209%20and%20its%20common%20reasons.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
seolac
Occasional Visitor

I have a very simple macro as below to run a UserForm (formHygiene) in VBA. It works fine in one file.

Capture1.JPG

I wanted to use it for other Excel file and copied the macro and the UserForm to other (by dragging them into a new VBA Project in VBA). Then when I ran the macro in the new Excel, I am getting a "Run-time error 9: Subscript out of range". When I tried to debug, it points to the line "formHygiene.Show" in the macro. So I think the error can be due to the new file not seeing the UserForm (formHygiene) somehow. But the UserForm is shown for the new one, as you see in the screenshot below. Can anyone tell me why I am getting this error?

 

Capture2.JPG

Thanks!

David

1 Reply
Highlighted

@seolac

 

Hi,

 

I've tried to reproduce that, but I didn't see any error!

If the new workbook has a different structure, so it's possible that the code in the UserForm refers to something missing.

 

Please watch this video to learn more about the Run-time error 9 and its common reasons.

 

Hope that helps

 

Related Conversations