Visual Basic/Excel VBA

Advertisement


Question
Hi Mr.Ravindra. I'm a beginner in VBA. The excel macro below is to calculate the date format (eg. 12/3/2005)as the input, in this case the result will be 4 (ie. 1+2+3+2+0+0+5 must equal not more than 9. The function below fail to read the numbers coz it come together with slashes. So how to make the function reads only the numbers of the date format. If I change IsNumeric to IsDate, the function will read the numbers reside behind the date not the displayed numbers.
Function Numerology(Number As Variant)
'Written by Barrie Davidson
Dim Length As Double
Dim Holder As Double
Application.Volatile
On Error Resume Next
If IsNumeric(Number) = False Then
  Numerology = "N/A"
  Exit Function
ElseIf Number = "" Then
  Numerology = "N/A"
  Exit Function
Else
Evaluation:
  Length = Len(CStr(Number))
  Holder = 0
  For i = 1 To Length
      Holder = Holder + CDbl(Mid(CStr(Number), i, 1))
  Next i
  If Len(CStr(Holder)) > 1 Then
      Number = Holder
      GoTo Evaluation
  End If
End If
Numerology = Holder
End Function

Answer
sample code

Sub DateStorer()
Dim adu As String, arra(1 To 10) As String, i As Integer
adu = Cells(1, 1)  ' this can be input box etc
For i = 1 To Len(adu)

If arra(i) = "/" Then GoTo 1
arra(i) = Mid(adu, i, 1)
1 Next i
For i = 1 To 10

If arra(i) <> "/" Then Debug.Print arra(i)
Next
End Sub

make changes as needed

ravindra mg

Visual Basic

All Answers


Answers by Expert:


Ask Experts

Volunteer


Ravindra

Expertise

visual basic application programming from design to access information, sql, engineering and commercial applications. access databases, excel.(VB6) basic questions in vb 2008. Optional: 1)a large number of people want me to do work which takes some time and effort. can do projects in vb 2008. Pl.note that i would like to be paid for such work. 2) if you want me to spend quality time and do special work, i expect to be paid a reasonable price for my time. 3) if you are pleased with my reply you could consider a good amount. 4) you can visit my website http://ravindra.coolpage.biz

Experience

programming since the past 15 years. have completed a large number of projects in Industrial applications, finance,accounts, correspondence. excel macros etc

Education/Credentials
engineering, management degrees.

Past/Present Clients
project work for a Norway company,USA company and a Canadian company completed. Freelance Project work and Teaching. helping programmers with their work
teaching vb

©2016 About.com. All rights reserved.