I need help with this program written in Visual Basic for Excel 2007.

M

musicalhacksaw

Dear Programmers,
I wanted to run a program that is on page 49 of 'Excel VBA Programming for the absolute beginner'

Though I am sure I have copied the text of it, it doesn't run, and takes exception to the first line, also subsequent lines of code appear in red, I think this is the 'knock on' effect of something amiss in the earlier part of the program, but I can't see where.

Thank you for reading.

Yours
Simon

Option Explicit
Private Sub cmdCalculate_Click()
Dim username As String
Dim yrPassed As Single, moPassed As Single, dayPassed As Single
Dim hrPassed As Single, minPassed As Single, secPassed As Single
Dim userBday As Date, curDate As Date
Dim bDate As String, bMonth As String
Dim bDay As Integer, bYear As Integer
Const SECSPERMIN = 60, MINSPERHOUR = 60
Const HOURSPERDAY = 24, DAYSPERYEAR = 365.25
Const PHYSICAL = 23, EMOTIONAL = 28, INTELLECTUAL = 33
Const PI = 3.14159265358979
'---------------------------
'Get the user's name and birth date.
'---------------------------
username = LCase(InputBox("What is your name?", "Name"))
userBday = DateValue(InputBox("When is your birthday? (month/day/year)", _"Birth Date"))
'--------------------------------
'Calculate length of life in different units.
'-------------------------------
curDate = Now 'Gets current time and date.
secPassed = DateDiff("s", userBday, curDate)
minPassed = secPassed / SECSPERMIN
hrPassed = minPassed / MINSPERHOUR
dayPassed = hrPassed / HOURSPERDAY
yrPassed = dayPassed / DAYSPERYEAR
moPassed = yrPassed * 12
'--------------------------------
'Get users birthday in proper format.
'----------------------------------
bDate = Format(userBday, "dddd")
bMonth = Format(userBday, "mmmm")
bDay = Day(userBday)
bYear = Year(userBday)
'---------------------------------
'Format user's name.
'---------------------------------
username = StrConv(username, vbProperCase)
'-----------------------------------
'Enter time values into appropriate cells in worksheet.
'-----------------------------------------
Range("G28").Value = Trim(Left(username, InStr(1, username, " ")))
Range("H28").Value = Trim(Right(userName, _ Len(userName) - Len(Range("G28").Value)))
Range("G29").Value = bMonth & " " & Str(bDay)
Range("H29").Value = bYear & "(" & bDate & ")"
Range("G30").Value = yrPassed
Range("G31").Value = moPassed
Range("G32").Value = dayPassed
Range("G33").Value = hrPassed
Range("G34").Value = minPassed
Range("G35").Value = secPassed
'------------------------------------
'Formula for day of cycle.
'-----------------------------------
Range("A38").Value = (Range("G32").Value / PHYSICAL - _
Int(Range("G32").Value / PHYSICAL)) * PHYSICAL

Range("A39").Value = (Range("G32").Value/EMOTIONAL-_
Int(Range("G32").Value/EMOTIONAL))*EMOTIONAL

Range("A40").Value = (Range("G32").Value/INTELLECTUAL -_
Int(Range("G32").Value/INTELLECTUAL))* INTELLECTUAL
'------------------------------------------------------
'Formula for magnitude of biorhythm.
'------------------------------------------------------
Range("B38").Value = Sin((Range("G32").Value/PHYSICAL-_
Int(Range("G32").Value/PHYSICAL))*_
PHYSICAL*2 * PI/PHYSICAL)
Range("C39").Value = Sin((Range("G32").Value/EMOTIONAL-_
Int(Range("G32").Value/EMOTIONAL))*_
EMOTIONAL *2 *PI/EMOTIONAL)
Range("D40").Value = Sin((Range("G32").Value/INTELLECTUAL -_
Int(Range("G32").Value/INTELLECTUAL))*_
INTELLECTUAL *2*PI/INTELLECTUAL)
End Sub
 
C

Claus Busch

Hi Simon,

Am Tue, 18 Nov 2014 07:04:25 -0800 (PST) schrieb
(e-mail address removed):
Though I am sure I have copied the text of it, it doesn't run, and takes exception to the first line, also subsequent lines of code appear in red, I think this is the 'knock on' effect of something amiss in the earlier part of the program, but I can't see where.

in front of the underline (for linefeed) has to be a space.
Try:

