Saturday, December 9, 2017

INDIAN CURRENCY FORMAT FOR VBA EXCEL

Hello Mates, 

When you work with Userform in Excel VBA, usually sometimes you need to retrieve or reflect currency figure in the Userform (Like in Textbox, in Label or in Listbox) from the range(s) of the workbook. 

When you directly assign values to the objects, Userform generally takes it as a number and never reflect the proper format that you required in as Indian or else country's currency's format. 

So, I have struggled a lot to find the solution indeed. After a constantly looking for the solution of this, I came across to the following worksheet function. 

It's ".Text" formula

Yes, here's the Syntax:

userform1.textbox1.value = Application.WorksheetFunction.Text(VariableName.value, "[>=10000000]##\,##\,##\,##0.0#;[>=100000] ##\,##\,##0.0#;##,##0.0#")

Thank you. 

- Kamal Bharakhda

Wednesday, November 29, 2017

Select Column data dynamically

I want to share my best work for the people who have a passion for Microsoft Excel.

I especially focussed on the VBA MACRO based Applications in MS EXCEL for,

Automation of Reports
Applications like sale purchase invoice printing. Etc...

Today, I want to share my first coding in VBA to all.

Sometimes you need to select dynamically the whole data from the column to copy them automatically in anywhere. Maybe sometimes it happens when your worksheet went full of data or filled partially but you need to select data of any column automatically till it's the last cell then?

I have done the coding for that task. Here it is. Just paste it into your VBA module.

Code:

Sub Test_Module_1()
'By Kamal Bharakhda
Sheets("Assignment No. 1").Activate
Columns("B").Select
A = Selection.Rows.Count
LastCellofSheet = ActiveSheet.Cells(A, 2).Value
MsgBox "WorkSheet's Last Cell Value is" & " = " & LastCellofSheet
If LastCellofSheet = 0 Then
MsgBox "Worksheet's Last Row!" & " is = " & A
ActiveSheet.Cells(A, 2).End(xlUp).Select
B = ActiveCell.Row
C = ActiveCell.Column
MsgBox "Last Row of Column's Data" & " is = " & B
Range("B1", Cells(B, C)).Select
End If
End Sub



By Kamal Bharakhda

IsValidPasswordString Function

'Following function will verify if the password string contains following characters or not? Rem : List of Characters Group - ASCII Rem ...