I don’t use VBA much now but a quick Google seems to show that this component is following redirects.
Quoting the top answer (warning - this is from a long time ago e.g. 2010):
The answer is, No - MSXML2.XMLHTTP automatically follows redirects.
If you need to track and optionally not follow redirects, then you can use the WinHttp.WinHttpRequest object, which, like MSXML2.XMLHTTP is accessible to script.
There look to be other people querying this as you would expect. Google is your friend.
Although this isn’t a Companies House query as this is to do with VBA URL handling, for reference of others searching in the future it would help if you post your findings back here!
Good luck.