The following codes did not work for me, today. I think somebody is tampering with my computer. I wonder, how this could be duplicated, or is there an error with my code:
'number of rows for a certain column:
dim c as integer
c = WorksheetFunction.counta(columns("A:A"))
'get a value
c = range("A1")
Additional:
My code is a homework assignment, "Monte Carlo Simulation". It basically changes the costs of a list of items used to bake cookies and is using different types of distributions (uniform, normal, discrete) to do so. Excel exhausted its resources, because of an ill-formed loop with circular referencing - so the error message). I restarted Excel repeatedly. Eventually, I used original codes, which were provided by the Professor, and step by step re-inserted functions and procedures I already had coded, in order to find out how I had caused the Excel error message with my coding efforts. I ended up with 1000 of the "Monte-Carlo-Simulations" in 1 minute, rather than 30 minutes, observed before. The codes I provided I consider basic, but I am not able to resolve the issue, even if I copy old codes into my VBA modules. How could I avoid that an i.e. third party can tamper with my computer?
Added on July 20, 2021
This code uses the controls collection in order to obtain values of data on a form. You need form with two text fields which should have i.e. have double value content. So the controls collection is not working, since today.
i.e. txtPerc1 and txt1
and then the function will return a value along with the discrete distribution.
The controls lines are commented out because I receive the error "sub or function not defined. I checked the name of the field and it was okay.
'sample call:
'discreteDistribtion("Hallo", 3)
' So I have six fields named:
' halloPerc1, halloPerc2, halloPerc3 and hallo1, hallo2, and hallo3
' the function takes here "hallo" and generates the names Hallo1, Hallo2, Hallo3 and halloPerc1, halloPerc2, and halloPerc3
' the function takes the value of hallo1, hallo2, or hallo3, each of these fields having doubles as content.
' According to the logic, hallo1, hallo2 or hallo3 values are returned!
' The code does not work on my computer, when I uncomment the controls code, it worked the weeks before.
Function discreteDistribution(strCtrl As String, iEnd As Integer, Optional iStart As Integer = 1)
Dim i As Integer, sum As Double, r As Double, strTemp As String
sum = 0
r = Rnd
discreteDistribution = 0
For i = iStart To iEnd
strTemp = strCtrl & "Perc" & i
''''''sum = sum + Controls(strTemp).Value
If r < sum Then
strTemp = strCtrl & i
'''''' discreteDistribution = Controls(strTemp)
Exit For
End If
Next i
End Function
I added a second userform to my work book on a separate sheet and the following code is working:
Option Explicit
Dim i As Integer
Private Sub CommandButton1_Click()
For i = 1 To 10
Controls("TextBox" & i).Value = Cells(i + 1, 1).Value
Next i
End Sub
Private Sub UserForm_Click()
End Sub
This code fills a bunch of textboxes with telephone numbers collected on a spread sheet. Still, I was not able to get my original code to work.
The code, recognized as faulty by Excel, highlighting the location, for the error shown in the previous image:

Though not the entire question is answered here, my today's problem is resolved. The controls collection is a property of the user form. So in order to call this collection from within a module, it needs to know the form it is related to. So rather than calling controls("Name of Control") = ... the call must be: form.controls("Name of Control") and then the code works.
Thank you to everybody who replied to my request for encouragement.