0

I want to assign certain time weightage values to the variables of a data set. So I am trying to use Enumerations/Constants so to improve my code quality and make it easy to maintain. But on compiling my project, it throws in a Circular Dependencies Between Modules error.

What I have understood is, one is allowed to use only constants [Eg: 2,3.14,56....], in the truest sense of the word. Bottom line, if I can't use Either Enumerations or Constants. Then how can I achieve my objective of keeping my weightages code-block, in a way that any changes in them is reflected everywhere in my project than me having to do a find and update manually every instance.

What I am getting at, is to have a global variable that can be accessed throughout the project and is dynamic.

Private Const Wtage As Double = ConvTohr(34) 'Error happens here

Enum Weightage
   Var1_Weightage = ConvTohr(3)  'Error happens here
   Var2_Weightage = ConvTohr(11)
   Var3_Weightage = ConvTohr(2)
   var4_Weightage = ConvTohr(9)
   var5_Weightage = ConvTohr(0)
End Enum

Private Function ConvTohr(val As Integer) As Double
    If val = 0 Then
       ConvTohr = 0
       Exit Function
    End If
    ConvTohr = Round((val / 60), 2)
End Function

Error Snap

Improve this question
7
  • Const values in VBA must be literals or an expression involving only other named Consts. You cannot assign a function result to a Const field in VBA. Commented Feb 1, 2022 at 8:57
  • Also, your ConvTohr function returns Double but your Wtage is Integer - you need to fix that first. Commented Feb 1, 2022 at 9:18
  • Also, you should read this: learn.microsoft.com/en-us/office/vba/language/reference/… - and note the restrictions on the expression part of the syntax. Commented Feb 1, 2022 at 9:19
  • Also, VBA's Enum type can only use Integer or Long values for its members, you cannot use Double values for Enum members (as your ConvTohr function returns Double). Commented Feb 1, 2022 at 9:22
  • 1
    Use a singleton Class Module with Get-properties to expose those values (VBA doesn't support true singletons, but it can be worked-around: stackoverflow.com/a/4338092/159145 ). Commented Feb 1, 2022 at 11:22

1 Answer 1

Reset to default
1
  • The error message is incorrect: your code does not have any circular references.
    • This is more of a bug in the VBA interpreter: your code is still incorrect and invalid, but VBA is showing the wrong error message.
    • Given that VBA remains effectively frozen-in-time since 2002 (excepting 64-bit support in 2007), so don't expect any fixes, let alone any enhancements, ever (Though MS Office's COM automation tooling is slowly shifting to JavaScript (maybe Python? please?).
  • The actual problem with your code is threefold:
    • You cannot use a Function to initialize a Const value in VBA.
    • You cannot use a Function to define values for a VBA Enum either.
    • You cannot have Enum members typed as Double: VBA only supports Integer and Long values for Enum members.
      • Curiously, VBA does allow Const values to be typed as Double - but most other languages don't because Double is an IEEE-754 floating point type that does not have a machine-portable representation, but as VBA is only for Win32 on x86/x64 I guess that means Microsoft made it work given the very narrow gamut of hardware that VBA programs will run on.

Anyway, if you want "named values" typed as Double that you can use anywhere, then try this:

  1. Create a new Module (not a Class Module).

  2. Rename the Module from Module1 to Weightage.

  3. Put this code in Weightage:

    Private Function ConvTohr(val As Integer) As Double
    ConvTohr = Round((val / 60), 2)
End Function

Public Property Get Var1_Weightage() As Double
    Var1_Weightage = ConvTohr(3)
End Property
Public Property Get Var2_Weightage() As Double
    Var2_Weightage = ConvTohr(11)
End Property
Public Property Get Var3_Weightage() As Double
    Var3_Weightage = ConvTohr(2)
End Property
Public Property Get Var4_Weightage() As Double
    Var4_Weightage = ConvTohr(9)
End Property
Public Property Get Var5_Weightage() As Double
    Var5_Weightage = ConvTohr(0)
End Property

  4. Screenshot proof:

    (See output in the Immediate pane):

    enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Neato. Thanks much for a solution and the accompanying notes... :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.