This is a fun question, and as an occasional user of MODIS products, I've asked it (to myself) also. Although I've never asked anyone on the MODIS team, who I see from time to time.
I have always supposed that they wanted to leave the door open for actual high (spatial) resolution observations in the future. The driving design requirement was for global coverage every two days, so this meant a wide field of view of the downward-looking optics (2200 km wide FOV) which in turn meant "moderate" spatial resolution.
It was designed in the early 1990s. If you look at the original MODIS design papers (e.g., The Moderate Resolution Imaging Spectrometer (MODIS) Science and Data System Requirements, IEEE TGARS, Jan. 1991), you read the design was inspired by instruments like AVHRR and HIRS. The "VHR" in AVHRR stands for "Very High Resolution" and it was 1km! And the H in HIRS stands for High-resolution and it was > 1km, as much as 10km (!!). So calling it "Moderate" seems like a corrective message to the community.
Why would you need more? Many of the MODIS products are 1000m X 1000m, which is too coarse for many "field-scale" applications, where you want to get down to the health of individual fields of crops, or mixed suburban/rural areas, or biomes where vegetation types are mixed at local scales.
For instance, transpiration from broad-leafed trees is very different from grasses, and "mixed pixels" of these two types can confuse models. Or scattered parking lots at the fringe of suburban areas impinging on vegetation.