We're going to say something that might sting a little: if you're writing a VBA macro so someone else can click a button to pull tide data, you've already lost. Not because macros are bad, they're not, but because you're spending your time building a workaround for a tool that was never designed to do this job in the first place. The person who will use this spreadsheet doesn't need a button. They need a system that understands what they're asking for without requiring you to pre-wire every possible future date into a string of code.
Here's the practical reality of what you're describing. You've already done the hard part: you figured out the NOAA API query, you understand the parameters, and you know exactly what data you need. The problem isn't the data, it's the container. Excel treats a URL as text, and text doesn't auto-update when tomorrow becomes today. So you're left writing a macro that concatenates a cell value into a query string, then firing it with a button. It works, but it's brittle. Change the station ID? Rewrite the macro. Add a second date range? More code. The person on the other end of that button isn't empowered, they're locked into whatever path you built for them.
What you really want is a spreadsheet that treats the API call as a first-class operation, not a text string you have to assemble by hand. A modern, AI-native spreadsheet can take that same NOAA endpoint, interpret the date parameter from a cell, and refresh the data on schedule, no macro, no button, no fragile concatenation. The person using it just changes a date and the data follows. That's the difference between automating a manual process and building a tool that actually adapts.
Our opinion is straightforward: stop building around Excel's limitations. You've already proven you can solve the data problem. Now give yourself, and the person clicking that button, a solution that doesn't require a custom script every time the requirements shift. The tide data will keep coming. Your approach to managing it shouldn't be stuck in the last century.