Declarar contantes para proyectos en vba

Una constante, como su propio nombre indica, no puede depender de un valor variable. Lo que necesitas es declarar una variable pública y asignarle el valor de esa celda:

En el encabezado del módulo:

Public fecha1 As Integer

Y en el evento que consideres más adecuado, le das el valor, que se mantendrá constante mientras no le vuelvas a asignar uno a la variable:

fecha1= Application.Weekday(Range("C3"), 2)

Hice los siguientes procedimientos de prueba:

Public fecha1 As Integer

Sub constantes()
fecha1 = Application.Weekday(Range("C3"), 2)

If fecha1 < 5 Then
Range("E3") = 8
Else
Range("E3") = 0
End If
End Sub

Sub constante2()
If fecha1 > 5 Then
Range("E4") = 9
Else
Range("E4") = 0
End If
End Sub

Sub prueba()
Call constantes
Call constante2
End Sub

Pero ponga la fecha que ponga en C3 siempre me queda E3= 0 y E4=9

Acabo de probar exactamente ese código, y funciona correctamente.

Haz la prueba: pon una fecha cualquiera y ejecuta la macro. Pon el día siguiente a esa fecha y ejecuta. Así 7 veces. Ya verás como los valores cambian según lo que has programado.

A mi no me funciona, puse 7 días como sugeriste y no cambia. Siempre aparecen los mismos valores.

Decime con que fecha probaste para que te den valores diferentes.