Private Sub cmdCalculate_Click()
Dim username As String
Dim yrPassed As Single, moPassed As Single, dayPassed As Single
Dim hrPassed As Single, minPassed As Single, secPassed As Single
Dim userBday As Date, curDate As Date
Dim bDate As String, bMonth As String
Dim bDay As Integer, bYear As Integer
Const SECSPERMIN = 60, MINSPERHOUR = 60
Const HOURSPERDAY = 24, DAYSPERYEAR = 365.25
Const PHYSICAL = 23, EMOTIONAL = 28, INTELLECTUAL = 33
Const PI = 3.14159265358979
'---------------------------
'Get the user's name and birth date.
'---------------------------
username = LCase(InputBox("What is your name?", "Name"))
userBday = DateValue(InputBox("When is your birthday?" & _
"(month/day/year)", "Birth Date"))
'--------------------------------
'Calculate length of life in different units.
'-------------------------------
curDate = Now 'Gets current time and date.
secPassed = DateDiff("s", userBday, curDate)
minPassed = secPassed / SECSPERMIN
hrPassed = minPassed / MINSPERHOUR
dayPassed = hrPassed / HOURSPERDAY
yrPassed = dayPassed / DAYSPERYEAR
moPassed = yrPassed * 12
'--------------------------------
'Get users birthday in proper format.
'----------------------------------
bDate = Format(userBday, "dddd")
bMonth = Format(userBday, "mmmm")
bDay = Day(userBday)
bYear = Year(userBday)
'---------------------------------
'Format user's name.
'---------------------------------
username = StrConv(username, vbProperCase)
'-----------------------------------
'Enter time values into appropriate cells in worksheet.
'-----------------------------------------
Range("G28").Value = Trim(Left(username, InStr(1, username, " ")))
Range("H28").Value = Trim(Right(username, _
Len(username) - Len(Range("G28").Value)))
Range("G29").Value = bMonth & " " & Str(bDay)
Range("H29").Value = bYear & "(" & bDate & ")"
Range("G30").Value = yrPassed
Range("G31").Value = moPassed
Range("G32").Value = dayPassed
Range("G33").Value = hrPassed
Range("G34").Value = minPassed
Range("G35").Value = secPassed
'------------------------------------
'Formula for day of cycle.
'-----------------------------------
Range("A38").Value = (Range("G32").Value / PHYSICAL - _
Int(Range("G32").Value / PHYSICAL)) * PHYSICAL

Range("A39").Value = (Range("G32").Value / EMOTIONAL - _
Int(Range("G32").Value / EMOTIONAL)) * EMOTIONAL

Range("A40").Value = (Range("G32").Value / INTELLECTUAL - _
Int(Range("G32").Value / INTELLECTUAL)) * INTELLECTUAL
'------------------------------------------------------
'Formula for magnitude of biorhythm.
'------------------------------------------------------
Range("B38").Value = Sin((Range("G32").Value / PHYSICAL - _
Int(Range("G32").Value / PHYSICAL)) * _
PHYSICAL * 2 * PI / PHYSICAL)
Range("C39").Value = Sin((Range("G32").Value / EMOTIONAL - _
Int(Range("G32").Value / EMOTIONAL)) * _
EMOTIONAL * 2 * PI / EMOTIONAL)
Range("D40").Value = Sin((Range("G32").Value / INTELLECTUAL - _
Int(Range("G32").Value / INTELLECTUAL)) * _
INTELLECTUAL * 2 * PI / INTELLECTUAL)
End Sub


Regards
Claus B.
 
M

musicalhacksaw

Dear Claus Busch,
Thank you, yes that must have been what was wrong with the program, as it runs now, although it doesn't do much really - just outputs the inputter's name and date of birth and a few zeroes to the worksheet, which is nothing like as elaborate as the output he suggests would be generated by the program on page 47.
Leaving 'VBA for absolute beginners' to one side, for the moment, I wanted to ask if you know anything much about using Visual Basic for Webscraping ?I wanted to access the internet to download specific information, not justthe xml of a homepage but items that will fall within specific tags, but which will vary on a day to day basis, originally I was going to try this inPython, but the actual software of Excel/ Visual Basic seems more suited to accessing and scraping the internet, if you know what you are doing. I have looked at quite a few 'You Tube' s on the subject, but so far I can't find one that uses code that will search for and download specific information.I don't know if you can help, but thank you for reading anyhow.
Yours
Simon Evans.
 
C

Claus Busch

Hi Simon,

Am Tue, 18 Nov 2014 10:32:18 -0800 (PST) schrieb
(e-mail address removed):
Thank you, yes that must have been what was wrong with the program, as it runs now, although it doesn't do much really - just outputs the inputter's name and date of birth and a few zeroes to the worksheet, which is nothing like as elaborate as the output he suggests would be generated by the program on page 47.
Leaving 'VBA for absolute beginners' to one side, for the moment, I wanted to ask if you know anything much about using Visual Basic for Webscraping ? I wanted to access the internet to download specific information, not just the xml of a homepage but items that will fall within specific tags, but which will vary on a day to day basis, originally I was going to try this in Python, but the actual software of Excel/ Visual Basic seems more suited to accessing and scraping the internet, if you know what you are doing. I have looked at quite a few 'You Tube' s on the subject, but so far I can't find one that uses code that will search for and download specific information.I don't know if you can help, but thank you for reading anyhow.

please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Birthday"
For me the macro works fine.

I don't use VBA for Webscraping. I work with Data => External Data =>
Data from Web.
But if you ask your question here in a new thread you will get answers.


Regards
Claus B.
 
Top