If they are combined with other visual fireworks at the grand finale, it adds a bit to building the moment, but even then, I can pass on them.
As a single firework, I hate them. You see it go up and anticipate something beautiful, but then it just startles you with the noise.
As I've gotten older, if you could make a noiseless firework, I'd be all for that and cut down the noise pollution. Fireworks are 3000 years old... you'd think they could make noiseless varieties by now. 😉