For a file you are authorized to work on with a lost password, the VBA project protection is fortunately weak, so opening it is practical, with with the routes matching the file format and the honest framing that this works because the protection was never strong:

Understand the protection scope: the VBA project password guards viewing and editing the macro code, not running the macros or the file's data, and it is a lightweight non cryptographic protection, historically trivial to reset, which is exactly why an authorized user regaining access to a lost password file succeeds where the same ease would be concerning for real security.

The modern format route, editing the project protection state: for xlsm, docm and similar modern Office files, which are zip archives, the VBA protection lives in the vbaProject.bin inside, and resetting the protection keys, the CMG, DPB and GC values in the project stream, opens the project without the password. Documented procedures walk through opening the archive, editing those keys in a hex editor and repacking, which resets rather than reveals the password, giving you the edit access that was the goal.

The tooling route for those preferring not to hex edit: VBA password removal tools automate the same reset, taking the file and returning the VBA project accessible, appropriate for an authorized user who wants the result without manual editing and the older xls binary format has its own well established reset approaches these tools handle.

The framing to carry forward: this succeeds because VBA project protection is genuinely weak, which is fine for recovering your own authorized file and is precisely why VBA passwords should never protect anything sensitive, since anyone with the file can do what you just did. If the macros contain something that truly needs protecting, that protection belongs elsewhere, the code signed or the sensitive logic moved server side, rather than relying on a VBA password that recovers this easily. For the common case of a lost password on a file you own, the reset gets you back in.