Thanks a lot Mike.
The code is working fine when it is written in "sub two() ... end sub".
But, it is not working when it is written in "function two() .. end
function". Could you explain me the difference between Function and Sub in
excel VBA macro?
Thanks & Regards
Karthi
"Mike H" wrote:
> Hi,
>
> I changed it from a fuction to a sub to make it easier for me so change it
> back. Also msgbox(rc) doesn't work because it interprets rc as a new variable
> and it's never populated so I changed that:-
>
> Sub two()
> Dim wbk As Excel.Workbook
> Dim sh As Excel.Worksheet
> Dim r As Integer
> Dim c As Integer
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> Application.EnableEvents = False
> Set wbk = Workbooks.Open("c:\june.xls", True, True) 'Change back to your path
> Set sh = wbk.Worksheets(1)
> r = sh.UsedRange.Rows.Count
> c = sh.UsedRange.Columns.Count
> MsgBox ("Rows used " & r & " Columns used " & c)
> wbk.Close
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
> Application.EnableEvents = True
> End Sub
>
>
> Mike
>
> "Karthi" wrote:
>
> > Hi All,
> > My objective is to get used range of rows and columns of another workbook
> > ("trial_2.xls").
> > I will run the following macro in "trial_1.xls" workbook.
> > ----------------------------------------------
> > software configuration:
> > Microsoft office Excel 2003 (11.8142.8132) SP2
> > Microsoft Visual basic 6.3
> > ----------------------------------------------
> > Function two()
> > Dim wbk As Excel.Workbook
> > Dim sh As Excel.Worksheet
> > Dim r As Integer
> > Dim c As Integer
> > Application.ScreenUpdating = False
> > Application.DisplayAlerts = False
> > Application.EnableEvents = False
> > Set wbk =
> > Workbooks.Open("D:\Karthi_Works\trial\Excel_Trials\trial_2.xls", True, True)
> > sh = wbk.Worksheets(1)
> > r = sh.UsedRange.Rows.count
> > c = sh.UsedRange.Columns.count
> > MsgBox (rc)
> > wbk.Close
> > Application.ScreenUpdating = True
> > Application.DisplayAlerts = True
> > Application.EnableEvents = True
> > End Function
> >
> > When I run this macro in debug mode, the value of 'wbk' shows "Nothing".
> > Because of this, I could not display used range of rows and columns.
> > The line "Set wbk =
> > Workbooks.Open("D:\Karthi_Works\trial\Excel_Trials\trial_2.xls", True, True)"
> > has
> > some problem. I couldnot figure it out. please, help me in this regard
> >
> > Thanks
> > Karthi
|
Similar Threads
